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>' ]

Copy

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 |
|------+-------+------+------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+------------+------------+------------+-------+---------+------------------+---------+----------+--------+-----------+------|

Copy

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 |
|------+-------+------+------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+------------+------------+------------+-------+---------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+------|

Copy

Column	Description
`name`	Name of the warehouse
`state`	Whether the warehouse is active/running (`STARTED`), inactive (`SUSPENDED`), or resizing (`RESIZING`).
`type`	Warehouse type; STANDARD and SNOWPARK-OPTIMIZED are the only currently supported types.
`size`	Size of the warehouse (X-Small, Small, Medium, Large, X-Large, etc.)
`min_cluster_count`	Minimum number of clusters for the (multi-cluster) warehouse (always 1 for single-cluster warehouses).
`max_cluster_count`	Maximum number of clusters for the (multi-cluster) warehouse (always 1 for single-cluster warehouses).
`started_clusters`	Number of clusters currently started.
`running`	Number of SQL statements that are being executed by the warehouse.
`queued`	Number of SQL statements that are queued for the warehouse.
`is_default`	Whether the warehouse is the default for the current user.
`is_current`	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.
`auto_suspend`	Period of inactivity, in seconds, after which a running warehouse will automatically suspend and stop using credits; a `null` value indicates the warehouse never automatically suspends.
`auto_resume`	Whether the warehouse, if suspended, automatically resumes when a query is submitted to the warehouse.
`available`	Percentage of the warehouse compute resources that are provisioned and available.
`provisioning`	Percentage of the warehouse compute resources that are in the process of provisioning.
`quiescing`	Percentage of the warehouse compute resources that are executing SQL statements, but will be shut down once the queries complete.
`other`	Percentage of the warehouse compute resources that are in a state other than `available`, `provisioning`, or `quiescing`.
`created_on`	Date and time when the warehouse was created.
`resumed_on`	Date and time when the warehouse was last started or restarted.
`updated_on`	Date and time when the warehouse was last updated, which includes changing any of the properties of the warehouse or changing the state (`STARTED`, `SUSPENDED`, `RESIZING`) of the warehouse.
`owner`	Role that owns the warehouse.
`comment`	Comment for the warehouse.
`enable_query_acceleration`	Whether the query acceleration service is enabled for the warehouse.
`query_acceleration_max_scale_factor`	Maximum scale factor for the query acceleration service.
`resource_monitor`	ID of resource monitor explicitly assigned to the warehouse; controls the monthly credit usage for the warehouse.
`actives` , `pendings` , `failed` , `suspended` , `uuid`	These five columns are for internal use and will be removed in a future release.
`scaling_policy`	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 |
+------------------+-----------+--------------------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+---------------------------------+---------------------------------+---------------------------------+---------------+----------------+------------------+---------+----------+--------+-----------+---------+

Copy