SHOW WAREHOUSES

Lists all the virtual 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>' ]
  [ WITH PRIVILEGES <objectPrivilege> [ , <objectPrivilege> [ , ... ] ] ]
Copy

Parameters

LIKE 'pattern'

Optionally 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%' ...

. Default: No value (no filtering is applied to the output).

WITH PRIVILEGES object_privilege [ , object_privilege [ , ... ] ]

Optionally limits rows to objects for which the active role for the current user has been granted all of the specified privileges in the list on the object.

If a CREATE <object> privilege is included in the privileges list, the command excludes objects for which secondary roles have been granted privileges. This is because only the primary role has the authorization to create objects. For more information, see Enforcement model with primary role and secondary roles.

Output

The columns in the output provide the following information. For accounts that have the query acceleration service feature enabled, the output provides additional information.

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 value of null 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.

budget

Name of the budget if the object is monitored by a budget. NULL otherwise.

owner_role_type

The type of role that owns the object, for example ROLE. . If a Snowflake Native App owns the object, the value is APPLICATION. . Snowflake returns NULL if you delete the object because a deleted object does not have an owner role.

resource_constraint

If type is SNOWPARK-OPTIMIZED, one of:

  • MEMORY_1X, MEMORY_1X_x86, MEMORY_16X, MEMORY_16X_x86, MEMORY_64X, MEMORY_64X_x86.

Otherwise NULL.

For more information about the properties that can be specified for a warehouse, see CREATE WAREHOUSE.

Usage notes

  • The command doesn’t require a running warehouse to execute.

  • The command only returns objects for which the current user’s current role has been granted at least one access privilege.

  • The MANAGE GRANTS access privilege implicitly allows its holder to see every object in the account. By default, only the account administrator (users with the ACCOUNTADMIN role) and security administrator (users with the SECURITYADMIN role) have the MANAGE GRANTS privilege.

  • 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.

  • The command returns a maximum of ten thousand records for the specified object type, as dictated by the access privileges for the role used to execute the command. Any records above the ten thousand records limit aren’t returned, even with a filter applied.

    To view results for which more than ten thousand records exist, query the corresponding view (if one exists) in the Snowflake Information Schema.

Examples

Show warehouses with names that start with test that you have privileges to view:

SHOW WAREHOUSES LIKE 'test%';
Copy
+---------------+-----------+--------------------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+--------------+---------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+----------+----------------+----------+-----------------+---------------------+
| 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     | scaling_policy | budget   | owner_role_type | resource_constraint |
|---------------+-----------+--------------------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+--------------+---------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+----------+----------------+----------|-----------------|---------------------+
| TEST1         | SUSPENDED | STANDARD           | Medium  |                 1 |                 1 |                0 |       0 |      0 | N          | N          |          600 | true        |           |              |           |       | 2023-01-27 14:57:07.768 -0800 | 2023-05-10 16:17:49.258 -0700 | 2023-05-10 16:17:49.258 -0700 | MY_ROLE      |         | true                      |                                   8 | null             |       0 |        0 |      0 |         4 | 76064    | STANDARD       | NULL     | ROLE            | NULL                +
| TEST2         | SUSPENDED | STANDARD           | X-Small |                 1 |                 1 |                0 |       0 |      0 | N          | N          |          600 | true        |           |              |           |       | 2023-01-27 14:57:07.953 -0800 | 1969-12-31 16:00:00.000 -0800 | 2023-01-27 14:57:08.356 -0800 | MY_ROLE      |         | true                      |                                  16 | MYTEST_RM        |       0 |        0 |      0 |         1 | 76116    | STANDARD       | MYBUDGET | ROLE            | NULL                +
| TEST3         | SUSPENDED | STANDARD           | Small   |                 1 |                 1 |                0 |       0 |      0 | N          | N          |          600 | true        |           |              |           |       | 2023-08-08 10:26:45.534 -0700 | 2023-08-08 10:26:45.681 -0700 | 2023-08-08 10:26:45.681 -0700 | MY_ROLE      |         | false                     |                                   8 | null             |       0 |        0 |      0 |         2 | 19464517 | STANDARD       | NULL     | ROLE            | NULL                +
| TEST4         | RESUMING  | SNOWPARK-OPTIMIZED | Large   |                 1 |                 1 |                0 |       0 |      0 | N          | Y          |          600 | true        |           |              |           |       | 2023-09-21 17:29:58.165 -0700 | 2023-09-21 17:29:58.165 -0700 | 2023-09-21 17:29:58.207 -0700 | MY_ROLE      |         | false                     |                                   8 | null             |       0 |        0 |      0 |         0 | 19464585 | STANDARD       | NULL     | ROLE            | MEMORY_16X_X86      +
+---------------+-----------+--------------------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+--------------+---------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+----------+----------------+----------+-----------------+---------------------+

