CREATE INTERACTIVE WAREHOUSE¶

Syntax¶

CREATE [ OR REPLACE ] INTERACTIVE WAREHOUSE [ IF NOT EXISTS ] <name>
       [ TABLES ( <table_name> [ , <table_name> ... ] ) ]
       [ [ WITH ] objectProperties ]
       [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
       [ objectParams ]

Where:

objectProperties ::=
  WAREHOUSE_SIZE = { XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE | X5LARGE | X6LARGE }
  MAX_CLUSTER_COUNT = <num>
  MIN_CLUSTER_COUNT = <num>
  AUTO_RESUME = { TRUE | FALSE }
  INITIALLY_SUSPENDED = { TRUE | FALSE }
  RESOURCE_MONITOR = <monitor_name>
  COMMENT = '<string_literal>'

Copy

objectParams ::=
  MAX_CONCURRENCY_LEVEL = <num>
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <num>
  STATEMENT_TIMEOUT_IN_SECONDS = <num>

Copy

Parameters¶

name

Specifies the identifier for the interactive warehouse. The identifier must be unique within your account.

For more details, see Identifier requirements.

TABLES ( ... )

Optionally specifies a comma-separated list of interactive table names to immediately associate with the interactive warehouse. Using this clause starts the cache-warming process for the specified tables when the warehouse is created.

table_name

Specifies the identifier for an interactive table to associate with the warehouse. You can specify multiple table names separated by commas.

Note

• All specified tables must be interactive tables created with the INTERACTIVE keyword.

• If this clause is omitted, you can associate interactive tables later using ALTER WAREHOUSE with the ADD TABLES clause.

• Cache warming may take significant time depending on the size of the data.

WAREHOUSE_SIZE = string_constant

Specifies the size of the interactive warehouse. Interactive warehouses support specific sizes optimized for interactive workloads.

Valid values:

• XSMALL , 'X-SMALL'

• SMALL

• MEDIUM

• LARGE

• XLARGE , 'X-LARGE'

• XXLARGE , X2LARGE , '2X-LARGE'

• XXXLARGE , X3LARGE , '3X-LARGE'

Default:

XSMALL

Note

• To use a value that contains a hyphen (for example, '2X-LARGE'), you must enclose the value in single quotes, as shown.

• Choose a warehouse size to match your workload requirements. You can adjust the MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT properties to optimize for concurrency.

MAX_CLUSTER_COUNT = num

Specifies the maximum number of clusters for a multi-cluster interactive warehouse.

Valid values:

1 to 10 (depending on warehouse size)

Default:

1 (single-cluster warehouse)

For an interactive warehouse, use the same value for MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT. A multi-cluster interactive warehouse doesn’t automatically scale up or down.

MIN_CLUSTER_COUNT = num

Specifies the minimum number of clusters for a multi-cluster interactive warehouse.

Valid values:

1 to the value of MAX_CLUSTER_COUNT

Default:

1

For an interactive warehouse, use the same value for MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT. A multi-cluster interactive warehouse doesn’t automatically scale up or down.

AUTO_RESUME = { TRUE | FALSE }

Specifies whether to automatically resume the interactive warehouse when a query is submitted.

Valid values:

• TRUE: The warehouse resumes automatically when a SQL statement is submitted

• FALSE: The warehouse only resumes when explicitly resumed using ALTER WAREHOUSE

Default:

TRUE

INITIALLY_SUSPENDED = { TRUE | FALSE }

Specifies whether the interactive warehouse is created in a suspended state.

Valid values:

• TRUE: The warehouse is created in a suspended state and must be resumed before use

• FALSE: The warehouse is created in a running state (if AUTO_RESUME is TRUE)

Default:

TRUE

RESOURCE_MONITOR = monitor_name

Specifies the identifier of a resource monitor to assign to the interactive warehouse for credit usage control.

Valid values:

Any existing resource monitor

Default:

No value (no resource monitor assigned)

COMMENT = 'string_literal'

Specifies a comment for the interactive warehouse.

TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas.

MAX_CONCURRENCY_LEVEL = num

Specifies the concurrency level for SQL statements executed by the interactive warehouse cluster.

STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = num

Specifies the time, in seconds, a SQL statement can be queued before being canceled.

STATEMENT_TIMEOUT_IN_SECONDS = num

Specifies the time, in seconds, after which a running SQL statement is canceled. Interactive warehouses have a maximum timeout interval of five seconds. Any larger values are ignored.

Access control requirements¶

A role used to execute this SQL command must have at least one of the following privileges at a minimum:

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

• Interactive warehouses are created in a SUSPENDED state by default. Use ALTER WAREHOUSE with the RESUME clause to start the warehouse.

• When you specify the TABLES clause, cache warming begins immediately for the specified interactive tables. This process may take significant time depending on data size.

• Interactive warehouses can only query interactive tables. To query standard tables, use a standard warehouse created with CREATE WAREHOUSE.

• Interactive warehouses support multi-cluster configuration for handling high-concurrency workloads.

• If you don’t specify the TABLES clause during creation, you can associate interactive tables later using ALTER WAREHOUSE with the ADD TABLES clause.

Examples¶

Create an interactive warehouse associated with specific interactive tables:

CREATE OR REPLACE INTERACTIVE WAREHOUSE sales_interactive_wh
  TABLES (orders, customers, products)
  WAREHOUSE_SIZE = 'MEDIUM'
  COMMENT = 'Interactive warehouse for sales team analytics';

Create an interactive warehouse without associated tables (to be added later):

CREATE INTERACTIVE WAREHOUSE analytics_interactive_wh
  WAREHOUSE_SIZE = 'LARGE'
  MAX_CLUSTER_COUNT = 3
  MIN_CLUSTER_COUNT = 3;

Create an interactive warehouse with resource monitoring:

CREATE INTERACTIVE WAREHOUSE dev_interactive_wh
  WAREHOUSE_SIZE = 'XSMALL'
  RESOURCE_MONITOR = dev_resource_monitor
  COMMENT = 'Development interactive warehouse';

Resume an interactive warehouse and associate tables with it:

-- Resume the warehouse
ALTER WAREHOUSE sales_interactive_wh RESUME;

-- Add additional tables if needed
ALTER WAREHOUSE sales_interactive_wh ADD TABLES (inventory);