Author: Ayushi Sharma
Create a procedure that automatically creates a reader account and assigns a resource monitor to the shared warehouse.
Reader Accounts
Data sharing is supported between Snowflake accounts. As a data provider, one might wish to share data with a consumer who does not have a Snowflake account and is not ready to become a Snowflake customer.
To ease sharing data with these consumers, Snowflake supports providers creating reader accounts. Reader account provides a speedy, easy, and cost-effective way to share data without requiring the consumer to become a snowflake customer.
Each reader account belongs to the provider account that created it. Similar to a standard consumer’s account, a provider account uses outbound shares to share databases with reader accounts; however, a reader account can only consume data from the provider’s account that created it. Users in a reader account can consume/query data that has been shared with it but cannot perform any of the DML operations that are allowed in a full account (data loading, insert, update, etc.).
Steps to create a reader account
Step1:
Using the admin account, create an outbound share. Use the ACCOUNNTADMIN role to create the outbound share. At this step, the share is simply a container waiting for objects and accounts to be added.
SQL: use role accountadmin;
CREATE SHARE “SHARE_NAME” COMMENT=’ Outbound share for reader account’;
Step 2:
Add Objects to the Share by Granting Privileges.
Use GRANT <privileges> … TO SHARE to grant the following object privileges to the share:
USAGE privileges on the database you wish to share.
USAGE privileges on database schema containing the objects required to share.
SELECT privileges for sharing the objects in each shared schema:
Tables
External tables
Secure views
Secure materialized views
Secure UDF
SQL: GRANT USAGE ON DATABASE “DATABASE_NAME” TO SHARE “SHARE_NAME”;
GRANT USAGE ON SCHEMA “DATABASE_NAME”.”SCHEMA_NAME” TO SHARE “SHARE_NAME”;
GRANT SELECT ON VIEW “DATABASE_NAME”.”SCHEMA_NAME”.”OBJECT_NAME” TO SHARE “SHARE_NAME”;
Step 3:
Use ACCOUNNTADMIN role to create reader account.
Keep note of the username and password you have entered. These are your reader account credentials used to log in and manage the Reader account as the ACCOUNTADMIN role.
SQL: CREATE MANAGED ACCOUNT READER_ACCOUNT_NAME admin_name=’USERNAME’, admin_password=’********’,type=reader, COMMENT=’Reader Account’;
Step 4:
Click on the share tab, and navigate to SHARE_NAME in the outbound share. Add the reader account as the consumer to the SHARE_NAME.
SQL: ALTER SHARE “SHARE_NAME” ADD ACCOUNTS = YJA66285;
Step 5:
Wait for the [Account URL] to appear. Click the URL link to open the Reader Account in a new browser tab. Take note of the locator and URL provided to the reader account. Log in to the Reader Account you just created using the Admin Account you just set up.
Click on “I’m a Data Provider”
Step 6:
Create a user who will log into the reader account and query data shared with the account, and perform any tasks you choose to allow.
SQL: use role accountadmin;
create user user_name password=’****’ default_role = PUBLIC must_change_password = true;
Step 7:
To allow querying the objects in the shared database, create a virtual warehouse.
SQL: CREATE WAREHOUSE READER_WH_NAME WITH WAREHOUSE_SIZE = ‘SMALL’ WAREHOUSE_TYPE = ‘STANDARD’ AUTO_SUSPEND = 600 AUTO_RESUME = TRUE;
Step 8:
Click [Shares] in the Navigation Ribbon and open inbound share. Click the SHARE_NAME row background to select that row. Click [Create Database From Secure Share].
Enter [Database Name]. Choose Public for the user role to [Grant access to]. Choose Public for the user role to [Grant access to]. Click [Create Database]. Click [OK].
SQL: CREATE DATABASE “READER_DATABASE_NAME” FROM SHARE FDA39576.”SHARE_NAME”;
GRANT IMPORTED PRIVILEGES ON DATABASE “READER_DATABASE_NAME” TO ROLE “PUBLIC”;
grant usage on WAREHOUSE READER_WH_NAME to role public;
Now the READER_DATABASE_NAME can be seen in the navigation panel containing shared secure objects.
Resource Monitor
Snowflake featured first-class object resource monitors to implement cost-tracking and avoid unexpected credit usage caused by running warehouses. A virtual warehouse is assigned to consume snowflake credits while it runs.
A single resource monitor can be assigned at the account level to monitor credit usage for all warehouses in your account. In addition, a monitor can be assigned to multiple warehouses, thereby monitoring the credit usage for each assigned warehouse.
Resource Monitor can be created using a web interface as well as SQL command.
SQL:
CREATE RESOURCE MONITOR IF NOT EXISTS PLAT_SYSTEM_RESOURCE_MONITOR
WITH CREDIT_QUOTA= 500
frequency = ‘MONTHLY’
start_timestamp = ‘2021-10-01 00:00’,
end_timestamp = null
TRIGGERS
ON 50 PERCENT DO NOTIFY
ON 75 PERCENT DO NOTIFY
ON 90 PERCENT DO NOTIFY
ON 95 PERCENT DO SUSPEND
ON 99 PERCENT DO SUSPEND_IMMEDIATE;
The procedure to create a reader account and assign the resource monitor to the warehouse is created to make it just a one-step process.
SQL:
create or replace procedure reader_account(NAME VARCHAR,
DATABASE_NAME STRING,
SCHEMA_NAME STRING,
TABLE_NAME STRING,
READER_ACCOUNT_NAME STRING,
ADMIN_NAME STRING,
ADMIN_PASSWORD STRING,
WH_NAME STRING
)
returns string
language javascript
strict
execute as caller
as
$$
var reader_account =
“CREATE OR REPLACE SHARE ” + NAME + ” COMMENT=”;”;
var usage_grant1 =
“GRANT USAGE ON DATABASE ” + DATABASE_NAME + ” TO SHARE ” + NAME + “;”;
var usage_grant2 =
“GRANT USAGE ON SCHEMA ” + DATABASE_NAME+ “.” +SCHEMA_NAME+ ” TO SHARE ” + NAME + “;”;
var select_grant =
“GRANT SELECT ON VIEW ” + DATABASE_NAME+ “.” +SCHEMA_NAME+ “.” +TABLE_NAME+ ” TO SHARE ” + NAME + “;”;
var create_reader_account =
“CREATE MANAGED ACCOUNT ” + READER_ACCOUNT_NAME
+ ” admin_name = ‘” + ADMIN_NAME + “‘, admin_password= ‘” + ADMIN_PASSWORD + “‘, type=reader, COMMENT=’Reader Account’;”;
var res_monitor = `CREATE RESOURCE MONITOR IF NOT EXISTS READER_RESOURCE_MONITOR
WITH CREDIT_QUOTA= 500
frequency = ‘MONTHLY’
start_timestamp = ‘IMMEDIATELY’,
end_timestamp = null
TRIGGERS
ON 50 PERCENT DO NOTIFY
ON 75 PERCENT DO NOTIFY
ON 90 PERCENT DO NOTIFY
ON 95 PERCENT DO SUSPEND
ON 99 PERCENT DO SUSPEND_IMMEDIATE;`
var assign_res_monitor = “ALTER WAREHOUSE ” + WH_NAME + ” SET RESOURCE_MONITOR = READER_RESOURCE_MONITOR;”;
var account = “show managed accounts;”;
try {
snowflake.execute ({sqlText: reader_account});
snowflake.execute ({sqlText: usage_grant1});
snowflake.execute ({sqlText: usage_grant2});
snowflake.execute ({sqlText: select_grant});
snowflake.execute ({sqlText: create_reader_account});
snowflake.execute ({sqlText: res_monitor});
snowflake.execute ({sqlText: assign_res_monitor});
var stmt = snowflake.createStatement({sqlText: account});
var rs = stmt.execute();
while (rs.next()) {
var out = rs.getColumnValue(4);
snowflake.execute ({sqlText: `ALTER SHARE ${NAME} ADD ACCOUNTS = ${out}`});
}
return “Succeeded.”; // Return a success/error indicator.
}
catch (err) {
return “Failed: ” + err; // Return a success/error indicator.
}
$$
;
To call the procedure used:
SQL:
Call reader_account (‘READER_ACCOUNT_SHARE’,’DB_NAME’,’SCHEMA_NAME’,’TABLE_NAME’,’READER_ACCOUNT’,’READER_ACCOUNT_NAME’,’PASSWORD’,’COMPUTE_WH’);