March 24, 2022

How do I Connect XYZ System via JDBC/ODBC to Snowflake?

By José Almonte

In this blog, we’re going to walk you through how to connect to the Snowflake Data Cloud using JDBC and ODBC Drivers.

For each system, we will be:

  1. Preparing our environment & installing dependencies
  2. Identifying & downloading the driver
  3. Updating the project to use the driver
  4. Testing the connection

Before we dive in, it’s important to note that both examples covered follow a generic approach that works for any environment (MacOS, Windows, Linux). Additionally, the snippets below are done in Linux environments. 

What is JDBC?

Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java. It is a Java-based data access technology used for Java database connectivity and defines how a client may access a database. It also provides methods to query and update data in the database.

What is ODBC?

Open Database Connectivity (ODBC) is a specification for a database API. The ODBC API is based on the CLI specifications from Open Group and ISO/IEC. ODBC 3.x fully implements both of these specifications.

What You Will Need

Before you begin you will need to have a Snowflake Virtual Warehouse up and running with a User that can connect and query the cluster. Here are some helpful links below to get you started:

Specific Snowflake account information you will need are.

  •   username 
  •   password
  •   database name
  •   schema name
  •   account
  •   region

Managing Sensitive Information

While it may be easier to use hard coded credentials, this creates a serious security risk. It is strongly recommended that one use a Secrets Manager for handling this type of sensitive information. 

What is Secrets Manager?

Secrets Managers are a cybersecurity best practice for digital businesses that allows organizations to consistently enforce security policies for non-human identities. Secrets management provides resources across tool stacks, platforms, and cloud environments that can only be accessed by authenticated and authorized entities. The service enables you to easily rotate, manage, and retrieve database credentials, API keys, and other secrets throughout their lifecycle.

The JDBC example will be using AWS Secrets Manager. You can reference the links below for more information on the AWS Secrets Manager service and how to create a secret. 

The ODBC example will be using Azure Key Vault. You can reference the links below for more information on the Azure Key Vault and how to create a secret. 

In the examples below, we will be retrieving a secret with the following schema.  

