# Snowflake

## Snowflake

### Configuration

| Option           | Info                                                                                    |
| ---------------- | --------------------------------------------------------------------------------------- |
| Type             | Relational                                                                              |
| Driver           | Included                                                                                |
| Version Included | 3.23.1                                                                                  |
| Hop Dependencies | None                                                                                    |
| Documentation    | [Documentation Link](https://docs.snowflake.net/manuals/user-guide/jdbc-configure.html) |
| JDBC Url         | `jdbc:snowflake://<account_name>.snowflakecomputing.com/?<connection_params>`           |
| Driver folder    | \<Hop Installation>/lib/jdbc                                                            |

### Establishing a Snowflake SSL Authenticated Connection.

#### Introduction

This short guide will take us through creating a Snowflake user that is configured as a service account with a public key attached and get Apache Hop configured so that it can have a connection that uses the private key, role, compute etc. to carry out further work.

#### Create an RSA Keypair and the Hop Service User in Snowflake

To get started, you should create an rsa keypair for the service user you want to use with Hop when connecting to Snowflake.

You can generate an RSA keypair on most Linux / WSL (Windows Subsystem for Linux) by running the following command in the terminal window

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

The rsa\_key.pub file contents is what you will then use in your Snowflake `CREATE USER` statement.

```highlight
USE ROLE SECURITYADMIN;

CREATE USER SVC_ETL_HOP_USER_TEST
    DEFAULT_WAREHOUSE = ING_DEV_WH
    DEFAULT_NAMESPACE = DB_DEV
    DEFAULT_ROLE = SVC_ETL_DEV_ROLE
    TYPE = SERVICE
    RSA_PUBLIC_KEY = 'public key copy paste in single quotes'
    COMMENT = 'Test ETL service user';
```

<figure><img src="/files/8ER7VtQ4Z4u0UCuyRDP0" alt=""><figcaption></figcaption></figure>

In the screenshot, we can define all the properties we intend the user to take on, including which default compute (warehouse) to use, namespace (database), and role. We also define a special attribute `TYPE = SERVICE` to ensure the user cannot log in using a sign-in page but only programmatically.

#### Download the Snowflake JDBC driver an Crypto Libraries

[Download](https://repo1.maven.org/maven2/net/snowflake/snowflake-jdbc/) a current Snowflake JDBC Driver. The filename has the naming pattern **snowflake-jdbc-3.19.0.jar**

To support certificate based authentication, we also need to [download](https://www.bouncycastle.org/download/bouncy-castle-java-lts/) two jar files from Bouncy Castle, who make a famous Java Crypto API library.

The first is a provider jar whose name indicates the JRE/JDK version supported. Make sure you download the correct version for your JDK that matches the version Hop requires. This first file to download is `bcprov-jdk<VERSION>.jar`

<figure><img src="/files/VXBrAp7ms2U9ZbTWMbaY" alt=""><figcaption></figcaption></figure>

Secondly, we need the encryption library, which also has JDK version support in the filename. The filename is `bcpkix-jdk<version>.jar.`

<figure><img src="/files/tnmQgZ0MUbSp2y51PDkK" alt=""><figcaption></figcaption></figure>

The JDBC driver and two Bouncy Castle cryptography library jar files need to go into `hop/lib/jdbc` . Be sure to delete any older version of the Snowflake JDBC driver jar you find in `hop/lib/jdbc`. The Snowflake drivers are well-maintained and updated often.

<figure><img src="/files/xFoeqjjwHkKiYqqOlwAE" alt=""><figcaption></figcaption></figure>

#### Place the RSA Private Key in Hop’s Folder

The private key file must be stored in Hop’s root folder.

{% hint style="danger" %}
There could be ways to include the private key from other paths than the Hop root folder (e.g. defined in the connection’s `Options` tab), but this is currently still unclear.
{% endhint %}

<figure><img src="/files/YMHSPwguq32dpA8adxVO" alt=""><figcaption></figcaption></figure>

#### Gather Snowflake Connection Properties

Let us begin with the end result and then describe where each of the properties came from and why.

Most connections in Hop use typical fields like `Server host name`, `Port number`, `Warehouse`, `Database name`, but since this is a more advanced connection configuration, we need to take advantage of extra JDBC parameters that we can set on the `Options` tab shown below.

<figure><img src="/files/rzQweQWXDSyBEp4EOkrK" alt=""><figcaption></figcaption></figure>

Looking at the `Options` tab, we make use of a few keywords, **`authenticator`, `private_key_file`, `role`, `schema`,** and `user`. The parameters are from the Snowflake JDBC parameters documentation site.

We first tell the JDBC driver that we are going to use `snowflake_jwt` for authenticating, which means that it will expect to see some kind of private and public key.

In this case, the `private_key_file` parameter is used. This could for example come from a certificate from an AWS Secret Store. In this case, the certificate or key file doesn’t have to be left in the environment and are disposed of when the image goes away.\
Other variables would allow you to encode it (BASE64) etc.

<figure><img src="/files/t4RnmYJko7Ex5HnUNEoS" alt=""><figcaption></figcaption></figure>

The URL for the Server host name can be retrieved by clicking on your name in the Snowflake console on the lower left corner, select your instance, and then there is a little link icon.\
When you paste this into Hop’s dialog box, get rid of the `HTTPS://` part as it is not required. The connections are always encrypted.

<figure><img src="/files/Wntf0irbfCM6WiHmYefy" alt=""><figcaption></figcaption></figure>

#### References

References consulted to produce this working configuration

[JDBC Configure](https://docs.snowflake.com/en/developer-guide/jdbc/jdbc-configure)

Snowflake Account Identifiers

[Admin account identifier](https://docs.snowflake.com/en/user-guide/admin-account-identifier)

Snowflake JDBC Connection Parameters\
(`authenticator`, `private_key_file`, `role`, `schema`, `user`)

[JDBC parameters](https://docs.snowflake.com/en/developer-guide/jdbc/jdbc-parameters)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.primeur.com/data-shaper-1.21/knowing-the-data-shaper-designer/relational-database-connections/snowflake.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.