Snowflake JDBC: How to Connect to Snowflake Using the JDBC Driver

A few years back, building a Data Warehouse was an expensive process that required the use of specially designed hardware appliances in your Data Center. 

However, the invention of Snowflake has saved many companies from this. It is a Data Warehouse solution that is offered as Software-as-a-Service (SaaS). 

Snowflake allows its users to build scalable Data Warehouses for the storage of their data. When using Snowflake, you will need some of your Java-based applications to access your Snowflake data. 

Some of these applications will help you to Visualize and Analyze your data. You can also send data into Snowflake or update your Snowflake data using such applications. 

However, for this to happen, integration between the two, that is, Snowflake and the Java-based app, is necessary.  Thus, you will need a tool to facilitate the connection or integration between Snowflake and your Java-based application.

Snowflake JDBC is an API that can help you to connect to Snowflake data from any application that supports JDBC connectivity. You can then move data between Snowflake and the application.

In this article, you will understand the steps to connect Snowflake JDBC with your Snowflake.

Prerequisites

Before moving ahead, to set up Snowflake JDBC, you need the following requirements:

• A Snowflake Account.
• OpenJDK.

Introduction to JDBC

JDBC is an Application Programming Interface (API) for connecting and executing queries against Databases. With JDBC, you can connect to the Database, issue queries, and then handle the result sets returned by the queries.

Initially, JDBC was conceived as a Client-side API, allowing Java clients to connect and interact with a data source. However, this changed with JDBC 2.0 which introduced an optional package that supports server-side JDBC connections. Since then, every new release of JDBC adds new features to both the Client-side and Server-side JDBC. You can use JDBC for free.

To learn more about JDBC, view its official documentation here.
To Download Snowflake JDBC Driver
Visit the official GitHub plugin page for more details.

Steps to Set Up Snowflake JDBC

You can use the Snowflake JDBC Driver to connect to Snowflake from any application provided it supports JDBC connectivity.

After the connection, you will be able to query for data from your Snowflake Database, update the data, view the Database tables, and more. Let us discuss how to set up the connection to Snowflake using JDBC.

• Step 1: Install Snowflake JDBC
• Step 2: Configure Snowflake JDBC

Step 1: Install Snowflake JDBC

The Snowflake JDBC Driver comes as a JAR file and is provided as an artifact in Maven. You can download the Driver and integrate it into your Java-based applications. After visiting the Maven repository, you will find many versions of the Driver. 

Identify the version of Snowflake JDBC that you want to download and click it. The latest versions of the Driver are located at the bottom of the page. You will then be taken to a page where you can download the .jar file of the Driver.

Step 2: Configure Snowflake JDBC

In this section, you will discuss how to configure the JDBC Driver and how to connect to JDBC using the Driver.

JDBC Driver Class

You should use the net.snowflake.client.jdbc.SnowflakeDriver Driver Class in your JDBC application.

Note that it’s not recommended for you to reference any other Snowflake methods or classes in your application code because they can change in the future to implement fixes or add improvements. 

The previous Driver Class, that is, com.snowflake.client.jdbc.SnowflakeDriver is deprecated but is still supported, however, it will be removed in the future. 

Thus, it will be good for you to update the code to reference the new class name since the change has already been implemented.

JDBC Driver Connection String

To connect to Snowflake using JDBC, you will need a Connection String. The work of the Connection String is to establish a connection to your Snowflake Database if you specify the correct parameter values.

However, if the parameter values are not correct, the connection will not go through. The Connection String should take the following syntax:

jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?<connection_params>

Below is a description of the parameters used in the above syntax:

• <account_identifier>: This parameter denotes the account identifier of your Snowflake account. 
• <connection_params>: It denotes a series of one or more parameters that take the form <param>=<value>. The parameters are separated using the ampersand operator (&) without any space between the parameters.

