////
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at
  http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.
////
[[database-plugins-snowflake]]
:documentationPath: /database/databases/
:language: en_US
:imagesdir: ../../../assets/images

= Snowflake

== Configuration

[cols="2*",options="header"]
|===
| Option | Info
|Type | Relational
|Driver | Included
|Version Included | 3.23.1
|Hop Dependencies | None
|Documentation | https://docs.snowflake.net/manuals/user-guide/jdbc-configure.html[Documentation Link]
|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.

[source,sql]
----
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';
----

image::database/databases/snowflake/snowflake_service_user_create.png[Snowflake Service Account Creation]


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

https://repo1.maven.org/maven2/net/snowflake/snowflake-jdbc/[Download^] 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 https://www.bouncycastle.org/download/bouncy-castle-java-lts/[download^]
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`

image::database/databases/snowflake/bcprov.png[Bouncy Castle Provider Jar,width="90%", caption="Bouncy Castle Provider for Java 15 to 18"]

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

image::database/databases/snowflake/bcpkix.png[Bouncy Castle Crypt Lib Jar,width="90%", caption="Bouncy Castle Crypto Library for Java 15 to 18"]

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.

image::database/databases/snowflake/snowflake_and_bc_jars.png[Snowflake and Bouncy Castle Jars in the Hop Lib JDBC folder,width="90%, caption="Snowflake JDBC and Bouncy Castle Jars"]


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

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

WARNING: 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.

image::database/databases/snowflake/private_key_file_in_hop_root.png[RSA Private Key in Hop Root,width="90%", caption="RSA Private Key in Hop root folder"]


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

image::database/databases/snowflake/hop-connection-general-tab.png[Hop Connection - General Tab,width="90%", caption="Hop Database Connection - General Tab"]

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.

image::database/databases/snowflake/hop-connection-options-tab.png[Hop Connection - Options Tab,width="90%", caption="Hop Database Connection - Options Tab"]

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.

image::database/databases/snowflake/snowflake_account_url.png[Finding the Snowflake Account URL,width="90%", caption="Snowflake User Console, finding your database URL"]

=== References

References consulted to produce this working configuration

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

Snowflake Account Identifiers

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

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

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