Create a Connection - Snowflake

  • Updated

This guide describes the process of setting up Analytics over data stored in a Snowflake warehouse. Following are some quick links to the different procedures that you will need to perform during the setup.

Pre-requisites

In order to be able to create a new warehouse, create roles, and add new users to the organization, you need to login and have Account Administrator privileges. The following steps need to be performed before you set up 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

Now, let us look at these steps in detail.

As an example, let us use example_role for all the operations specified below.

Create a warehouse for Analytics​

You can begin with a Medium size warehouse to begin with and scale up or down based on observed query latencies and warehouse cost. The warehouse_size would depend on the volume of data that needs to be analysed 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

The next step is to create a user and define a role.

Replace PASSWORD with the appropriate password. This password will be shared with Analytics later.

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​

For Analytics to be able to spin up a warehouse, it needs to have access to the warehouse in addition to having Snowflake access. To grant access to a specific warehouse to Analytics, you can use:

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 will differ depending on which connection you choose.

Grant access to databases, schemas, tables, and views​

You can use the following commands as appropriate:

FUNCTION COMMAND EXAMPLE
Grant usage access to relevant databases and schemas. grant usage on database <DATABASE_NAME> to role <NETSPRING_ROLE>; grant usage on database <DATABASE_NAME> to role <NETSPRING_ROLE>;
Grant usage access to relevant schemas. grant usage on all schemas in database <DATABASE_NAME> to role <NETSPRING_ROLE> grant usage on all schemas in database netspring_db to role example_role
Grant access to execute select on all tables in database grant select on all tables in database <DATABASE_NAME> to role <NETSPRING_ROLE>; grant select on all tables in database netspring_db to role example_role;
Grant access to execute select on all futures tables in database grant select on future tables in database <DATABASE_NAME> to role <NETSPRING_ROLE>; grant select on future tables in database netspring_db to role example_role
Grant access to execute select on all tables in schema grant select on all tables in schema <DATABASE_NAME>.<SCHEMA_NAME> to role <NETSPRING_ROLE>; grant select on all tables in schema netspring_db.netspring_schema to role example_role;
Grant access to execute select on all future tables in schema grant select on future tables in schema <DATABASE_NAME>.<SCHEMA_NAME> to role <NETSPRING_ROLE>; grant select on future tables in schema netspring_db.netspring_schema to role example_role;
Grant access to execute select on 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

You can repeat the above commands for views if you want Analytics to have access to views in addition to tables.

IP Whitelisting​

You also need to perform IP Whitelisting - following is the list of IPs that need to be whitelisted for Analytics to access Snowflake.

  • 3.215.188.76
  • 3.217.27.33
  • 3.233.246.20
  • 54.164.43.201

Whitelisting IPs is not a mandatory step. It is required only if the Snowflake cluster is guarded by a security group (a firewall) that is preventing access to the cluster from Analytics.

Share login credentials with Analytics​

Finally, share your Snowflake login password with Analytics from step 2 above to create a connection in Analytics using the instructions that follow.

Creating a writable schema in Snowflake​

Once you have set up your warehouse and granted all permissions, create a writable scratch space for Analytics. Analytics will use this scratch space to optimize its internal state. This is an optional step.

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;