If you need your parameter values to have spaces, equal signs (=), ampersands  (&), or any other special characters, ensure that your URL-encode the special characters. For example, to use a value that contains an ampersand, equal sign, and space:

String connectionURL = "jdbc:snowflake://organization-myaccount.snowflakecomputing.com/?query_tag='folder=folder11 folder12&'

Ensure that you encode the

• Equal Sign as “%3D”
• Space as “%20”
• Ampersand as “%26” as shown below:

String connectionURL = "jdbc:snowflake://organization-myaccount.snowflakecomputing.com/?query_tag='folder%3Dfolder11%20folder12%26'

Alternatively, instead of specifying the parameters in a Connection String, you can set them in a “Properties” object and pass it to the “DriverManager.getConnectionIO” method. This is shown below:

Properties props = new Properties();
props.put("parameter1", Value);
props.put("parameter2", Value);
Connection con = DriverManager.getConnection("jdbc:snowflake://<account_identifier>.snowflakecomputing.com/", props);

You can also add Session parameters to the Connection String. For example:

CLIENT_SESSION_KEEP_ALIVE=<Boolean>

The work of the above parameter is to specify whether the current Session should be kept active after a period of inactivity, or whether the user should be forced to log in again.

• If you give it a value of “true”, Snowflake will indefinitely keep the Session active, even if the user doesn’t do anything.
• If you set the value of the parameter to “false”, the user will be forced to log in again after four hours of inactivity.
• The parameter takes a default value of “false”. 

The following example shows a Connection String that uses an account specifier specifying the account named myaccount in the organization named organization:

jdbc:snowflake://organization-myaccount.snowflakecomputing.com/?user=nic&warehouse=wh&db=db&schema=public

The following example shows a Connection String using an Account Locator as the Account Identifier:

jdbc:snowflake://abc12345.snowflakecomputing.com/?user=nic&warehouse=wh&db=db&schema=public

The abc12345 is the Account Locator. Note that for accounts not located in the AWS US West (Oregon) region or if it uses a different cloud service provider, you should specify additional segments about the Account Locator. This will ensure that everything runs successfully without raising any errors.

Using Java JDBC with External OAuth Tokens in Snowflake

Connecting your Java application to Snowflake using an external OAuth token is a secure and efficient way to manage authentication. Instead of using traditional username-password authentication, you can leverage OAuth tokens for enhanced security. Here’s how you can set it up.

1. Obtain the OAuth Token

First, you need to retrieve an OAuth token from your identity provider, like Azure AD or Okta. This token will be used for authentication when connecting to Snowflake.

2. Set Up the JDBC Connection

