Parallel processing of workflows - 1

This is the first post in a series of post about parallel job scheduling.

In computer operations batch or background processing; a workflow is a number of steps where the steps are dependent of one or more of the predecessing steps.

Normally you run these steps in sequence one by one. From the viewpoint of a workflow you check if all prerequisites are satisfied for the first step and then you execute the step, when the first step has successfully executed you repeat the the process for the next step and so on until all steps are executed. In real life it is a bit more complicated, sometimes you like to bump over a step and you have to decide what actions to take if a step execution is unsuccessful. But basically you run all steps in sequence.

In the old days of single CPU computers (or very few CPUs) this was all fine and dandy. If you needed to speed things up all you could do was to run a few non dependent schedules in parallel. And that was OK most of times, since there were not so much data to process. Now this has changed, the data volumes processed in workflows has virtually exploded, and grows much faster the processing powers of computers. This is especially true for Business Intelligence activities, Extract Transform and Load processes may process very large data volumes. Volumes so large there is not time enough to process workflow steps in strict sequence. This leaves you with little choice, in order to have the job or rather the workflow done, steps must be processed in parallel one way or another. Today when multi CPU computers are a commodity we have an opportunity to parallel process steps in workflows. The workflow engine must allow for simple and safe parallel processing of workflow steps. There is no use for a multi CPU computer if you need to be a rocket scientist to set up parallel processing workflows.

Parallel processing in computers is complex. Execute a workflow is a high level process, we do not have to deal with the nitty gritty of low level parallel processing like setting up threads and semaphores, still on a high level parallel is complex. Not only is it fundamentally more complex to supervise two process than one. (Those readers who have had the pleasure of attending two three years old in the playground know what I mean.) All the logics needed for sequential processing of workflow steps, deciding when steps go right or wrong etc. must work for steps in parallel which is significantly harder to manage than the plain sequential processing.

The are some workflow step dependencies very common in batch processing, e.g.

1. Step a and b can run in parallel and the subsequent step c is dependent of them and steps d and e is dependent of step c.
2. Step a and b can run in parallel, c is dependent on a and cut up in ‘sub-steps’ and step d is dependent on c. Finally step e is dependent on b and c.    

In this picture I tried to depict the two workflows. In real life it is a bit more complicated, e.g. what to do when steps go wrong? But most batch workflow dependencies are combinations of these two workflows.

How do you describe these process patterns, logic and dependencies and how do you process them? In the next posts , I will describe how I dealt with parallel processing in my job scheduler and the accompanying ITL language.

 

Twitter from the MySQL Data Warehouse

Some weeks ago I created a tweeting PHP script . Now I use this script for posting job activity from the Data Warehouse on Twitter. Since the  Data Warehouse jobs are registered in MySQL databases, we had to sum up the figures and feed them into the PHP script twitter02.php.  It turned out to an easy task, using the Integration Tag Language . I decided to test this with a  status message showing how many jobs have run the last 24 hours. Here is the schedule:

The first job ‘crtSQLMsg’ sums up all job activity and pass the result to the sqlconverter_CSV.php  script which converts the result table to a file:

Which looks like:

Now it’s only to post this file with the job ‘twittaMsg’, if you study the job you see how the job status message is prefixed with #DataWarehouse.

If you follow @tooljn at Twitter you have seen this tweet as:

 

I’m very happy with the SQL converter  functionality, which out of the box converted the result table into a readable message, and the  @tag GETSQLMSG  which slurps up the message in the subsequent twittaMsg job.

I end this post with the sqlconverter_CSV.php script:

<?php

/**

* SQL result converter - dynamically included in function {@link execSql()}

*

* This converter converts a SQL select result to a CSV file.

*

* This converter also accepts:

*

* Syntax:  <sqlconverter name='sqlconverter_CSV.php' target='report0' headers='no' delim='space' enclosed=''/>

* 1 delim                field delimiter                 default ';' semicolon

* 2 header        field headers                default TRUE/yes

* 3 enclosed        field enclosed by                default NULL

*

* Note delim ' ' doesn't work for unknown reason, so use 'space' instead. Bug?

*

* @see sqlconverter_default.php

* @author Lasse Johansson <lars.a.johansson@se.atlascopco.com>

* @version  1.0.0

* @package adac

* @subpackage sqlconverter

*/

