Snowflake (Edge)

Prerequisites

• An active Snowflake account is available.

• You have either the ACCOUNTADMIN or SECURITYADMIN role in Snowflake to create a new role for Hevo.

• You have either the ACCOUNTADMIN or SYSADMIN role in Snowflake to create a warehouse.

• Hevo is assigned the USAGE permission on data warehouses.

• Hevo is assigned the USAGE and CREATE SCHEMA permissions on databases.

• Hevo is assigned the USAGE, MONITOR, CREATE TABLE, CREATE EXTERNAL TABLE, and MODIFY permissions on the current and future schemas.

Refer to the section Create and Configure your Snowflake Warehouse to create a Snowflake warehouse with adequate permissions for Hevo to access your data.

Perform the following steps to configure your Snowflake Destination in Edge:

(Optional) Create a Snowflake Account

Create and Configure your Snowflake Warehouse

Hevo provides you with a ready-to-use script to create the resources needed to configure your Snowflake Edge Destination.

Follow these steps to run the script:

1. Log in to your Snowflake account.

2. In the left navigation pane, under Projects, click Worksheets.

   

3. In the top right corner of the Worksheets tab, click the + icon and select SQL Worksheet from the drop-down to create a worksheet.

   

4. Copy the following script and paste it into the SQL Worksheet. Replace the sample values provided for warehouse_name and database_name (in lines 2-3 of the script) with your own. You can specify new warehouse and database names to create them now or use the names of pre-existing resources.

   -- Create variables for warehouse/database (needs to be uppercase for objects)
   set warehouse_name = 'HOGWARTS'; -- Replace "HOGWARTS" with the name of your warehouse
   set database_name = 'RON'; -- Replace "RON" with the name of your database
   
   begin;
   
   -- Change role to sysadmin for the warehouse/database steps
   use role sysadmin;
   
   -- Create a warehouse for Hevo only if it does not exist
   create
   warehouse if not exists identifier($warehouse_name)
   warehouse_size = xsmall
   warehouse_type = standard
   auto_suspend = 60
   auto_resume = true
   initially_suspended = true;
   
   -- Create a database for Hevo only if it does not exist
   create database if not exists identifier($database_name);
   
   commit;
   

   Note: The values for warehouse_name and database_name must be in uppercase.

5. Press CMD + A (Mac) or CTRL + A (Windows) inside the worksheet area to select the script.

6. Press CMD + return (Mac) or CTRL + Enter (Windows) to run the script.

7. Once the script runs successfully, you can specify the warehouse and database names (from lines 2-3 of the script) to connect your warehouse to Hevo while creating a Snowflake Destination in Edge.

Create a Snowflake User and Grant Permissions

Hevo does not need a user with the ACCOUNTADMIN role to connect to your Snowflake warehouse. You can create a non-administrative user and assign a custom role to it or use one of the admin roles. Hevo provides you with a ready-to-use script to create the user and grant it only the essential permissions required by Hevo to load data into your Snowflake warehouse.

Perform these steps to run the script:

1. Follow Steps 1-3 from the section above.

2. Copy the following script and paste it into the SQL Worksheet. Replace the sample values provided for role_name, user_name, and user_password (in lines 7-9 of the script) with your own. Also, substitute those for existing_warehouse_name and existing_database_name (in lines 2-3 of the script) with the names of pre-existing resources or those created by the script above.

   Note: The following script creates the role and user; it does not create the warehouse, database, or schema.

   -- Create variables for the resources created by the script in the Create and Configure your Snowflake Warehouse section
   set existing_warehouse_name = 'HOGWARTS'; -- Replace "HOGWARTS" with the name of the warehouse created earlier for Hevo
   set existing_database_name = 'RON'; -- Replace "RON" with the name of the database created earlier for Hevo
   
   -- Create variables for user/password/role/warehouse/database/schema
   set role_name = 'HEVO'; -- Replace "HEVO" with your role name
   set user_name = 'HARRY_POTTER'; -- Replace "HARRY_POTTER" with your username
   set user_password = 'Gryffindor'; -- Replace "Gryffindor" with the user password
   
   begin;
   
   -- Change the role to securityadmin for user/role steps
   use role securityadmin;
   
   -- Create a role for Hevo
   create role if not exists identifier($role_name);
   grant role identifier($role_name) to role SYSADMIN;
   
   -- Create a user for Hevo
   create user if not exists identifier($user_name)
   password = $user_password
   default_role = $role_name
   default_warehouse = $existing_warehouse_name;
   
   -- Grant access to the user
   grant role identifier($role_name) to user identifier($user_name);
   
   -- Grant the Hevo role access to the warehouse
   use role sysadmin;
   grant USAGE
   on warehouse identifier($existing_warehouse_name)
   to role identifier($role_name);
   
   -- Grant the Hevo role access to the database and existing schemas
   use role accountadmin;
   grant CREATE SCHEMA, MONITOR, USAGE, MODIFY
   on database identifier($existing_database_name)
   to role identifier($role_name);
   
   -- Grant the Hevo role access to future schemas and tables
   use role accountadmin;
   grant SELECT on future tables in database identifier($existing_database_name) to role identifier($role_name);
   grant MONITOR, USAGE, MODIFY on future schemas in database identifier($existing_database_name) to role identifier($role_name);
   
   commit;
   

   Note: The values for all the variables, such as role_name and user_name, must be in uppercase.

