CREATE WAREHOUSE¶
Creates a new virtual warehouse in the system.
Initial creation of a virtual warehouse might take some time to provision the compute resources, unless the warehouse is created initially
in a SUSPENDED
state.
- See also:
ALTER WAREHOUSE , DESCRIBE WAREHOUSE , DROP WAREHOUSE , SHOW WAREHOUSES
Syntax¶
CREATE [ OR REPLACE ] WAREHOUSE [ IF NOT EXISTS ] <name>
[ [ WITH ] objectProperties ]
[ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
[ objectParams ]
Where:
objectProperties ::= WAREHOUSE_TYPE = { STANDARD | 'SNOWPARK-OPTIMIZED' } WAREHOUSE_SIZE = { XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE | X5LARGE | X6LARGE } RESOURCE_CONSTRAINT = { MEMORY_1X | MEMORY_1X_x86 | MEMORY_16X | MEMORY_16X_x86 | MEMORY_64X | MEMORY_64X_x86 } 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>' ENABLE_QUERY_ACCELERATION = { TRUE | FALSE } QUERY_ACCELERATION_MAX_SCALE_FACTOR = <num>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 (for example,
"My object"
). Identifiers enclosed in double quotes are also case-sensitive.For more details, see Identifier requirements.
Optional properties (objectProperties
)¶
WAREHOUSE_TYPE = { STANDARD | 'SNOWPARK-OPTIMIZED' }
Specifies the warehouse type.
- Valid values:
STANDARD
,'STANDARD'
'SNOWPARK-OPTIMIZED'
- Default:
STANDARD
Note
To use a value that contains a hyphen (
'SNOWPARK-OPTIMIZED'
), you must enclose the value in single quotes, as shown.WAREHOUSE_SIZE = { XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE | X5LARGE | X6LARGE }
Specifies the size of the virtual warehouse. The size determines the amount of compute resources 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'
X5LARGE
'5X-LARGE'
X6LARGE
'6X-LARGE'
- Default:
XSMALL
Note
X5LARGE, and X6LARGE sizes do not support Snowpark-optimized warehouses. The default size for Snowpark-optimized warehouses is MEDIUM.
To use a value that contains a hyphen (for example,
'2X-LARGE'
), you must enclose the value in single quotes, as shown.Larger warehouse sizes 5X-Large and 6X-Large are generally available in all Amazon Web Services (AWS) and Microsoft Azure regions.
Larger warehouse sizes are in preview in US Government regions (requires FIPS support on ARM).
RESOURCE_CONSTRAINT = { MEMORY_1X| MEMORY_1X_x86 | MEMORY_16X | MEMORY_16X_x86 | MEMORY_64X | MEMORY_64X_x86 }
Specifies the memory and CPU architecture for Snowpark-optimized warehouses. The following table includes the valid values for the property, available memory, CPU architecture, and the minimum warehouse size required for the
resource_constraint
setting:- Valid values:
Value
Memory (up to)
CPU architecture
Min warehouse size required
Max warehouse size
MEMORY_1X
16 GB
Standard
XSMALL
X4LARGE
MEMORY_1X_x86
16 GB
x86
XSMALL
X4LARGE
MEMORY_16X
256 GB
Standard
MEDIUM
X6LARGE
MEMORY_16X_x86
256 GB
x86
MEDIUM
X4LARGE
MEMORY_64X
1 TB [1]
Standard
LARGE
X4LARGE
MEMORY_64X_x86
1 TB [1]
x86
LARGE
X4LARGE
- Default value:
MEMORY_16X
This property can only be set if the
WAREHOUSE_TYPE
is'SNOWPARK-OPTIMIZED'
.MAX_CLUSTER_COUNT = num
Specifies the maximum number of clusters for a multi-cluster warehouse. For a single-cluster warehouse, this value is always
1
.- Valid values:
1
to10
Note that specifying a value greater than
1
indicates the warehouse is a multi-cluster warehouse; however, the value can only be 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 a multi-cluster warehouse. This also helps ensure continuity in the unlikely event that a cluster fails.MIN_CLUSTER_COUNT = num
Specifies the minimum number of clusters for a multi-cluster 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
0
or greater, orNULL
:The background process that suspends a warehouse runs approximately every 30 seconds and therefore, the setting for this property is not intended for enabling precise control over warehouse suspension.
Setting a value less than 30, or a value that is not a multiple of 30, is allowed but might not result in the expected behavior due to the 30 second poll interval for warehouse suspension.
Setting a
0
orNULL
value means the warehouse never suspends.
- Default:
600
(the warehouse suspends automatically after 10 minutes of inactivity)
Important
Setting
AUTO_SUSPEND
to0
orNULL
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 (for example, 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 = monitor_name
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.
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 for objects and columns.
Query acceleration properties¶
This feature requires Enterprise Edition (or higher). To inquire about upgrading, please contact Snowflake Support.
ENABLE_QUERY_ACCELERATION = { TRUE | FALSE }
Specifies whether to enable the query acceleration service for queries that rely on this warehouse for compute resources.
- Valid values:
TRUE
Enables Query AccelerationFALSE
Disables Query Acceleration
- Default:
FALSE
: Query Acceleration is disabled
QUERY_ACCELERATION_MAX_SCALE_FACTOR = num
Specifies the maximum scale factor for leasing compute resources for query acceleration. The scale factor is used as a multiplier based on warehouse size.
Setting the QUERY_ACCELERATION_MAX_SCALE_FACTOR to 0 eliminates the limit and allows queries to lease as many resources as necessary and as available to service the query.
Regardless of the QUERY_ACCELERATION_MAX_SCALE_FACTOR value, the amount of available compute resources for query acceleration is bound by the available resources in the service and the number of other concurrent requests. For more details, refer to Adjusting the scale factor.
- Valid values:
0
to100
- Default:
8
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.
Access control requirements¶
A role used to execute this SQL command must have the following privileges at a minimum:
Privilege |
Object |
Notes |
---|---|---|
CREATE WAREHOUSE |
Account |
Only the SYSADMIN role, or a higher role, has this privilege by default. The privilege can be granted to additional roles as needed. |
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¶
Creating a virtual warehouse automatically sets it as the warehouse in use for the current session (equivalent to using the USE WAREHOUSE command for the warehouse).
To change the warehouse in use for the current session, execute an explicit USE WAREHOUSE statement after the CREATE WAREHOUSE statement. For example, create warehouse
my_wh
but continue to use the current warehouse, notmy_wh
, to execute additional statements:SET current_wh_name = (SELECT CURRENT_WAREHOUSE()); CREATE OR REPLACE WAREHOUSE my_wh WAREHOUSE_SIZE = 'XSMALL'; USE WAREHOUSE IDENTIFIER($current_wh_name);
Regarding metadata:
Attention
Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.
Using
OR REPLACE
is the equivalent of using DROP WAREHOUSE on the existing warehouse and then creating a new warehouse with the same name.CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.
Any queries running on the dropped warehouse are aborted.
Initial creation and resumption of a Snowpark-optimized virtual warehouse may take longer than standard warehouses.
Snowpark-optimized warehouses are not supported on
XSMALL
,SMALL
,X5LARGE
, orX6LARGE
warehouse sizes.
Billing and Pricing¶
The following table shows the Snowflake Credits charged per hours for Snowpark Optimized Warehouses.
Credits per hour |
||||||||||
---|---|---|---|---|---|---|---|---|---|---|
Resource Constraint |
XS |
S |
M |
L |
XL |
2XL |
3XL |
4XL |
5XL |
6XL |
MEMORY_1X |
1.00 |
2.00 |
4.00 |
8.00 |
16.00 |
32.00 |
64.00 |
128.00 |
||
MEMORY_1X_X86 |
1.10 |
2.20 |
4.40 |
8.80 |
17.60 |
35.20 |
70.40 |
140.80 |
||
MEMORY_16X |
6.00 |
12.00 |
24.00 |
48.00 |
96.00 |
192.00 |
384.00 |
768.00 |
||
MEMORY_16X_X86 |
6.25 |
12.50 |
25.00 |
50.00 |
100.00 |
200.00 |
||||
MEMORY_64X |
15.00 |
30.00 |
60.00 |
120.00 |
240.00 |
|||||
MEMORY_64X_x86 |
16.00 |
32.00 |
64.00 |
128.00 |
256.00 |
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;
Create an X-Large Snowpark-optimized warehouse named so_warehouse
with 256 GB memory for Snowpark workloads that require x86 Python:
CREATE WAREHOUSE so_warehouse WITH
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
WAREHOUSE_SIZE = xlarge
RESOURCE_CONSTRAINT = 'MEMORY_16X_x86';