$metafile = $sqltarget.'meta_';

$metasfx = '.TXT';

$targetsfx = 'CSV';

$fieldDelimiter = ';';        // default

$fieldEnclosed = "'";        // default

$headers = TRUE;        // default

if(array_key_exists('delim',$xmlconverter))

  $fieldDelimiter = is_string($xmlconverter['delim']) ? $xmlconverter['delim'] : $xmlconverter['delim'][0]['value'];

if(array_key_exists('enclosed',$xmlconverter))

  $fieldEnclosed = is_string($xmlconverter['enclosed']) ? $xmlconverter['enclosed'] : $xmlconverter['enclosed'][0]['value'];

if(array_key_exists('headers',$xmlconverter))

  $t_headers = is_string($xmlconverter['headers']) ? $xmlconverter['headers'] : $xmlconverter['headers'][0]['value'];

if ($t_headers == 'no') $headers = FALSE;

if ("$fieldDelimiter" == 'space') $fieldDelimiter = ' ';

if ("$$fieldEnclosed" == 'space') $fieldEnclosed = ' ';

$sqllog->logit('Note',"Enter sqlconverter_CVS.php using target=$sqltarget");

if(is_numeric(substr($sqltarget, -1,1))) {

        $metafile = "$metafile$metasfx";

        $sqltarget = "$sqltarget.$targetsfx";

} else {

        clearstatcache();

        for ($x=0; 1==1; $x++){

                if (!file_exists("$metafile$x$metasfx")){

                        $metafile = "$metafile$x$metasfx";

                        $sqltarget = "$sqltarget$x.$targetsfx";

                        break;

                }

        }

}

if (file_exists($sqltarget)) $fpc_flag = 'FILE_APPEND';

else $fpc_flag = NULL;

$report = '';

if ($fpc_flag == NULL and $headers){;

        $meta = '';

        while ($finfo = $result->fetch_field()) {

          $report .= $finfo->name."$fieldDelimiter";

          $meta .= sprintf("Name:    %s;", $finfo->name);

          $meta .= sprintf("OrgName:    %s;", $finfo->orgname);

          $meta .= sprintf("Table:    %s;", $finfo->table);

          $meta .= sprintf("OrgTable:    %s;", $finfo->orgtable);

          $meta .= sprintf("Default:    %s;", $finfo->def);

          $meta .= sprintf("MaxLen:    %d;", $finfo->max_length);

          $meta .= sprintf("Len:    %d;", $finfo->length);

          $meta .= sprintf("Charsetnr:    %d;", $finfo->charsetnr);

          $meta .= sprintf("Flags:    %d;", $finfo->flags);

          $meta .= sprintf("Type:    %d;", $finfo->type);

          $meta .= sprintf("Decimals:    %d;", $finfo->decimals);

          $meta .= "\n";

        }  

        file_put_contents($metafile,$meta);

        unset($meta);

        $report .= "\n";

}

//  Here comes the working code

while ($row = $result->fetch_row()) {

  foreach($row as &$fld) {$fld = "$fieldEnclosed"."$fld"."$fieldEnclosed";}

  $rowstr = implode("$fieldDelimiter", $row);

  $log->logit('Note',"$rowstr");

  $report .= $rowstr."\n";

}

if($fpc_flag == 'FILE_APPEND') file_put_contents($sqltarget,$report,FILE_APPEND);

else file_put_contents($sqltarget,$report);

unset($report);

$sqllog->logit('Note',"Exit sqlconverter_CVS.php");

   

Splitting XML-reports zip & CIFS copy them to Windows

The other day I had a meeting with the project manager for an application that receives files from the Data Warehouse . The procedure for sending files to this application is interesting. First we create a complex XML file, then we zip it and lastly we connect to a Windows server share and copy the zip file. Three jobs to create the file, zip it and send it. The first job creates the XML file by execute a SQL Select and then hand result over to the converter  sqlconverter_xml02.php .

 

