Pentaho Data Integration
Das auch als Kettle bekannte Pentaho Data Integration (PDI) [1] ist ein Programm zum Laden, Transformieren und Speichern von Daten. Üblicherweise werden ETL-Tools (Extraction, Transform, Load [2]) im
Datenbankbereich eingesetzt, wenn große Datenmengen zwischen verschiedenen Quell- und Zielformaten konvertiert werden. Neben einmaligen Transformationen in Migrationsprojekten [3] sind auch periodische Transaktionen für die Datenübertragung von einer Anwendung in eine andere üblich.
Neben diversen kommerziellen Programmen stellt die Firma Pentaho eine Open-Source-Lösung namens Pentaho Data Integration bereit. Anhand eines konkreten Anwendungsfalls wird ein Einblick in den Leistungsumfang und die Vorgehensweise gegeben.
Der Anwendungsfall
Als beispielhaftes Szenario wurde die Migration einer Kundendatei mit Adressdaten gewählt. Die Quelldaten liegen in Form von kommaseparierten Werten (CSV) in einer Datei vor. Diese könnte aus einer Microsoft-Excel- oder OpenOffice.org-Calc-Anwendung heraus erstellt worden sein, welche vorher als Kundendatei gedient hat. Die Daten sollen in ein neues Unternehmens-CRM [4] geladen werden. Als Zielsystem wurde SugarCRM gewählt – eine Open-Source-Lösung, welche auch in freiesMagazin 07/2009 vorgestellt wurde [5].
Technische Beschreibung
Die Adressdaten in SugarCRM werden in einer MySQL-Datenbank [6] gespeichert.
Die relevanten Tabellen heißen
- contacts
- email_addresses
- email_addr_bean_rel
Die folgende Tabelle zeigt die Felder der CSV-Datei und die entsprechenden Zielspalten in der Datenbank:
| Datenbanklayout |
| Feld in CSV-Datei |
Zieltabelle |
Zielspalte |
| Name |
contacts |
first_name |
|
contacts |
last_name |
| Street |
contacts |
primary_address_street |
| Zipcode |
contacts |
primary_address_postalcode |
| City |
contacts |
primary_address_city |
| Phone |
contacts |
phone_work |
| Fax |
contacts |
phone_fax |
| Email |
email_addresses |
email_address |
|
email_addresses |
email_address_caps |
|
Vor- und Zuname stehen in der CSV-Datei mit Leerzeichen getrennt in nur einem Feld. Diese müssen anhand des Leerzeichens getrennt und separat gespeichert werden. Die E-Mailadresse steht in einer eigenen Tabelle, die neben der Adresse selbst diese auch in Großbuchstaben enthält (email_address_caps). Eine Zuordnung zwischen den Kontaktdaten (Tabelle contacts) und den E-Mail-Adressen (Tabelle email_addresses) findet über die Tabelle email_addr_bean_rel statt.
Mit PDI ist daher folgender Ablauf zu erstellen:
- Einlesen der CSV-Datei
- Aufteilung des Namens in Vor- und Zuname
- E-Mailadresse zusätzlich in Großbuchstaben konvertieren
- Entsprechende Daten in den drei genannten Tabellen speichern
Die Arbeitsfläche
Um diesen Ablauf zu erstellen, bietet PDI eine Arbeitsfläche (Workspace), auf der diverse Prozessschritte grafisch per Drag-and-Drop arrangiert werden können. Die Prozessschritte stehen als Icons in einer Werkzeugleiste zur Verfügung. Diese sind logisch in Aufgabenbereiche gegliedert, darunter unter anderen „Input“ für Ladevorgänge, „Output“ für Ausgabeformate oder „Transform“ für Datentransformationen.
Darstellung der Werkzeugleiste.
Einlesen der Daten
Um die CSV-Datei einzulesen, wird das Icon „CSV-Input“ verwendet. Dieses wird auf die Arbeitsfläche gezogen und anschließend mit einem Doppelklick geöffnet. Im nun erscheinenden Dialog werden Einstellungen wie der Dateiname, das Trennzeichen der CSV sowie die einzelnen Feldnamen und Datentypen angegeben.
Einstellungsdialog zum Laden der CSV-Datei.
Als nächstes müssen Vor- und Zuname, welche in der CSV-Datei in einem Feld vorkommen, in zwei Felder aufgetrennt werden. Dazu dient das Icon „Split Fields“. In dessen Dialog muss das Feld, welches getrennt werden soll, angegeben werden. Auch muss das Zeichen, welches als Trennkriterium dient, definiert werden – in diesem Beispiel das Leerzeichen. Zusätzlich muss angegeben werden, wie die beiden neuen Felder heißen, die das linke und das rechte Ergebnis der Trennung symbolisieren.
Dialog zum Trennen von Vor- und Zuname.
Den Datenfluss zwischen den Icons repräsentieren sogenannte „Hops“. Dieses werden durch gerichtete Pfeile dargestellt. Um zwei Icons mit einem Hop zu verbinden, müssen diese markiert werden. Durch einen Rechtsklick und die Wahl „New Hop“ werden die beiden Icons mit einem Pfeil verbunden.
Datenfluss zwischen zwei Icons.
Erzeugen von Primärschlüsseln
Nachdem die CSV-Datei eingelesen und Vor- und Zuname in zwei neue Felder getrennt sind, muss noch eine ID (Primärschlüssel in der Datenbank [7]) generiert werden. Hierzu gibt es das Icon „Generate Random Value“, welches auf Universally Unique Identifier (UUID) eingestellt wird. Somit werden IDs erzeugt, die syntaktisch den bereits verwendeten SugarCRM-IDs in der MySQL-Datenbank entsprechen. Andere IDs würden hier auch funktionieren.
Befüllen der erste Zieltabelle mit Kontaktdaten
Nun könnte die Tabelle contacts bereits gefüllt werden. Allerdings erwartet ein „Output Icon“, dass alle gelieferten Felder gespeichert werden. Daher muss die E-Mailadresse noch herausgefiltert werden, denn diese wird nicht in contacts gespeichert.
Zum Löschen von Feldern im Datenstrom bietet sich das Icon „Select Values“ an. Dort kann unter anderem angegeben werden, welche Felder aus dem Strom entfernt werden sollen. Anschließend werden die Daten an das „Output Table Icon“ übergeben.
Entfernen der E-Mail-Adresse aus dem Datenstrom.
Im „Output Table Icon“ wird über eine definierte JDBC-Verbindung [8] die MySQL-Datenbank angesprochen. Es wird die Zieltabelle contacts angegeben, sowie unter dem Reiter „Database fields“ definiert, welches Feld aus dem Datenstrom in welche Tabellenspalte geschrieben werden soll. Diese Zuordnung wird als Mapping bezeichnet.
Einstellungsdialog zum Speichern der Kontaktdaten.
Tabelle mit E-Mailadressen
Nun müssen die E-Mailadressen in Großbuchstaben konvertiert und anschließend in der Tabelle email_addresses gespeichert werden. Dazu wird der Datenstrom vom ersten abgezweigt (ein Icon kann zwei Hops gleichzeitig bedienen). Der Datenstrom wird dann an beide Hops kopiert. Aus dem aktuellen Strom müssen alle Felder, bis auf die E-Mailadresse, herausgefiltert werden, denn nur diese soll in der Tabelle gespeichert werden.
Zum Großschreiben der E-Mailadresse wurde das Icon „Formula“ genutzt. Dieses bietet diverse mathematische und logische Operationen. Mit der „upper“-Funktion von „Formula“ können Zeichen in Großbuchstaben konvertiert werden.
Um die Daten nun in die Tabelle email_addresses zu schreiben, wird ein weiteres „Table Output Icon“ verwendet. Allerdings wird auch hier ein Primärschlüssel benötigt, welcher im Vorfeld wieder über das Icon „Generate Random Value“ erzeugt werden muss.
Zuordnung zwischen Kontakt und E-Mail-Adresse
Die dritte Tabelle email_addr_bean_rel beinhaltet die Zuordnung zwischen contacts und email_addresses. Es müssen daher die beiden
erzeugten Primärschlüssel gespeichert werden. Aus diesem Grund wurde der Datenstrom jeweils von „Generate Random Value“ abgezweigt, damit die erzeugte ID mit übernommen und weiter verwendet werden kann.
Als letzte Schwierigkeit erweist sich die Spalte beani_module in der Tabelle. Diese beinhaltet eine Konstante, die angibt, um welchen Verbindungstyp (hier contacts) es sich handelt. Auch hierfür wurde die „Formula“-Funktion genutzt. Als Funktion wird der Wert der Konstante in Hochkommata angegeben.
Der fertige ETL-Prozess auf dem PDI-Workspace.
Zusammenfassung
Mit PDI ist es nach kurzer Einarbeitung möglich, Datentransformationen zu erzeugen. Dabei ist keine Programmierung notwendig, da alle Teilschritte grafisch modelliert werden können. Dies ermöglicht gegenüber einer eigenen Programmierung eine schnellere und standardisiertere Entwicklung. Abgerundet wird die Entwicklungsoberfläche mit einem Job-Monitor, mit dessen Hilfe periodische Transformationen umgesetzt werden können. Eine hilfreiche Community steht bei Fragen über ein Forum und Wiki zu
Verfügung [9].
Die CSV-Datei wurde mit Hilfe von GenerateData [10] erzeugt und kann mitsamt dem PDI-Mapping heruntergeladen werden:
Links
- http://kettle.pentaho.org/
- http://de.wikipedia.org/wiki/ETL-Prozess
- http://de.wikipedia.org/wiki/Migration_(Informationstechnik)
- http://de.wikipedia.org/wiki/Customer-Relationship-Management
- http://www.freiesmagazin.de/freiesMagazin-2009-07
- http://de.wikipedia.org/wiki/Mysql
- http://de.wikipedia.org/wiki/Primärschlüssel
- http://de.wikipedia.org/wiki/JDBC
- http://forums.pentaho.org/
- http://www.generatedata.com/
