Integrating with Snowflake (Small Datasets)

Prerequisites

Snowflake Setup

Creating Storage Integration Step 1: Create storage integration with provider as GCS

Connecting via username & password Step 1: Create a new warehouse/use an existing warehouse Step 2: Create a user & role Step 3: Get Snowflake identifier details Step 4: Add permissions to tables Step 5: Provide access to warehouse Connecting via key-pair Step 1: Generate a private key Step 2: Generate a public key Step 3: Assign the public key to a Snowflake user

Drivetrain Setup Step 1: Connect to Snowflake Step 2: Import data from Snowflake

Prerequisites

To connect Snowflake to Drivetrain, you will need:

• Admin privileges in Snowflake

• Owners of a user, or users with the SECURITYADMIN role or higher in Snowflake to be able to alter a user (if connecting via key-pair) . For more information, see Overview of Access Control and GRANT OWNERSHIP.

• Admin permissions in Drivetrain.

Snowflake Setup

Creating Storage Integration

Step 1: Create storage integration with provider as GCS

1. Execute the below query command to create GCS integration.

CREATE STORAGE INTEGRATION gcs_int
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'GCS'
ENABLED = TRUE
STORAGE_ALLOWED_LOCATIONS = ('gcs://dt-data-import/{Tenant_ID}/');

1. Execute the below query to retrieve the Service Account details, get the property_value of "STORAGE_GCP_SERVICE_ACCOUNT" property and store it in a secured document.

// Some code
DESC STORAGE INTEGRATION gcs_int;

Connecting via username & password

Step 1: Create a new warehouse/use an existing warehouse

1. Go to Admin → Warehouses in the left navigation section.

1. We recommend creating a new warehouse to run data transfer queries for Drivetrain. However, you can also choose an existing data warehouse. If Warehouse is not present already, follow the next step to create one

1. Click on the top right button to create a new warehouse. Choose the Size as per the amount of data you need to query. You can change this later if needed.

Step 2: Create a user & role

1. We recommend creating a new user. However, you can also choose an existing user. If user is not already present, follow the next step to create one.

2. To create a user and role, go to Admin → Users & Roles in the left navigation section.

1. Select the Roles tab and click on the + Role button at the top right to create a custom role. This is an optional step and you can skip this if you already have a relevant role defined

1. Then click on the + User button at the top right and fill in the details. Please choose the role that you created in the step above. Choose the warehouse that we had created or selected in the previous section as the Default Warehouse. You can also choose some Default Namespace if needed (this is optional).

Step 3: Get Snowflake identifier details

1. To get this, click on the Account name next to the Snowflake icon at the bottom left menu. Mouse hover to the respective Snowflake Account.

2. Then click on the Copy icon. This will copy the Account Idetifier. This should be something like: ICKXXXX.ACCOUNT_NAME. Please note this as well.

Step 4: Add permissions to tables

1. Please go to Data → Databases in the left navigation bar. Select the tables that you want to give permissions for.

1. Click on the + Privilege icon and then select the role that you had created in the sections above. Choose the default privilege in the Privileges dropdown and click on Grant Privileges.

Step 5: Provide access to warehouse

1. Execute the below command to provide warehouse access to the role defined in Step 2.

GRANT USAGE ON WAREHOUSE EXAMPLE_WAREHOUSE TO ROLE EXAMPLE_ROLE;

EXAMPLE_WAREHOUSE = name of the warehouse as per Step 1.

EXAMPLE_ROLE = name of the warehouse as per Step 2.

Connecting via key-pair

Step 1: Generate a private key

1. Open a terminal window on your system and type the below command line:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

1. The commands generate a private key in PEM format.

-----BEGIN PRIVATE KEY----- 
MIIBIj... 
-----END PRIVATE KEY-----

1. Make a note of this key and store securely. You will need to enter this key on Drivetrain when connecting Drivetrain to Snowflake.

Step 2: Generate a public key

1. Open the same terminal window on which you ran the command to generate a private key and now run the below command line:

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

1. The commands generate a private key in PEM format.

-----BEGIN PUBLIC KEY-----
MIIE6T...
-----END PUBLIC KEY-----

1. Make a note of this key and store securely.

Step 3: Assign the public key to a Snowflake user

1. Execute an ALTER USER command to assign the public key to a Snowflake user.

ALTER USER example_user SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

example_user = username of the account you want to connect to Drivetrain.

Drivetrain Setup

Step 1: Connect to Snowflake

1. Open you Drivetrain account and navigate to the Data tab, click on + New data source.

2. Search for Snowflake.

3. Click on Connect.

4. Choose your preferred method for authentication and click on OK.

1. Fill all the fields with relevant details of your Snowflake account and click Submit. Follow the steps listed here to understand how to get your Snowflake account details such as Username, Warehouse and Database.

1. Click on Data tab again and you will see a new connection to Snowflake.

Step 2: Import data from Snowflake

1. Navigate to the new Snowflake connection and click on + Add data.

2. You will see a list of all available tables. The tables follow the below nomenclature:

Schema_Name.Table_Name

1. Select the tables from which you want to get data into Drivetrain and move them to the right side of the window.

1. Click Add. That's it! All your relevant data from Snowflake will now be available in Drivetrain for your use.