Am veröffentlicht

Talend Data Integration: Ladestrategien

In unserem letzten Beitrag zu Talend haben wir uns intensiv mit der Komponente tMap befasst. In diesem Beitrag werden wir erneut nicht ohne diese Komponente auskommen, wir wollen jedoch den Schwerpunkt auf das typische Ende eines ETL Jobs setzen: Die Ladestrategie in eine Datenbank. Sie ist abhängig von vielen Faktoren, wie beispielsweise der Umsetzung des Datenmodells, der Datenmenge, Ladezeit und -rhythmus, alleiniger Zugriff, unit-of-work sowie Art und Performance der Datenbank bzw. Performanceverhältnis zwischen Datenbank und Talend Jobserver.

Durch die vielseitigen Einsatzmöglichkeiten von Talend ist es keinesfalls möglich, in diesem Artikel alle denkbaren Szenarien zu betrachten, vielmehr wird der Fokus auf typische ETL Jobs auf der dispositiven Seite inklusive des Metadatenmanagements gelegt.

Im Folgenden werden zunächst die Talend Komponenten zum Beladen der Zieldatenbank aufgezeigt und beschrieben mit welchen Optionen sie verfügbar sind. Anschließend werden mehrere Anwendungsfälle betrachtet, die sich mit diesen Rahmenbedingungen umsetzen lassen.

Talend Komponenten zum Beladen einer Datenbank

Für jede Datenbank bietet Talend DI in der Regel jeweils zwei Komponenten zum Laden an: Zum einen tDBOutput und zum anderen tDBBulkExec.

tDBOutput

Die tDBOutput ist das Pendant zur tDBInput Komponente. Hierbei wird die Datenbanktabelle direkt aus dem Datenstrom des Talend DI Jobs mittels prepared Statements beladen. Die Einstellungsmöglichkeiten dieser Komponente sind vielseitig, sodass ein breites Spektrum an Anwendungsszenarien abgedeckt werden kann.

Talend data pipeline from tDBInput to tDBOuput
Data stream: Die extrahierten Daten aus der tDBInput werden Zeile für Zeile direkt wieder in die Zieldatenbank via tDBOutput geladen.

Für diesen Artikel sind insbesondere folgende Optionen relevant:

  • Action on table: Im Normalfall ist hier “default” die richtige Wahl. Diese setzt voraus, dass eine Tabelle existiert und diese via „action on data“ (s.u.) beladen werden kann. Es kann jedoch auch für das Beladen einer temporären Tabelle oder auch für zyklische Vollladestrategien ein „drop and create“ oder „truncate“ gewählt werden – je nachdem, ob die DDL von Talend oder von einem externen Tool vorgegeben bzw. definiert werden soll.
  • Action on data: Diese Option definiert, wie die Daten in die Tabelle geladen werden sollen bzw. können: INSERT, UPDATE, UPSERT (UPDATE and INSERT) oder DELETE. Falls nur INSERTs durchgeführt werden sollen, so muss nicht zwangsläufig ein Schlüssel in den Metadaten oder in den „Use Fields“ Optionen definiert sein, für alle anderen Ladestrategien jedoch schon!
  • Batch Size: In den Advanced Settings lässt sich nach Setzen von “Use Batch Size” eben diese definieren. Diese Option ist nur möglich, wenn in “Action on data” INSERT, UPDATE oder DELETE gewählt wurde. Ist eine Batch Size gesetzt, so wird eine definierte Zeilenanzahl als ganzes Paket verarbeitet, wodurch eine Performancesteigerung ermöglicht wird. Nachteilig ist jedoch die dann notwendige Einschränkung, dass keine Einzelsatzverarbeitungen durchgeführt werden können. Das bedeutet, dass auf keine spezifischen REJECTS der Datenbank reagiert werden kann und darüber hinaus i.d.R. der gesamte Batch nicht festgeschrieben wird.
  • Commit every: Das Commitintervall ist ebenfalls ein essentieller Parameter. Dieser definiert, nach wie vielen geladenen Zeilen ein COMMIT durchgeführt werden soll, also die Datenmenge festgeschrieben wird. Wird die Verbindung zur Datenbank über eine tDBConnection aufgebaut, ist diese Option nicht verfügbar und die Commitstrategie muss entweder über „auto commit“ in der tDBConnection oder via expliziter tDBCommit Komponente definiert werden. Mit der auto commit Funktion wird mindestens nach jedem batch ein COMMIT durchgeführt.

 

tDBBulkExec

Die tDBBulkExec Komponente lädt ausschließlich von einer flat file ausgehend über eine datenbankspezifische Ladestrategie. Diese Strategie ist hauptsächlich bei großen Datenmengen von Vorteil. In seltenen Fällen steht die flat file bereits lokal oder in einem gemountetem Netzwerkordner zur Verfügung, so dass diese direkt in die Datenbank geladen werden kann. In der Regel muss diese Datei erst aus einer Quelldatenbank extrahiert werden oder die flat files müssen zunächst in ein bestimmtes Format überführt oder zusammengefasst werden. Dieser erste Schritt wird entweder über die generische tFileOutputDelimited Komponente oder, falls für die spezifische Datenbank verfügbar, über die Komponente tDBOutputBulk durchgeführt.

Talend data pipeline into a local flat file and subsequent subjob to upload the file via tDBBulkExec into the database.
Batch Import: Die extrahierten Daten werden zunächst auf dem Jobserver zwischengespeichert und anschließend über tDBBulkExec im Ganzen importiert.

Wie die flat file in die Tabelle geladen wird ist abhängig von der jeweiligen Datenbank. Im Fall der Exasol wird das effiziente IMPORT Statement abgesetzt, im Fall einer DB2 wird entweder ein LOAD oder ein INGEST verwendet, im Fall einer PostgreSQL wird COPY verwendet.

Anwendungsszenarien

Massendaten

Die Verarbeitung von Massendaten, sei es im Deltabetrieb oder in regelmäßigen Vollladeläufen, ist ein typischer Anwendungsfall von Talend DI Jobs. Die Datenmenge ist hierbei so groß, dass sie in der Zieldatenbank nicht in eine unit-of-work passt, und daher eine optimale batch und commit Größe gefunden werden muss. Ebenfalls entscheidend sind folgende essenziellen Rahmenbedingungen:

  • Könnten aus der Quelle Dubletten extrahiert werden bzw. müssten diese gefiltert werden?
    Eine mögliche Lösung für dieses Problem ist eine zeilenbasierte Dublettenentfernung (hier ein Beispiel mit Informatica PowerCenter, welches sich analog auch in Talend in einer tMap oder tJavaRow umsetzen lässt).
  • Muss beim Laden in die Zieldatenbank auf existierende Daten geprüft werden bzw. sollen UPDATES abgesetzt werden?
    An dieser Stelle auf die Rejects der Datenbank zu setzen, scheidet als Lösungsszenario aus, da aufgrund der Datenmenge auf batch-INSERTs gesetzt werden muss. Der Lookup auf die Zieldatenmenge lässt sich im Talendjob durchführen, siehe folgende Abbildung.
  • Muss der Job restartfähig sein? Wenn der Talendjob während der Verarbeitung abbricht, soll an dieser Stelle ein Restart möglich sein, der entweder alle zuvor geladenen Daten ignoriert oder genau an dieser Stelle wieder aufsetzen kann?
  • Muss die Ladegeschwindigkeit durch parallele Verarbeitung erhöht werden?
Talend Lookup UPSERT
Batch UPSERT: In der tMap werden die Eingangsdaten mit der zuvor geladenen Zieltabelle gejoint. Falls der (inner) Join nicht trifft, wird diese Zeile über “Catch inner join reject”-Output an die INSERT Output-Komponente weitergegeben. Optional lässt sich über einen DIFF-HASH entscheiden, ob ein UPDATE nötig ist.

 

simple Talend ELT strategy
Talend LOAD and MERGE: Durch das Importieren der Daten mittels der tDBBulkExec Komponente lässt sich performant eine große Datenmenge in die Datenbank importieren. Mit Hilfe eines anschließenden MERGE Statements kann diese per WHEN MATCHED THEN UPDATE und WHEN NOT MATCHED THEN INSERT in die Zieltabelle integriert werden. Diese Strategie ist nur bei einer hinreichend potenten bzw. OLAP orientierten Datenbank sinnvoll, wie beispielsweise Snowflake oder Exasol. Das MERGE Statement lässt sich bei einem generischen Vorgehen auch mit Hilfe von Talend parametrisieren und zentral als Joblet Komponente ablegen.

 

Einzelinserts & Commitstrategie

Es existieren eine Menge Einsatzszenarien für ETL-Prozesse, in denen die zu verarbeitende Datenmenge nicht groß ist. Es kann (und in vielen Fällen muss) verstärkt auf die Datenintegrität geachtet werden. Wird in der tDBOutput Komponente keine Batch Size angegeben, so werden die Zeilen alle einzeln behandelt und es kann aus der Output Komponente ein neuer Datenstrang gezogen werden. Rejects entstehen durch die Datenbank, beispielsweise wenn eine PK oder FK Beschränkung verletzt wird. Diese abgewiesenen Daten können im Talend Job je nach Anwendungszweck behandelt werden: Soll der Job unter Angabe des abgewiesenen Datums hart abbrechen? Sollen alle abgewiesenen Daten in eine separate “Rejecttabelle” geschrieben werden?

Talend: Rejects from database
Talend single INSERTS: Falls keine Batch Size in der Output Komponente angegeben wird, kann auf die Rejects der Datenbank individuell reagiert werden. In diesem Beispiel werden die fehlerhaften Daten in eine Rejecttabelle ausgeschleust und zusätzlich wird per tWarn darüber informiert. Nach erfolgreicher Beladung der Zieltabelle werden die Daten per explizitem COMMIT festgeschrieben.

Good to know

Was gibt es zu beachten, falls aus einem Datenfluss mehrere Tabellen beladen werden sollen? Im obigen Bild zum “Batch UPSERT” ist sichtbar in welcher Reihenfolge die Datenlinks bearbeitet werden: Main order: 1 oder 2. Diese beiden Datenflüsse verlaufen nicht unabhängig voneinander. Talend arbeitet weiterhin Zeile für Zeile und zwar immer in der angegebenen Reihenfolge. Könnte eine Zieltabelle prinzipiell schneller beladen werden, wird der Ladevorgang durch das parallele Laden der anderen Tabelle ausgebremst. Sind Abhängigkeiten, wie zum Beispiel FK Beziehungen, vorhanden, so muss zwingend die richtige Ladereihenfolge gesetzt werden.

Zusammenfassung und Ausblick

Talend bietet mit den zwei Komponenten tDBOutput und tDBBulkExec sehr flexible Möglichkeiten an, Daten in eine Datenbank zu laden. Es können sowohl große Datenmengen im batch möglichst performant, als auch kontrolliert auf Einzelsatzbasis geladen werden. Mit welcher genauen Commitsize oder Batch Size gearbeitet werden sollte; ob ein Lookup im Talendjob durchgeführt wird, oder direkt über die Datenbank; ob eine LOAD Strategie sinnvoll ist, oder direkte INSERTs, ist immer stark abhängig vom Use Case und der Infrastruktur. Wir unterstützen Sie dabei den optimalen Weg zu finden.