KNIME Snowflake Extension Guide

Once you have downloaded and installed the KNIME Analytics Platform as described here you need to install the KNIME Snowflake Integration. To do so you have two options:

KNIME Hub:

KNIME Analytics Platform:

For more information on how to install Extensions and Integrations see the KNIME Analytics Platform Installation Guide.

Once you have installed the KNIME Snowflake Integration, you can find the Snowflake Connector node in the DB → Connection subcategory in the Node Repository. The Snowflake Connector node creates a connection to the Snowflake database via the Snowflake JDBC driver.

In the configuration dialog of the Snowflake Connector node you need to provide information such as account name, virtual warehouse, and the user credentials. To open the configuration dialog, double-click the node. For the full account name the domain .snowflakecomputing.com will be appended automatically. Note that your full account name might include additional segments that identify the region and cloud platform where your account is hosted e.g. xy12345.us-east-2.aws, xy12345.us-central1.gcp or xy12345.west-us-2.azure that need to be entered as well.

Once you have provided all necessary information click OK and execute the node to establish a connection.

The Snowflake Data Marketplace allows Snowflake users to access curated data from different data providers. Once you have access to a requested database it will show up in the Database Metadata Browse e.g. when clicking the Select a table button of the DB Table Selector node. To access any table or view simply double click its name. The database, schema and table/view name is then automatically entered into the corresponding fields in the node dialog. If you are writing your own statements you might need to qualify each table or view using the database and schema name it is located in.

To upload a large amount of data you can use the DB Loader node. The node either writes a CSV or Parquet file into a Snowflake stage prior loading the data into the specified table using the Snowflakes COPY command.

In the node dialog you can specify the existing database table you want to upload the data to. Depending on your use case you can choose between CSV and Parquet as data exchange formats. CSV processing is faster but might cause problems with complex string values whereas Parquet has better type support but is slower to process.

In the stage section select a stage where you have write access to. The uploaded file will be automatically deleted once the data is loaded into the destination Snowflake table.

Depending on the selected file format the Advanced tab offers you different options to further define the characteristics of the exchanged file such as the compression method, quote characters or file and chunk size.

KNIME Analytics Platform supports model push-down into Snowflake. This allows you to perform data prediction within Snowflake without the need to move the data out of Snowflake. Supported are H2O MOJO models that can be learned via the KNIME H2O Machine Learning Integration but also via KNIME H2O Sparkling Water Integration where the model learning is performed at scale within a Spark runtime.

The different Snowflake H2O MOJO Predictor nodes create a temporary User-Defined Function (UDF) in the default database and schema that lives as long as the Snowflake session is not closed. To create the function KNIME uploads the model as well as all required dependencies to a temporary stage created in the default database and schema in Snowflake. To successfully execute the nodes require the USAGE rights on the DATABASE and the SCHEMA.

For more information about the H2O Integration check out the H2O blog post.

The following screenshot shows an example flow that learns a Random Forest model using a local H2O context which is then registered as UDF and used to predict the classes for a database table with previously unseen data. The prediction result is then stored in a Snowflake table. For another example see the following blog post.

In order to use Microsoft Entra ID authentication you need to create a new application registration for the Snowflake OAuth Resource in your Microsoft Entra ID as described in Step 1 of the Snowflake documentation. You do not need to create an OAuth Client (Step 2) since KNIME Analytics Platform will be the OAuth client that uses the registered application. But you will need to register the Snowflake OAuth Resource in Snowflake as described in Step 3 and 4 of the documentation.

Once everything is set up correctly you need to enter one scope of the Snowflake OAuth Resource into the Other field of the Microsoft Authentication node. The scope is a URL that looks like the following:

For more details on how to use the scopes in KNIME see the Azure Active Directory Authentication section above.

The KNIME Snowflake Integration comes with a built- in Snowflake driver and is thus ready to go. However because of some special circumstances you might be required to use a particular version of the driver which you can do by manually registering your own JDBC driver.

To register your own Snowflake JDBC driver, you need to download the JDBC driver as described in the Snowflake documentation. Once you have downloaded the single jar file. Open KNIME Analytics Platform and go to File → Preferences → KNIME → Databases.

Clicking Add will open a new database driver window where you can provide the JDBC driver path and all necessary information. In the dialog enter a unique identifier and name and make sure to select snowflake as database type. Once the database type is selected the URL template is automatically generated to be compatible with the Snowflake driver and usually does not need to be altered. Click Add file and browse for the downloaded driver jar file. Finally click Find driver classes. Once satisfied, close the dialog by hitting OK.

Once the driver is registered you can select it in the Snowflake Connector node.

First of all you will need to generate the key files following Snowflake documentation.

Once you have created the key files, store them in a secure location. You then need to provide the path to the location to the Snowflake Connector node.

To do this follow these steps: