Pierrick Le Gall - talend

Talend Exchange, the new Ecosystem

Pierrick Le Gall — Thu, 19 Feb 2009 00:14:00 +0100

I'm happy to announce that Talend Ecosystem has moved to Talend Exchange. It's a new name. "Ecosystem" was also used to describe the business partners and it was confusing for Talend customers. But the name is obviously not the only change. visit Talend Exchange

While the Ecosystem was limited to share Talend Open Studio, Talend Exchange is available for Talend Open Studio and Talend Open Profiler.

Concerning Talend Open Studio, while the Ecosystem was limited to components, Talend Exchange is also available for Job Designs, routines and SQL templates. The categories available are {perl, java, component, job, template, routine} and you can combine categories to filter available extensions:

"component" : list all components whatever the language
"perl+component" : get only the Perl components
"java" : get components, jobs and routines for Java

The new comer is Talend Open Profiler. In Talend Exchange for Talend Open Profiler, you can share your patterns, regular expressions and SQL.

Talend Exchange homepage give many statistics: number of extensions, number of revisions, number of contributors, number of downloads, most active contributors, for each categories (component/job/routine and TOP) the list of most recent extensions, most popular extensions, most active extensions.

When memory matters, multiple keys hash

Pierrick Le Gall — Fri, 17 Oct 2008 00:41:00 +0200

One more time I need to load a huge number of data in memory with a Perl hash. See previous post about this in When memory matters. This time the hash key is made of several values. The value corresponding to each key is an array of scalar data. I have 6 string values for the key and 10 numeric float values for the corresponding value.

My data looks like this:

x5;7Sm;aI8w;2bNuW;lcDogi;pBTAdSKxha;2.73718;6.86649;0.49441;6.97707;2.62658;0.29478;3.97692;1.76468;4.86533;7.82656
sR;Ndu;PvlX;k3IJu;ZTwnAN;jTmtvmlqru;3.03941;1.84579;3.66076;7.14519;8.18409;5.87612;0.56569;1.49385;8.77644;6.98281
jm;cQL;ZXul;J0NIC;UwBz4S;O9VfAA74ao;6.20567;0.16487;3.02578;9.16104;0.14110;8.24097;8.23658;3.53050;7.11429;8.31212
3s;gId;wjXk;jJ3X2;7C833a;tzlTSHet4Y;8.08147;2.62935;9.00534;9.98493;5.17147;4.15576;8.60537;0.41931;9.79758;6.75664

Here comes my basic script. The hash key is made of 6 first columns, the values are the next 10 columns. In the next examples, only code between mark 1 and mark 2 will change.

#!/usr/bin/perl

use strict;
use warnings;

use Time::HiRes qw(gettimeofday tv_interval);
use Sys::Statistics::Linux::Processes;

my $lxs = Sys::Statistics::Linux::Processes->new;
$lxs->init;

my $start = [gettimeofday];
my %cache = ();

open(my $ifh, '<'.$ARGV[0])
    or die 'cannot open input file';

while (<$ifh>) {
    chomp;
    my @fields = split ';', $_;

    # mark 1
    $cache{$fields[0]}{$fields[1]}{$fields[2]}{$fields[3]}{$fields[4]}{$fields[5]}{operation1}{sum} = $fields[6];
    $cache{$fields[0]}{$fields[1]}{$fields[2]}{$fields[3]}{$fields[4]}{$fields[5]}{operation1}{sum} = $fields[7];
    $cache{$fields[0]}{$fields[1]}{$fields[2]}{$fields[3]}{$fields[4]}{$fields[5]}{operation1}{sum} = $fields[8];
    $cache{$fields[0]}{$fields[1]}{$fields[2]}{$fields[3]}{$fields[4]}{$fields[5]}{operation1}{sum} = $fields[9];
    $cache{$fields[0]}{$fields[1]}{$fields[2]}{$fields[3]}{$fields[4]}{$fields[5]}{operation1}{sum} = $fields[10];
    $cache{$fields[0]}{$fields[1]}{$fields[2]}{$fields[3]}{$fields[4]}{$fields[5]}{operation1}{sum} = $fields[11];
    $cache{$fields[0]}{$fields[1]}{$fields[2]}{$fields[3]}{$fields[4]}{$fields[5]}{operation1}{sum} = $fields[12];
    $cache{$fields[0]}{$fields[1]}{$fields[2]}{$fields[3]}{$fields[4]}{$fields[5]}{operation1}{sum} = $fields[13];
    $cache{$fields[0]}{$fields[1]}{$fields[2]}{$fields[3]}{$fields[4]}{$fields[5]}{operation1}{sum} = $fields[14];
    $cache{$fields[0]}{$fields[1]}{$fields[2]}{$fields[3]}{$fields[4]}{$fields[5]}{operation1}{sum} = $fields[15];
    # mark 2
}

