Perl Goodness

I managed to get the ETL script from before reimplimented in perl.  In PHP, with 1 day’s worth of data, it took about 14 minutes to process.  Now, in perl, it takes just 2 minutes to process.  Here’s the code:

#!/usr/bin/perl

# use strict
use strict;

# Use MySQL connection library
use Mysql;

# use CPAN's date::Format and date::Parse libraries
use Date::Format;
use Date::Parse;

my @row;
my $sql;
my $date_exec;
my @date_row;
my $localdatekey;
my $time_exec;
my @time_row;
my $localtimekey;
my $gmt_ts_exec;
my @gmt_ts_row;
my $gmttimestamp;
my $gmt_date_exec;
my @gmt_date_row;
my $gmtdatekey;
my $gmt_time_exec;
my @gmt_time_row;
my $gmttimekey;

# MySQL config variables
my $host     = "";
my $user     = "";
my $database = "CLICKSTREAM_OLTP";
my $pw       = "";

# Connect to MySQL
my $connect = Mysql->connect($host, $database, $user, $pw);

# Select the transaction schema
$connect->selectdb($database);

# query the transaction schema
my $sql = "SELECT `TIMESTAMP`, 1MINLOAD, 5MINLOAD, 15MINLOAD, SWAP, FREE, BUFFER, CACHE, SWAPIN, SWAPOUT,
 IOIN, IOOUT, USER, SYS, IDLE, IOWAIT, INFOCON FROM CPU_MEMORY C1";

my $execute = $connect->query($sql);

# lock the tables
my $lock_query = "LOCK TABLES CLICKSTREAM_OLTP.CPU_MEMORY C1 READ, CLICKSTREAM_OLTP.CPU_MEMORY WRITE,
 00CLICKSTR.01CPUFACT WRITE, 00CLICKSTR.02LCLDTEDM READ, 00CLICKSTR.02LCLTMEDM READ,
 00CLICKSTR.02GMTDTEDM READ, 00CLICKSTR.02GMTTMEDM READ, mysql.time_zone_name READ, mysql.time_zone READ,
 mysql.time_zone_transition_type READ, mysql.time_zone_transition READ";

my $lock_execute = $connect->query($lock_query);

#$connect->query("BEGIN");

# loop through rows in transaction schema CPU table
while (@row = $execute->fetchrow()) {
 $sql          = "SELECT id02LCLDTEDM FROM 00CLICKSTR.02LCLDTEDM WHERE 03SQLDATE = DATE('" . $row[0] . "')
 LIMIT 1";

 $date_exec    = $connect->query($sql);
 @date_row     = $date_exec->fetchrow();
 $localdatekey = $date_row[0];

 $sql          = "SELECT id02LCLTMEDM FROM 00CLICKSTR.02LCLTMEDM WHERE 03HOURS24 = HOUR('" . $row[0] . "')
 AND 03MINUTES = MINUTE('" . $row[0] . "') AND 03SECONDS = SECOND('" . $row[0] . "') LIMIT 1";

 $time_exec    = $connect->query($sql);
 @time_row     = $time_exec->fetchrow();
 $localtimekey = $time_row[0];

 # now do the GMT dates
 $sql           = "SELECT CONVERT_TZ('" . $row[0] . "', 'CST6CDT', 'GMT')";

 $gmt_ts_exec   = $connect->query($sql);
 @gmt_ts_row    = $gmt_ts_exec->fetchrow();
 $gmttimestamp  = $gmt_ts_row[0];

 $sql           = "SELECT id02GMTDTEDM FROM 00CLICKSTR.02GMTDTEDM WHERE 03SQLDATE = DATE('$gmttimestamp')
 LIMIT 1";
 $gmt_date_exec = $connect->query($sql);
 @gmt_date_row  = $gmt_date_exec->fetchrow();
 $gmtdatekey    = $gmt_date_row[0];

 $sql           = "SELECT id02GMTTMEDM FROM 00CLICKSTR.02GMTTMEDM WHERE 03HOURS24 = HOUR('$gmttimestamp')
 AND 03MINUTES = MINUTE('$gmttimestamp') AND 03SECONDS = SECOND('$gmttimestamp') LIMIT 1";
 $gmt_time_exec = $connect->query($sql);
 @gmt_time_row  = $gmt_time_exec->fetchrow();
 $gmttimekey    = $gmt_time_row[0];

 my ($timestamp, $_1minload, $_5minload, $_15minload, $swap, $free, $buffer, $cache, $swapin, $swapout,
 $ioin, $ioout, $user, $sys, $idle, $iowait, $infocon) = @row;

 # build the insert query
 $sql = "INSERT INTO 00CLICKSTR.01CPUFACT(04GMTDTKEY, 04GMTTMKEY, 04LCLDTKEY, 04LCLTMKEY, 051MINLOAD,
 055MINLOAD, 0515MINLOAD, 05SWAP, 05FREE, 05BUFFER, 05CACHE, 05SWAPIN, 05SWAPOUT, 05IOIN, 05IOOUT,
 05USER, 05SYS, 05IDLE, 05IOWAIT, 04INFOCON) VALUES ($gmtdatekey, $gmttimekey, $localdatekey,
 $localtimekey, $_1minload, $_5minload, $_15minload, $swap, $free, $buffer, $cache, $swapin, $swapout,
 $ioin, $ioout, $user, $sys, $idle, $iowait, '$infocon')";

 # test the insert query

 # execute the insert query
 $connect->query($sql);

 # build the delete query
 $sql = "DELETE FROM CLICKSTREAM_OLTP.CPU_MEMORY WHERE TIMESTAMP = '$timestamp' LIMIT 1";

 # execute the delete query
 $connect->query($sql);

}

$connect->query("UNLOCK TABLES");
#$connect->query("COMMIT");
Advertisements

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: