Snowflake SQL Dialect User Guide

Snowflake operates a platform that provides data storage via cloud computing and allows for data analysis.

Telemetry

This virtual schema uses telemetry-java to send anonymous feature-usage events.

For details on what is collected and how to disable telemetry, see the documentation.

Uploading the JDBC Driver to Exasol BucketFS

1. Download the SnowflakeJDBC driver.

2. Upload the driver to BucketFS, see BucketFS documentation.

   Hint: Put the driver into folder default/drivers/jdbc/ to register it for ExaLoader, too.

Registering the JDBC driver for ExaLoader

In order to enable the ExaLoader to fetch data from the external database you must register the driver for ExaLoader as described in the Installation procedure for JDBC drivers.

1. ExaLoader expects the driver in BucketFS folder default/drivers/jdbc.
   If you uploaded the driver for UDF to a different folder, then you need to upload the driver again.
2. Additionally, you need to create file settings.cfg and upload it to the same folder in BucketFS. Contents below:

DRIVERNAME=SNOWFLAKE_JDBC_DRIVER
JAR=<jar file containing the jdbc driver>
DRIVERMAIN=net.snowflake.client.api.driver.SnowflakeDriver
PREFIX=jdbc:snowflake:
FETCHSIZE=100000
INSERTSIZE=-1
NOSECURITY=YES

Make sure there's an empty line at the end of the settings.cfg file, as shown above, or it will not be properly read out, the EXALoader will display an error message.

Variable	Description
<jar file containing the jdbc driver>	E.g. snowflake-jdbc-4.1.0.jar

Please note that you will need to change the driver name from net.snowflake.client.jdbc.SnowflakeDriver to net.snowflake.client.api.driver.SnowflakeDriver when you upgrade the Snowflake JDBC driver from version 3.x to 4.x. See the Snowflake documentation for details.

Installing the Adapter Script

Upload the latest available release of Snowflake Virtual Schema JDBC Adapter to Bucket FS.

Then create a schema to hold the adapter script.

CREATE SCHEMA ADAPTER;

The SQL statement below creates the adapter script, defines the Java class that serves as entry point and tells the UDF framework where to find the libraries (JAR files) for Virtual Schema and database driver.

--/
CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.SNOWFLAKE_JDBC_ADAPTER AS
  %scriptclass com.exasol.adapter.RequestDispatcher;
  %jar /buckets/<BFS service>/<bucket>/virtual-schema-dist-14.0.4-snowflake-1.0.1.jar;
  %jar /buckets/<BFS service>/<bucket>/drivers/jdbc/snowflake-jdbc-<snowflake-driver-version>.jar;
/

Defining a Named Connection

Define the connection to the Snowflake database as shown below.

CREATE OR REPLACE CONNECTION SNOWFLAKE_CONNECTION
TO 'jdbc:snowflake://<account identfier>.snowflakecomputing.com?JDBC_QUERY_RESULT_FORMAT=JSON'
USER '<user>'
IDENTIFIED BY '<password>';

Account Identifier

The account identifier in the JDBC URL is usually in the form of xxxxxxx-yyyyyyy: It is part of the specific login url you get upon registering (e.g.:igzdtnt-du40000 in https:// igzdtnt-du40000 .snowflakecomputing.com/). It can also be found under the admin panel in Snowflake. For more info see: https://docs.snowflake.com/en/user-guide/admin-account-identifier

Specify JSON Format for Fetching Results

Please note that starting with Snowflake JDBC driver version 4.x you need to specify JDBC URL parameter JDBC_QUERY_RESULT_FORMAT=JSON. Without this, queries against the virtual schema will fail with the following error:

TL-5402: JDBC-Client-Error: Failed to initialize Query: JDBC driver internal error: exception creating result java.lang.ExceptionInInitializerError at net.snowflake.client.jdbc.internal.apache.arrow.memory.unsafe.UnsafeAllocationManager.<clinit>(UnsafeAllocationManager.java:28)

Creating a Virtual Schema

Use the following SQL statement in the Exasol database to create a Snowflake Virtual Schema (Make sure to use UPPERCASE for the catalog and schema name):

CREATE VIRTUAL SCHEMA <virtual schema name>
  USING ADAPTER.SNOWFLAKE_JDBC_ADAPTER
  WITH
  CATALOG_NAME = '<catalog name>'
  SCHEMA_NAME = '<schema name>'
  CONNECTION_NAME = 'SNOWFLAKE_CONNECTION';

Variable	Description
<virtual schema name>	Name of the virtual schema you want to use.
<catalog name>	Name of the catalog, usually equivalent to the name of the Snowflake database. Please use UPPERCASE.
<schema name>	Name of the database schema you want to use in the Snowflake database. Please use UPPERCASE.

For additional parameters (optional), see also Adapter Properties for JDBC-Based Virtual Schemas.

Data Types Conversion

Snowflake Data Type	Supported	Converted Exasol Data Type	Known limitations
NUMBER,DECIMAL , DEC , NUMERIC	✓	DECIMAL(36,0)	Precision > 36 is not supported.
INT , INTEGER , BIGINT , SMALLINT , TINYINT , BYTEINT	✓	VARCHAR(2000000)	Alias for NUMBER(38,0) in Snowflake
BOOLEAN	✓	BOOLEAN
VARCHAR,CHAR, CHARACTER, NCHAR, STRING, TEXT, NVARCHAR, NVARCHAR2, CHAR VARYING, NCHAR VARYING	✓	VARCHAR
DATE	✓	DATE
FLOAT,DOUBLE PRECISION,DOUBLE , REAL	✓	DOUBLE
TIME	✓	TIME
TIMESTAMP	✓	TIMESTAMP
TIMESTAMP WITH TIME ZONE	✓	TIMESTAMP (UTC)