# use Data::Dumper;
# print Dumper(\%cache);

close($ifh);

my $stop = [gettimeofday];
my $stat = $lxs->get;

printf(
    "time: %.1f seconds, memory : %uM\n",
    tv_interval($start, $stop),
    $stat->{$$}{vsize} / (1024 * 1024)
);

It corresponds to the way Talend Open Studio currently stores the aggregation hash, so you may understand what "operation1" and "sum" mean :-). Here comes a second way to do it:

    # mark 1
    $cache{$fields[0]}{$fields[1]}{$fields[2]}{$fields[3]}{$fields[4]}{$fields[5]} = [
        $fields[6],
        $fields[7],
        $fields[8],
        $fields[9],
        $fields[10],
        $fields[11],
        $fields[12],
        $fields[13],
        $fields[14],
        $fields[15],
    ];
    # mark 2

and a third one, very compact:

    # mark 1
    $cache{join($;, @fields[0..5])} = join($;, @fields[6..15]);
    # mark 2

I've performed the test on a 1 million lines file :

$ perl hash_memory1.pl /tmp/agg.in; perl hash_memory2.pl /tmp/agg.in; perl hash_memory3.pl /tmp/agg.in
time: 35.4 seconds, memory : 991M
time: 22.1 seconds, memory : 1205M
time: 15.7 seconds, memory : 197M

As hash_memory1.pl is the current "way to do it" in Talend Open Studio, I take it as the reference. The second method is 1.6 times faster, but 1.2 times more memory consuming. Third method is 2.25 times faster and 5 times less memory consuming. Huge improvement, really!

According to the limit of 3GB per process on a Linux x86 32bits architecture, it means that before we could have 3 millions keys and with this improvement we can have 15 million lines in memory.

When memory matters

Pierrick Le Gall — Wed, 16 Jul 2008 00:31:00 +0200

I need to load a huge number of data in memory with a Perl hash. The value corresponding to each key is an array of scalar data. The key is most of the time created with a single field of my array, but it can be made of several fields. The number of fields in the array may vary a lot, but most of the time it will be around 5 scalar values.

My goal is to load as many keys as possible with a limited memory size. Perl interpreter only takes 5MB at the beginning of the process, as Sys::Statistics::Linux::Processes tells me with the virtual size (which is the same as the real size for Perl scripts).

My data to load looks like this (500k lines, 5 columns):

1,1948-10-26,1951-12-08,8TBhXzkOvc,l0YO0ghDND
2,1920-04-16,1959-06-10,eyCFd4IjRo,41YTEnB7Qh
3,1978-12-28,2005-06-23,9LeBBiR2sw,qk30zZdftW
4,2004-01-05,1997-03-25,66K6gvdd5D,bmL3LpuLKT
5,2019-05-11,1995-08-27,rGRtJHioa7,qF7bhwfGeE

Here comes my basic script. The hash key is made of only one column, the first field in the line. In the next examples, only code between mark 1 and mark 2 will change.

#!/usr/bin/perl

use strict;
use warnings;

use Time::HiRes qw(gettimeofday tv_interval);
use Sys::Statistics::Linux::Processes;

