SHOW WAREHOUSES¶
Lists all the warehouses in your account for which you have access privileges.
- See also:
ALTER WAREHOUSE , CREATE WAREHOUSE , DESCRIBE WAREHOUSE , DROP WAREHOUSE
Syntax¶
SHOW WAREHOUSES [ LIKE '<pattern>' ]
Parameters¶
LIKE 'pattern'
Filters the command output by object name. The filter uses case-insensitive pattern matching, with support for SQL wildcard characters (
%
and_
).For example, the following patterns return the same results:
... LIKE '%testing%' ...
... LIKE '%TESTING%' ...
Usage Notes¶
The command does not require a running warehouse to execute.
The command returns a maximum of 10K records for the specified object type, as dictated by the access privileges for the role used to execute the command; any records above the 10K limit are not returned, even with a filter applied.
To view results for which more than 10K records exist, query the corresponding view (if one exists) in the Snowflake Information Schema.
To post-process the output of this command, you can use the RESULT_SCAN function, which treats the output as a table that can be queried.
Output¶
The columns in the output provide the following information:
+------+-------+------+------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+------------+------------+------------+-------+---------+------------------+---------+----------+--------+-----------+------+
| name | state | type | size | min_cluster_count | max_cluster_count | started_clusters | running | queued | is_default | is_current | auto_suspend | auto_resume | available | provisioning | quiescing | other | created_on | resumed_on | updated_on | owner | comment | resource_monitor | actives | pendings | failed | suspended | uuid |
|------+-------+------+------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+------------+------------+------------+-------+---------+------------------+---------+----------+--------+-----------+------|
For accounts that have the query acceleration service feature enabled, the columns in the output provide the following information:
+------+-------+------+------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+------------+------------+------------+-------+---------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+------+
| name | state | type | size | min_cluster_count | max_cluster_count | started_clusters | running | queued | is_default | is_current | auto_suspend | auto_resume | available | provisioning | quiescing | other | created_on | resumed_on | updated_on | owner | comment | enable_query_acceleration | query_acceleration_max_scale_factor | resource_monitor | actives | pendings | failed | suspended | uuid |
|------+-------+------+------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+------------+------------+------------+-------+---------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+------|
Column |
Description |
---|---|
|
Name of the warehouse |
|
Whether the warehouse is active/running ( |
|
Warehouse type; STANDARD and SNOWPARK-OPTIMIZED are the only currently supported types. |
|
Size of the warehouse (X-Small, Small, Medium, Large, X-Large, etc.) |
|
Minimum number of clusters for the (multi-cluster) warehouse (always 1 for single-cluster warehouses). |
|
Maximum number of clusters for the (multi-cluster) warehouse (always 1 for single-cluster warehouses). |
|
Number of clusters currently started. |
|
Number of SQL statements that are being executed by the warehouse. |
|
Number of SQL statements that are queued for the warehouse. |
|
Whether the warehouse is the default for the current user. |
|
Whether the warehouse is in use for the session. Only one warehouse can be in use at a time for a session. To specify or change the warehouse for a session, use the USE WAREHOUSE command. |
|
Period of inactivity, in seconds, after which a running warehouse will automatically suspend and stop using credits; a |
|
Whether the warehouse, if suspended, automatically resumes when a query is submitted to the warehouse. |
|
Percentage of the warehouse compute resources that are provisioned and available. |
|
Percentage of the warehouse compute resources that are in the process of provisioning. |
|
Percentage of the warehouse compute resources that are executing SQL statements, but will be shut down once the queries complete. |
|
Percentage of the warehouse compute resources that are in a state other than |
|
Date and time when the warehouse was created. |
|
Date and time when the warehouse was last started or restarted. |
|
Date and time when the warehouse was last updated, which includes changing any of the properties of the warehouse or changing the state ( |
|
Role that owns the warehouse. |
|
Comment for the warehouse. |
|
Whether the query acceleration service is enabled for the warehouse. |
|
Maximum scale factor for the query acceleration service. |
|
ID of resource monitor explicitly assigned to the warehouse; controls the monthly credit usage for the warehouse. |
|
These five columns are for internal use and will be removed in a future release. |
|
Policy that determines when additional clusters (in a multi-cluster warehouse) are automatically started and shut down. |
For more information about the properties that can be specified for a warehouse, see CREATE WAREHOUSE.
Examples¶
Show warehouses with names that start with test
that you have privileges to view:
SHOW WAREHOUSES LIKE 'test%'; +------------------+-----------+--------------------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+---------------------------------+---------------------------------+---------------------------------+---------------+----------------+------------------+---------+----------+--------+-----------+---------+ | name | state | type | size | min_cluster_count | max_cluster_count | started_clusters | running | queued | is_default | is_current | auto_suspend | auto_resume | available | provisioning | quiescing | other | created_on | resumed_on | updated_on | owner | comment | resource_monitor | actives | pendings | failed | suspended | uuid | |------------------+-----------+--------------------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+---------------------------------+---------------------------------+---------------------------------+---------------+----------------+------------------+---------+----------+--------+-----------+---------| | TEST1 | SUSPENDED | STANDARD | X-Small | 1 | 1 | 0 | 0 | 0 | N | N | 900 | true | | | | | Mon, 23 May 2016 09:59:07 -0700 | Mon, 23 May 2016 09:59:07 -0700 | Mon, 23 May 2016 10:14:14 -0700 | SECURITYADMIN | | null | 0 | 0 | 0 | 1 | 3410417 | |------------------+-----------+--------------------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+---------------------------------+---------------------------------+---------------------------------+---------------+----------------+------------------+---------+----------+--------+-----------+---------| | TEST2 | SUSPENDED | SNOWPARK-OPTIMIZED | X2LARGE | 1 | 1 | 0 | 0 | 0 | N | N | 900 | true | | | | | Tue, 14 Jun 2016 11:27:34 -0800 | Tue, 14 Jun 2016 11:44:23 -0700 | Tue, 14 Jun 2016 12:05:16 -0700 | SECURITYADMIN | | null | 0 | 0 | 0 | 1 | 3324568 | +------------------+-----------+--------------------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+---------------------------------+---------------------------------+---------------------------------+---------------+----------------+------------------+---------+----------+--------+-----------+---------+