Syntax Examples for Warehouse

1. CREATE WAREHOUSE

Example 1. Create an Extra Small (XS) warehouse

USE ROLE SYSADMIN;

CREATE WAREHOUSE SNOWDBA_XS_WH WITH WAREHOUSE_SIZE='X-SMALL';
Warehouse SNOWDBA_XS_WH successfully created.

Example 2. Create a multi-clustered that is initially suspended

USE ROLE SYSADMIN;

CREATE WAREHOUSE SNOWDBA_XS_MULTI_WH WITH WAREHOUSE_SIZE='X-SMALL'
MIN_CLUSTER_COUNT=1 MAX_CLUSTER_COUNT=4 INITIALLY_SUSPENDED=true;
Warehouse SNOWDBA_XS_MULTI_WH successfully created.

Example 3. Create a warehouse with auto resume and auto suspend

USE ROLE SYSADMIN;

CREATE WAREHOUSE SNOWDBA_XS_AUTO_WH WITH WAREHOUSE_SIZE='X-SMALL'
AUTO_SUSPEND=300 AUTO_RESUME=true;
Warehouse SNOWDBA_XS_AUTO_WH successfully created.

2. ALTER WAREHOUSE

Example 4. Start a warehouse

USE ROLE SYSADMIN;

ALTER WAREHOUSE SNOWDBA_XS_WH RESUME;
Statement executed successfully.

Example 5. Stop a warehouse

USE ROLE SYSADMIN;

ALTER WAREHOUSE SNOWDBA_XS_WH SUSPEND;
Statement executed successfully.

Example 6. Change the size of a warehouse

USE ROLE SYSADMIN;

ALTER WAREHOUSE SNOWDBA_XS_WH SET WAREHOUSE_SIZE='SMALL';
Statement executed successfully.

Example 7. Rename a warehouse

USE ROLE SYSADMIN;

ALTER WAREHOUSE SNOWDBA_XS_WH RENAME TO SNOWDBA_S_WH;
Statement executed successfully.

Example 8. Kill all queries running on a warehouse

USE ROLE SYSADMIN;

ALTER WAREHOUSE SNOWDBA_S_WH ABORT ALL QUERIES;
Statement executed successfully.

3. DROP WAREHOUSE

Example 9. Drop a warehouse

USE ROLE SYSADMIN;

DROP WAREHOUSE SNOWDBA_S_WH;
SNOWDBA_S_WH successfully dropped.

4. SHOW WAREHOUSES

Example 10. Show details on warehouses

USE ROLE SYSADMIN;

SHOW WAREHOUSES;

-- Also pattern match
SHOW WAREHOUSES LIKE 'SNOW%';

5. USE WAREHOUSE

Example 11. Set warehouse for session

USE WAREHOUSE SNOWDBA_XS_AUTO_WH;
Statement executed successfully.

6. MISCELLANEOUS

Example 12. Change role ownership of a schema

use role SYSADMIN;

GRANT OWNERSHIP ON WAREHOUSE SNOWDBA_XS_MULTI_WH TO ROLE APPLICATION_SUPPORT;
Statement executed successfully.