Pentaho und SQLite

Kein Datenbank Managementsystem mit Datenbank zur Verfügung und man möchte mit Pentaho trotzdem eine Datenbank nutzen? Eine Alternative kann hier eine SQLite Datenbank sein. Aber warum sollte man überhaupt eine zusatzliche Datenbank mit Pentaho nutzen? Ein Fallbeispiel möchte ich heute in diesem Artikel berschreiben.

In dem Artikel “Pentaho – Das ETL-Strecken Wunderkind für Controller und ITler” wurde ja bereits über die Vorzüge der automatisierten Erstellung von Excel Dateien mit Pentaho geschrieben. Die ETL-Strecke lässt sich noch weiter optimieren, wenn man eine Zwischendatenbank einsetzt. In der alten ETL-Strecke werden die Quelldaten bei der Generierung jeder Projektdatei gelesen. Wenn die Quelldateien klein sind stellt dieses kein Problem dar. Sollten die Quelldateien größer sein, so verlängert sich die Laufzeit durch einen Faktor in Abhängigkeit der Anzahl von Projekten. Lädt man die Quelldaten zu Beginn einmal in eine Datenbank und liest nur die (wenigen) relevanten Daten für jedes Projekt aus der Datenbank lassen sich die Laufzeiten reduzieren.

Ein kleines Rechenbeispiel

Parameter
Datenquelle lesen: 1 Minute
Datenquelle schreiben: 10 Sekunden
100 Projektdateien

Ohne Datenbank:
100 * 1 Minute lesen (= 100 Minuten / 1,6 Stunden) + 100 * 10 Sekunden schreiben (= 16,7 Minuten) = 116,7 Minuten = 1,9 Stunden

Mit Datenbank:
1 * 1 Minute + 100 * 10 Sekunden schreiben (= 16,7 Minuten) = 17,7 Minuten

Das heißt die Laufzeit lässt sich von knapp 2 Stunden auf knapp 18 Minuten reduzieren.

Ebenfalls können mehrere Datenquellen (temporär) konsolidiert werden.

In dem untenstehenden Beispiel wollen wir Order Daten einmal einlesen und pro Land eine Datei schreiben.

Warum SQLite Datenbank?

Die SQLite Datenbank hat den Vorteil, dass sie keine Installation für ein Datenbankmanagementsystem und/oder Dienste benötigt. Die Datenbank wird als Datei in das Dateiverzeichnis abgelegt und der Pentaho Data Integrator kann SQLite Datenbanken ohne Zusatz-Software lesen und schreiben. Damit kann man Datenbankfunktionalitäten, wie Daten in Tabellen speichern und selektieren, auch in Umgebungen nutzen, ohne ein weiteres Werkzeug installieren zu müssen.
Die SQLite ist Public Domain und daher lizenzkostenfrei verwendbar. Dokumentation und weitere Informationen gibt es auf der Hompage der SQLite Datenbank.

Für die Entwicklung, Test und Administration der Datenbank ist die SQL-Client Software SquirrelSQL hilfreich.

Für SquirrelSQL kann man einen SQLite Treiber herunterladen und im LIB-Verzeichnis unter dem SQuirrelSQL Programm Verzeichnis ablegen und einbinden

SQLite Treiber herunterladen

Treiber hinzufügen

Alias und Verbindung zur Datenbank anlegen

Die geöffnete Datenbank in SquirrelSQL

Anschließend kann man einen neuen Alias anlegen und mit der ersten Verbindung die Datenbank anlegen. in dem URL-Feld gibt man den Ort an, wo die Datenbank abgelegt werden soll.

Die Datenbank liegt im Dateiverzeichnis

Die Quelldaten

Excel als Datenquelle

In diesem Beispiel nutzen wir eine Excel-Datei mit Order Daten und Attributen wie Ship Country als Datenquelle. Datenquellen könnten aber genauso gut andere Datenbanken, CSV Dateien etc. sein. Natürlich können auch Daten aus verschiedenen Quellen zusammengeführt werden.

Die Pentaho Strecke im Überblick

Pentaho Hauptjob

Nachdem wir eine Datenbank angelegt haben, können wir die Pentaho ETL Strecke aufbauen indem wir einen Job anlegen. In diesem Job werden, in Form von Transformationen,  drei Schritte ausgeführt:

  • lese Datenquelle
  • lese Länder
  • schreibe Länder

Schauen wir uns die einzelnen Transformationen näher an.

Transformation lese Datenquelle

Transformation lese Datenquelle

Mit einem Excel Writer auf der Quell-Excel-Datei lesen wir zunächst die Daten in den Stream. Dann nutzen wir den Insert/Update Step, um die Daten in Datenbank zu schreiben.
Hierfür müssen wir zunächst im dem Insert/Update Steps eine Datenbankverbindung zu unser eben erstellten Datenbank erstellen. Dieses kann man praktischerweise direkt aus dem Step erledigen:

Datenbankverbindung erstellen

Die Taste “Get fields” liefert die Felder aus dem Stream. Wenn man die neue Zieltabelle “orders” angibt, liefert die Taste “SQL” direkt das CREATE Statement für die Datenbank und die Tabelle kann mit Execute direkt aus der Pentaho Oberfläche angelegt werden.

Step Insert/Update

Tabelle orders anlegen

Die Tabelle oders ist angelegt

Die gerade angelegte Datenbankverbindung kann man nun über View > Database connections > Share für andere Transformationen freigeben, so dass man die Verbindung nur einmal erstellen muss und dann weiterverwenden kann.

Share Database

Führt man die Transformation (testweise) aus, so werden nun die Daten in die Orders Tabelle der Datenbank geladen.

Transformation lese Länder


Die Transformation “lese Länder” wird genutzt, um eine Liste der Länder zurück zu liefern. Hierfür wird der Step “Input Table” und ein einfaches Select mit

 select distinct
 "Ship Country"
 from orders

genutzt.

Liste der Länder per Datenbankabfrage

Abfrage im Input Table Step

Das Ergebnis wird zurück in den Hauptjob gegeben.

In anderen Szenarien kann der Input Table Step natürlich auch genutzt werden, um komplexere Abfragen mit Joins, Berechnungen, Aggregationen und Filterungen auszuführen. Auch hier könnten Daten aus verschiedenen Quellen (temporär) konsolidiert werden.

Transformationsaufruf pro Zeile

Im Hauptjob muss die letzte Transformation so konfiguriert werden, dass sie für jede Stream-Zeile (hier jedes Land). ausgeführt wird:

Transformation für jede Zeile ausführen

Zudem werden beim Transformationsaufruf die Länder als Parameter an die Transformation übergeben:

Übergabe Land als Parameter

Transformation schreibe Länder

Transformation schreibe Länder

In der Transformation muss zuerst ein Parameter angelegt werden, um die Ländernamen aus dem Hauptjob entgegenzunehmen. Anschließend können mit einem “Input Table Step” die Orderdaten selektiert werden. Hierbei kann der Parameter “$Land” als Filterkriterium in der SELECT Anweisung genutzt werden.

Select mit Parameter im Filter

Im Excel Writer nutzen wir den Parameter ${Land} als Dateinamen.

Mit “Get Fields” werden die Stream Werte als Felder übernommen.

Hauptjob ausführen

Setzt man nun alle Teile zusammen und führt den Job aus, wird für jedes Land eine Excel Datei erstellt.

Eine Excel Datei pro Land

Länderdatei ist gefiltert

Fazit

Über die SQLite Datenbank lassen sich Laufzeiten verkürzen. Auch Konsolidierungen aus mehreren Datenquellen und komplexere Abfragen sind über die Zwischendatenbank möglich, die in Pentaho aufwändiger wären (z.B. gleichzeitig Filtern, Sortieren und aggreggieren durch ein SQL).
Die SQLite Datenbank selber benötigt keine Installation für ein Datenbankmanagementsystem und ist daher recht schlank.

Leave a Comment

Your email address will not be published. Required fields are marked *