DZone Snippets: Ejboy's Code Snippets

Using JavaScript for ETL transformations

ejboy (Fyodor Kupolov) — Sun, 16 Mar 2008 12:01:32 GMT

Scriptella provides a simple way to perform various transformations in JavaScript (or other scripting language which have a corresponding driver).
Our example transformation consists of 3 steps:
1) Select rows from source table.
2) Transform a column value from number to text
3) Insert a transformed value into a destination table.







    

     

     



    



    

        SELECT * FROM Table_In

        

        

         

                           if (Error_Code < 5) {

                    Error_Code='WARNING'; //Set a transformed value

               } else {

                    Error_Code='ERROR'; //Set a transformed value

               }

               query.next(); //Don't forget to trigger nested scripts execution

            ]]>

How to execute Scriptella ETL files

ejboy (Fyodor Kupolov) — Fri, 07 Dec 2007 21:10:26 GMT

Scriptella ETL provides several ways to execute ETL files:

Invocation from Ant





EtlExecutor.newExecutor(getClass().getResource("etl.xml")).execute(); //Execute etl.xml file loaded from classpath

EtlExecutor.newExecutor(

    servletContext.getResource("/WEB-INF/etl.xml")).execute(); //Execute etl.xml file from web application WEB-INF dir

Integration with Spring Framework

The usage of executor is straightforward:



EtlExecutor exec = (EtlExecutor) beanFactory.getBean("executor");

exec.execute();

See Spring Driver JavaDoc for additional details.

How to produce daily database table dumps in CSV format

ejboy (Fyodor Kupolov) — Fri, 16 Nov 2007 21:08:12 GMT

The following code demonstrates how to produce CSV files with dynamic
file name pattern based on a current day. The produced files have the following naming format:
TABLE_NAME_MM_DD_YYYY.csv







     

        table_name=test

    

          classpath="ojdbc14.jar" user="scott" password="tiger"/>

    

     

        SELECT * FROM ${table_name}

Use Scriptella ETL to run the example.

See How to execute an ETL file from command line, Ant or directly from Java .

Splitting large Scriptella ETL files

ejboy (Fyodor Kupolov) — Wed, 27 Jun 2007 20:33:23 GMT

The following example demonstrates how to split a large Scriptella ETL file into several parts. This example is based on a traditional XML parsed entities approach:

List of Countries and ISO codes Supported by the JVM.

ejboy (Fyodor Kupolov) — Mon, 09 Apr 2007 09:04:33 GMT

The following code displays country names and ISO-codes for the locales supported by the JVM.



        for (Locale locale : Locale.getAvailableLocales()) {

            final String contry = locale.getDisplayCountry();

            if (contry.length() > 0) {

                System.out.println("Country = " + contry + ". ISO code: " + locale.getISO3Country());

            }

        }

Package Name as a Constant

ejboy (Fyodor Kupolov) — Wed, 04 Apr 2007 19:21:14 GMT

getClass().getPackage().getName() is not a reliable solution, because getPackage() can be null depending on a class loader.



static final String PACKAGE_NAME = MyClass.class.getName().substring(0, 

            MyClass.class.getName().lastIndexOf('.'));

Importing XML into a database with Scriptella ETL

ejboy (Fyodor Kupolov) — Sun, 18 Feb 2007 19:07:06 GMT

The following Scriptella ETL simple usage example imports RSS file into a database table.







    

    

classpath="hsqldb.jar"/>

    

        /rss/channel/item

Here is the full version of the example described above. It creates an RSS table, downloads rss file, inserts rss records into a database, converts rss.xml to a plain text file and saves it to rss.txt.







    

    

    

    

    

        /rss/channel/item

Script to insert BLOB from file into a database

ejboy (Fyodor Kupolov) — Sun, 18 Feb 2007 18:26:57 GMT

Scriptella ETL allows inserting files into a database. This is achieved by a simple bind variables extension syntax ?{file ...}.
The following sample initializes table of music tracks. Each track has a DATA field containing a file loaded from an external location. File song1.mp3 is stored in the same directory as etl.xml and song2.mp3 is loaded from the web:

Copy table from one database to another

ejboy (Fyodor Kupolov) — Tue, 13 Feb 2007 18:06:19 GMT

This Scriptella ETL script copies all rows from Src_Table to Dest_Table.
Src_Table contains the following columns: id, first_name, last_name
Dest_Table contains the following columns: id, name
The name column of the Dest_Table is produced by a concatenation of first_name and last_name from the Src_Table
This example demonstrates HSQLDB-To-Oracle copy procedure, although it works between virtually any databases.







                classpath="hsqldb.jar" user="sa"/>

                classpath="ojdbc14.jar" user="scott" password="tiger"/>

  

  

      SELECT * FROM Src_Table --Selects all rows

Load CSV data into a database (Scriptella ETL tool)

ejboy (Fyodor Kupolov) — Tue, 13 Feb 2007 09:42:42 GMT

This example demonstrates usage of Scriptella ETL Tool to load CSV data into a database table.

Input CSV file data.csv:



id,priority,summary,status

1,Critical,NullPointerException in Main class,Open

5,Low,"Checkstyle, PMD, Findbugs issues",Reopened

7,Low,Maven integration,Open

10,High,SPI API,Closed

The CSV loading script has the following content:







  

        classpath="ojdbc14.jar" user="scott" password="tiger"/>

Use RegEx to filter CSV data:





    

    ,,,open|reopened