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:
- Preparing our environment & installing dependencies
- Identifying & downloading the driver
- Updating the project to use the driver
- 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.
- What is AWS Secrets Manager?
- How to create a AWS Secret
- In the JDBC example we retrieve a secret from AWS region “us-east-1” under the name “tutorial/snowflake”.
- AWS Secrets – JAVA Reference
- Creating an IAM user in your AWS Account.
- In the JDBC example a user with Pragmatic Access is used for authentication.
- Your IAM user will also need to be granted policies to be able to access AWS Secrets Manager.
- There are several ways to authenticate AWS credentials for JAVA packages. In the JDBC example the user authenticates using an “Access Key Id” and “Secret Access Key”.
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.
- What is Azure Key Vault?
- How to create Azure Secrets
- In the ODBC example we retrieve a secret from “example-snowflake” vault under the name “example-snowflake”
- Azure Keys – Python Reference
- How to create a Service Principal
- In the ODBC example, a Service Principal is used for authentication.
- The Service Principal will need to be granted policies to be able to access Azure Key Vault.
- There are several methods of authentication for Azure Key Vault. In the ODBC example the user authenticates using a “Tenant Id”, “Client Id”, and “Client 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
software.amazon.awssdk
bom
2.17.46
pom
import
org.json
json
20201115
software.amazon.awssdk
secretsmanager
Identify & Download Snowflake Driver
Add dependency to pom.xml.
net.snowflake
snowflake-jdbc
3.13.11
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
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 \
# 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
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.