best simple Java ETL

[timjowers]

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

 

[D@niele]

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.

 

[timjowers]

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




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?

 

[timjowers]

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.

 

[Andrey Yavin]

Try Scriptella ETL - An open source (Apache licensed!), fast, powerful and simple ETL written in Java with a simple one-liner for integration with Java code, it also supports Spring Framework.

Example of copying a table between different databases:

<etl>
    <connection id="in" url="jdbc:oracle:thin:@localhost:1521:ORCL" 
          classpath="ojdbc14.jar" user="scott" password="tiger"/>

    <connection id="out" url="jdbc:mysql://localhost:3306/fromdb" 
          classpath="mysql-connector.jar" user="user" password="password"/>

    <!-- Copy all table rows from one to another database -->
    <query connection-id="in">
        SELECT * FROM Src_Table
        <!-- For each row executes insert -->  
        <script connection-id="out"> 
            INSERT INTO Dest_Table(ID, Name) VALUES (?id,?name)
        </script>
    </query>
</etl>

It supports virtually any JDBC-compliant database and comes with built-in support for CSV, XML or an arbitrary text data. More examples are available in the tutorial