Loading snowflake from Oracle

Single class to illustrate loading data…

import java.sql.*;
import java.util.*;

public class loadSnowflake {
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Class.forName("com.snowflake.client.jdbc.SnowflakeDriver");
    Properties properties = new Properties();
    properties.put("user", "showard");
    properties.put("password", "*****");
    properties.put("account", "rcaccount#");
    properties.put("schema", "PUBLIC");
    Connection connection = DriverManager.getConnection("jdbc:snowflake://rcaccount#.us-east-1.snowflakecomputing.com?db=SDHPOC", properties);
    Connection connOra = DriverManager.getConnection("jdbc:oracle:thin:showard/****@orahost:1521/service.domain");
    ResultSet rst = connOra.createStatement().executeQuery("select * from sa_tran_head where tran_datetime > sysdate - 7 and rownum <= 1000");
    ResultSetMetaData rsmd = rst.getMetaData();
    PreparedStatement snowflakePst = connection.prepareStatement("insert into snowflake_sa_tran_head values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                                                                                                            ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    int i = 0;
    while (rst.next()) {
      for (int j = 1; j <= rsmd.getColumnCount(); j++) {
	if (rsmd.getColumnTypeName(j).equals("NUMBER")) {
          snowflakePst.setLong(j,rst.getLong(j));
	}
	else if (rsmd.getColumnTypeName(j).equals("DATE")) {
          snowflakePst.setDate(j,rst.getDate(j));
	}
	if (rsmd.getColumnTypeName(j).equals("VARCHAR2")) {
          snowflakePst.setString(j,rst.getString(j));
	}
      }
      snowflakePst.addBatch();
      if (++i % 1000 == 0) {
        System.out.println("Starting to execute batch (" + i + ") at " + new java.util.Date());
   	snowflakePst.executeBatch();
	connection.commit();
        System.out.println("Finished batch (" + i + ") at " + new java.util.Date());
      }
    }
    try {
      snowflakePst.executeBatch();
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
    }
    connection.commit();
    connection.createStatement().execute("truncate table if exists snowflake_sa_tran_head");
    System.out.println("loaded then truncated data");
    //too lazy to close, doesn't really matter since JVM is exiting...
  }
}

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.