Once you have the OAuth token, you can use it to configure your JDBC connection. Here’s a sample code snippet to guide you:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class SnowflakeOAuthConnection {
    public static void main(String[] args) {
        // Connection URL
        String url = "jdbc:snowflake://<account_name>.snowflakecomputing.com";
        
        // Properties for the connection
        Properties properties = new Properties();
        properties.put("user", "<username>");
        properties.put("authenticator", "oauth");
        properties.put("token", "<OAuth_access_token>");  // Use the OAuth token here
        
        // Optional: Specify the role, warehouse, etc.
        properties.put("role", "MY_ROLE");
        properties.put("warehouse", "MY_WAREHOUSE");

        try {
            // Establish the connection
            Connection connection = DriverManager.getConnection(url, properties);
            System.out.println("Connected successfully with OAuth token.");
            
            // Do something with the connection...
            
            // Close the connection
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

In this code, you use the authenticator parameter set to oauth and provide the OAuth token with the token property. Make sure to replace the placeholder values (<account_name>, <username>, and <OAuth_access_token>) with your actual details.

3. Test the Connection

After setting up the connection, test it by running the code to ensure it is established successfully. Once connected, you can execute queries like any other JDBC connection.

Using OAuth removes the need to manage passwords, giving you both security and flexibility. Snowflake’s support for external OAuth tokens means you can integrate easily with your organization’s identity provider.

Querying Your Data in Snowflake Using JDBC

Once you’ve established a connection with Snowflake using the Java JDBC client, querying your data is straightforward. You can execute SQL queries and retrieve results just like you would in any other database. Let’s walk through how you can run queries and fetch results.

Example: Running a Query

Here’s a simple example of how you can execute a query in Snowflake using the established JDBC connection:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class SnowflakeQueryExample {
    public static void main(String[] args) {
        // Connection URL
        String url = "jdbc:snowflake://<account_name>.snowflakecomputing.com";

        // Properties for the connection
        Properties properties = new Properties();
        properties.put("user", "<username>");
        properties.put("authenticator", "oauth");
        properties.put("token", "<OAuth_access_token>");
        properties.put("role", "MY_ROLE");
        properties.put("warehouse", "MY_WAREHOUSE");

        try {
            // Establish the connection
            Connection connection = DriverManager.getConnection(url, properties);
            System.out.println("Connected to Snowflake.");
            
            // Create a statement
            Statement statement = connection.createStatement();
            
            // SQL query to execute
            String sqlQuery = "SELECT * FROM MY_DATABASE.MY_SCHEMA.MY_TABLE LIMIT 10";
            
            // Execute the query and get the result set
            ResultSet resultSet = statement.executeQuery(sqlQuery);
            
            // Process the result set
            while (resultSet.next()) {
                System.out.println("Column 1: " + resultSet.getString(1));
                System.out.println("Column 2: " + resultSet.getString(2));
                // Fetch other columns as needed
            }
            
            // Close the resources
            resultSet.close();
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

In this example:

• You connect with your Snowflake account using the JDBC driver and OAuth token.
• The Statement object allows you to execute SQL queries like SELECT, INSERT, or any other valid SQL command.
• The ResultSet object helps retrieve the query results, which you can loop through and process as needed.

Example Output:

When you run this query, the result might look like this in the console:

Column 1: 123
Column 2: SampleData

Column 1: 456
Column 2: AnotherSample

Best Practices for Querying Data

• Limit your queries: When working with large datasets, always use the LIMIT clause to restrict the number of rows fetched.
• Optimize your SQL: Use indexing and partitioning where applicable to make your queries more efficient.
• Handle exceptions: JDBC operations can throw SQLException, so it’s a good practice to handle errors gracefully.

By following these steps, you can easily query your Snowflake data using JDBC, making data retrieval quick and efficient for your applications. Whether you’re running complex analytics or fetching simple results, JDBC provides a reliable way to interact with your Snowflake data.

Best Practices for Querying Data

• Limit your queries: When working with large datasets, always use the LIMIT clause to restrict the number of rows fetched.
• Optimize your SQL: Use indexing and partitioning where applicable to make your queries more efficient.
• Handle exceptions: JDBC operations can throw SQLException, so it’s a good practice to handle errors gracefully.

By following these steps, you can easily query your Snowflake data using JDBC, making data retrieval quick and efficient for your applications. Whether you’re running complex analytics or fetching simple results, JDBC provides a reliable way to interact with your Snowflake data.

Conclusion

Snowflake is a major player in the Cloud Data Warehousing industry and it is really important for organizations to understand how to connect Java-based apps to Snowflake. 

This article introduced us to JDBC Programming Interface, Snowflake JDBC in particular, and helped us set up the Snowflake JDBC. It later took us through a step-by-step guide on how to configure the JDBC Driver and how to connect to a Snowflake Database using the Driver.

With your Snowflake Warehouse live and running, you’ll need to extract and ingest data from multiple sources to Snowflake in order to carry out an effective Data Analysis.

However, extracting complex data from a diverse set of data sources into Data Warehouses like Snowflake can be quite challenging and tiresome. So, you can use an easier alternative like Hevo.

Frequently Asked Questions (FAQs)