Scriptella ETL, from SQL Server to PostgreSQL

Posted on Wednesday, 22 August 2012

1



Download Scriptella and unpack it in some folder:

/home/user/applications/scriptella/

From GNU/Linux in our .bashrc

#Scriptella things
export SCRIPTELLA_DIR="/home/user/applications/scriptella/scriptella-1.0"
PATH=${PATH}:$SCRIPTELLA_DIR/bin

This will give us access to the scriptella.sh from any directory. We need the  respective drivers from SQL Server and PostgreSQL to achieve a successful connection. At this time we can download the SQLServer driver sqljdbc_4.0.2206.100_enu.tar.gz  and the PostgreSQL driver postgresql-9.1-902.jdbc4.jar

We can put each driver in some directory that will have access from our scriptella scripts, that may be:

/home/user/applications/scriptella/drivers/sqlserver

and

/home/user/applications/scriptella/drivers/postgresql

or directly in the:

/home/user/applications/scriptella/scriptella-1.0/lib

Create the template..

[user@user-pc bea]$ scriptella.sh -t
Aug 21, 2012 11:46:23 AM scriptella.tools.template.TemplateManager create
INFO: Files etl.xml, etl.properties have been successfully created.

Now we have two files, I’ll rename and copy the etl.properties, obtaining two files: etl.properties.postgresql and etl.properties.sqlserver with this contents:

#Scriptella ETL Configuration Properties
driver1=com.microsoft.sqlserver.jdbc.SQLServerDriver
url1=jdbc:sqlserver://200.xx.xxx.xx:1433;databaseName=BASE1
user1=user1
password1=password1
classpath1=/home/bduarte/applications/driversqlserver/sqljdbc_4.0/enu/sqljdbc4.jar

and

#Scriptella ETL Configuration Properties PostgreSQL
driver2=org.postgresql.Driver
url2=jdbc:postgresql://200.xx.xxx.xx:5432/BASE2
user2=user2
password2=password2
classpath2=/home/bduarte/applications/driverpostgres/postgresql-9.1-902.jdbc4.jar

We complete the etl.xml


<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
 <etl>
 <description>Scriptella ETL File Template. </description>
 <properties>

<!--Loading from external properties file-->
 <include href="etl.properties.sqlserver"/>
 <include href="etl.properties.postgresql"/>
 </properties>
 <!-- Connection declarations -->
 <connection id="sqlserver" driver="$driver1" url="$url1" user="$user1" password="$password1" classpath="$classpath1"/>
 <connection id="postgresql" driver="$driver2" url="$url2" user="$user2" password="$password2" classpath="$classpath2"/>

<query connection-id="sqlserver">
Select * from big_sql_complecated_stuff

<script connection-id="postgresql">
 INSERT INTO some_table(
 dni, lastname, name, address, dateofbirth, etc)
 VALUES (?1, ?2, ?3, ?4, ?5, ?6);
 </script>
 </query>
 </etl>

Then we run, and that is all

[user@user-pc sandbox]$ scriptella.sh etl.xml

Aug 21, 2012 2:45:38 PM <INFO> Execution Progress.Initializing properties: 1%
Aug 21, 2012 2:45:39 PM <INFO> Execution Progress.Connection id=sqlserver, JdbcConnection{com.microsoft.sqlserver.jdbc.SQLServerConnection}, Dialect{Microsoft SQL Server 10.00.1600} registered: 3%
Aug 21, 2012 2:45:39 PM <INFO> Execution Progress.Connection id=postgresql, JdbcConnection{org.postgresql.jdbc4.Jdbc4Connection}, Dialect{PostgreSQL 7.4.5} registered: 5%
Aug 21, 2012 2:45:39 PM <INFO> Execution Progress./etl/query[1] prepared: 10%
Aug 21, 2012 2:45:39 PM <INFO> Registered JMX mbean: scriptella:type=etl,url="file:/home/user/sandbox/etl.xml"
Aug 21, 2012 2:45:51 PM <INFO> Execution Progress./etl/query[1] executed: 95%
Aug 21, 2012 2:45:51 PM <INFO> Execution Progress.Complete
Aug 21, 2012 2:45:51 PM <INFO> Execution statistics:
Executed 1 query, 1 script, 9808 statements
/etl/query[1]: Element successfully executed (1 statement). Working time 237 milliseconds. Avg throughput: 4.21 statements/sec.
/etl/query[1]/script[1]: Element successfully executed 9807 times (9807 statements). Working time 12182 milliseconds. Avg throughput: 805.02 statements/sec.
Total working time: 13.02 seconds
Aug 21, 2012 2:45:51 PM <INFO> Successfully executed ETL file /home/user/sandbox/etl.xml
Posted in: ETL, GNU/Linux