The second job zips the output file report0.xml.

The third job ships the zip file ItemMessage.xml to the Windows share:

The job navettiCopy2WIN.xml mounts the windows share as a CIFS file and the copy the zip file to the share and finally unmounts the Windows share:

 If you follow the init actions  you see how it’s done. Note I put in a sleep before unmount, in the past we have had some timing problems. If something goes wrong we execute exit failure .

XML wrong tool for data-interchange.

This job schedule is complex and I was not happy when the project manager of the receiving application told me ‘ The file is too large, can you please chop it up in smaller chunks ’. It’s probably their XML parser that cannot cope with large input. Most XML encoding/decoding I’ve seen is done in memory, so very large XML files tend to exhaust RAM. Actually XML code large data streams is a bad idea since XML encoding is verbose and parsing inefficient. I had problems with my SQL to XML conversion. First I based it on PHP simplexml  but when I exhausted the memory, I took the very bad decision to create my own XML coder sqlconverter_xml02.php (see above), it’s a piece of crap but it works. But I should have said no to large XML data-interchange, Google Buffers or CSV or even JSON are better alternatives.

Job Iterator to the rescue.

Anyway to make the other application work I had to split the XML file. Due to the XML mess I thought the split would be problematic, but after some thinking I realized I could use a  job iterator  and it turned out to be very easy to chop the file. The XML converter sqlconverter_xml02.php do not append the result to an existing file but creates a new sequenced numbered file. If you look below you see the target for the sqlconverter is  ItemMessage . The sqlconverter_xml02.php will name the first result file ItemMessage 0 .xml, the next ItemMessage 1 .xml etc. Subsequently the zipfiles job sweep them all up by the glob wildcard ‘ItemMessage * ’. This test was  done in minutes and worked right out of the box. In production the iterator would be SQL generated based on the actual result size.

First I created a job iterator <forevery> with offset and limit for each chunk, then I added the limit clause  to the SELECT statement and changed the SQL target to ItemMessage. (This SQL converter adds a sequence number to the filename if the file already exists.)

Then I just had to slurp up all created output tables in the zip job and send the zipfile to the Windows share.

If you scrutinize the schedule XML  you find there are some functionality under the hood. I’m still very happy with my PHP job scheduler  and the Integration Tag Language.

I end this post with the zipfile.php  script. The ZipArchive and Zipper classes I found somewhere on the net but I forgot to credit the authors, if you know who should have the honor for these classes please tell me.

$ziplib = fullPathName($context,$job['library']);

$ziplibcreate = $job['create'];

$log->enter('Info',"Zip library=$ziplib, create=$ziplibcreate");

$zip = new Zipper;

$ok = $zip->open("$ziplib", ZipArchive::CREATE);

if ($ok === FALSE) {

        $log->logit('Error',"Failed to create/open zip library=$ziplib, create=$ziplibcreate");

        return FALSE;

}

foreach($job['file'] as $finx => $file) {

  foreach (glob($file['name']) as $globfilename) {

      if ($file['name'] == "$globfilename") $localname=$file['localname'];

      else $localname = '';

      $path=$globfilename;

      if ($localname == '') {

              $path_parts = pathinfo("$path");

              $localname = $path_parts['filename'].'.'.$path_parts['extension'];

      }

      if (is_file($path)) {

          $log->logit('Note',"Zip $path into $localname");

          $zip->addFile("$path", "$localname");

      } else {

          $zip->addDir("$path");

      }        

  }

}

$zip->close();

$_RESULT = TRUE;

return $_RESULT;

class Zipper extends ZipArchive  {

    public function addDir($path) {

//        print 'adding ' . $path . "\n";

        $this->addEmptyDir($path);

        $nodes = glob($path . '/*');

        foreach ($nodes as $node) {

            print $node . '<br>';

            if (is_dir($node)) {

                $this->addDir($node);

            } else if (is_file($node))  {

                $this->addFile($node);

            }

        }

    }

} // class Zipper

Moving a Data Warehouse - 3

In some posts I have written about the migration of my Data Warehouse  from my own hardware to Dell servers. This move was partly initiated by my transition to a new job in the company, the management didn’t dare to run the Data Warehouse on the hardware I built .

When I originally designed the Data Warehouse infrastructure two key principles were low cost  and simplicity . I needed a database so I created a database server. I needed an ETL engine so I created an ETL server. Then I needed PhpMyAdmin so I created a PhpMyAdmin server. One function one physical server.  The only extra in my Irons was an extra Network Interface for an internal server network. And in the beginning servers were scrapped IBM desktops. I maximized RAM, replaced the hard disk and added a network interface, dirt cheap. Then I installed a Linux and one application, fired up the new server and forgot about it for two to five years. My servers were mostly replaced when I needed more capacity not due to hardware failures. One of very few hardware problems I have had is described here .

I avoid software tweaking and optimization, I try do do standard installs right from the distro, e.g. I choose ‘big’ for Mysql config file that’s is about how much tuning I do. I have all databases and indexes on the same disk! About half a terabyte database with about eight million queries a day ( I have seen peaks over 15 million queries a day), this is on a custom made server with 16GB RAM.

Now this has changed with the migration to ‘real’ servers. The one server one function  approach would have been all too expensive, so I had a choice either pack more functions in one server or go virtual. I have for some years wanted to test a virtual solution, so I decided to go virtual without testing. I decided I go for two servers one physical database server and virtual host for all other servers. I do not believe for a second you can have a low cost simple virtual high performance database server. But the rest of my Data Warehouse servers could well be virtual, this way I could keep my one function one server  philosophy and still be reasonable cost efficient. I was right and I was wrong.

The new environment is much more complex, virtual servers add a software abstraction layer between the iron and Linux, and by going virtual you also need a software layer between your hard disks and the virtual servers for practical space management. For all this to work you need an expert to manage this environment. And an expert costs and the expert has his own preferences and experiences, e.g. a Linux professional does not necessarily know Mageia Linux. Since we didn’t have in house Linux operations expertise we hired a consultant. A mistake was not to listen to the consultants recommendation of virtualization software and Linux distro.  Not that it’s difficult for a Linux professional to learn another distro it just takes some time, but more important, the support of my now non standard infrastructure it will always be exotic for the consultants operations team. I should have spent more time with the consultants upfront going thru the server setup. We would probably have had a better server setup still adapted to my Data Warehouse.

This is complication I would not have had if we had used Windows Server instead of Linux distros, since Windows is a singular opsys. I do not know if this is good or bad.

I end this post with a humble statement. Still few people seem to have my insights in hardware  and infrastructure for Business Intelligence systems. Actually very few I talk to make any distinctions between any type of applications in this respect, the same hardware fits all give or take some RAM and CPU; that is the adaptation to applications you see. I believe most hardware infrastructure is grossly overpowered/priced and designed for ERP transactional applications.    

Twitter from the coolest Data Warehouse

Last week a colleague said - Why don’t we Twitter the status from our Data Warehouse servers?

My reply was I tried to set it up a year ago or so but I failed. I used the Zend Framework one dot something. But of course I had to try again. So I started by downloading the Zend Framework 2 version 2.2.2. According to the documentation this was a simple task. After failing a number of times I realised the Twitter class was not in the the vs 2.2.2, after some googling around I found the code and installed it. But still my code did not find the ZF2 twitter code. I could not get the ZF2 autoloader to work so I had to require the ZF2 twitter code manually. Now there were some Oauth classes missing, after some googling I found the code and installed it. I could not get the autoloader to include the Oauth code so I had to require the code manually. And now I descended  into class bonanza hell, the Twitter and Oauth classes referred to other classes and interfaces and abstractions and God knows what. Since the autoloader is too hard for me I had to load all manually, trial and error until I had included all code needed to set up my classes to call Twitter. I was able to verify my credentials and fetch basic information about my Twitter account. But when I tried to post ‘Hello World!’ I got error Http return code 400. After trying a good number of times and googling a lot without any result, I had to give up.

One aspect I do not like with Object Orientation is the extreme complexity. All OO projects starts with a few simple classes with logical methods. But as the system grows (and changes) new classes are introduced which inherits from the original simple classes with almost identical methods. And then hell break loose, now come interfaces, roles, traits, mixins, abstractions, adapters and God knows what. And all these entities should be coupled and attached to each other in ways only logical to the developer. It is no simple task to dig into lots of alien errors like:  

PHP Fatal error:  Uncaught exception 'Zend\Http\Client\Adapter\Exception\RuntimeException' with message 'Error in cURL

request: Received HTTP code 400 from proxy after CONNECT' in /home/tooljn/PHPZF/ZendFramework-2.2.2/library/Zend/Http/Client/Adapter/Curl.php:422

Even if you have stack traces (which always are very long) you wonder what code is executed where and why?

There is seldom intuitivity in OO system, i.e. easy to understand and learn how connect OO object, you very often have to learn case by case.

This is more or less how my ZF2 code looked like when I gave up:

require_once 'Zend/Loader/StandardAutoloader.php';

$loader = new Zend\Loader\StandardAutoloader(array('autoregister_zf' => true));

// Register with spl_autoload:

$loader->register();

require_once 'Zend/ZendService/Twitter/Twitter.php';

require_once 'Zend/ZendService/Twitter/Response.php';

require_once 'Zend/ZendOAuth/OAuth.php';

require_once 'Zend/ZendOAuth/Config/ConfigInterface.php';

require_once 'Zend/ZendOAuth/Config/StandardConfig.php';

require_once 'Zend/ZendOAuth/Client.php';

require_once 'Zend/ZendOAuth/Http/Utility.php';

require_once 'Zend/ZendOAuth/Token/TokenInterface.php';

require_once 'Zend/ZendOAuth/Token/AbstractToken.php';

require_once 'Zend/ZendOAuth/Token/Access.php';

require_once 'Zend/ZendOAuth/Signature/SignatureInterface.php';

require_once 'Zend/ZendOAuth/Signature/AbstractSignature.php';

require_once 'Zend/ZendOAuth/Signature/Hmac.php';

array_key_exists('host',$job) ? $hostInfo = $xmlconverter['host'][0]['value'] : $hostInfo = 'twitter';

array_key_exists('app',$job) ? $tapp = $xmlconverter['app'][0]['value'] : $tapp = 'DW status';

$txml = getHostSysDsn($context, $hostInfo, "$tapp");

if (!$txml) return FALSE;

$curl_config = array(

        'adapter' => 'Zend\Http\Client\Adapter\Curl',

        'curloptions' => array(

            CURLOPT_SSL_VERIFYHOST => false,

            CURLOPT_SSL_VERIFYPEER => false));

           

$twitter_config = array(

    'access_token' => array(

        'token'  => $txml['Accesstoken'],

        'secret' => $txml['Accesstokensecret'],

    ),

    'oauth_options' => array(

        'consumerKey' => $txml['Consumerkey'],

        'consumerSecret' => $txml['Consumersecret'],

    ),

    'http_client_options' =>$curl_config

);

//var_dump($twitter_config);

$twitter = new ZendService\Twitter\Twitter($twitter_config);

$twitter->statuses->update('Hello World!');

The last line blows up with an error code 400 from Twitter, I have no idea why.

(In all fairness I should link to a post I wrote a long time ago with a cool example using  Zend Framwork1 at the end  .)

I do not think ZF2 is bad , it’s just too complex for me, and this often the case with OO systems. OO invites you to go complex over time. But this does not have to be, in this particular case I found an OO gem , A very simple class for posting on Twitter (the author  David Grudl  claims you can do any Twitter task from this class). Using David’s OO class I only had to write this code:

$message = trim(substr($job['message'][0]['value'],0,140));

$log->logit('Info',"Enter script=$scriptName message=$message");

require_once '/home/tooljn/TWITTER/twitter-php-master/src/twitter.class.php';

array_key_exists('host',$job) ? $hostInfo = $xmlconverter['host'][0]['value'] : $hostInfo = 'twitter';