Show warehouses that you have been granted the MODIFY and OPERATE privileges on:

SHOW WAREHOUSES WITH PRIVILEGES MODIFY, OPERATE;
Copy
+------------------------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+--------------+-------------------------------------------------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+----------+----------------+--------------------------+-----------------+---------------------+
| 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     | scaling_policy | budget                   | owner_role_type |
|------------------------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+--------------+-------------------------------------------------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+----------+----------------+--------------------------+-----------------+---------------------+
| TEST_WH                      | SUSPENDED | STANDARD | X-Small |                 1 |                 1 |                0 |       0 |      0 | Y          | Y          |          600 | true        |           |              |           |       | 2023-01-27 14:57:07.768 -0800 | 2024-07-30 13:39:24.118 -0700 | 2024-07-30 13:39:24.118 -0700 | TEST_ROLE    |                                                 | true                      |                                  32 | TEST_RM          |       0 |        0 |      0 |         1 | 76056    | STANDARD       | KATS_RENAMED_TEST_BUDGET | ROLE            | NULL                +
| SNOWPARK_DEMO                | SUSPENDED | STANDARD | X-Large |                 1 |                 1 |                0 |       0 |      0 | N          | N          |          600 | true        |           |              |           |       | 2023-01-27 14:57:07.903 -0800 | 2023-04-10 11:47:03.146 -0700 | 2023-04-10 11:47:03.146 -0700 | ACCOUNTADMIN | Created by straut for Snowpark quickstart       | false                     |                                   8 | null             |       0 |        0 |      0 |        16 | 76104    | STANDARD       | NULL                     | ROLE            | NULL                +
| TASTY_DEV_WH                 | SUSPENDED | STANDARD | X-Small |                 1 |                 1 |                0 |       0 |      0 | N          | N          |           60 | true        |           |              |           |       | 2023-10-25 16:25:43.681 -0700 | 2023-10-25 16:25:43.681 -0700 | 2023-10-25 16:25:43.711 -0700 | SYSADMIN     | developer warehouse for tasty bytes             | false                     |                                   8 | null             |       0 |        0 |      0 |         1 | 19464633 | STANDARD       | NULL                     | ROLE            | NULL                +
| TB_DOCS_WH                   | SUSPENDED | STANDARD | X-Small |                 1 |                 1 |                0 |       0 |      0 | N          | N          |           60 | true        |           |              |           |       | 2024-07-24 15:02:32.172 -0700 | 2024-07-24 15:33:30.502 -0700 | 2024-07-24 15:33:30.502 -0700 | SYSADMIN     | developer warehouse for tasty bytes             | false                     |                                   8 | null             |       0 |        0 |      0 |         1 | 19465097 | STANDARD       | NULL                     | ROLE            | NULL                +
+------------------------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+--------------+-------------------------------------------------+---------------------------+-------------------------------------+------------------+---------+----------+--------+-----------+----------+----------------+--------------------------+-----------------+---------------------+

Show certain details about warehouses by filtering and reordering data from the full SHOW WAREHOUSES output. This stored procedure runs a SHOW WAREHOUSES command, then calls the RESULT_SCAN function to filter and transform the result set from the most recent SQL command. You can use this technique to generate different types of reports if you don’t need the entire output of a SHOW command.

CREATE OR REPLACE PROCEDURE started_and_suspended_warehouses()
  RETURNS TABLE(name VARCHAR, status VARCHAR, type VARCHAR, size VARCHAR)
  LANGUAGE SQL
  AS
  $$
    DECLARE
      res RESULTSET;
    BEGIN
      SHOW WAREHOUSES;
      res := (SELECT "name" name, "state" state, "type" type, "size" size
        FROM TABLE(RESULT_SCAN(LAST_QUERY_ID(-1)))
        WHERE "state" IN ('STARTED','SUSPENDED')
        ORDER BY "state", "name");
      RETURN TABLE(res);
    END;
  $$
  ;

CALL started_and_suspended_warehouses();
Copy
+------------------------------+-----------+--------------------+---------+
| NAME                         | STATUS    | TYPE               | SIZE    |
|------------------------------+-----------+--------------------+---------|
| COMPUTE_WH                   | STARTED   | STANDARD           | X-Small |
| DEFAULT_SIZE                 | SUSPENDED | STANDARD           | Small   |
| DEFAULT_SIZE_2               | SUSPENDED | STANDARD           | X-Small |
| MEDIUM                       | SUSPENDED | SNOWPARK-OPTIMIZED | Medium  |
| PRIV_WH                      | SUSPENDED | STANDARD           | X-Small |
| SYSTEM$STREAMLIT_NOTEBOOK_WH | SUSPENDED | STANDARD           | X-Small |
| XSMALL                       | SUSPENDED | STANDARD           | Medium  |
+------------------------------+-----------+--------------------+---------+
Language: English