- Categories:
CREATE WAREHOUSE¶
Creates a new virtual warehouse in the system.
Initial creation of a virtual warehouse might take some time to provision the servers, unless the warehouse is created initially in a SUSPENDED
state.
- See also:
Syntax¶
CREATE [ OR REPLACE ] WAREHOUSE [ IF NOT EXISTS ] <name>
[ [ WITH ] objectProperties ]
[ objectParams ]
Where:
objectProperties ::= WAREHOUSE_SIZE = XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE MAX_CLUSTER_COUNT = <num> MIN_CLUSTER_COUNT = <num> SCALING_POLICY = STANDARD | ECONOMY AUTO_SUSPEND = <num> | NULL AUTO_RESUME = TRUE | FALSE INITIALLY_SUSPENDED = TRUE | FALSE RESOURCE_MONITOR = <monitor_name> COMMENT = '<string_literal>'objectParams ::= MAX_CONCURRENCY_LEVEL = <num> STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <num> STATEMENT_TIMEOUT_IN_SECONDS = <num>
Required Parameters¶
name
Identifier for the virtual warehouse; must be unique for your account.
In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g.
"My object"
). Identifiers enclosed in double quotes are also case-sensitive.For more details, see Identifier Requirements.
Optional Properties (objectProperties
)¶
WAREHOUSE_SIZE = XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE
Specifies the size of the virtual warehouse. The size determines the number of servers in each cluster in the warehouse and, therefore, the number of credits consumed while the warehouse is running.
- Valid values
Supported Values
Synonyms
XSMALL
'X-SMALL'
SMALL
MEDIUM
LARGE
XLARGE
'X-LARGE'
XXLARGE
X2LARGE
,'2X-LARGE'
XXXLARGE
X3LARGE
,'3X-LARGE'
X4LARGE
'4X-LARGE'
- Default
XSMALL
Note
To use a value that contains a hyphen (e.g.
'2X-LARGE'
), you must enclose the value in single quotes, as shown.MAX_CLUSTER_COUNT = num
Specifies the maximum number of server clusters for the warehouse.
- Valid values
1
to10
Note that specifying a value greater than
1
indicates the warehouse is a multi-cluster warehouse; however, the value can be only set to a higher value in Snowflake Enterprise Edition (or higher).For more information, see Multi-cluster Warehouses.
- Default
1
(single-cluster warehouse)
Tip
For Snowflake Enterprise Edition (or higher), we recommend always setting the value greater than
1
to help maintain high-availability and optimal performance of the warehouse. This also helps ensure continuity in the unlikely event that a cluster fails.MIN_CLUSTER_COUNT = num
Specifies the minimum number of server clusters for the warehouse (only applies to multi-cluster warehouses).
- Valid values
1
to10
However, note that
MIN_CLUSTER_COUNT
must be equal to or less thanMAX_CLUSTER_COUNT
:If both parameters are equal, the warehouse runs in Maximized mode.
If
MIN_CLUSTER_COUNT
is less thanMAX_CLUSTER_COUNT
, the warehouse runs in Auto-scale mode.
For more information, see Multi-cluster Warehouses.
- Default
1
SCALING_POLICY = STANDARD | ECONOMY
Specifies the policy for automatically starting and shutting down clusters in a multi-cluster warehouse running in Auto-scale mode.
- Valid values
STANDARD
: Minimizes queuing by starting clusters.ECONOMY
: Conserves credits by favoring keeping running clusters fully-loaded.
For a more detailed description, see Setting the Scaling Policy for a Multi-cluster Warehouse.
- Default
STANDARD
AUTO_SUSPEND = num | NULL
Specifies the number of seconds of inactivity after which a warehouse is automatically suspended.
- Valid values
Any integer greater than
0
orNULL
:Setting a value less than
60
is allowed, but many not result in the desired/expected behavior because the background process that suspends a warehouse runs approximately every 60 seconds and, therefore, is not intended for enabling exact control over warehouse suspension.Setting a
NULL
value means the warehouse never suspends.
- Default
600
(the warehouse suspends automatically after 10 minutes of inactivity)
Important
Setting
AUTO_SUSPEND
toNULL
is not recommended, unless your query workloads require a continually running warehouse. Note that this can result in significant consumption of credits (and corresponding charges), particularly for larger warehouses.AUTO_RESUME = TRUE | FALSE
Specifies whether to automatically resume a warehouse when a SQL statement (e.g. query) is submitted to it.
- Valid values
TRUE
: The warehouse resumes when a new query is submitted.FALSE
: The warehouse only resumes when explicitly resumed using ALTER WAREHOUSE or through the Snowflake web interface.
- Default
TRUE
(the warehouse resumes automatically when a SQL statement is submitted to it)
INITIALLY_SUSPENDED = TRUE | FALSE
Specifies whether the warehouse is created initially in the ‘Suspended’ state.
- Valid values
TRUE
: The warehouse is created, but suspended.FALSE
: The warehouse starts running after it is created.
- Default
FALSE
RESOURCE_MONITOR = string
Specifies the name of a resource monitor that is explicitly assigned to the warehouse. When a resource monitor is explicitly assigned to a warehouse, the monitor controls the monthly credits used by the warehouse (and all other warehouses to which the monitor is assigned).
- Valid values
Any existing resource monitor.
For more details, see Working with Resource Monitors.
- Default
No value (no resource monitor assigned to the warehouse)
Tip
To view all resource monitors and their identifiers, use the SHOW RESOURCE MONITORS command.
COMMENT = 'string_literal'
Specifies a comment for the warehouse.
Optional Parameters (objectParams
)¶
MAX_CONCURRENCY_LEVEL = num
Object parameter that specifies the concurrency level for SQL statements (i.e. queries and DML) executed by a warehouse cluster.
For a detailed description of this parameter, see MAX_CONCURRENCY_LEVEL.
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = num
Object parameter that specifies the time, in seconds, a SQL statement (query, DDL, DML, etc.) can be queued on a warehouse before it is canceled by the system.
For a detailed description of this parameter, see STATEMENT_QUEUED_TIMEOUT_IN_SECONDS.
STATEMENT_TIMEOUT_IN_SECONDS = num
Object parameter that specifies the time, in seconds, after which a running SQL statement (query, DDL, DML, etc.) is canceled by the system.
For a detailed description of this parameter, see STATEMENT_TIMEOUT_IN_SECONDS.
Usage Notes¶
Creating a virtual warehouse automatically sets it as the active/current warehouse for the current session (equivalent to using the USE WAREHOUSE command for the warehouse).
Examples¶
Create an X-Large warehouse:
CREATE OR REPLACE WAREHOUSE my_wh WITH WAREHOUSE_SIZE='X-LARGE';
Create a Large warehouse in a suspended state:
CREATE OR REPLACE WAREHOUSE my_wh WAREHOUSE_SIZE=LARGE INITIALLY_SUSPENDED=TRUE;