“`json

				
					{
    "username": "***",
    "password" : "***",
    "database": "***",
    "schema": "***",
    "account_id": "***",
    "region": "***"
}
				
			

How to Connect to Snowflake via JDBC

In this example, we use Ubuntu:20.04, Java, and Maven to connect to Snowflake using snowflake-jdbc-driver 3.13.11.

Set up Environment & Install Dependencies

Step 1: Install Java.

				
					# Identify and set desired JAVA version  
export JDK_VERSION=11

# Install JAVA
apt-get install -y openjdk-${JDK_VERSION}-jdk

# Check to see if installed successfully
java --version
				
			

Step 2: Install Maven

This is used for installing dependencies and the packaging of java code into a .jar

				
					# Install Maven
apt-get install -y maven

# Check to see if installed Successfully
mvn --version
				
			

Step 3: Create a Java Project using Maven.

				
					# Create JAVA Project using Maven
mvn archetype:generate -DgroupId=io.phdata.SnowflakeJDBCExample -DartifactId=SnowflakeJDBCExample -DpackageName=io.phdata.SnowflakeJDBCExample -DarchetypeVersion=1.4 -DinteractiveMode=false
				
			

This will create a directory like below:  

				
					SnowflakeJDBCExample
  - pom.xml
  - main/java/io/phdata/SnowflakeJDBCExample/
    - App.java
				
			

Step 3: Add AWS Secrets Manager dependencies to pom.xml

				
					<dependencyManagement>
  <dependencies>
    <dependency>
      <groupId>software.amazon.awssdk</groupId>
      <artifactId>bom</artifactId>
      <version>2.17.46</version>
      <type>pom</type>
      <scope>import</scope>
    </dependency>
  </dependencies>
</dependencyManagement>

<dependencies>
  <dependency>          
     <groupId>org.json</groupId>
     <artifactId>json</artifactId>
     <version>20201115</version>
  </dependency>
  <dependency>
     <groupId>software.amazon.awssdk</groupId>
     <artifactId>secretsmanager</artifactId>
  </dependency>
 </dependencies>
				
			

Identify & Download Snowflake Driver 

Add dependency to pom.xml.

				
					<dependencies>
  <dependency>
    <groupId>net.snowflake</groupId>
      <artifactId>snowflake-jdbc</artifactId>
      <version>3.13.11</version>
    </dependency>
</dependencies>
				
			

Update Project

Step 1: Create “src / main / java / io / phdata / SnowflakeJDBCExample / SnowflakeJBDCExample.java”

				
					package io.phdata.SnowflakeJDBCExample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import org.json.JSONObject;
import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.services.secretsmanager.SecretsManagerClient;
import software.amazon.awssdk.services.secretsmanager.model.GetSecretValueRequest;
import software.amazon.awssdk.services.secretsmanager.model.GetSecretValueResponse;


public class SnowflakeJDBCExample {
 public static void main(String[] args) throws Exception {
   try {
     Class.forName("net.snowflake.client.jdbc.SnowflakeDriver");
   }
   catch (ClassNotFoundException ex) {
     System.err.println("Driver not found");
   }
  
   String secretName = "tutorial/snowflake";
   Region region = Region.US_EAST_1;
   SecretsManagerClient secretsClient = SecretsManagerClient.builder()
     .region(region)
     .build();

   GetSecretValueRequest valueRequest = GetSecretValueRequest.builder()
     .secretId(secretName)
     .build();

   GetSecretValueResponse valueResponse = secretsClient.getSecretValue(valueRequest);
   JSONObject secret = new JSONObject(valueResponse.secretString());
   secretsClient.close();

   // build connection properties
   String url = "jdbc:snowflake://%s.%s.aws.snowflakecomputing.com";
   url = String.format(url,secret.get("account_id").toString(),secret.get("region").toString());
   Properties prop = new Properties();
   prop.put("user", secret.get("username"));
   prop.put("password", secret.get("password"));
   prop.put("warehouse", secret.get("warehouse"));
   prop.put("db", secret.get("database"));
   prop.put("schema", secret.get("schema"));
   prop.put("role", "SYSADMIN");

   Connection conn = DriverManager.getConnection(url,prop);
   Statement stat = conn.createStatement();
   ResultSet res = stat.executeQuery("SELECT CURRENT_USER();");
   res.next();
   System.out.println(res.getString(1));
   conn.close();
 }
}
				
			
				
					SnowflakeJDBCExample
  - pom.xml
  - main/java/io/phdata/SnowflakeJDBCExample/
    - App.java
    - SnowflakeJBDCExample.java
				
			

Step 2: Install project dependencies & package .jar

				
					# Install packages and package .jar used by JAVA
mvn install
				
			
				
					SnowflakeJDBCExample
  - pom.xml
  - main/java/io/phdata/SnowflakeJDBCExample/
    - App.java
    - SnowflakeJBDCExample.java
  - target/
    - SnowflakeJDBCExample-1.0-SNAPSHOT.jar 
				
			

Test Connection

Before running, be sure to set your AWS Credentials.

				
					# Test connection using JAVA
mvn compile exec:java -Dexec.mainClass="io.phdata.SnowflakeJDBCExample.SnowflakeJDBCExample"
				
			

How to Connect to Snowflake via ODBC

In this example we use Ubuntu:20.04, Python with pyodbc to connect to Snowflake using snowflake-odbc-driver 2.24.3.

Set up Environment & Install Dependencies

Step 1: Install Python.

				
					export PY_VERSION=3.8.10
export VIRTUAL_ENV=/opt/venv
export PYSPARK_PYTHON=${VIRTUAL_ENV}
export SPARK_HOME=$VIRTUAL_ENV/lib/python${PY_VERSION}/site-packages/pyspark

# Install/Update Python
apt-get install -y  python3 python3-pip

# Install virtualenv
pip3 install virtualenv

# Set Up Virtual Environment
virtualenv -p /usr/bin/python${PY_VERSION} ${VIRTUAL_ENV}

# Add Python to PATH
PATH="$VIRTUAL_ENV/bin:$PATH"

# Check Python Version
python --version
				
			
A screenshot of python code

Step 2: Install ODBC dependencies.

				
					# Install Python Packages
pip install pyodbc

# Install OS Dependencies
apt-get install unixodbc unixodbc-dev
				
			

Step 3: Install Azure Key Vault Dependencies.

				
					# Install Python Packages
pip install azure-keyvault-secrets azure-identity
				
			

Identify & Download Snowflake Driver

				
					export SNOWFLAKE_DRIVER=2.24.3
export OS_ARCH=x86_64

# Download Driver Using Curl
RUN curl \
  --output snowflake-odbc-${SNOWFLAKE_DRIVER}.${OS_ARCH}.deb \
<div class="ose-wrapper ose-uid-346e3c53cb3b1816d9e0603e2a0315e7 ose-embedpress-responsive" style="width:600px; height:550px; max-height:550px; max-width:100%; display:inline-block;"><iframe loading="lazy" allowFullScreen="true" width="600" height="550" src="about:blank"  data-rocket-lazyload="fitvidscompatible" data-lazy-src="https://sfc-repo.snowflakecomputing.com/odbc/linux/$SNOWFLAKE_DRIVER/snowflake-odbc-$SNOWFLAKE_DRIVER.$OS_ARCH.deb"> </iframe><noscript><iframe allowFullScreen="true" width="600" height="550" src="https://sfc-repo.snowflakecomputing.com/odbc/linux/$SNOWFLAKE_DRIVER/snowflake-odbc-$SNOWFLAKE_DRIVER.$OS_ARCH.deb" > </iframe></noscript></div> 

# Install Driver
dpkg -i snowflake-odbc-${SNOWFLAKE_DRIVER}.${OS_ARCH}.deb
				
			

Update Project

Create file “odbc_snowflake_example.py” or update current project with snippet below.

				
					import pyodbc
from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient
import json

credential = DefaultAzureCredential()
sc = SecretClient(vault_url="https://example-snowflake.vault.azure.net/", credential=credential)
get_secret_value_response = sc.get_secret("example-snowflake").value
secret = json.loads(get_secret_value_response)

host = "{0}.{1}.snowflakecomputing.com".format(secret['account_id'], secret['region'])
uid = secret['username']
pwd = secret['password']
db = secret['database']
sch = secret['schema']
query = "SELECT CURRENT_USER()"

# Define Connection String
conn_str = "Driver=SnowflakeDSIIDriver; Server={0}; Database={1}; schema={2}; UID={3}; PWD={4}".format(host,db,sch,uid,pwd)
# Define Connection
conn = pyodbc.connect(conn_str)
# Define Cursor
cur=conn.cursor()
# Execute SQL statement
cur.execute(query)
# Display the content of cursor
row = cur.fetchone()
print(row)
				
			

Test Connection

Before running be sure to set your Azure Credentials.

				
					python odbc_pyodbc_snowflake.py
				
			
A screenshot of 2 lines of linux code

Conclusion

In this post, we’ve demonstrated how to add a Snowflake Cluster using JDBC & ODBC. This gives you the flexibility to further develop your Analytics Infrastructure by being able to incorporate and work with many different frameworks and tools. 

If your team is interested in learning more about how to extract more value from your Snowflake account, please reach out! We’d love to help kick off your project! If you’re looking for more helpful Snowflake resources, be sure to check out our free guide to getting started with Snowflake.

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.

Accelerate and automate your data projects with the phData Toolkit