Administrator

How To Use Java JDBC Driver To Connect Snowflake

How To Use Java JDBC Driver To Connect Snowflake

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

  1. 🙋 Does snowflake support JDBC type-4 driver?
  2. 🙋 Does Snowflake JDBC Driver required JDK1.8 or higher?
  3. 🙋 What are the pre-requisite to run a Snowflake JDBC driver program?
  4. 🙋 How to download and configure JDBC driver to interact with Snowflake.
  5. 🙋 How JDBC driver works if my Snowflake authentication happens via SSO or Idp?
  6. 🙋 Can this JDBC driver support Java/Maven based projects?If so, how the POM dependency looks like.
  7. 🙋 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:

  1. Snowflake Cloud Data Warehouse Instance
  2. 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
//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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
//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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
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-----------------------------------");
    }
}