Der list members,

does anyone of you know a tool for ETL[1] tasks?

At best it would be written in object pascal and be open source.

I'm currently charged with doing some simple data transfer actions like loading CSV (aka SDF) data and pushing those records to a relational database and the like. I have the strong feeling that someone has already done something like that. ;)

Options to solve this request are

1. whipping together minimal code that does it

2. make a more generic tool that can be used for all tasks, having to be extended when needed

3. use some tool already existing

Some tools I tried or looked at are mostly written in Java and do suck ressources, or they are not able to handle some requirements (like databases in use) or are closed source. Last tests were done using OpenAdaptor[2] which is really cool but has problems with posgresql and demands being familiar with spring. There is still Pentaho Data Integration[3] n the list of tools to do some testing with.

What do you use for ETL tasks?

TIA,
Marc

[1] http://en.wikipedia.org/wiki/Extract,_transform,_load [2] https://www.openadaptor.org/
[3] http://www.pentaho.com/explore/pentaho-data-integration/

I'm a heretic: I tend to use Perl.

I notice that somebody's embedded Perl in Delphi- I wonder if that would be portable to FPC?

Always option 1.

Because it always turns out you need to check for doubles and do updates; making self written code the most optimal tool. You always know your own data best of all.

Michael.

I can only confirm this. The division I work for develops their own data migration _framework_ which is then specialized for the specific migration project. This is done via customization (XML configs) and Java code.

Since each of the previous (and current) migrations required a lot of data validation, cleanup as well as changes to the data structures, a run-of-the-mill tool would have probably caused a lot more trouble than it would have solved.

So yes, I definitely also recommend the do-it-yourself approach :-)

Since FPC has a dataset class for CSV files and also plenty of options to connect to different databases I'd either go for option 1 or 2 depending on the specific situation.

Regards,
Sven
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20120324/d4567a49/attachment-0001.html>

1 and 2. Mostly (1) but to with generic internal format for which we had some tools and libraries of routines and help tables (e.g. zipcode to address/town mappings etc, encoding helpers).

Internal format was domainspecific though, not truely general purpose.

The problem with (3) is that it takes really long to dig into such frameworks, and from what I've seen is that they are only worthwhile if datatransformation is your dialy bread.

It was on Delphi but we did use TDBF with Micha's help for many auxilary tables.

Hi again,

at first thanks for all the input. Summing up it boils down to hacking together some quick lines is the most used option - which I agree with in the end. Although those tasks pop up quite often in my todo list, it is still not enough to make something bigger and better.

In the past I had some good result using OpenAdaptor, there are three basic scripts that have been used a couple of times with slight modifications each. Once used to swing and witing programs as xml configuration it is very easy and quickly done. Especially there are lots of good documentation and examples for getting up to speed.

But for now, where posgres isn't usable, I'm hosed.

Would that be object pascal code I'd have been able to fix it myself, for Java I would have to setup a new development environment. And that's the way to go now, hacking some pascal code. ;)

Many thanks for all valuable input,
Marc

Sounds like they are re-inventing OpenAdaptor. Have a look at it, it makes configuring ETL tasks easy and can do the T(ransform) part of ETL by filtering using specialized processors or Javascript. If Java plus Swing is what they use - this is one really good way to go. That thingy can handle text file, relational databases, e-mails and services - I really do like it. As long as it works. ;)

Ahh, now I read the bad word two times, I'll have to wash out my eyes with soap! ;)

Better name it Python, that one is readable and is an interpreted language, too.

Marc

Marc Santhoff wrote

If Java plus Swing is what they use - this is one really good way to go. That thingy can handle text file, relational databases, e-mails and services - I really do like it. As long as it works. ;)

Oops, I wrote "Swing" all the time which is a GUI library. What I really wanted to write is "Spring".

http://en.wikipedia.org/wiki/Spring_Framework

Sorry for confusion ...