Using a Warehouse (Snowflake)

227 viewsConnecting to SnowflakeVirtualwarehouses
0

Using a Warehouse (Snowflake)

Alejandro Penzini Answered question December 19, 2023
0

Executing Queries and DML Statements:

Warehouse Requirements: Running queries and DML statements in Snowflake requires two conditions:

-A running warehouse.
-The warehouse specified as the current warehouse for the session.

Session Scope: Each Snowflake session can only have one current warehouse at a time.
Warehouse Selection: Use the USE WAREHOUSE command to set or change the current warehouse for the session.
Query Processing: Once set, queries and DML statements within the session are processed by the current warehouse. You can view the warehouse used for each query/statement in the Classic Console's History and Worksheets pages.

Delegating Warehouse Management:

Default Access: The ACCOUNTADMIN role holds default privileges to manage all warehouses in the account (alter, suspend, describe, etc.).
Custom Role Delegation: For finer control, use the GRANT command to grant the MANAGE WAREHOUSES privilege to a custom role. This grants equivalent privileges (MODIFY, MONITOR, OPERATE) to all account warehouses.

Example: The following demonstrates delegating warehouse management to a custom role named manage_wh_role:

1. Create and Own a New Warehouse:

SQL
CREATE ROLE create_wh_role;
GRANT

CREATE WAREHOUSE ON ACCOUNT TO ROLE create_wh_role;
GRANT ROLE create_wh_role TO ROLE SYSADMIN;
CREATE ROLE manage_wh_role;
GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE manage_wh_role;
GRANT ROLE manage_wh_role TO ROLE SYSADMIN;
Use code with caution. Learn more

2. Use manage_wh_role to modify "test_wh" owned by create_wh_role:

SQL
USE ROLE manage_wh_role;
ALTER WAREHOUSE test_wh ...;

Delegating Warehouse Management with Roles:

This example demonstrates creating two roles to manage Snowflake warehouses:

1. create_wh_role:

This role has the CREATE WAREHOUSE privilege, allowing it to:

Create and own new warehouses.
Grant ownership of warehouses to other roles.

2. manage_wh_role:

This role has the MANAGE WAREHOUSES privilege, granting it comprehensive control over all warehouses in the account, regardless of ownership. This includes the ability to:

Suspend and resume warehouses.
Resize warehouses.
Describe warehouse properties.

Scenario:

Create both roles and grant them the necessary privileges:
SQL
CREATE ROLE create_wh_role;
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE create_wh_role;
GRANT ROLE create_wh_role TO ROLE SYSADMIN;

CREATE ROLE manage_wh_role;
GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE manage_wh_role;
GRANT ROLE manage_wh_role TO ROLE SYSADMIN;
Use code with caution. Learn more

Create a new warehouse with create_wh_role:
SQL
USE ROLE create_wh_role;
CREATE OR REPLACE WAREHOUSE test_wh WITH WAREHOUSE_SIZE = XSMALL;
Use code with caution. Learn more

Switch to manage_wh_role and manage the warehouse:
SQL
USE ROLE manage_wh_role;

ALTER WAREHOUSE test_wh SUSPEND; -- Suspend the warehouse
ALTER WAREHOUSE test_wh SET WAREHOUSE_SIZE = SMALL; -- Resize the warehouse
DESC WAREHOUSE test_wh; -- Describe the warehouse properties

Alejandro Penzini Answered question December 19, 2023
You are viewing 1 out of 1 answers, click here to view all answers.
Feedback on Q&A