As part of setting up the Datacoral platform with Snowflake, you will be asked to provide the following fields.
|Account||This can be found in the URL that you use to login to Snowflake. For example |
|Password||Password for |
Run the following commands to setup the Datacoral user in Snowflake and provide the right permissions.
-- 1. Use the SYSADMIN role for the next steps. The system administrator (SYSADMIN) -- role includes the privileges to create warehouses, databases, and all -- database objects (schemas, tables, etc.). USE ROLE "SYSADMIN"; -- 2. Create a new Snowflake database for all Datacoral created and owned tables. -- Datacoral will create separate schemas for each Datacoral connector and the -- tables corresponding to loadunits or materialized views within each schema. CREATE DATABASE IF NOT EXISTS <DB_NAME> -- pick a database name COMMENT = 'DATACORAL_OWNED'; -- 3. Create a new Snowflake warehouse for compute use by Datacoral. Size this -- small and expect it to grow as the data and materialized views grow. -- This isolates the Datacoral owned pipelines from the rest of the work loads. CREATE WAREHOUSE IF NOT EXISTS <WH_NAME> -- pick a warehouse name WITH WAREHOUSE_SIZE = XSMALL AUTO_RESUME = TRUE AUTO_SUSPEND = 60 COMMENT = 'DATACORAL_OWNED'; -- 4. Use the SECURITYADMIN for the rest of the steps. The security administrator -- (SECURITYADMIN) role includes the privileges to create and manage users and roles. USE ROLE "SECURITYADMIN"; -- 5. Create a new Role that will be used by the Snowflake User used by Datacoral. CREATE ROLE IF NOT EXISTS DATACORAL_ROLE COMMENT = 'DATACORAL_OWNED'; GRANT ROLE DATACORAL_ROLE TO ROLE SYSADMIN; -- 6. Grant permissions of the database, warehouse, and PUBLIC schema (to create a STAGE) GRANT ALL PRIVILEGES ON DATABASE <DB_NAME> TO DATACORAL_ROLE; GRANT ALL PRIVILEGES ON WAREHOUSE <WH_NAME> TO DATACORAL_ROLE; GRANT ALL PRIVILEGES ON SCHEMA <DB_NAME>.PUBLIC TO DATACORAL_ROLE; -- 7. Create a new Snowflake user and assign it a Snowflake Role that -- gives the user access to the database and warehouse created in Step 1 & 2. CREATE USER IF NOT EXISTS <USER_NAME> PASSWORD = '<PASSWORD>' DEFAULT_WAREHOUSE = <WH_NAME> DEFAULT_NAMESPACE = <DB_NAME> DEFAULT_ROLE = DATACORAL_ROLE COMMENT= 'DATACORAL_OWNED'; -- 8. Grant the newly created role to the Snowflake user. GRANT ROLE DATACORAL_ROLE TO USER <USER_NAME>;
Verify that the above commands worked properly.
DESCRIBE USER <USER_NAME>;
The output of the above statement should contain the following based on the names you chose.
|COMMENT||DATACORAL_OWNED||null||user comment associated to an object in the dictionary|
|DISPLAY_NAME||null||Display name of the associated object|
|LOGIN_NAME||null||Login name of the user|
|DEFAULT_WAREHOUSE||null||Default warehouse for this user|
|DEFAULT_NAMESPACE||null||Default database namespace prefix for this user|
|DEFAULT_ROLE||DATACORAL_ROLE||null||Primary principal of user session will be set to this role|