Add Data Source

SET acceldata_role = 'AD_COMPUTE_MONITOR';

​

-- This role will be created and should be used to enable compute reliability on Acceldata platform

​

SET acceldata_warehouse = 'AD_COMPUTE_WH'; 

​

-- This warehouse will be created and granted necessary privileges required to enable compute reliability on Acceldata platform.

​

SET acceldata_user = 'AD_USER'; 

​

-- This user will be created with the below password and should be used to enable compute reliability on Acceldata platform

​

SET acceldata_password = '<password>'; 

​

-- Replace the word password with the password you want to set.

​

SET acceldata_database = 'AD_MONITOR_DB'; 

​

-- This database will be created (if not exists) and used to create/run a task. Additionally, this database should be used to enable compute reliability on Acceldata platform.

​

-- You must use the ACCOUNTADMIN role to assign the required privileges

​

use role ACCOUNTADMIN;

​

-- Create warehouse $acceldata_warehouse

​

create or replace warehouse identifier($acceldata_warehouse) warehouse_size = 'X-Small';

alter warehouse identifier($acceldata_warehouse) set auto_suspend=30;

​

-- Create a new role intended to monitor Snowflake usage.

​

create or replace role identifier($acceldata_role);

​

-- Grant privileges on the SNOWFLAKE database to the new role.

​

grant imported privileges on database SNOWFLAKE to role identifier($acceldata_role);

​

-- To access to see account-level usage, features, and objects (credits used, databases present, warehouses present, etc.).

​

grant monitor on account to role identifier($acceldata_role);

​

-- Grant usage on $acceldata_warehouse used to monitor Snowflake.

​

grant usage on warehouse identifier($acceldata_warehouse) to role identifier($acceldata_role);

​

-- Create a user.

create or replace user identifier($acceldata_user)

    LOGIN_NAME          = $acceldata_user

    password            = $acceldata_password

    default_warehouse   = $acceldata_warehouse

    default_role        = $acceldata_role

    default_namespace   = SNOWFLAKE.ACCOUNT_USAGE

;

​

-- Grant the monitor role to the user.

​

grant role identifier($acceldata_role) to user identifier($acceldata_user);

​

-- Database is needed to create a stored procedure & external_stage

​

create database if not exists identifier($acceldata_database);

​

grant USAGE ON database identifier($acceldata_database) to role identifier($acceldata_role);

​

use database identifier($acceldata_database);

​

-- Grant permission on schema $acceldata_database.PUBLIC to $acceldata_role

​

grant MONITOR, USAGE, CREATE FILE FORMAT, CREATE STAGE on schema PUBLIC to role identifier($acceldata_role);

​

-- create a procedure to grant privilege to all warehouses

​

create or replace procedure grant_privileges_to_all_warehouses(ROLE_NAME VARCHAR)

  returns VARCHAR

  language javascript

  as

  $$

    var show_warehouses_command = "SHOW WAREHOUSES";

    var warehouse_result_set = snowflake.createStatement( {sqlText: show_warehouses_command} ).execute();

​

    while (warehouse_result_set.next())  {

      var warehouse_name = warehouse_result_set.getColumnValue(1);

      var grant_usage_command = "grant monitor, usage on warehouse " + warehouse_name + " to role " + ROLE_NAME;

      snowflake.createStatement( {sqlText: grant_usage_command} ).execute();

    }

​

    var show_resource_monitors_command = "SHOW RESOURCE MONITORS IN ACCOUNT";

    var resource_monitor_result_set = snowflake.createStatement( {sqlText: show_resource_monitors_command} ).execute();

​

    while (resource_monitor_result_set.next())  {

      var resource_monitor_name = resource_monitor_result_set.getColumnValue(1);

      var grant_monitor_rm_command = "grant monitor on RESOURCE MONITOR " + resource_monitor_name + " to role " + ROLE_NAME;

      snowflake.createStatement( {sqlText: grant_monitor_rm_command} ).execute();;

    }

​

     return 'Granted monitor privilege to role ' + ROLE_NAME + ' on all warehouses & resource monitors';

  $$

  ;

grant USAGE ON PROCEDURE grant_privileges_to_all_warehouses(STRING) TO ROLE identifier($acceldata_role);

​

CALL grant_privileges_to_all_warehouses($acceldata_role);