my $lxs = Sys::Statistics::Linux::Processes->new;
$lxs->init;

my $start = [gettimeofday];
my %cache = ();

open(my $ifh, '<'.$ARGV[0])
    or die 'cannot open input file';

while (<$ifh>) {
    chomp;
    my @fields = split ',', $_;

    # mark 1
    $cache{$fields[0]} = \@fields;
    # mark 2
}

close($ifh);

my $stop = [gettimeofday];
my $stat = $lxs->get;

printf(
    "time: %.1f seconds, memory : %uM\n",
    tv_interval($start, $stop),
    $stat->{$$}{vsize} / (1024 * 1024)
);

We can use less memory if we store a string instead of an array reference:

# mark 1
$cache{$fields[0]} = join $;, @fields;
# mark 2

plegall@miro:~/bench/hash$ perl load-01.pl in_5c_500kl.csv; perl load-02.pl in_5c_500kl.csv
time: 2.1 seconds, memory : 173M
time: 2.3 seconds, memory : 70M

This is of course a major improvement for me! 2.5 times less memory used and a really low extra time. Let's verify the memory usage is linear, let's have a data file with only the half of lines:

plegall@miro:~/bench/hash$ head -250000 in_5c_500kl.csv  > in_5c_250kl.csv

plegall@miro:~/bench/hash$ perl load-01.pl in_5c_250kl.csv; perl load-02.pl in_5c_250kl.csv
time: 1.0 seconds, memory : 89M
time: 1.1 seconds, memory : 37M

It confirms the memory usage is linear : half the data size, half the memory usage. On a unix-like operating system like Linux, a single process can consume up to 3GB of memory. According to this limit, it would mean that a hash can have nearly 22 millions records at once.

In Talend Open Studio, the tMap has one main link and several lookup links as input. Each lookup link corresponds to a data join. The data joined are stored in memory, thanks to hashes. So I have opened a feature request to implement this improvement.

Install ecosystem components directly from Talend Open Studio

Pierrick Le Gall — Wed, 18 Jun 2008 00:21:00 +0200

Another new feature in Talend Open Studio 2.4.0 : the ability to install ecosytem components directly from your installed Talend Open Studio. No need to browse the web application ecosytem to find the component that fit your need.

Window > Show View > Talend > Ecosystem
Refresh component list with the dedicated button. A webservice on talendforge.org ecosystem is called. Talend Open Studio asks "what are the available components compatible with this specific Talend Open Studio release". The webservice returns a list of component, the last revision compatible with the Talend Open Studio release.
Clic on the component line to select it.
Clic on the "Download Selected components" button (green arrow). The zip file corresponding to the component revision is downloaded, extracted and indexed in the ecosystem components. The download component is immediately available in the palette, you don't have to relaunch Talend Open Studio.
Use your new component.

Parallel executions on iterate links

Pierrick Le Gall — Fri, 13 Jun 2008 00:49:00 +0200

New feature in Talend Open Studio 2.4, the ability to run iterations in parallel. After a tLoop or a tFileList you can set the "number of parallel executions" on the iterate link. If you're running on a quad core computer, it might be interesting to ask for 4 executions in parallel. 4 executions in parallel means that 4 "iterations" will be executed in parallel as long as some iterations are remaining. If your tFileList finds 1,000 files, you won't have 1,000 parallel executions, but 4, theoretically reducing the total execution time by 4.

With iterate parallel, my whole job takes 42 seconds...

Starting job iterate_parallel at 00:44 13/06/2008.
[tLoop_1][20080613 00:44:27] parallel execution #1 running
[tLoop_1][20080613 00:44:27] parallel execution #2 running
[tLoop_1][20080613 00:44:35] parallel execution #3 running
[tLoop_1][20080613 00:44:35] parallel execution #4 running
[tLoop_1][20080613 00:44:43] parallel execution #5 running
[tLoop_1][20080613 00:44:43] parallel execution #6 running
[tLoop_1][20080613 00:44:51] parallel execution #7 running
[tLoop_1][20080613 00:44:52] parallel execution #8 running
[tLoop_1][20080613 00:44:59] parallel execution #9 running
[tLoop_1][20080613 00:45:00] parallel execution #10 running
===
execution time: 42186 milliseconds
===
Job iterate_parallel ended at 00:45 13/06/2008. [exit code=0]

