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.30

Product version

Snowflake 8.8.3

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.
  • Create a new JDBC data source from the Design Studio by navigating to  “File > New > Data Source” and selecting the Snowflake data source.

Note: For older Denodo Platform versions, navigate to  “File > New > Data source > JDBC”.

To create a connection, fill in all the required fields:

  • Name: ds_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.

Note: For older Denodo Platform versions, choose the Database adapter as Snowflake. The driver class & sample URI will be populated automatically.

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 Snowflake Data Source

Create a new JDBC data source from the Design Studio by navigating to  “File > New > Data Source” and selecting the Snowflake datasource.

Note: For older Denodo Platform versions, navigate to  “File > New > Data source > JDBC”.

To create a connection, fill in all the required fields:

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

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

Note: For Denodo Platform 8.0 versions, choose the Database adapter as Snowflake. The driver class & sample URI will be populated automatically.

Then, in Advanced tab, expand the  Driver Properties and add the below two parameters,

  • 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.

Tracking Denodo queries

Snowflake query tags can be used to associate any kind of information to any query. They are a session level parameter that allows you to tag any Snowflake SQL statement with a string value; a default value can also be set at user level.

Snowflake query tags can be used to monitor the performance of the queries. Here we are going to see how to use them to track the queries coming from a Denodo data source.

When creating a Denodo JDBC data source it is possible to define an Initial SQL Sentence that will be executed before the execution of every query on the data source, this option is available under the Advanced tab.

These SQL sentences may contain interpolation variables that will be replaced with the variable values available in the context of the query. By default, some variables are already defined in the context of any query, one of those variables is the QUERY_ID variable that represents the id of the Denodo query that is being delegated to the data source.

Using this QUERY_ID and the Snowflake syntax to set the query tag we can use the following statement as Initial SQL Sentence:

alter session set query_tag='Denodo query @QUERY_ID';

After adding this configuration to any Snowflake data sources, all Snowflake queries will have a query tag with the id of the Denodo query that was delegated to Snowflake.

With this information that can be obtained from the query history in Snowflake and the Denodo logs where the query ids are logged it will be possible to identify and monitor all queries delegated from Denodo to Snowflake.

When enabling Initial SQL sentences in a JDBC data source, by default, the connections in the connection pool will not be reused after a query is executed using a connection. This is to avoid issues when the Initial SQL sentence is not always executed (for instance, when the interpolation variable is not defined) and running different queries without running the Initial SQL sentence is undesired, for instance, if the sentence sets a session level value, different queries could share the same value.

In this scenario, the QUERY_ID interpolation variable will always be defined so the Initial SQL sentence will always be executed before every query to the data source. It is safe to enable the reuse of connections in the pool, to do so we can run the following command from a VQL shell:

SET 'com.denodo.vdb.misc.connection.jdbc.JDBCRoute.returnConnectionToPoolAfterInitialSQL.<database_name>.<datasource_name>'='true';

replacing <database_name> and <datasource_name> with the actual values for the virtual database and the Snowflake data source.

NOTE: Do not set this property in a general scenario where there is no guarantee that the Initial SQL Sentence will be executed every time a query is executed on the data source.

References

JDBC Sources

Supported JDBC Data Sources

Uploading a JDBC Driver

Snowflake JDBC Driver

Key Pair Authentication - Snowflake

Snowflake Data Warehouse

Disclaimer
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.

Questions

Ask a question

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