Dremio Snowflake Connector

17.0.0 Release

If you are running Dremio 17.0.0+ release please download the latest version of the plugin here: https://github.com/narendrans/dremio-snowflake/releases Older versions compiled against 16.x versions of Dremio and below are NOT compatible with this.

Contents

• Overview
  • Use Cases
  • Features
  • Demo
• Downloading a Release
• Usage
• Development
  • Building and Installation
  • Building a Docker image
  • Debugging
• Contribution
  • Submitting an issue
  • Pull Requests
• Troubleshooting

Overview

This is a community based Snowflake Dremio connector made using the ARP framework. Check Dremio Hub for more examples and ARP Docs for documentation.

What is Dremio?

Dremio delivers lightning fast query speed and a self-service semantic layer operating directly against your data lake storage and other sources. No moving data to proprietary data warehouses or creating cubes, aggregation tables and BI extracts. Just flexibility and control for Data Architects, and self-service for Data Consumers.

Use Cases

• Join data from Snowflake with other sources (On prem/Cloud)
• Interactive SQL performance with Data Reflections
• Offload Snowflake tables using CTAS to your cheap data lake storage - HDFS, S3, ADLS
  • Or use COPY INTO to export data from Snowflake into S3/ADLS and query them directly using Dremio or create external reflections on top of them.
• Curate Datasets easily through the self-service platform

Features

• Complete datatype support
• Pushdown of over 50+ functions
• Verified push downs of all TPCH queries

Demo

Downloading a Release

• To download a release, click here

Usage

Creating a new Snowflake Source

Required Parameters

• JDBC URL
  • Ex: jdbc:snowflake://<account_name>.snowflakecomputing.com/?param1=value&param2=value. More details.
• Username, Password
  • The username and password with which you want to connect to Snowflake. Password is not needed if you want to use a PEM file. In that case you can use a JDBC string like below (The pem must exist on all the nodes) jdbc:snowflake://account.us-east-1.snowflakecomputing.com?warehouse=compute_wh&private_key_file=/Users/naren/Desktop/rsa_key.pem

Development

Building and Installation

0. Change the pom's dremio.version to suit your Dremio's version. <dremio.version>17.0.0-202107060524010627-31b5222b</dremio.version>
1. In root directory with the pom.xml file run mvn clean install -DskipTests. If you want to run the tests, add the JDBC jar to your local maven repo along with environment variables that are required. Check the basic test example for more details.
2. Take the resulting .jar file in the target folder and put it in the <DREMIO_HOME>\jars folder in Dremio
3. Download the Snowflake JDBC driver from (https://mvnrepository.com/artifact/net.snowflake/snowflake-jdbc) and put in in the <DREMIO_HOME>\jars\3rdparty folder
4. Restart Dremio

Debugging

To debug pushdowns for queries set the following line in logback.xml

  <logger name="com.dremio.exec.store.jdbc">
    <level value="${dremio.log.level:-trace}"/>
  </logger>

You can then notice lines like below in server.log file after which you can revist the YAML file to add pushdowns based on Snowflake SQL Reference:

- 2019-07-11 18:56:24,001 [22d879a7-ce3d-f2ca-f380-005a88865700/0:foreman-planning] DEBUG c.d.e.store.jdbc.dialect.arp.ArpYaml - Operator / not supported. Aborting pushdown.

You can also take a look at the planning tab/visualized plan of the profile to determine if everything is pushed down or not.

Contribution

Submitting an issue

• Go to the issue submission page: https://github.com/narendrans/dremio-snowflake/issues/new/choose. Please select an appropriate category and provide as much details as you can.

Pull Requests

PRs are welcome. When submitting a PR make sure of the following:

• Try to follow Google's Java style coding when modifying/creating Java related content.
• Use a YAML linter to check the syntactic correctness of YAML file
• Make sure the build passes
• Run basic queries at least to ensure things are working properly

Troubleshooting

Snowflake unable to create the cache directory

If you see the following trace in dremio:

Caused by: java.lang.RuntimeException: Failed to locate or create the cache directory: /home/dremio/.cache/snowflake
        at net.snowflake.client.core.FileCacheManager.build(FileCacheManager.java:159) ~[snowflake-jdbc-3.8.7.jar:3.8.7]
        at net.snowflake.client.core.SFTrustManager.<clinit>(SFTrustManager.java:197) ~[snowflake-jdbc-3.8.7.jar:3.8.7]
        ... 21 common frames omitted

You should then set the File cache environment variables documented here

export SF_TEMPORARY_CREDENTIAL_CACHE_DIR=<path>
export SF_OCSP_RESPONSE_CACHE_DIR=<path>

To set them as JAVA properties, add them to the conf/dremio-env file

DREMIO_JAVA_SERVER_EXTRA_OPTS='-Dnet.snowflake.jdbc.temporaryCredentialCacheDir=/tmp -Dnet.snowflake.jdbc.ocspResponseCacheDir=/tmp'