Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > best simple Java ETL

Reply
Thread Tools

best simple Java ETL

 
 
timjowers
Guest
Posts: n/a
 
      01-13-2010
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
 
Reply With Quote
 
 
 
 
D@niele
Guest
Posts: n/a
 
      01-13-2010
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.
 
Reply With Quote
 
 
 
 
timjowers
Guest
Posts: n/a
 
      01-13-2010
On Jan 13, 10:37*am, "D@niele" <(E-Mail Removed)> 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?

 
Reply With Quote
 
timjowers
Guest
Posts: n/a
 
      01-13-2010
On Jan 13, 11:42*am, timjowers <(E-Mail Removed)> wrote:
> On Jan 13, 10:37*am, "D@niele" <(E-Mail Removed)> 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.
 
Reply With Quote
 
Andrey Yavin Andrey Yavin is offline
Junior Member
Join Date: Aug 2012
Posts: 1
 
      08-07-2012
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:

Code:
<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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sr. Data Integrator Analyst (ETL) (Contract) - Baltimore, MD - Long Term kin Java 0 05-07-2007 07:09 PM
Java ETL Programmer Sought for Toronto - Perm position hr@hessjobs.com Java 0 05-30-2006 07:04 PM
python ETL arielgr@gmail.com Python 3 08-04-2005 05:55 PM
2nd CFV: comp.databases.etl Cara XML 0 12-04-2003 05:09 PM
RFD: comp.databases.etl Cara Altman XML 0 09-05-2003 06:09 AM



Advertisments