Connect to Snowflake with JDBC

Thank you for reading our previous post #10 Query Result Sets available to users via history.

As promised in my original post, here is a deeper dive into another one of the top 10 cool features from Snowflake:

#9 Ability to connect with JDBC

This seems like a no brainer but is very important. If you're interested in connecting any custom or packaged Java based applications to Snowflake, JDBC is what you need. JDBC technology lets you access information in SQL databases using standard SQL queries.

So why is this cool? Because all of the modern applications written in Java can take advantage of our elastic cloud based data warehouse through a JDBC connection.

And we have plenty of customers doing that today with industry leading tools.

You can easily connect various ETL, BI and visualization tools to Snowflake using the JDBC driver, just like they connect with many legacy databases.

Simple to Use

You can download and install the the Snowflake JDBC driver through our user interface. To do that, login to your Snowflake account, go to the online help and select the JDBC driver under the downloads menu. Click on the link provided for details on setup and configuration.

Once you have the driver installed, you have several ways you can take advantage of it. If you have, or are building, a custom Java application, you can connect that app directly to Snowflake quite easily. This example shows how simple it is to connect and query data in Snowflake with a Java program, using the JDBC driver for Snowflake.

/*
 * Copyright (c) 2012, 2013 Snowflake Computing Inc. All right reserved.
 */
package com.snowflake.client;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class SnowflakeDriverExample
{
  public static void main(String[] args) throws Exception
  {
    // get connection
    System.out.println("Create JDBC connection");
    Connection connection = getConnection();
    System.out.println("Done creating JDBC connectionn");
    // create statement
    System.out.println("Create JDBC statement");
    Statement statement = connection.createStatement();
    System.out.println("Done creating JDBC statementn");
    // create a table
    System.out.println("Create demo table");
    statement.executeUpdate("create or replace table demo(C1 STRING)");
    statement.close();
    System.out.println("Done creating demo tablen");
    // insert a row
    System.out.println("Insert 'hello world'");
    statement.executeUpdate("insert into demo values ('hello world')");
    statement.close();
    System.out.println("Done inserting 'hello world'n");
    // query the data
    System.out.println("Query demo");
    ResultSet resultSet = statement.executeQuery("SELECT * FROM demo");
    System.out.println("Metadata:");
    System.out.println("================================");
    // fetch metadata
    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
    System.out.println("Number of columns=" +
                       resultSetMetaData.getColumnCount());
    for (int colIdx = 0; colIdx < resultSetMetaData.getColumnCount();
                         colIdx++)
    {
      System.out.println("Column " + colIdx + ": type=" +
                         resultSetMetaData.getColumnTypeName(colIdx+1));
    }
    // fetch data
    System.out.println("nData:");
    System.out.println("================================");
    int rowIdx = 0;
    while(resultSet.next())
    {
      System.out.println("row " + rowIdx + ", column 0: " +
                         resultSet.getString(1));
    }
    statement.close();
  }
   private static Connection getConnection()
          throws SQLException
  {
    try
    {
      Class.forName("net.snowflake.client.jdbc.SnowflakeDriver");
    }
    catch (ClassNotFoundException ex)
    {
     System.err.println("Driver not found");
    }
    // build connection properties
    Properties properties = new Properties();
    properties.put("user", "");     // replace "" with your username
    properties.put("password", ""); // replace "" with your password
    properties.put("account", "");  // replace "" with your account name
    properties.put("db", "");       // replace "" with target database name
    properties.put("schema", "");   // replace "" with target schema name
    //properties.put("tracing", "on");

    // create a new connection
    String connectStr = System.getenv("SF_JDBC_CONNECT_STRING");
    // use the default connection string if it is not set in environment
    if(connectStr == null)
    {
     connectStr = "jdbc:snowflake://accountName.snowflakecomputing.com"; // replace accountName with your account name
    }
    return DriverManager.getConnection(connectStr, properties);
  }
}

If, on the other hand, you have a packaged application to connect, you can do that just as easily. For example, you can connect to Java based applications like Oracle SQL Developer Data Modeler (SDDM), which can be used to reverse engineer the design of whatever tables and views you have been granted access to in Snowflake. For further details on setting up SDDM to talk to Snowflake, see this detailed blog. (NB: The specifics for configuring each tool will vary but this blog will give you an example of what to look for)

Continue to keep an eye on this blog site, our Snowflake Twitter feed (@SnowflakeDB), (@kentgraziano), and (@cloudsommelier) for more Top 10 Cool Things About Snowflake as well as all the other happenings and news at Snowflake Computing.

Kent Graziano and Saqib Mustafa