Snowflake

You can use Snowflake as a target data platform in a data pipeline or in a replication task. In a data pipeline, various ELT operations can be performed on the target platform including storing data, transforming data, creating data marts, and registering data. A replication task, on the other hand, involves replication of data directly from a source system to a target system with basic transformation capabilities, but without support for ELT operations.

Setting up Snowflake as a target involves:

• Configuring the Required permissions on the database.
• Installing the required driver. Note that this is only required if you are connecting to Snowflake via Data Movement gateway.
• Configuring a connection to Snowflake

Setting connection properties

To configure the connector, do the following:

1. In Connections, click Create connection.

2. Select the Snowflake target connector and then provide the following settings:

Data Movement gateway

Data gateway

A Data Movement gateway is only required if the target database is not accessible from Qlik Cloud and can only be accessed using a Private Link (for instance, if it's located in a Virtual Private Cloud). If this is the case, select the Data Movement gateway through which you want to access the target database.

Depending on your use case, this will either be the same Data Movement gateway deployed to move data from the data source, or a different one.

For information about Data Movement gateway use cases, see When is Data Movement gateway required? and Common use cases.

If the target database is directly accessible from Qlik Cloud, select None.

Connection properties

• Server: Your URL for accessing Snowflake. For example, myaccount.snowflakecomputing.com.

• Port: The port through which you want to access your Snowflake database. The default is 443.

Authentication

There are different ways of authenticating a connection:

• Username and password

• Key Pair

  Your Snowflake database must be configured to use key pair authentication. The process is described in Snowflake documentation:

  • Configuring Key Pair Authentication

  Information noteIf you use Data Movement gateway, version 2024.5.14 or higher is required to use key pair authentication.

Account properties

Username and password

User Name and Password: The user name and password of a user authorized to access the Snowflake database.

Key Pair

Database properties

• Default Warehouse: The name of your Snowflake warehouse. You can override this in your project settings.

• Database name: There are two methods you can use to specify a database:

  • Method 1 - Select from a list: Click Load databases and then select a database.

  • Method 2 - Manually: Select Enter database name manually and then enter the database name.

Internal properties

Internal properties are for special use cases and are therefore not exposed in the dialog. You should only use them if instructed by Qlik Support.

Use the and buttons to the right of the fields to add or remove properties as needed.

Name

The display name for the connection.

Prerequisites

Required permissions

The following permissions are required:

Permissions required if you want schemas that do not exist to be created automatically

• USAGE ON DATABASE
• CREATE SCHEMA ON DATABASE

Permissions required if the schema already exists

• USAGE ON DATABASE
• USAGE ON SCHEMA
• CREATE FILE FORMAT ON SCHEMA
• CREATE STAGE ON SCHEMA
• CREATE TABLE ON SCHEMA

Driver setup

A driver is only required if you are accessing the database via Data Movement gateway.

You can install the driver using the driver installation utility (recommended) or manually. Manual installation should only be attempted in the unlikely event that you encounter an issue with the driver installation utility.

Using the driver installation utility to install the driver

This section describes how to install the required driver. The process involves downloading the driver, copying it to the required folder (see below), and running a script that will automatically install and configure it. You can also run scripts to update and uninstall the driver as needed.

Preparing the installation

• Make sure that Python 3.6 or later is installed on the Data Movement gateway server.

  Python comes preinstalled on most Linux distributions. You can check which Python version is installed on your system, by running the following command:

  python3 --version

• Download the Snowflake ODBC driver "snowflake-odbc-<version>.x86_64.rpm". You can find a direct download link for the supported version under binary-artifacts in /opt/qlik/gateway/movement/drivers/manifests/snowflake.yaml. Once the download completes, copy the file to:

  /opt/qlik/gateway/movement/drivers/snowflake

Installing the driver

To install the driver:

1. Stop the Data Movement gateway service:

   sudo systemctl stop repagent

2. Optionally, confirm that the service has stopped:

   sudo systemctl status repagent

   The status should be as follows:

   Active: inactive (dead) since <timestamp> ago

3. On the Data Movement gateway machine, change the working directory to:

   opt/qlik/gateway/movement/drivers/bin

4. Run the following command:

   Syntax:

   ./install snowflake

   Otherwise, the EULA for the driver will be displayed.

5. Do one of the following:

   • Press [Enter] repeatedly to slowly scroll through the EULA.
   • Press the Spacebar repeatedly to quickly scroll through the EULA.
   • Press q to quit the license text and be presented with the EULA acceptance options.

6. Do one of the following:

   • Type "y" and press [Enter] to accept the EULA and begin the installation.
   • Type "n" and press [Enter] to reject the EULA and exit the installation.

   • Type "v" and press [Enter] to view the EULA again.

The driver will be installed.

7. Wait for the installation to complete (indicated by "Complete!") and then start the Data Movement gateway service:

   sudo systemctl start repagent

8. Optionally confirm that the service has started:

   sudo systemctl status repagent

   The status should be as follows:

   Active: active (running) since <timestamp> ago

Updating the driver

Run the update command if you want to uninstall previous versions of the driver before installing the provided driver.

To update the driver:

1. Stop the Data Movement gateway service:

   sudo systemctl stop repagent

2. Optionally, confirm that the service has stopped:

   sudo systemctl status repagent

   The status should be as follows:

   Active: inactive (dead) since <timestamp> ago

3. On the Data Movement gateway machine, change the working directory to:

   opt/qlik/gateway/movement/drivers/bin

4. Run the following command:

   Syntax:

   ./update snowflake

   Otherwise, the EULA for the driver will be displayed.

5. Do one of the following:

   • Press [Enter] repeatedly to slowly scroll through the EULA .
   • Press the Spacebar repeatedly to quickly scroll through the EULA.
   • Press q to quit the license text and be presented with the EULA acceptance options.

6. Do one of the following:

   • Type "y" and press [Enter] to accept the EULA and begin the installation.
   • Type "n" and press [Enter] to reject the EULA and exit the installation.
   • Type "v" and press [Enter] to review the EULA from the beginning.

The old driver will be uninstalled and the new driver will be installed.

7. Wait for the installation to complete (indicated by "Complete!") and then start the Data Movement gateway service:

   sudo systemctl start repagent

8. Optionally confirm that the service has started:

   sudo systemctl status repagent

   The status should be as follows:

   Active: active (running) since <timestamp> ago

Uninstalling the driver

Run the uninstall command if you want to uninstall the driver.

To uninstall the driver:

1. Stop all tasks configured to use this connector.

2. On the Data Movement gateway machine, change the working directory to:

   opt/qlik/gateway/movement/drivers/bin

3. Run the following command:

   Syntax:

   ./uninstall snowflake

   The driver will be uninstalled.

Manually installing the driver

You should only attempt to install the driver manually if the automated driver installation did not complete successfully.

You need to install both an ODBC driver and a JDBC driver.

Installing the ODBC driver

1. After Data Movement gateway is installed, download the snowflake-odbc-<version>x86_64.rpm driver file listed under binary-artifacts in /opt/qlik/gateway/movement/drivers/manifests/snowflake.yaml, and copy it to the Data Movement gateway machine.
2. Install the driver on the Data Movement gateway machine.

Installing the JDBC driver

1. Download the snowflake-jdbc-<version>.jar file listed under binary-artifacts in /opt/qlik/gateway/movement/drivers/manifests/snowflake.yaml, and copy it to the following folder on the Data Movement gateway machine:

   /opt/qlik/gateway/movement/qcs_agents/qdi-db-commands/lib

2. Restart the Data Movement gateway service and check that it has started by running the commands described in Data Movement gateway service commands

Port

Firewall port 443 needs to be opened for outbound communication.

Performance and cloud services usage optimization

Limitations and considerations

The following limitations apply when moving data to a Snowflake target:

• Replication of tables with backslashes in their names (e.g. SPECIAL\\TABLE\N) is not supported.

Data types

The following table shows the Snowflake data types that are supported when using Qlik Cloud and the default mapping from Qlik Cloud data types.

Native data type information is preserved, and is displayed in the Native data type column in dataset views. If the column is not visible, you need to open the column picker of the data set view and select the Native data type column.

Supported data types

Qlik Cloud data types	Snowflake data types
BOOLEAN	BOOLEAN
BYTES	BINARY (length in Bytes)
DATE	DATE
TIME	TIME (precision)
DATETIME	TIMESTAMP_NTZ (precision)
INT1	BYTEINT
INT2	SMALLINT
INT4	INTEGER
INT8	BIGINT
NUMERIC	NUMBER (precision, scale)
REAL4	FLOAT4
REAL8	FLOAT8
UINT1	BYTEINT
UINT2	INTEGER
UINT4	INTEGER
UINT8	BIGINT
STRING	VARCHAR (length in Bytes)
WSTRING	NVARCHAR (length in Bytes)
BLOB	BINARY (8388608)
NCLOB	NVARCHAR (8388608)
CLOB	VARCHAR (8388608)