best simple Java ETL

Discussion in 'Java' started by timjowers, Jan 13, 2010.

  1. timjowers

    timjowers Guest

    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
    timjowers, Jan 13, 2010
    #1
    1. Advertising

  2. timjowers

    D@niele Guest

    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.
    D@niele, Jan 13, 2010
    #2
    1. Advertising

  3. timjowers

    timjowers Guest

    On Jan 13, 10:37 am, "D@niele" <> 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?
    timjowers, Jan 13, 2010
    #3
  4. timjowers

    timjowers Guest

    On Jan 13, 11:42 am, timjowers <> wrote:
    > On Jan 13, 10:37 am, "D@niele" <> 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.
    timjowers, Jan 13, 2010
    #4
  5. timjowers

    Andrey Yavin

    Joined:
    Aug 7, 2012
    Messages:
    1
    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
    Andrey Yavin, Aug 7, 2012
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Cara Altman

    RFD: comp.databases.etl

    Cara Altman, Sep 5, 2003, in forum: XML
    Replies:
    0
    Views:
    567
    Cara Altman
    Sep 5, 2003
  2. Cara
    Replies:
    0
    Views:
    423
  3. Replies:
    0
    Views:
    380
  4. python ETL

    , Aug 1, 2005, in forum: Python
    Replies:
    3
    Views:
    779
    Jorgen Grahn
    Aug 4, 2005
  5. kin
    Replies:
    0
    Views:
    340
Loading...

Share This Page