Snowflake Prerequisites¶
Table of Contents
Introduction¶
This document includes the prerequisite steps for Snowflake.
Warehouse¶
A new or existing Snowflake warehouse needs to be allocated for use by Gluent Data Platform.
Database¶
A new or existing Snowflake database needs to be allocated for use by Gluent Data Platform.
Cloud Storage¶
A new or existing cloud storage bucket or container needs to be allocated for use by Gluent Data Platform and the Snowflake Storage Integration.
During an offload Gluent Data Platform will stage data in cloud storage in this bucket or container.
The privileges required for operations by Gluent Data Platform on the bucket or container are dependent on the cloud storage provider used.
Amazon S3¶
A service account or IAM role should be provisioned with a policy incorporating the actions listed below.
Resources |
Actions |
|---|---|
S3 bucket |
ListBucket, GetObject, DeleteObject, PutObject |
* |
ListAllMyBuckets |
Google Cloud Storage¶
A service account should be provisioned with the permissions below on the Google Cloud Storage bucket:
storage.buckets.get
storage.objects.create
storage.objects.delete
storage.objects.get
storage.objects.list
A private JSON key file for the service account should be created as this will be needed during installation.
Microsoft Azure¶
The access key for the storage account used by the Snowflake storage integration is needed to provide read and write access to Gluent Data Platform.
Storage Integration¶
A new or existing storage integration needs to be available for use by Gluent Data Platform. This storage integration must be configured to have read access on the assigned cloud storage bucket or container.
Guided instructions for creating storage integrations with Amazon S3, Google Cloud Storage or Microsoft Azure are available in the Snowflake product documentation.
Role¶
A role should be created for use by the Snowflake user created for Gluent Data Platform. The default name for this role is GLUENT_OFFLOAD_ROLE but, if required, this can be reconfigured in the Gluent Data Platform environment file.
The name of the created role is assumed to be GLUENT_OFFLOAD_ROLE from this point forward:
CREATE ROLE GLUENT_OFFLOAD_ROLE COMMENT = 'Role for Gluent Data Platform';
User¶
A Snowflake user should be created for use by Gluent Data Platform.
The user can be authenticated either by password, by RSA key or by passphrase-protected RSA key. Instructions for setting up key-based access can be found in the Snowflake product documentation.
The user must be granted the Role created:
GRANT ROLE GLUENT_OFFLOAD_ROLE TO USER <snowflake_user>;
Access and Privileges¶
The GLUENT_OFFLOAD_ROLE needs sufficient privileges to be able to operate with databases, integrations, stages, file formats, schemas, tables and views.
There are mandatory privileges that are needed regardless of the granularity of access preferred, and there are additional privileges that depend on how fine-grained the access requirements are.
The following sets of privileges required for Gluent Data Platform to operate are covered below:
Mandatory global privileges
Either one of the following or a combination of the two:
Permissive access privileges to cover all existing and future requirements
Restricted access privileges to cover specific existing requirements
Mandatory Global Privileges¶
Gluent Data Platform requires the following mandatory privileges, regardless of which permissive/restrictive option is taken for schema and object access:
GRANT USAGE ON DATABASE <database> TO GLUENT_OFFLOAD_ROLE;
GRANT USAGE ON INTEGRATION <integration> TO GLUENT_OFFLOAD_ROLE;
GRANT USAGE ON WAREHOUSE <warehouse> TO GLUENT_OFFLOAD_ROLE;
Permissive Access Privileges¶
Use this option if it is acceptable for Gluent Data Platform to:
Have global read access to all existing schemas, tables or views in the database
Have the ability to offload new tables to any new or existing schema in the Snowflake database, including the ability to create schemas as required
Present any existing table or view to the RDBMS for querying
To grant Gluent Data Platform the ability to do all of these now and in the future, issue the following grants.
Create Schemas¶
GRANT CREATE SCHEMA ON DATABASE <database> TO GLUENT_OFFLOAD_ROLE;
Schema Access¶
GRANT USAGE, CREATE STAGE, CREATE FILE FORMAT, CREATE TABLE, CREATE VIEW ON ALL SCHEMAS IN DATABASE <database> TO GLUENT_OFFLOAD_ROLE;
GRANT USAGE, CREATE STAGE, CREATE FILE FORMAT, CREATE TABLE, CREATE VIEW ON FUTURE SCHEMAS IN DATABASE <database> TO GLUENT_OFFLOAD_ROLE;
Object Access¶
GRANT SELECT ON FUTURE TABLES IN DATABASE <database> TO GLUENT_OFFLOAD_ROLE;
GRANT SELECT ON FUTURE VIEWS IN DATABASE <database> TO GLUENT_OFFLOAD_ROLE;
For each existing schema that is to be used with Gluent Data Platform:
GRANT SELECT ON ALL TABLES IN SCHEMA <existing_schema> TO GLUENT_OFFLOAD_ROLE;
GRANT SELECT ON ALL VIEWS IN SCHEMA <existing_schema> TO GLUENT_OFFLOAD_ROLE;
Restricted Access Privileges¶
Use this option if Gluent Data Platform access needs to adhere to the principle of least privilege, such as:
Having read access to limited existing schemas and/or limited existing tables or views
Offloading data to a limited set of new or existing schemas
Any or all of the permissive privileges can be replaced with the restricted equivalent below.
Create Schemas¶
If the CREATE SCHEMA privilege is not permitted, the following actions must be done for every schema that Gluent Data Platform is to operate with.
For each new schema to be offloaded from the RDBMS to Snowflake:
CREATE SCHEMA <rdbms_schema_name> COMMENT = 'Offload schema for Gluent Data Platform';
GRANT OWNERSHIP ON SCHEMA <rdbms_schema_name> TO GLUENT_OFFLOAD_ROLE;
CREATE SCHEMA <rdbms_schema_name>_LOAD COMMENT = 'Staging schema for Gluent Data Platform';
GRANT OWNERSHIP ON SCHEMA <rdbms_schema_name>_LOAD TO GLUENT_OFFLOAD_ROLE;
For every existing Snowflake schema with tables or views to be presented to the RDBMS:
CREATE SCHEMA <existing_schema_name>_LOAD COMMENT = 'Staging schema for Gluent Data Platform';
GRANT OWNERSHIP ON SCHEMA <existing_schema_name>_LOAD TO GLUENT_OFFLOAD_ROLE;
Schema Access¶
If the ON ALL SCHEMAS and ON FUTURE SCHEMAS privileges are not permitted, then for every schema that is to be used with Gluent Data Platform, the following are required:
GRANT USAGE, CREATE STAGE, CREATE FILE FORMAT, CREATE TABLE, CREATE VIEW ON SCHEMA <schema_name> TO GLUENT_OFFLOAD_ROLE;
Object Access¶
If the SELECT ON ALL or SELECT ON FUTURE table/view privileges are not permitted, then for every existing table or view that is to be presented to an RDBMS with Gluent Data Platform, the following is required:
GRANT SELECT ON TABLE|VIEW <schema_name>.<table|view_name> TO GLUENT_OFFLOAD_ROLE;