Setting Up Snowflake as a Target
  • 13 Apr 2022
  • 4 Minutes to read
  • Dark
    Light
  • PDF

Setting Up Snowflake as a Target

  • Dark
    Light
  • PDF

Overview

Getting started with Rivery and Snowflake:

This guide will show you how to create an S3 bucket for Snowflake staging while also setting the required credentials for using Snowflake with Rivery.

Before you use this guide, Please make sure you have a valid Snowflake account.

If you don't have any valid Snowflake account, please create one. If you or your company already has a Snowflake account, you can connect/create a Rivery user.

Setting up your Snowflake environment

There are two ways to set up your Snowflake environemtn for Rivey, you need to choose only one method for granting access.

The first method will grant an existing user the SYSADMIN role, which will allow rivery access to all object in the database and will be able to extract from, and ingest to, new and existing tables.

The second method will either create or use existing user, role, database and warehouse depending on the settings, and will either give Rivery the ability to ingest data to and/or extract data from (depending on the settings) said objects.

First method - Global permission with SYSADMIN role.
Snowflake allows a single user to have multiple roles. Rivery will need to execute various commands on Snowflake, so you must assign a SYSADMIN role to the Rivery user you’ll be leveraging:

  1. Access your Snowflake console. Utiliza a user that has access to the ACCOUNTADMIN role

  2. Select the worksheet tab.

  3. Run the following commands:

    begin;
    
     /* Set variables for script, select an existing user or create a new one, replace the value after var_user with the name of the user, 
    Do not forget to replace {password} with the password of your choice if you are creating a new user. */
    
       set var_user = 'RIVERY_USER';
       set var_password = '{password}';
       
     /* switch to ACCOUNTADMIN role: only an ACCOUNTADMIN can set or unset a user’s role */
        USE ROLE accountadmin;
        
        /* Create a user for Rivery or use an existing one */
       create user if not exists identifier($var_user)
       password = $var_password;
       
        GRANT ROLE SYSADMIN TO USER identifier($var_user);
        ALTER USER identifier($var_user) SET DEFAULT_ROLE = SYSADMIN;
    commit; 
    

Second method - Specific permission with custom role.
Copy the following script to your Snowflake console, make sure to change the variables to your desired variable and only run necessary steps, pay attention to the instructions on the code.

begin;
 
   /* Set variables for script, You can chose to work with the default suggested values in the script or use your own. 
	  Do not forget to replace {password} with the password of your choice if you are creating a new user. */
   set var_user = 'RIVERY_USER';
   set var_password = '{password}';
   set var_role = 'RIVERY_ROLE';
   set var_database = 'RIVERY_DATABASE';
   set var_warehouse = 'RIVERY_WAREHOUSE';

   
   /* Switch to securityadmin role:  
	Role that can manage any object grant globally, as well as create, monitor, and manage users and roles */
   use role securityadmin;
 
   /* Create role for Rivery */
   create role if not exists identifier($var_role);
   grant role identifier($var_role) to role SYSADMIN;
 
   /* Create a user for Rivery */
   create user if not exists identifier($var_user)
   password = $var_password
   default_role = $var_role
   default_warehouse = $var_warehouse;
 
   grant role identifier($var_role) to user identifier($var_user);
 
   /* switch to sysadmin role: 
   Role that has privileges to create warehouses and databases (and other objects) in an account.
   */
   use role sysadmin;
 
   /* Create a warehouse for Rivery, this step is optional */
   create warehouse if not exists identifier($var_warehouse)
   warehouse_size = xsmall
   warehouse_type = standard
   auto_suspend = 300
   auto_resume = true
   initially_suspended = true;
 
   /* Create database for Rivery, this step is optional */
   create database if not exists identifier($var_database);
   
 
   /* Grant Rivery role access to warehouse */
   grant USAGE
   on warehouse identifier($var_warehouse)
   to role identifier($var_role);
 
   /* grant Rivery access to database */
   grant CREATE SCHEMA, MONITOR, USAGE
   on database identifier($var_database)
   to role identifier($var_role);
   
   /* ATTENTION! if you are looking to ingest and extract data from objects created within Rivery 
   and not objects that already exist you can stop the flow here */
   
   /* Grant access to all existing sachems on the database */
   grant ALL on all SCHEMAS IN DATABASE identifier($var_database) to ROLE identifier($var_role);

   /* Grant access to all  existing tables on the database, might take several minutes if there are many tables */
   grant ALL on all TABLES to ROLE identifier($var_role);
   
 
 commit; 
 

Creating a Network Policy for Rivery IPs [Optional]

In some cases, your Snowflake account may be access restricted by IPs or domains. In such cases, you must add Rivery IPs to your Snowflake Network Policy in order to connect successfully.

Note: Open Rivery IPs in Snowflake Network Policies may block any other unspecified IPs in the network policy. Make sure you've whitelisted all of your IPs in Snowflake’s network policies before creating Rivery's. Read more about network policies here .

In order to create a Network Policy for Rivery IPs:

  1. Log into your Snowflake account.

  2. Make sure the user is set to a ACCOUNTADMIN or SYSADMIN role

  3. In the worksheet, run the following command:

CREATE OR REPLACE NETWORK POLICY RiveryIPs ALLOWED_IP_LIST = (Copy our most recent whitelisted IPs here)

Create a Snowflake Connection

Next, setup Rivery for use with Snowflake ETL.

  1. Log into Rivery.

  2. Create a new connection for your Snowflake ETL instance:

    • Go to Connections.

    • Select New Connection

    • From the source list, choose Snowflake.

      image.png

  3. Now, enter the credentials information that you’ve created in Snowflake’s connection details:
    Snowflake Username
    Snowflake Password
    The correct Warehouse you want to run on
    Snowflake's Account Name . You can find this in your Snowflake URL

The account name is the trailing letter-number combination of the domain and the region id, if it exists, before the snowflakecomputing.com .

For example:

If your Snowflake web address is
https://mysfaccount.eu-central-1.snowflakecomputing.com
Your account name is mysfaccount.eu-central-1

  1. Optional : Set your Custom FileZone to save the data in your own staging area.
  2. You can test your connection by clicking the Test Connection button.
  3. Give your connection a name, and click Save.
    You can now pipe data to Snowflake by leveraging this connection in any river!

Was this article helpful?