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.

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:

convert apache http combined logs into sql (and import it into a mysql database eventually)

slim (Slim Amamou) — Sat, 24 Mar 2007 10:40:53 GMT

you need to extract the data in your http server log files and put it in a database to query it with your usual tools using SQL. this perl script does just this.

it was hard to find it, that's why i put it here.



#!/usr/bin/perl -w

# Written by Aaron Jenson.

# Original source: http://www.visualprose.com/software.php

# Updated to work under Perl 5.6.1 by Edward Rudd

# Updated 24 march 2007 by Slim Amamou 

#  - output SQL with the option '--sql'

#  - added SQL create table script to the HELP

#

#  NOTE : you need the TimeDate library (http://search.cpan.org/dist/TimeDate/)

#

use strict;

use Getopt::Long qw(:config bundling);

use DBI;

use Date::Parse;



my %options = ();

my $i = 0;

my $sql = '';

my $valuesSql = '';

my $line = '';

my $dbh = 0;

my $sth = 0;

my @parts = ();

my $part;

my $TIMESTAMP = 3;

my $REQUEST_LINE = 4;

my @cols = (

	'remote_host',			## 0

	'remote_logname',		## 1

	'remote_user',			## 2

	'request_time',			## 3.string

	'time_stamp',			## 3.posix

	'request_line',			## 5

	'request_method',		## 6

	'request_uri',			## 7

	'request_args',			## 8

	'request_protocol',		## 9

	'status',				## 10

	'bytes_sent',			## 11

	'referer',				## 12

	'agent'					## 13

);

my $col = '';



GetOptions (\%options,

		"version" => sub { VERSION_MESSAGE(); exit 0; },

		"help|?" => sub { HELP_MESSAGE(); exit 0; },

		"host|h=s",

		"database|d=s",

		"table|t=s",

		"username|u=s",

		"password|p=s",

		"logfile|f=s",

		"sql");



$options{host} ||= 'localhost';

$options{database} ||= '';

$options{username} ||= '';

$options{password} ||= '';

$options{logfile} ||= '';

$options{sql} ||= '';



if( ! ($options{database} || $options{sql}))

{

	HELP_MESSAGE();

	print "Must supply a database to connect to.\n";

	exit 1;

}



if( ! $options{table} )

{

	HELP_MESSAGE();

	print "Must supply table name.\n";

	exit 1;

}



if( $options{logfile} )

{

	if( ! -e $options{logfile} )

	{

		print  "File '$options{logfile}' doesn't exist.\n";

		exit 1;

	}

	open(STDIN, "<$options{logfile}") || die "Can't open $options{logfile} for reading.";

}



if( $options{database} )

{

	$dbh = Connect();

	if (! $dbh) {

		exit 1;

	}

}



$sql = "INSERT INTO $options{table} (";

foreach $col (@cols)

{

	$sql .= "$col," if( $col );

}

chop($sql);

$sql .= ') VALUES (';

my ($linecount,$insertcount) = (0,0);

while($line = )

{

	$linecount++;

	@parts = SplitLogLine( $line );

	next if( $parts[$TIMESTAMP+1] == 0 );

	$valuesSql = '';

	for( $i = 0; $i < @cols; ++$i )

	{

		$parts[$i] =~ s/\\/\\\\/g;

		$parts[$i] =~ s/'/\\'/g;

		$valuesSql .= "'$parts[$i]'," if( $cols[$i] );

	}

	chop($valuesSql);



	if( $options{database} )

	{

		$sth  = $dbh->prepare("$sql$valuesSql)");

		if( ! $sth->execute() )

		{

			print "Unable to perform specified query.\n$sql$valuesSql\n" . $sth->errstr() . "\n";

		} else {

			$insertcount++;

		}

		$sth->finish();

	}

	if( $options{sql} )

	{

		print "$sql$valuesSql);\n";

	}

}

if( ! $options{sql} )

