Summary
How To Use Snowflake Java JDBC Type-4 Driver to build utilities
Snowflake Java JDBC Driver
A JDBC type 4 driver from Snowflake supports fundamental JDBC features. Java 1.8 is necessary for the JDBC driver, which must be installed in a 64-bit environment (or higher).
Most client software/applications that accept JDBC for connecting to a database server can utilise the driver.
The power of Snowflake Snowflake Java JDBC is not well known and the usefulness it brings to data developers and data ops engineers are not discussed anywhere. (Watch complete video )
We have published a detailed video on snowflake java jdbc driver and this video is a comprehensive & practical guide with hands-on excercise on snowflake jdbc driver. It will help you to answer the following questions
- 🙋 Does snowflake support JDBC type-4 driver?
- 🙋 Does Snowflake JDBC Driver required JDK1.8 or higher?
- 🙋 What are the pre-requisite to run a Snowflake JDBC driver program?
- 🙋 How to download and configure JDBC driver to interact with Snowflake.
- 🙋 How JDBC driver works if my Snowflake authentication happens via SSO or Idp?
- 🙋 Can this JDBC driver support Java/Maven based projects?If so, how the POM dependency looks like.
- 🙋 Can JDBC driver be used when Multi Factor Authentication enabled?
You can watch the complete hands on video tutorial {:target=”_blank”} on resource monitor
Prerequisites
Before moving ahead, to set up Snowflake JDBC, you need the following requirements:
- Snowflake Cloud Data Warehouse Instance
- OpenJDK
Snowflake Java JDBC Example
JDBC Driver Class
In your JDBC programme, you want to employ the net.snowflake.client.jdbc.SnowflakeDriver Driver Class.
It’s important to note that you shouldn’t mention any other Snowflake classes or methods in your application code because they might alter in the future to make fixes or enhance functionality.
com.snowflake.client.jdbc, which was the former Driver Class.
Although deprecated, SnowflakeDriver is currently supported; however, it will eventually be removed.
Since the change has already been made, it will be beneficial for you to update the code to refer to the new class name.
Find all the code in gitlab {:target=”_blank”} location
//change package as per your requirement.
//it will work without package also.
//package com.util;
//java.sql library for all connection objects
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//java util library
import java.util.Properties;
//class definition
public class SnowflakeJDBC {
//default constructor
public SnowflakeJDBC() {
}
//entry main method
public static void main(String[] args) {
//properties object
Properties properties = new Properties();
//setting properties
properties.put("user", "<my-snowflake-user-id>");
properties.put("password", "<my-password>");
properties.put("account", "vq1234.ap-southeast-2"); //account-id followed by cloud region.
properties.put("warehouse", "COMPUTE_WH");
properties.put("db", "TEST_DB");
properties.put("schema", "TEST_SCHEMA");
properties.put("role", "SYSADMIN");
//change this below URL as per your snowflake instance
String jdbcUrl = "jdbc:snowflake://vq1234.ap-southeast-2.snowflakecomputing.com/";
//change this select statement, but make sure the logic below is hard coded for now.
String selectSQL = "SELECT * FROM TEST_DB.TEST_SCHEMA.Employees";
//try-catch block
try {
Connection connection = DriverManager.getConnection(jdbcUrl, properties);
System.out.println("\tConnection established, connection id : " + connection);
Statement stmt = connection.createStatement();
System.out.println("\tGot the statement object, object-id : " + stmt);
ResultSet rs = stmt.executeQuery(selectSQL);
System.out.println("\tGot the result set object, object-id : " + rs);
System.out.println("\t----------------------------------------");
while(rs.next()) {
//following rs.getXXX should also change as per your select query
System.out.println(" \tEmployee ID: " + rs.getInt("ID"));
System.out.println(" \tEmployee Age: " + rs.getInt("AGE"));
System.out.println(" \tEmployee First: " + rs.getString("FIRST"));
System.out.println(" \tEmployee Last: " + rs.getString("LAST"));
}
} catch (SQLException exp) {
exp.printStackTrace();
}
System.out.println("\t----------------------------------------");
System.out.println("\tProgram executed successfully");
}
}
Snowflake JDBC Example with DDL & DML
Find all the code in gitlab {:target=”_blank”} location
//package com.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class SnowflakeJDBCDDLDML {
public SnowflakeJDBCDDLDML() {
}
public static void main(String[] args) {
Properties properties = new Properties();
properties.put("user", "<my-user-id>");
properties.put("password", "<my-password>");
properties.put("account", "vq1234.ap-southeast-2");
properties.put("warehouse", "COMPUTE_WH");
properties.put("db", "TEST_DB");
properties.put("schema", "TEST_SCHEMA");
properties.put("role", "SYSADMIN");
//jdbc URL
String jdbcUrl = "jdbc:snowflake://vq1234.ap-southeast-2.snowflakecomputing.com/";
//ddl statement
String sqlQuery = "create or replace table jdbc_demo02 (id number, name text ) ";
System.out.println("\tStarting the Snowflake Java JDBC Connection Program");
try {
Connection connection = DriverManager.getConnection(jdbcUrl, properties);
Statement stmt = connection.createStatement();
int positiveInt = stmt.executeUpdate(sqlQuery);
System.out.println("\tConnection established, connection id : " + connection);
System.out.println("\tGot the statement object, object-id : " + stmt);
System.out.println("\tDDL statement executed : " + positiveInt);
//number of records to be inserted
int recordInsert = 10;
for(int i = 0; i < recordInsert; ++i) {
String dmlQuery = "insert into jdbc_demo02 values (" + i + ", 'Name-" + i + "')";
System.out.println("The query is:" + dmlQuery);
int insertCnt = stmt.executeUpdate(dmlQuery);
System.out.println("\t(" + i + ") Row inserted: " + insertCnt);
}
} catch (SQLException var11) {
var11.printStackTrace();
}
System.out.println("\t----------------------------------------");
System.out.println("\tProgram executed successfully");
}
}
Free Java Utility To Load RDBMS to Snowflake
Find all the code in gitlab {:target=”_blank”} location
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Properties;
public class IngestRDBS2Snowflake {
public IngestRDBS2Snowflake() {
}
public static void main(String[] args) {
//step-1: Get all the table names
ArrayList<String> tableNames = getTableNames(getSourceDBConnection());
//step-2: based on table names, build all the ddls
ArrayList<String> tableDDLs = createDDLForSnowflake(getSourceDBConnection(), tableNames);
//step-3: based on table names, fetch column names and types and build prepared statements
ArrayList<String> prepStmt = createPreparedStmtForSnowflake(getSourceDBConnection(), tableNames);
//step-4 based on table names, fetch all data and build data in memory
HashMap<String, ArrayList<ArrayList<String>>> insertDataMap = getInsertData(getSourceDBConnection(), tableNames);
//step-5 now run the ddl in snowflake environment
createTablesInSnowflake(getSnowflakeConnection(), tableDDLs);
//step-6 load data using insert batch statement
insertDataInSnowflake(getSnowflakeConnection(), tableNames, prepStmt, insertDataMap);
}
public static Connection getSourceDBConnection() {
String pgUser = "<postgres-user-id>";
String pgPwd = "<postgres-pwd";
String jdbcUrl = "jdbc:postgresql://host-name:5432/db-name";
Connection sourceDBConnection = null;
String var4 = "SELECT table_name FROM information_schema.tables WHERE table_schema='public'";
try {
Class.forName("org.postgresql.Driver");
sourceDBConnection = DriverManager.getConnection(jdbcUrl, pgUser, pgPwd);
} catch (Exception var6) {
var6.printStackTrace();
System.exit(1);
}
return sourceDBConnection;
}
public static ArrayList<String> getTableNames(Connection srcDBConnection) {
System.out.println("\n\t\t-----------------------------------");
System.out.println("\t\tStep 1: Getting Table Names From Postgres");
System.out.println("\t\t-----------------------------------");
ArrayList<String> tblNames = new ArrayList();
String sqlGetTable = "SELECT table_name FROM information_schema.tables WHERE table_schema='public' and table_name not in ('pg_stat_statements')";
try {
Statement stmt = srcDBConnection.createStatement();
ResultSet rs = stmt.executeQuery(sqlGetTable);
while(rs.next()) {
String tblName = rs.getString("table_name");
System.out.println("\t\t\tTable Name: " + tblName);
tblNames.add(tblName);
}
stmt.close();
rs.close();
srcDBConnection.close();
} catch (Exception var6) {
var6.printStackTrace();
System.exit(1);
}
System.out.println("\n\t\tStep 1: Completed");
return tblNames;
}
public static ArrayList<String> createDDLForSnowflake(Connection srcDBConnection, ArrayList<String> tblNames) {
System.out.println("\n\t\t-----------------------------------");
System.out.println("\t\tStep 2: Getting Table DDLs Statement From Postgres");
System.out.println("\t\t-----------------------------------");
ArrayList ddlSqls = new ArrayList();
try {
Iterator var3 = tblNames.iterator();
while(var3.hasNext()) {
String table = (String)var3.next();
String finalTableDDL = "create or replace table " + table + " (";
String ddlSql = "SELECT * FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '" + table + "' order by ordinal_position";
Statement stmt = srcDBConnection.createStatement();
ResultSet rsDDL;
String columnName;
String columnType;
for(rsDDL = stmt.executeQuery(ddlSql); rsDDL.next(); finalTableDDL = finalTableDDL + columnName + " " + columnType + ",") {
columnName = rsDDL.getString("column_name");
columnType = rsDDL.getString("data_type");
}
String var10000 = finalTableDDL.substring(0, finalTableDDL.length() - 1);
finalTableDDL = var10000 + " );";
System.out.println("\t\t\tDDL for table " + table + " : " + finalTableDDL);
ddlSqls.add(finalTableDDL);
stmt.close();
rsDDL.close();
}
srcDBConnection.close();
} catch (Exception var11) {
var11.printStackTrace();
System.exit(1);
}
System.out.println("\n\t\tStep 2: Completed");
return ddlSqls;
}
public static ArrayList<String> createPreparedStmtForSnowflake(Connection srcDBConnection, ArrayList<String> tblNames) {
System.out.println("\n\t\t-----------------------------------");
System.out.println("\t\tStep 3: Getting Prepared Statement From Postgres");
System.out.println("\t\t-----------------------------------");
ArrayList prepStmtSqls = new ArrayList();
try {
Iterator var3 = tblNames.iterator();
while(var3.hasNext()) {
String table = (String)var3.next();
String finalPrepStmt = "insert into " + table + " ( ";
String finalPrepStmtValues = " values ( ";
String ddlSql = "SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '" + table + "' order by ordinal_position";
Statement stmt = srcDBConnection.createStatement();
ResultSet rsDDL;
for(rsDDL = stmt.executeQuery(ddlSql); rsDDL.next(); finalPrepStmtValues = finalPrepStmtValues + "? ,") {
String columnName = rsDDL.getString("column_name");
finalPrepStmt = finalPrepStmt + columnName + " ,";
}
String var10000 = finalPrepStmt.substring(0, finalPrepStmt.length() - 1);
finalPrepStmt = var10000 + " )";
var10000 = finalPrepStmtValues.substring(0, finalPrepStmtValues.length() - 1);
finalPrepStmtValues = var10000 + " )";
prepStmtSqls.add(finalPrepStmt + finalPrepStmtValues);
System.out.println("\t\t\tPrepared Stmt for table " + table + " : " + finalPrepStmt + finalPrepStmtValues);
stmt.close();
rsDDL.close();
}
srcDBConnection.close();
} catch (Exception var11) {
var11.printStackTrace();
System.exit(1);
}
System.out.println("\n\t\tStep 3: Completed");
return prepStmtSqls;
}
public static HashMap<String, ArrayList<ArrayList<String>>> getInsertData(Connection srcDBConnection, ArrayList<String> tblNames) {
System.out.println("\n\t\t-----------------------------------");
System.out.println("\t\tStep 4: Getting Data From Postgres Tables as Java Object");
System.out.println("\t\t-----------------------------------");
HashMap tableDataMap = new HashMap();
try {
Iterator var3 = tblNames.iterator();
while(var3.hasNext()) {
String table = (String)var3.next();
ArrayList<ArrayList<String>> tblData = new ArrayList();
String selectSQL = "SELECT * FROM " + table;
Statement stmt = srcDBConnection.createStatement();
ResultSet rsDDL = stmt.executeQuery(selectSQL);
while(rsDDL.next()) {
ArrayList<String> rowData = new ArrayList();
rowData.add(rsDDL.getString(1));
rowData.add(rsDDL.getString(2));
tblData.add(rowData);
}
tableDataMap.put(table, tblData);
System.out.println("\t\t\tRow counts for table " + table + " : " + tblData.size());
stmt.close();
rsDDL.close();
}
srcDBConnection.close();
} catch (Exception var10) {
var10.printStackTrace();
System.exit(1);
}
System.out.println("\n\t\tStep 4: Completed");
return tableDataMap;
}
public static Connection getSnowflakeConnection() {
Connection targetConnection = null;
Properties properties = new Properties();
properties.put("user", "<snowflake-user-id>");
properties.put("password", "<snowflake-pwd>");
properties.put("warehouse", "COMPUTE_WH");
properties.put("db", "DATA_WAREHOUSE_QA");
properties.put("schema", "STAGE_SCHEMA");
properties.put("role", "SYSADMIN");
String jdbcUrl = "jdbc:snowflake://vq1234.ap-southeast-2.snowflakecomputing.com/";
try {
targetConnection = DriverManager.getConnection(jdbcUrl, properties);
} catch (SQLException var4) {
var4.printStackTrace();
System.exit(1);
}
return targetConnection;
}
public static void createTablesInSnowflake(Connection targetDBConnection, ArrayList<String> tableDDLs) {
System.out.println("\n\t\t-----------------------------------");
System.out.println("\t\tStep 5:All DDLs executed in snowflake");
System.out.println("\t\t-----------------------------------");
try {
Statement stmt = targetDBConnection.createStatement();
Iterator var3 = tableDDLs.iterator();
while(var3.hasNext()) {
String ddlStmt = (String)var3.next();
System.out.println("\t\t\tDDL Statement is : \n\t\t\t" + ddlStmt);
stmt.executeUpdate(ddlStmt);
}
stmt.close();
targetDBConnection.close();
} catch (Exception var5) {
var5.printStackTrace();
System.exit(1);
}
System.out.println("\n\t\tStep 5: Completed");
}
public static void insertDataInSnowflake(Connection targetDBConnection, ArrayList<String> tableNames, ArrayList<String> prepStmt, HashMap<String, ArrayList<ArrayList<String>>> insertDataMap) {
try {
System.out.println("\n\t\t-----------------------------------");
System.out.println("\t\tStep 6: Snowflake Batch Insert Operation Method");
System.out.println("\t\t-----------------------------------");
targetDBConnection.setAutoCommit(false);
int index = 0;
for(Iterator var5 = prepStmt.iterator(); var5.hasNext(); ++index) {
String insertStmt = (String)var5.next();
PreparedStatement pstmt = targetDBConnection.prepareStatement(insertStmt);
String tblName = (String)tableNames.get(index);
System.out.println("\t\t\tFor Table: " + tblName + " \n\t\t\tStmt = " + insertStmt);
ArrayList<ArrayList<String>> dataSet = (ArrayList)insertDataMap.get(tblName);
Iterator var10 = dataSet.iterator();
while(var10.hasNext()) {
ArrayList<String> row = (ArrayList)var10.next();
pstmt.setString(1, (String)row.get(0));
pstmt.setString(2, (String)row.get(1));
pstmt.addBatch();
}
int[] count = pstmt.executeBatch();
targetDBConnection.commit();
System.out.println("\t\t\tBatch Commit Is Done..\n");
pstmt.close();
}
targetDBConnection.close();
} catch (Exception var12) {
var12.printStackTrace();
System.exit(1);
}
System.out.println("\n\t\tStep 6: Completed");
System.out.println("\t\t-----------------------------------");
}
}