Creating Snowflake Resources
This section provides a guide to creating the required resources and Service User Accounts to execute PAI using the Snowflake Data Warehouse platform.
 |
A Snowflake account and an Administrative user must exist to create the Snowflake resources. Contact Snowflake at the following site: https://www.snowflake.com/contact/
|
The following objects need to be created in Snowflake
|
Object
|
Object Type
|
Description
|
|---|---|---|
|
PAI Database
|
Database
|
The database for the analytics data
|
|
PAI Schema
|
Schema
|
The schema in the database for the analytics data
|
|
PAI Stage
|
Stage
|
The “Snowflake Managed” stage for the schema
|
|
AutoPilot Warehouse
|
Warehouse
|
The warehouse for the Service Account to be used by the AutoPilot server
|
|
Intellicus Warehouse
|
Warehouse
|
The warehouse for the Service Account to be used by Intellicus
|
|
AutoPilot Service Account
|
User
|
The Service Account to be used by the AutoPilot server
|
|
Intellicus Service Account
|
User
|
The Service Account to be used by the Intellicus server
|
|
AutoPilot Role
|
Role
|
The role for assigning privileges to the AutoPilot Service Account
|
|
Intellicus Role
|
Role
|
The role for assigning privileges to the Intellicus Service Account
|
Use these instructions to create your Snowflake resources with the necessary Servigistics AutoPilot and Intellicus access. The instructions can be performed using the Snowflake user interface or by executing commands. Sample commands are included.
1. Create the AutoPilot and Intellicus warehouses:
USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE WAREHOUSE <warehouse> WITH
WAREHOUSE_SIZE = 'XSMALL' WAREHOUSE_TYPE = 'STANDARD'
AUTO_SUSPEND = 60 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 1 SCALING_POLICY = 'STANDARD';
CREATE OR REPLACE WAREHOUSE <warehouse> WITH
WAREHOUSE_SIZE = 'XSMALL' WAREHOUSE_TYPE = 'STANDARD'
AUTO_SUSPEND = 60 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 1 SCALING_POLICY = 'STANDARD';
2. Create the database, schema, and stage:
USE ROLE ACCOUNTADMIN;
CREATE DATABASE <database>;
CREATE SCHEMA <database>.<schema>;
CREATE STAGE <database>.<schema>.<stage>;
CREATE DATABASE <database>;
CREATE SCHEMA <database>.<schema>;
CREATE STAGE <database>.<schema>.<stage>;
3. Create the AutoPilot and Intellicus Service Accounts and select their default warehouse:
USE ROLE USERADMIN;
CREATE USER <user> PASSWORD = '<password>'
DEFAULT_WAREHOUSE = '<warehouse>'
MUST_CHANGE_PASSWORD = FALSE;
CREATE USER <user> PASSWORD = '<password>'
DEFAULT_WAREHOUSE = '<warehouse>'
MUST_CHANGE_PASSWORD = FALSE;
4. Create the AutoPilot role with the following privileges:
|
Object
|
Privileges
|
|---|---|
|
PAI Database
|
USAGE
|
|
PAI Schema
|
USAGE, CREATE TABLE
|
|
PAI Stage
|
READ, WRITE
|
|
Tables in PAI Schema
|
SELECT, INSERT, UPDATE, DELETE, TRUNCATE
|
|
Future Tables in PAI Schema
|
SELECT, INSERT, UPDATE, DELETE, TRUNCATE
|
|
AutoPilot Warehouse
|
USAGE
|
USE ROLE USERADMIN;
CREATE ROLE <role>;
GRANT USAGE ON DATABASE <database> TO ROLE <role>;
GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE <role>;
GRANT USAGE ON SCHEMA <database>.<schema> TO ROLE <role>;
GRANT SELECT,INSERT,UPDATE,DELETE,TRUNCATE ON
ALL TABLES IN SCHEMA <database>.<schema> TO ROLE <role>;
GRANT SELECT,INSERT,UPDATE,DELETE,TRUNCATE
ON FUTURE TABLES IN SCHEMA <database>.<schema> TO ROLE <role>;
GRANT CREATE TABLE ON SCHEMA <database>.<schema> TO ROLE <role>;
GRANT READ,WRITE ON STAGE <database>.<schema>.<stage> TO ROLE <role>;
CREATE ROLE <role>;
GRANT USAGE ON DATABASE <database> TO ROLE <role>;
GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE <role>;
GRANT USAGE ON SCHEMA <database>.<schema> TO ROLE <role>;
GRANT SELECT,INSERT,UPDATE,DELETE,TRUNCATE ON
ALL TABLES IN SCHEMA <database>.<schema> TO ROLE <role>;
GRANT SELECT,INSERT,UPDATE,DELETE,TRUNCATE
ON FUTURE TABLES IN SCHEMA <database>.<schema> TO ROLE <role>;
GRANT CREATE TABLE ON SCHEMA <database>.<schema> TO ROLE <role>;
GRANT READ,WRITE ON STAGE <database>.<schema>.<stage> TO ROLE <role>;
5. Create the Intellicus role with the following privileges:
|
Object
|
Privileges
|
|---|---|
|
PAI Database
|
USAGE
|
|
PAI Schema
|
USAGE
|
|
Tables in PAI Schema
|
SELECT
|
|
Future Tables in PAI Schema
|
SELECT
|
|
AutoPilot Warehouse
|
USAGE
|
USE ROLE USERADMIN;
CREATE ROLE <role>;
GRANT USAGE ON DATABASE <database> TO ROLE <role>;
GRANT USAGE ON SCHEMA <database>.<schema> TO ROLE <role>;
GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE <role>;
GRANT SELECT ON ALL TABLES IN SCHEMA <database>.<schema> TO ROLE <role>;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <database>.<schema> TO ROLE <role>;
CREATE ROLE <role>;
GRANT USAGE ON DATABASE <database> TO ROLE <role>;
GRANT USAGE ON SCHEMA <database>.<schema> TO ROLE <role>;
GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE <role>;
GRANT SELECT ON ALL TABLES IN SCHEMA <database>.<schema> TO ROLE <role>;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <database>.<schema> TO ROLE <role>;
6. Assign the AutoPilot and Intellicus roles to the corresponding Service Accounts:
USE ROLE USERADMIN;
ALTER USER <user> SET DEFAULT_ROLE = <role>;
ALTER USER <user> SET DEFAULT_ROLE = <role>;