{

	print "Parsed $linecount Log lines\n";

	print "Inserted $insertcount records\n";

	print "to table '$options{table}' in database '$options{database}' on '$options{host}'\n";

}



# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 

# Connects to a MySQL database and returns the connection.

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 

sub Connect

{

	my $dsn = "DBI:mysql:$options{database};hostname=$options{host}";

	return DBI->connect( $dsn, $options{username}, $options{password} );

}





# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 

# Splits up a log line into its parts.

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 

sub SplitLogLine

{

	my $line = shift;

	my $i = 0;

	my $inQuote = 0;

	my $char = '';

	my $part = '';

	my @parts = ();

	my $count = 0;

	chomp($line);

	for( $i = 0; $i < length($line); ++$i )

	{

		$char = substr($line, $i, 1);

		if( $char eq ' ' && ! $inQuote )

		{

			## print "Found part $part.\n";

			if( $count == $TIMESTAMP )

			{

				push(@parts, "[".$part."]");

				$part = str2time($part);

			}

			push(@parts, $part);

			if( $count == $REQUEST_LINE )

			{

				my @request = split(/[ ?]/, $part);

				push(@parts, $request[0]);

				push(@parts, $request[1]);

				if( $request[3] )

				{

					push(@parts, $request[2]);

					push(@parts, $request[3]);

				}

				else

				{

					push(@parts, '');

					push(@parts, $request[2]);

				}

				$count += 5;

			}

			else

			{

				++$count;

			}

			$part = '';

		}

		elsif( $char eq '"' || $char eq '[' || $char eq ']' )

		{

			$inQuote = !$inQuote;

		}

		else

		{

			$part .= $char;

		}

	}

	push(@parts,$part) if $part;



	return @parts;

}





# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 

# Prints the usage/help message for this program.

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 

sub HELP_MESSAGE

{

	print<Imports an Apache combined log into a MySQL database.

Usage: mysql_import_combined_log.pl -d  -t  [-h ] [-u ] [-p ] [-f  --host|-h          The host to connect to.  Default is localhost.

 --database|-d  The database to use.  Required.

 --username|-u       The user to connect as.

 --password|-p       The user's password.

 --table|-t        The name of the table in which to insert data.

 --logfile|-f       The file to read from.  If not given, data is read from stdin.

 --sql                         Output SQL

 --help|-?                     Print out this help message.

 --version                     Print out the version of this software.



----------------------------------

-- SQL create statements for the table

--



create table  (

    remote_host varchar(50) ,

    remote_logname varchar(50) ,

    remote_user varchar(50) ,

    request_time char(28),

    time_stamp varchar(10) ,

    request_line varchar(255),

    request_method varchar(10) ,

    request_uri varchar(255),

    request_args varchar(255),

    request_protocol varchar(10) ,

    status varchar(10) ,

    bytes_sent varchar(10) ,

    referer varchar(255) ,

    agent varchar(255)

);



EOF

}







# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 

# Prints the version information for this program

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 

sub VERSION_MESSAGE

{

	print "mysql_import_combined_log.pl version 1.2\n";

	print "Version 1.0 Written by Aaron Jenson.\n";

	print "Update to work with perl 5.6.1 by Edward Rudd\n";

}



1;






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

        

      

  








Scriptella script to shutdown HSQLDB server
ejboy (Fyodor Kupolov) — Wed, 20 Dec 2006 15:33:45 GMT
The following snippet performs shutdown of HSQLDB database.









    

    







Running Scriptella from Ant is simple:











Command line launcher is even simpler:



scriptella [file_path]

DZone Snippets: etl code

Using JavaScript for ETL transformations

How to execute Scriptella ETL files

Splitting large Scriptella ETL files

convert apache http combined logs into sql (and import it into a mysql database eventually)

Importing XML into a database with Scriptella ETL

Script to insert BLOB from file into a database

Copy table from one database to another

Scriptella script to shutdown HSQLDB server