array_key_exists('app',$job) ? $tapp = $xmlconverter['app'][0]['value'] : $tapp = 'DW status';

$txml = getHostSysDsn($context, $hostInfo, "$tapp");

if (!$txml) return FALSE;

$twitter = new Twitter($txml['Consumerkey'], $txml['Consumersecret'], $txml['Accesstoken'], $txml['Accesstokensecret']);

try {

        $tweet = $twitter->send("$message");

        $_RESULT = TRUE;

} catch (TwitterException $e) {

        $log->logit('Errror', $e->getMessage());

}

return $_RESULT;

And the code posted on Twitter without any fuzz on first attempt. The code was run from this job :

<?xml version='1.0' encoding='UTF-8' standalone='yes'?>

<schedule mustcomplete='yes' logmsg='This is a Twitter test'>

   <job name='twittaMsg' type='script' pgm='twitter01.php'>

      <iconv input='UTF-8' output='UTF-8' internal='UTF-8'/>

      <message>@TODAY-@NOW Hello Twitter</message>

  </job>

</schedule>

Bluetooth, Spotify and Keith Richards

Finally I starting to find my new phone  useful. After some training I’m not only able to answer incoming calls but I can also call people with ease. Still I find it impossible to use the phone in the car. (When I figure out how the voice control works I may be able to use the phone in the car.) My sons tell me it’s an age thing, older people have difficulties with these new smart phones.

 

Anyway last week I managed to install Spotify in my phone and connected the phone to my home stereo. This is great, with my telephone I can play any music I like. But there was a slight inconvenience my phone was connected with a cord to my amplifier and I didn’t want a long cord lying across the floor so I can disc-jockey from my sofa. After googling around I found this little wonder . So now I can stream music wirelessly from the net via wifi to my smartphone and via bluetooth to my home stereo, extremely comfortable. I always resisted bluetooth with the argument the wire is a feature, making sure the devices stay in place, but to be honest I go bonkers on all wires I have connecting devices and they just get more. It’s time for a rethink. And I been told everyone is streaming wireless these days but me, I’m out of touch with modern times my sons tell me.  

Spotify and Bluetooth is great, from a comfortable position in my Sofa I can play any music of my own choice. But where do Keith Richards comes in the picture? Well I thought now I should reread his biography Life  and play the music he is writing about while reading. I listen to all kinds of music even some modern House music. But I always come back to old Rolling Stones albums, especially Beggars Banquet, Sticky Fingers & Exile on Main Street . In his book Mr Richards writes a lot about blues musicians like Muddy Waters, Little Walters, Elmore James, Robert Johnson. I probably still have lots of LP’s with these guys but I do not have a record player anymore.

And I’m not likely to buy anyone either. My phone is my music device now. While comfortably reading the book in the sofa, I will be able to listen to the music in Mr Richards book. Not that I listen to music that much these days, but it’s nice anyway. My sons tell me it comes with age, old people do not listen that much to music.

Vacation again

Last year I went on a vacation road trip with my sons and the older ones girlfriend down to southern France. Same procedure this year, for some reason we set out on a similar trip down to Gignac in France, but this time we went home via Cannes, Nice, Milano and Hamburg. Last year we had a lot of of computers and internet devices with us which I described in Always online , the amount of devices at that time was insane and we said we only use smart phones next time, and we almost did,two Samsung Galaxy s4 and two Iphones 4s. But I didn’t dare to leave my laptop if something happened back home at the office. And of course I had my Kindle and the GPS Navigon 72 from last year. The PC-modem stopped work when we crossed the border to Denmark, fortunately nothing happened that required my intervention while I was away. The new Galaxy S4 phones were very good substitutes for pads as browsers. And if I only were allowed to connect  to the corporate network with the Galaxy it would be a great  emergency replacement for my laptop. But I was able to manage my bank accounts via the phone and this was very important since my credit card stopped work in Germany on our way down to France. Hotel rooms were booked via the phones all along the way, it’s very convenient to compare and book hotels via the web.

In Milano