3. Press CMD + A (Mac) or CTRL + A (Windows) inside the worksheet area to select the script.

4. Press CMD + return (Mac) or CTRL + Enter (Windows) to run the script.

5. Once the script runs successfully, you can specify the values of user_name and user_password (from lines 8-9 of the script) when connecting to your Snowflake warehouse using access credentials.

   Note: Hevo recommends connecting to the Snowflake warehouse using key pair authentication.

If you are a user in a Snowflake account created after the BCR Bundle 2024_08, Snowflake recommends connecting to ETL applications, such as Hevo, through a service user. For this, run the following command:

ALTER USER <your_snowflake_user> SET TYPE = SERVICE;

Replace the placeholder value in the command above with your own. For example, <your_snowflake_user> with HARRY_POTTER.

Note: New service users will not be able to connect to Hevo via password authentication; they must connect with a key pair. Read Obtain a Private and Public Key Pair for the steps to create a key pair.

Obtain a Private and Public Key Pair (Recommended Method)

You can authenticate Hevo’s connection to your Snowflake data warehouse using a public-private key pair. For this, you need to:

1. Generate a private key.

2. Generate the public key for your private key.

3. Assign the public key to your Snowflake user.

1. Generate a private key

You can connect to Hevo using an encrypted or unencrypted private key.

Note: Hevo supports only private keys encrypted using the Public-Key Cryptography Standards (PKCS) #8-based triple DES algorithm.

Open a terminal window, and on the command line, do one of the following:

• To generate an unencrypted private key, run the command:

  openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out <unencrypted_key_name> -nocrypt
  

• To generate an encrypted private key, run the command:

  openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out <encrypted_key_name>
  

  You will be prompted to set an encryption password. This is the passphrase that you need to provide while connecting to your Snowflake Edge Destination using key pair authentication.

Note: Replace the placeholder values in the commands above with your own. For example, <encrypted_key_name> with encrypted_rsa_key.p8.

The private key is generated in the PEM format.

-----BEGIN ENCRYPTED PRIVATE KEY-----
MIIFJDBWBg...
----END ENCRYPTED PRIVATE KEY-----

Open the private key file and remove the extra blank space or empty line at the bottom of the file. Save the private key file in a secure location and provide it while connecting to your Snowflake Edge Destination using key pair authentication.

2. Generate a public key

To use a key pair for authentication, you must generate a public key for the private key created in the step above. For this:

Open a terminal window, and on the command line, run the following command:

openssl rsa -in <private_key_file> -pubout -out <public_key_file>

Note:

• Replace the placeholder values in the command above with your own. For example, <private_key_file> with encrypted_rsa_key.p8.

• If you are generating a public key for an encrypted private key, you will need to provide the encryption password used to create the private key.

The public key is generated in the PEM format.

-----BEGIN PUBLIC KEY-----
MIIBIjANBgk...
-----END PUBLIC KEY-----

Save the public key file in a secure location. You must associate this public key with the Snowflake user that you created for Hevo.

3. Assign the public key to a Snowflake user

To authenticate Hevo’s connection to your Snowflake warehouse using a key pair, you must associate the public key generated in the step above with the user that you created for Hevo. To do this:

1. Log in to your Snowflake account as a user with the SECURITYADMIN role or a higher role.

2. In the left navigation bar, click Projects, and then click Worksheets.

3. In the top right corner of the Worksheets tab, click the + icon to create a SQL worksheet.

   

4. Run the following command in the SQL worksheet:

   ALTER USER <your_snowflake_user> SET RSA_PUBLIC_KEY='<public_key>';
   
   // Example
   
   ALTER USER HARRY_POTTER set RSA_PUBLIC_KEY='MIIBIjANBgk...';
   

   Note:

   • Replace the placeholder values in the command above with your own. For example, <your_snowflake_user> with HARRY_POTTER.

   • Set the public key value to the content between -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY-----.

To check whether the public key is configured correctly, you can follow the steps provided in the verify the user’s public key fingerprint section.

Obtain your Snowflake Account URL

The organization name and account name are visible in your Snowflake web interface URL.

For most accounts, the URL looks like https://<orgname>-<account_name>.snowflakecomputing.com.

For example, https://hevo-westeros.snowflakecomputing.com. Here, hevo is the organization name and westeros is your account name.

Perform the following steps to obtain your Snowflake account URL:

1. Log in to your Snowflake instance.

2. Navigate to the bottom of the left navigation pane, click the account selector, and then hover over the Account section.

   

3. In the pop-up, locate the account whose URL you want to obtain and hover over it.

4. In the account details pop-up dialog, click the Copy icon to copy the account identifier, and save it securely.

   

The account identifier is provided in the format <orgname>.<account_name>. To convert it to the account URL, substitute the values of orgname and account_name in the format https://<orgname>-<account_name>.snowflakecomputing.com.

For example, if the account identifier is KRCHCUO.CN07513, the account URL is https://krchcuo-cn07513.snowflakecomputing.com.

Use this URL while configuring your Destination.