while it take 79 seconds without! nearly twice as fast.

Starting job iterate_parallel at 00:42 13/06/2008.
===
execution time: 78664 milliseconds
===
Job iterate_parallel ended at 00:43 13/06/2008. [exit code=0]

TalendForge.org planet is on its orbit

Pierrick Le Gall — Thu, 05 Jun 2008 00:47:00 +0200

I am proud to introduce you to the TalendForge planet. A planet is an online aggregation of several blog feeds related to a common topic. Currently registered planet members are Stéphane Mallet (Java developer at Talend), Sebastiao Correia (Java developer at Talend), Olivier Carbone (Training and Support manager at Talend) and me (Community Manager, Perl developer at Talend).

The purpose of this planet is to keep you informed about Talend Open Studio and related technologies/products, from the inside. This is not the Talend corporate blog, but a place where people who make Talend Open Studio give information to Talend Open Studio users.

As far as I'm concerned, only my posts with the "Talend" tag will be visible in this planet. TalendForge.org planet was placed into orbit thanks to Planet Planet. This Python script refreshes the planet every one hour.

The planet earth picture comes from wikipedia commons.

External command piped to Talend data flow

Pierrick Le Gall — Thu, 22 May 2008 00:14:00 +0200

With Talend Open Studio 2.4 and a Perl project, component tPipeRow appears in the palette. tPipeRow sends each input row to an external command, fetch the returned line and send it to the next component. tPipeRow does not launch the external script as many times as there are input rows, but only once at the data flow initialization. It makes the whole thing very performant. For each STDIN line, the external script must produce one STDOUT line, without buffering.

On a technical point of view, it's very interesting to see 2 scripts running in parallel and communicating through file descriptors. tPipeRow code is very short (but was not that short to write) because it simply uses IPC::Open2.

As this component is not very simple to understand, an example might help. As input, we have a file with 3 lines and 5 comma separated fields on each line:

1,2,3,4,5
a,b,c,d,e
➊,➋,➌,➍,➎

Drag'n drop components from the palette to the designer, as described in the above screenshot (2 new components only in 2.4 : tPipeRow and tExtractDelimitedFields). tFileInputDelimited, tLogRow (1 and 2) and tExtractDelimitedFields have a 5 columns schema. tPipeRow is configured as follows:

shuffle.pl is a Perl script (what else?) that shuffle fields:

#!/usr/bin/perl

use strict;
use warnings;

use List::Util qw/shuffle/;

# force flush
$|++;

while (<STDIN>) {
    chomp;
    print join(",", shuffle split /,/, $_), "\n";
}

The output of my job is:

Starting job piperow at 00:18 22/05/2008.
[tLogRow_1] 1 2 3 4 5
[tLogRow_2] 3 2 4 1 5
[tLogRow_1] a b c d e
[tLogRow_2] c a e b d
[tLogRow_1] ➊ ➋ ➌ ➍ ➎
[tLogRow_2] ➋ ➍ ➊ ➎ ➌
===
execution time: 9 milliseconds
===
Job piperow ended at 00:18 22/05/2008. [exit code=0]

Of course, this specific example could be done with a tPerlRow, but it would have been useless to demonstrate the power of tPipeRow with a more complex external script.

Talend Open Studio 2.4.0RC1 is available since 2008-05-12 on Talend download page

Automated test results on talendforge.org

Pierrick Le Gall — Sat, 12 Apr 2008 00:15:00 +0200

After many months of work, Talend development team is proud to announce the public availability of our automated test results. You can browse them on talendforge.org. As said on the about view: It should allow our development team to: detect regressions, ensure backward compatibility, follow-up bug fixing

