You can translate the document:

Goal

This document describes how to access Snowflake from Denodo using Basic authentication and Key pair authentication.

Content

Snowflake is an analytic data warehouse provided as Software-as-a-Service (SaaS), which uses cloud-based hardware and software.

In this document, to test the connectivity from the Denodo Platform to Snowflake the following software versions have been used:

Software

Version

Snowflake Client Driver

Snowflake JDBC 3.13.1

Product version

Snowflake 5.29.1

Connecting Snowflake from Denodo using Basic authentication

  • To connect to Snowflake, you need to create a JDBC data source within the Denodo Platform. Then you can create the base views that will represent the Snowflake tables.
  • From the Design Studio, create a new JDBC data source by navigating to  “File > New > Data source > JDBC”.

To create a connection, fill all the required fields:

  • Name: ds_snowflake
  • Database adapter: Snowflake
  • Database URI: use a connection string in the form of: jdbc:snowflake://host/?db=database&schema=schema&warehouse=warehouse

For example:  jdbc:snowflake://host/?db=SAMPLEKB_DB&schema=DKP&warehouse=VDP_WH

  • Username: Enter the username to connect to Snowflake
  • Password: Enter the appropriate password.

Click on Test connection to test the data source connection and click on the Save button to save the data source.

Connecting Snowflake from Denodo using key pair authentication

The Key Pair authentication support for Snowflake is available in their latest Snowflake  driver 3.12. Starting from Denodo 8 update 20210715, this driver is shipped with the Denodo platform. For versions prior to Denodo 8 update 20210715 you can  download the latest snowflake JDBC driver (3.12) from their official website.

Install the JDBC Client Driver in the Denodo Platform

Note: this step is required only for Denodo updates prior to  Denodo 8 update 20210715.

After downloading the driver from the Snowflake website, we need to install it. This can be done via Design Studio or the Virtual DataPort Administration Tool by navigating to File > Extension Management > Import.

 

Note: For previous versions of the Denodo Platform, instead of uploading the JDBC driver, copy the driver into <DENODO_HOME>\lib-external\jdbc-drivers\snowflake-3.12.0 on the machine where the Denodo Virtual DataPort server is installed.

Connectivity

In order to allow Denodo to access the Snowflake using key pair authentication, it is necessary to manually obtain private-public key pairs by following these three steps.

Step 1: Generate the Private Key

Step 2: Generate a Public Key

Step 3: Assign the Public Key to a Snowflake User

Step 1: Generate the Private Key

As a first step, to connect to Snowflake,  generate an encrypted or unencrypted private key using OpenSSL.

To begin, open an OpenSSL terminal window and generate a private key.

For an unencrypted version, use the following command in the  openssl:

genrsa -out keypair.pem 2048

rsa -in keypair.pem -pubout -out publickey.crt

pkcs8 -topk8 -inform PEM -outform PEM -nocrypt -in keypair.pem -out rsa_key.p8

Note: For an encrypted version, use the above command without  “-nocrypt

Step 2: Generate a Public Key

From the command line, generate the public key by referencing the private key. The following command is used to generate the public key.

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

The above command generates the public key in PEM format as follows:

Step 3: Assign the generated Public Key to a Snowflake User

In order to assign the public key to a Snowflake user, execute ‘alter user’ command as follows:

alter user <user_name> set rsa_public_key='MIIBIjANBgkqh...';

Create a JDBC Data Source

Open the Design Studio and go to File > New > Data source > JDBC.

 

Enter the connection details for your Snowflake database in the Connection tab:

  • Name: ds_snowflake
  • Database adapter: Snowflake
  • Driver class path : snowflake-1.x
  • Database URI: use a connection string in the form of: jdbc:snowflake://host/?db=database&schema=schema&warehouse=warehouse&authenticator=snowflake_jwt

For instance jdbc:snowflake://host/?db=SAMPLEKB_DB&schema=DKP&warehouse=VDP_WH&authenticator=snowflake_jwt

 

Then, in Advanced tab, add the below two parameters in the Driver Properties of the JDBC data source

  • Click on  Driver Properties.
  • Add two  New Properties:
  • user: Enter the username to connect to Snowflake
  • private_key_file: The  path to the private key file.

 

Click on Test connection to test the data source connection and click on the Save button to save the data source.

Once the data source is created, click on “Create base view” to create a base view to introspect source metadata available through the Data Source.

References

JDBC Sources

Supported JDBC Data Sources

Uploading a JDBC Driver

Snowflake JDBC Driver

Key Pair Authentication - Snowflake

Denodo and Snowflake

Snowflake Data Warehouse

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here