Re: best simple Java ETL

From:

timjowers <timjowers@gmail.com>

Newsgroups:

comp.lang.java.programmer

Date:

Wed, 13 Jan 2010 11:47:09 -0800 (PST)

Message-ID:

<5489405f-fb9a-4641-9675-415e98b7c8e0@s31g2000yqs.googlegroups.com>

On Jan 13, 11:42 am, timjowers <timjow...@gmail.com> wrote:

On Jan 13, 10:37 am, "D@niele" <danymbox-n...@yahoo.it> wrote:

Il 13/01/2010 16:26, timjowers ha scritto:

Quick Google reveals Pentaho and Enhydra Octopus but these both seem
quite complex. We just want something to suck from Oracle to Sybase
and even use the same db schemas (I mean "schema" in the denotation
and not the typical Oracle connotation).

We can use some simply Java to do it but does someone already have a
tool?

Thanks,
TimJowers

You can try "talend"; I think it's a good graphical ETL tool based on
eclipse.

Thanks,

They already have Informatica Power Center and everything.... they
just want a simple-to-use command line thing. Maye talend or ocotopus
creates a script file which can then be used from the command line in
nightly updates?

It's pretty easy actually. Thanks to getObject/setObject. Assuming the
destination table already exists:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import com.util.Cn;
import com.util.DBConfig;

public class ExtractAndLoadTable {

  public Connection cn;
  public static int BATCH_MAX = 2000; // batch updates to be faster
but don't blow the memory out on the db driver (sybase).

  public ExtractAndLoadTable( DBConfig dbConfig ) {
    cn = Cn.getConnection(dbConfig);
  }

  public void copy(Connection inConnection, String inTable, Connection
outConnection, String outTable) throws SQLException{
    String sqlFind = "Select * from " + inTable;
    PreparedStatement pstatement = inConnection.prepareStatement
( sqlFind );
    ResultSet rsIn = pstatement.executeQuery();
    String sqlInsert = "insert into " + outTable +" (";
    String questionMarks="";
    ResultSetMetaData rsmd = rsIn.getMetaData();
    int iColumns = rsmd.getColumnCount();
    for( int c=1; c<=iColumns; c++ ) {
      if( c>1 ) {
        sqlInsert += ",";
        questionMarks += ",";
      }
      sqlInsert += rsmd.getColumnName(c);
      questionMarks += "?";
    }
    sqlInsert += ") " +"values ("+questionMarks+")";
    PreparedStatement pstatementOut = outConnection.prepareStatement
( sqlInsert );
    int iRecords = 0;
    while( rsIn.next() ) {
      for( int c=1; c<=iColumns; c++ ) {
        pstatementOut.setObject(c, rsIn.getObject(c));
      }
      pstatementOut.addBatch();
      iRecords++;
      if( iRecords > BATCH_MAX ) {
        iRecords = 0;
        pstatementOut.executeBatch();
      }
    }
    if( iRecords > 0 )
      pstatementOut.executeBatch();
  }

  public static void main(String[] args) throws SQLException {
    DBConfig dbConfig = null;
    dbConfig = new DBConfig("QRTD","oracle","cltrsksddb01",
49125,"CARS_OPS","pass1234");
    dbConfig.schema = "CARS_OPS";
    Connection cnIn = Cn.getConnection(dbConfig);
    dbConfig = new DBConfig("CARS_OPS","postgres","localhost",
5432,"postgres","letmein12#");
    dbConfig.catalog = "CARS_OPS";
    Connection cnOut = Cn.getConnection(dbConfig);
    ExtractAndLoadTable elt = new ExtractAndLoadTable(dbConfig);
    try{
     elt.copy( cnIn, "TAN_LMTGCI_INDUSTRYGROUP", cnOut,
"TAN_LMTGCI_INDUSTRYGROUP" );
    }catch(SQLException sqle) {
      while(sqle != null ) {
        System.err.println( sqle );
        sqle = sqle.getNextException();
      }
    }
  }

}

// Note, used the db2app code base from source force to simplify stuff.