Create a connection - Snowflake

  • Updated

You can configure Analytics using data stored in a Snowflake warehouse. Use the following links to read about specific configuration procedures.

Prerequisites

You must log in with Account Administrator privileges to create a warehouse, assign roles, or add users to your organization.

Complete the following steps to configure a Snowflake connection in Analytics:

  1. Create a warehouse for Analytics.
  2. Create a user and role.
  3. Grant usage permissions.
  4. Grant access to databases, schemas, tables, and views.
  5. Whitelist IPs.
  6. Share login credentials with Analytics.

The following steps are used example_role as the role for all operations.

You can begin with a medium-sized warehouse and scale up or down based on observed query latencies and warehouse costs. The warehouse_size depends on the volume of data that needs to be analyzed by Analytics and varies based on the latencies of the queries.

SYNTAX –
create warehouse if not exists [WAREHOUSE_NAME] warehouse_size=[WAREHOUSE_SIZE] initially_suspended=true auto_suspend=1 statement_queued_timeout_in_seconds=300;

EXAMPLE –
create warehouse if not exists netspring_wh_a warehouse_size='Medium' initially_suspended=true auto_suspend=1 statement_queued_timeout_in_seconds=300;

Create a user and role for Analytics

Create a user and define a role. Replace [PASSWORD] with your password. Later, you share this password with Analytics.

SYNTAX –
create role if not exists [NETSPRING_ROLE];
EXAMPLE –
create role if not exists example_role;

SYNTAX –
create user if not exists [NETSPRING_USER] password=[PASSWORD] default_role=[NETSPRING_ROLE] default_warehouse=[WAREHOUSE_NAME];
EXAMPLE –
create user if not exists 'Mavis Gates' password='password@123' default_role=example_role default_warehouse='netspring_wh_a';

SYNTAX –
grant role [NETSPRING_ROLE] to user [NETSPRING_USER];
EXAMPLE –
grant role example_role to user 'Mavis Gates';

Analytics uses the default role of the user to perform operations on Snowflake. So, you need to make the role you have created for Analytics the default role. This can be done by executing the following SQL:

ALTER USER SYS_NETSPRING SET DEFAULT_ROLE = NEW_ROLE;

Grant usage permission

You must grant Analytics access to the warehouse and Snowflake access. To grant access to a specific warehouse to Analytics, you can use the following:

SYNTAX –
`grant operate, usage, monitor on warehouse [WAREHOUSE_NAME] to role [NETSPRING_ROLE];`

EXAMPLE –
`grant operate, usage, monitor on warehouse netspring_wh_a to role example_role;`

The fields differ depending on which connection you choose.

Grant access to databases, schemas, tables, and views

You can use the following commands:

FUNCTION COMMAND EXAMPLE
Grant usage access to relevant databases and schemas. grant usage on database [DATABASE_NAME] 
to role [NETSPRING_ROLE];		 grant usage on the database netspring_db to role example_role;
Grant usage access to all schemas in a database. grant usage on all schemas in the database  [DATABASE_NAME] to role [NETSPRING_ROLE]; grant usage on all schemas in the database netspring_db to role example_role;
Grant access to execute select on all tables in the database. grant selects all tables in the database [DATABASE_NAME] to role  [NETSPRING_ROLE]; grant selects all tables in the database netspring_db to role example_role;
Grant access to execute select on all future tables in the database. grant select on future tables in the database  [DATABASE_NAME] to role  [NETSPRING_ROLE]; grant select on future tables in the database netspring_db to role example_role;
Grant access to execute select on all tables in a schema. grant selects all tables in the schema  [DATABASE_NAME].[SCHEMA_NAME] to role [NETSPRING_ROLE]; grant selects all tables in the schema netspring_db.netspring_schema to role example_role;
Grant access to execute select on all future tables in a schema. grant select on future tables in the schema  [DATABASE_NAME].[SCHEMA_NAME] to role [NETSPRING_ROLE]; grant select on future tables in the schema netspring_db.netspring_schema to role example_role;
Grant access to execute select on the table. grant select on  [DATABASE_NAME].[SCHEMA_NAME].[TABLE_NAME] to role [NETSPRING_ROLE]; grant select on netspring_db.netspring_schema.netspring_table to role example_role;

If you want Analytics to access views in addition to tables, you can repeat the previous commands for views.

Add IP whitelisting (optional)

You can optionally perform IP Whitelisting. Whitelisting IPs is not a mandatory step. It is only required if the Snowflake cluster is guarded by a security group (a firewall), which prevents access to the cluster from Analytics.

The following IPs are needed to be whitelisted for Analytics to access Snowflake: 

  • 3.215.188.76
  • 3.217.27.33
  • 3.233.246.20
  • 54.164.43.201

Create a writable schema in Snowflake (optional)

After configuring your warehouse and granting permissions, you can optionally create a writable scratch space for Analytics. Analytics uses this scratch space to optimize its internal state. 

SYNTAX –
create database [DATABASE_NAME];
EXAMPLE –
create database netspring_db;

SYNTAX –
use database [DATABASE_NAME];
EXAMPLE –
use database netspring_db;

SYNTAX –
create schema [SCHEMA_NAME];
EXAMPLE –
create schema netspring_schema;

SYNTAX –
grant all privileges on [DATABASE_NAME] to role [NETSPRING_ROLE];
EXAMPLE –
grant all privileges on netspring_db to role example_role;

SYNTAX –
grant all privileges on [SCHEMA_NAME] to role [NETSPRING_ROLE];
EXAMPLE –
grant all privileges on netspring_schema to role netspring_role;