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.
Table of Contents
Introduction to JDBC
Image Source
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.
Image Source
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.
Hevo Data, a No-code Data Pipeline, helps load data from any Data Source such as Databases, SaaS applications, Cloud Storage, SDKs, and Streaming Services and simplifies the ETL process. It supports 100+ Data Sources including 30+ Free Sources.
Hevo loads the data onto the desired Data Warehouse/Destination like Snowflake and enriches the data and transforms it into an analysis-ready form without having to write a single line of code.
Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.
Get started with hevo for free
Steps to Set Up Snowflake JDBC
Image Source
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
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.
Image Source
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.
Frequently Asked Questions (FAQs)
What is JDBC in Snowflake?
Snowflake JDBC is an Application Programming Interface (API) that can help you to connect most client tools/applications that support JDBC for connecting to a Database Server. You can simply download the JDBC Driver JAR file and integrate it into your Java-based applications.
What are the Java requirements for the Snowflake JDBC Driver?
It is necessary to have Java 1.8 or higher for Snowflake JDBC. However, if you don’t have the minimum required version of Java installed on the client machine, you can install either Oracle Java or OpenJDK.
From where can we download the Snowflake JDBC Driver?
The Snowflake JDBC Driver is provided as a JAR file in Maven. On visiting the Maven repository, you can identify the latest version of Snowflake JDBC and click on it for the download to start.
Does Snowflake support ODBC?
Yes, Snowflake also provides an ODBC Driver for connecting ODBC-based client applications to Snowflake. However, the ODBC Driver has different requirements for different platforms. For more details, you need to refer to the installation and configuration instructions for each platform.
What is the Syntax for Snowflake JDBC Connection String?
You will need a Connection String to establish a connection to your Snowflake Database through JDBC. The Connection String should specify correct parameters and it should take the following syntax: jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?<connection_params>
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.
Want to take Hevo for a spin?
sign up for a 14-day free trial and experience the feature-rich Hevo suite firsthand.
Have a look at the unbeatable pricing that will help you choose the right plan for your business needs!
Share your experience of setting up Snowflake JDBC in the comments section below!
Skilled in freelance writing within the data industry, Nicholas is passionate about unraveling the complexities of data integration and data analysis through informative content for those delving deeper into these subjects. He has written more than 150+ blogs on databases, processes, and tutorials that help data practitioners solve their day-to-day problems.