Categories:
Warehouse & Resource Monitor DDL

SHOW WAREHOUSES

Lists all the warehouses in your account for which you have access privileges.

See also:
ALTER WAREHOUSE , CREATE 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 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 |
|------+-------+------+------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+------------+------------+------------+-------+---------+------------------+---------+----------+--------+-----------+------|
Column Description
name Name of the warehouse
state Whether the warehouse is active/running (STARTED) or inactive (SUSPENDED).
type Warehouse type; STANDARD is the only currently supported type.
size Size of the warehouse (X-Small, Small, Medium, Large, X-Large, etc.)
min_cluster_count Minimum number of clusters for the warehouse (always 1 for single-cluster warehouses).
max_cluster_count Maximum number of clusters for the 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 servers that are provisioned and available.
provisioning Percentage of the warehouse servers that are in the process of provisioning.
quiescing Percentage of the warehouse servers that are executing SQL statements, but will be shut down once the queries complete.
other Percentage of the warehouse servers 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) of the warehouse.
owner Role that owns the warehouse.
comment Comment for the warehouse.
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 whose name starts 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 |
+------------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+---------------------------------+---------------------------------+---------------------------------+---------------+----------------+------------------+---------+----------+--------+-----------+---------+