Snowflake Configuration (Azure)

Snowflake is a cloud data warehouse software built on top of Amazon Web Services or Microsoft Azure infrastructure. Similar to the Qrvey product, it functions as a SaaS (Software as a Service) platform.

Before You Begin

• Snowflake Account: A Snowflake account is required to access the Snowflake Data Cloud.
• Snowflake User with Database Permissions: A dedicated Snowflake user with sufficient permissions to interact with the necessary databases.

Step 1: Configure Snowflake

Complete the following steps to configure communication between Snowflake and Azure.

Create a Snowflake Storage Integration Object

The storage integration object stores a generated service principal for your Azure cloud storage, along with an optional set of allowed or blocked storage locations (such as containers). Cloud provider administrators in your organization grant permissions for the storage locations to the generated service principal. This object eliminates the need to supply credentials when creating stages or loading data.

Create a storage integration object using the CREATE STORAGE INTEGRATION command.

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'AZURE'
  ENABLED = TRUE
  AZURE_TENANT_ID = '<tenant_id>'
  STORAGE_ALLOWED_LOCATIONS = ('azure://<StorageAccountName>.blob.core.windows.net/<container>/<path>/')

Property	Value
Integration Name	Name of the integration.
Azure Tenant ID	To find your tenant ID, log into the Azure portal and search for Tenant Properties. The tenant ID is displayed in the Tenant ID field.
Storage Allowed Locations	Specify allowed or blocked access to containers. You can find the StorageAccountName in the resource group of your deployment. Replace <container>/<path> with dataload-drdatacommons/sql_export.

Step 2: Grant Access to the Storage Account

In the Snowflake console, execute the following command:

DESC STORAGE INTEGRATION integration_name;

Where:

• integration_name is the name of the storage integration object created in Step 1.

Record the values in the following columns:

• AZURE_CONSENT_URL - URL to the Microsoft permissions request page.
• AZURE_MULTI_TENANT_APP_NAME - Name of the Snowflake client application created for your account.

1. Navigate to the URL in the AZURE_CONSENT_URL column. The page displays a Microsoft permissions request.

2. Select Accept to grant the Azure service principal created for your Snowflake account access to specified resources.
   The page redirects to the Snowflake corporate site (snowflake.com).

3. Log into the Microsoft Azure portal.

4. Navigate to Azure Services > Storage Accounts. Select the storage account that requires access.

5. Select Access Control (IAM) > Add role assignment.

6. Select a role to grant to the Snowflake service principal:

   • Storage Blob Data Reader: Grants read-only access.
   • Storage Blob Data Contributor: Grants read and write access, including the ability to execute the REMOVE command.

7. Search for the Snowflake service principal. Use the string preceding the underscore in the AZURE_MULTI_TENANT_APP_NAME property.

Step 3: Grant Access to the Storage Location

GRANT USAGE ON INTEGRATION <azure_int> TO ROLE <myrole>;

• <myrole> - Name of the role used by the Snowflake user.
• <azure_int> - Name of the storage integration object created in Step 1.

Step 4: Create RSA Keys

Generate a private key and public key to store as Kubernetes secrets. Use the openssl command or a similar tool.

1. Generate a private key:

   openssl genpkey -algorithm RSA -out private-key.pem

2. Generate a public key:

   openssl rsa -pubout -in private-key.pem -out public-key.pem

3. Assign the public key to the Snowflake user:

   ALTER USER USER_NAME SET RSA_PUBLIC_KEY = public_key;

Note: Remove all line breaks from the public key before assigning it in the Snowflake console.

Step 5: Create Secrets on Kubernetes

Use the following command to create the secret:

kubectl create secret generic <snowflakesecret> --from-literal=account=<SNOWFLAKEACCOUNT> --from-literal=username=<user> --from-literal=password=<password> --from-file=rsaPrivateKey=./private-key.pem --from-literal=storageIntegration=<integration> -n qrveyapps

Properties:

Property	Description
account	Snowflake account, formatted as accountid.region.
username	Username for Snowflake operations.
password	Password for the user.
rsaPrivateKey	Private key for generating API tokens.
storageIntegration	Name of the storage integration object.

Step 6: Create a Snowflake Connection

1. In Qrvey, go to Datasets > New Connections.
2. Create a new Snowflake connection.
3. Test the connection.