The project started last summer with a defined data model for test results. Siwei Ding (engineer trainee) built the first version of what will later be called Colibri, the web interface. Siwei developped the "search" page.

In parallel, we needed to fill the test results table. Our first problem was to generate each test job from Talend job format to generated code (Perl or Java) with the current Subversion revision of the code generator source code. Matthieu Helleboid (our "Talend repository master") created the Talend command line to perform this task. The command line is able to import a job, generate the script and run it.

Stéphane Mallet took the lead on the project, he created the automated execution system. The system consists of Talend Open Studio jobs. In a simplified way, the automated system does:

Subversion update Talend Open Studio source code
build the sources into a runnable application
import and execute each test jobs
compute statistics and charts about the global execution

Then it was time for me to enhance Colibri (PHP/MySQL based web application, something I know quite well...), with new views such as the execution view and the jobs view. I've added some filters to display the most important information by default.

We've bought a computer (Core2 Quad 2.4GHz) and I installed Debian on it. This is our automated test machine. I've developped a Perl daemon to launch the execution scripts created by smallet.

We then needed to create as many test jobs as possible. We call test jobs TUJ for Test Unit Job. We currently have 404 TUJs, 217 Perl TUJ and 187 Java TUJ. TUJ are available in Talend Open Studio Subversion repository, see tuj directory. The list of TUJ is growing everyday, as you can see on the executions view of Colibri. TUJ are added by component developpers, mainly. Basically, a TUJ produces output data and compare it with the expected output data.

Don't hesitate to give us your feedback. We've spend quite a lot of time on this automated system, it's very useful for us, we hope it will give interesting informations to Talend Open Studio users.

multithreading for Perl jobs

Pierrick Le Gall — Mon, 17 Mar 2008 00:50:00 +0100

In Talend Open Studio, the multithreading option makes possible to execute 2 subjobs in parallel. It was implemented in Java code generation last summer (see feature 1335) for TOS 2.1 and I implemented it for Perl code generation last monday (see feature 3302). Current multithreading option was not implemented with threads in Perl, but with processes, I fork the parent process in children.

As you can see in the example above, multithreading makes things nearly twice as faster on this kind of job, on a Core2 duo processor.

The current way to perform parallelism is very limited. For example, you can't design a "execute subjob A and subjob B in parallel and once A and B are finished, execute subjob C". To achieve this, you have to use child jobs that will contain the parallel subjobs. But don't worry, a synchronize component is expected before next big TOS release.

This multithreading option let users design task parallelism. I'm in charge of designing the next step in parallelism, ie data parallelism.

Extract fields from a positionnal file with Perl

Pierrick Le Gall — Wed, 20 Feb 2008 23:13:00 +0100

Here is a sample of a positional:

Pierrick   LE GALL    026169
Erwann     LE GALL    002080
Larry      WALL       053174

We have the firstname on 11 characters, lastname on 11 characters, age on 3 characters and size on 3 characters. We want to extract these fields into an array. I propose to use the unpack function.

# in.pos is the positionnal file
open(my $fh, '<in.pos');
my $pattern = 'A11A11A3A3';
while (<$fh>) {
    chomp;
    @fields = unpack($pattern, $_);
}

This was the way I did it until I read utf-8 file with non-ascii characters. The problem is that the character "é" in a utf-8 file is stored on 2 bytes. I didn't find the clean way to read it with unpack, so I've searched another way to do it. The very usual substr function can do the job and has no problem with utf-8 data.

open(my $fh, '<in.pos');
while (<$fh>) {
    chomp;
    my @fields = (
        substr($_,  0, 11),
        substr($_, 11, 22),
        substr($_, 22,  3),
        substr($_,  25,  3),
    );
}

Which can be rewritten in a more generic way:

open(my $fh, '<in.pos');
my @lengths = qw/11 11 3 3/;
while (<$fh>) {
    chomp;
    my @fields = ();
    my $pos = 0;

    foreach my $len (@lengths) {
        push @fields, substr($_, $pos, $len);
        $pos+= $len;
    }
}

What about performances? I've generated a 1 million line file, with 100 fields of 10 characters on each line (1000 characters on each line). unpack lasts 84.3 seconds versus 70.6 seconds for substr method (unpack is approximately 20% slower for this specific task).

Conclusion: substr is my favorite choice for this task, it is faster and more compliant to utf-8 than unpack.

PostgreSQL and case sensitivity

Pierrick Le Gall — Fri, 01 Dec 2006 17:39:00 +0000

I've been working recently with PostgreSQL (see Queries on error with PostgreSQL in Talend Open Studio) and I've discovered the way PostgreSQL handles case sensitivity.

Column, table and schema names are case sensitive, ie you can have a column Id and ID in the same table. Not very usual (you can't do this in MySQL or Oracle). The problem is that PostgreSQL automaticaly lowercase schema/table/column names in the queries! Very funny, isn't it?

db1=> create table blog_ticket (Id int, Title character varying(50));
CREATE TABLE
db1=> \d blog_ticket
           Table «public.blog_ticket»
 Column  |         Type          |
---------+-----------------------+
 id      | integer               |
 title   | character varying(50) |

As you can see, my capitalized column names have been lowercased. This is not a real problem as long as you never try to have two column names differentiated only by case.

If you really want to have ID and Id in the same table, you have to double quote column names.

db1=> drop table blog_ticket;
DROP TABLE
db1=> create table blog_ticket ("Id" int, "ID" int, Title character varying(50));
CREATE TABLE
db1=> \d blog_ticket
           Table «public.blog_ticket»
 Column  |         Type          |
---------+-----------------------+
 Id      | integer               |
 ID      | integer               |
 title   | character varying(50) |

Even with a good reason, don't do that, please.

Now I want to retrieve all the tables of a given schema with a Perl script. The equivalent of :

db1=> \dt public.*
             Relation list
 Schema |    Name     | Type  | Owner
--------+-------------+-------+-------
 public | Blog_Ticket | table | root
 public | blog_ticket | table | root
(2 lines)

Here is the Perl script, I name it list_schema_tables.pl:

[perl]
#!/usr/bin/perl

use strict;
use warnings;

use Getopt::Long;
use DBI;

my %opt = ();
GetOptions(
    \%opt,
    qw/dbname=s dbuser=s dbpass=s dbhost=s dbport=i schema=s/
);

foreach my $option (qw/dbname dbuser dbpass/) {
    if (!exists($opt{$option})) {
        die 'Error: '.$option.' is a mandatory option'."
";
    }
}

$opt{schema} ||= 'public';
$opt{dbhost} ||= 'localhost';
$opt{dbport} ||= 5432;

my $dbh = DBI->connect(
    sprintf(
        'DBI:Pg:dbname=%s;host=%s;port=%s;',
        $opt{dbname},
        $opt{dbhost},
        $opt{dbport},
    ),
    $opt{dbuser},
    $opt{dbpass},
)
    or die "can't connect to database";

my $sth = $dbh->table_info('%', $opt{schema});
my @tables = ();

while (my $entity = $sth->fetchrow_hashref()) {
    if ($entity->{TABLE_TYPE} eq 'TABLE') {
        push @tables, $entity->{TABLE_NAME};
    }
}

print(
    join(
        "
",
        @tables
    ),
    "
"
);

This script, is used in the following way:

[bash]
$ ./list_schema_tables.pl --dbn=talend --dbu=root --dbpass=toor --schema=public
blog_ticket
"Blog_Ticket"

What we see here is that table names mixing lower and upper case characters are double quoted. Very... unpredictable, isn't it? The funniest is that in the table_info function, I mustn't surround my schema with double quotes, even if it contains uppercased characters...

As a conclusion, I would simply say: don't ever use explicit case mixing in column/table/schema names, whatever the database management system you use.