Informationsintegration ETL & Datenherkunft ( Lineage )

InformationsintegrationETL & Datenherkunft (Lineage) 2.2.2006 Felix Naumann

Überblick • ETL • Extract • Transform • Load • Data Lineage (Datenherkunft) • Motivation und Beispiel • Datentransformationen (jeweils Definition und Tracing Prozedur) • Dispatcher • Aggregatoren • Black Boxes & MISOs • Inverse Transformation • Transformationssequenzen Felix Naumann, VL Informationsintegration, WS 05/06

... Lookup tables ETL: Extract, Transform, Load ETL Schritte Daten quellen Data Extraction Data Transformation Data Loading Data Warehouse Data scrubbing Data cleansing Similarity Functions Object Fusion Felix Naumann, VL Informationsintegration, WS 05/06

ETL – Überblick • Häufig aufwändigster Teil des Data Warehousing • Vielzahl von Quellen • Heterogenität • Datenvolumen • Komplexität der Transformation • Schema- und Instanzintegration • Datenbereinigung • Kaum durchgängige Methoden- und Systemunterstützung • Vielzahl von Werkzeugen vorhanden • => Großes Forschungspotenzial! Quelle: VL „Data Warehouse Technologien“ Sattler/Saake Felix Naumann, VL Informationsintegration, WS 05/06

ETL – Überblick • Zwei Schritte • Von den Quellen zur Staging Area (Arbeitsbereich) • Extraktion von Daten aus den Quellen • Erstellen / Erkennen von differentiellen Updates • Erstellen von LOAD Files • Von der Staging Area zur Basisdatenbank • Data Cleaning und Tagging • Erstellung integrierter Datenbestände • Kontinuierliche Datenversorgung des DWH • Sicherung der DWH Konsistenz bzgl. Datenquellen • Zielkonflikt • Effiziente Methoden essentiell → Sperrzeiten minimieren • Rigorose Prüfungen essentiell → Datenqualität sichern Quelle: VL „Data Warehouse Technologien“ Sattler/Saake Felix Naumann, VL Informationsintegration, WS 05/06

Extraktion • Selektion eines Ausschnitts der Daten aus den Quellen und Bereitstellung für Transformation • Aufgabe • Regelmäßige Extraktion von Änderungsdaten aus Quellen • Datenversorgung des DWH • Unterscheidung • Zeitpunkt der Extraktion • Art der extrahierten Daten Quelle: VL „Data Warehouse Technologien“ Sattler/Saake Felix Naumann, VL Informationsintegration, WS 05/06

Extraktion – Zeitpunkt • Synchrone Benachrichtigung • Asynchrone Benachrichtigung • Periodisch • Quellen erzeugen regelmäßig Extrakte • DWH fragt regelmäßig Datenbestand ab • Ereignisgesteuert • DWH erfragt Änderungen vor jedem Jahresabschluss • Quelle informiert nach 100 Änderungen • Anfragegesteuert • DWH erfragt Änderungen Quelle: VL „Data Warehouse Technologien“ Sattler/Saake Felix Naumann, VL Informationsintegration, WS 05/06

Extraktion – Art der Daten • Snapshots: Quelle liefert immer kompletten Datenbestand • Neuer Lieferantenkatalog, neue Preisliste, etc. • Logs: Quelle liefert jede Änderung • Transaktionslogs, Anwendungsgesteuertes Logging • Netto-Logs: Quelle liefert Netto-Änderungen • Katalogupdates, Snapshot-Deltas Quelle: VL „Data Warehouse Technologien“ Sattler/Saake Felix Naumann, VL Informationsintegration, WS 05/06

Transformation • Anpassung der Daten an vorgegebene Schema- und Qualitätsanforderungen • Siehe diese gesamte VL • Problem • Daten im Arbeitsbereich nicht im Format der Basisdatenbank • Struktur der Daten unterschiedlich • Schematransformation • Schema Mapping etc. • Datentransformation • Data Scrubbing, Data Cleansing Quelle: VL „Data Warehouse Technologien“ Sattler/Saake Felix Naumann, VL Informationsintegration, WS 05/06

Laden • Physisches Einbringen der Daten aus dem Arbeitsbereich (staging area) in das Data Warehouse (einschl. eventuell notwendiger Aggregationen) • Aufgabe • Effizientes Einbringen von externen Daten in DWH • Kritischer Punkt • Load-Vorgänge blockieren unter Umständen die komplette DB (Schreibsperre auf Faktentabelle) • Aspekte • Trigger • Integritätsbedingungen • Indexaktualisierung • Update oder Insert? Quelle: VL „Data Warehouse Technologien“ Sattler/Saake Felix Naumann, VL Informationsintegration, WS 05/06

Satzbasiert Benutzung von Standard-Schnittstellen: PRO*SQL, JDBC, ODBC, ... Arbeitet im normalen Transaktionskontext Trigger, Indices und Constraints bleiben aktiv Manuelle Deaktivierung möglich Keine großräumigen Sperren Sperren können durch COMMIT verringert werden Benutzung von Prepared Statements Bulk-Load DB-spezifische Erweiterungen zum Laden großer Datenmengen Läuft (meist) in speziellem Kontext Komplette Tabellensperre Keine Beachtung von Triggern oder Constraints Indexe werden erst nach Abschluss aktualisiert Kein transaktionaler Kontext – kein Logging Laden Quelle: VL „Data Warehouse Technologien“ Sattler/Saake Felix Naumann, VL Informationsintegration, WS 05/06

Überblick • ETL • Extract • Transform • Load • Data Lineage (Datenherkunft) • Motivation und Beispiel • Datentransformationen (jeweils Definition und Tracing Prozedur) • Dispatcher • Aggregatoren • Black Boxes & MISOs • Inverse Transformation • Transformationssequenzen Nach [CW03] Foto von Jennifer Widom Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Motivation • Data Lineage • Data Lineage ist das Problem, zu Objekten im DWH diejenigen Objekte in den Quellen zu bestimmen, aus denen das DWH Objekt abgeleitet wurde. • Auch: Data Provenance • Auch: Data Pedigree • Data Warehouses • Datenanalyse • Decision Support • Data Mining • Aggregation Hilfe durch Data Lineage Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Motivation • Schwierigkeit des Data Lineage hängt von Transformationen ab • SQL: Leichter aber unrealistisch • Data Lineage durch SQL Sichten • Data Lineage durch Operatoren der relationalen Algebra • Allgemeine Transformationen: Schwierig aber wichtig • Data Lineage durch komplexe, nutzerdefinierte Transformationen • Data Lineage durch ETL Prozesse • Data Lineage durch Ketten von 60+ Transformationen • Data Lineage geschieht auf Datenebene. • Metadata Lineage • Schema Mapping • Schemaintegration Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Motivation Transformation T • Herkunft des Tupels (a, 2)? • T = B0 •  lin(a,2) = {(a,2)} • T = Gruppierung nach A und Aggregation: 2x SUM(B) •  lin(a,2) = {(a,-1); (a,2)} • T = Gruppierung nach A und Aggregation: MAX(B) • ... Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Motivation • Zusätzliche Schwierigkeiten • Runtime overhead • ETL • Bei virtueller Integration • Speicherbedarf • Metadaten • Transformationen • Einzeln • In Ketten • In (azyklischen) Graphen • Trade-off zwischen Nutzen und Kosten Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Beispiel • Produkt(PID, Name, Kategorie, Preis, Gültig) • Bestellung(BID, KundenID, Datum, Produkte) Quelle: [CW03] Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Beispiel • Ziel: Tabelle „Verkaufssprung“ • Computer-Produkte, die im letzten Quartal mehr als doppelt so viel verkauften wie im Durchschnitt der drei vorigen Quartale • Tabelle anlegen • Transformationen als Graph definieren • Transformationen ausführen Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Beispiel Verkaufssprung • T1: Bestellungen (Produktlisten) aufspalten • Neues Schema: Bestellung(BID, KundenID, Datum, PID, Menge) • T2: Kategorie selektieren • Filter für Computer Kategorie • T3: Join (und Projektion) über Bestellungen und Produkte • Neues Schema: (BID, Datum, PID, Menge, Name, Preis, Gültig) • T4: Aggregation und Pivotisierung • Verkaufsmenge pro Quartal und Produkt • Neues Schema: (Name, Q1, Q2, Q3, Q4) • T5: Durchschnittsberechnung • Neues Schema: (Name, Q1, Q2, Q3, AVG3, Q4) • T6: Selektion für Verkaufsprünge • T7: Projektion • Neues Schema: Verkaufssprung(Name, AVG3, Q4) T7 T6 T5 T4 T3 T2 T1 Produkt(PID, Name, Kategorie, Preis, Gültig) Bestellung(BID, KundenID, Datum, Produkte) Produkt Bestellung Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Beispiel Data Lineage für Verkaufssprung Tupel (Sony VAIO, 11250, 39600): 1950 Quelle: [CW03] Felix Naumann, VL Informationsintegration, WS 05/06

Überblick • ETL • Extract • Transform • Load • Data Lineage (Datenherkunft) • Motivation und Beispiel • Datentransformationen (jeweils Definition und Tracing Prozedur) • Dispatcher • Aggregatoren • Black Boxes & MISOs • Inverse Transformation • Transformationssequenzen Nach [CW03] Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen • Datenmenge: • Menge aus beliebigen Daten: • Tupel, Werte, Objekte • Hier: i.d.R. Tupel • Transformation: • Beliebige Prozedur, mit einer Datenmenge als Input und einer Datenmenge als Output. • T(I) = O • Komposition von Transformationen • T = T1∘T2: T(I) = T2(T1(I)) • [ Assoziativ: (T1∘T2)∘T3 = T1∘(T2∘T3) ] Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen – Eigenschaften • Stabil: Kein erfundener Output • Also: T() =  • Gegenbeispiel: Transformationen, die jedem Tupel einen festen Wert anhängen • Deterministisch: Immer gleicher Output bei gleichem Input • Gegenbeispiel: Transformationen, die einen zufälligen Sample produziert. Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Definition • Allgemein gilt: Transformationen können für jeden Outputwert alle Inputwerte betrachten. • I.d.R. ist das nicht so. • Sei T(I) = O und oO • I*  I ist die Menge der Inputwerte, die zum Output o beitragen. • I* = T*(o,I) • Sei O*O, dann T*(O*,I) = ⋃oO*T*(o,I) • O* ist der „interessante“ Output Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Motivation Transformation T • Herkunft des Tupels (a, 2)? • T = B0 •  T*((a,2),I) = {(a,2)} • T gruppiert nach A und aggregiert 2x SUM(B) •  T*((a,2),I) = {(a,-1); (a,2)} Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen • Zwei Extreme • Relationale Operatoren oder Sichten: • Exakte Data Lineage kann bestimmt werden. • Völlig unbekannte Transformation • Der gesamte Input ist Data Lineage. • Realität liegt dazwischen. • Drei Transformationsklassen • Hinzu kommen • Schema Mappings (nicht hier) • Inverse Transformationen Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen - Klassifikation • Dispatcher (wörtlich: „Abfertiger“) • Jeder Input produziert null oder mehr Outputs • Aggregatoren • Gruppen von Inputs produzieren einen Output • Black-Boxes • Alles andere Quelle: [CW03] Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen – Dispatcher • Jeder Input produziert unabhängig null oder mehr Outputs. • Formal: • ∀I, T(I) = ⋃iI T({i}) • Lineage: • T*(o,I) = {iI | o  T({i})} Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Beispiel Verkaufssprung • T1: Bestellungen aufspalten • T2: Kategorie selektieren • T3: Join und Projektion • T4: Aggregation und Pivotisierung • T5: Durchschnittsberechnung • T6: Selektion für Verkaufsprünge • T7: Projektion T7 Dispatcher Dispatcher T6 T5 Dispatcher T4 Dispatcher T3 Dispatcher T2 T1 Produkt Bestellung Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen – Dispatcher • Tracing Prozedur • Definiert für Outputmengen, deshalb geeignet für Kompositionen • Aufwand: • Vollständiger Scan des Input • Transformationsaufruf für jeden Inputwert Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen – Dispatcher • Dispatcher-Spezialfall: Filter • Filter: • ∀iI, T({i}) = {i} oder T({i}) =  • Data Lineage: • T*(o) = {o} • Bzw. T*(O) = O • Tracing Prozedur trivial Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Beispiel Verkaufssprung • T1: Bestellungen aufspalten • T2: Kategorie selektieren • T3: Join und Projektion • T4: Aggregation und Pivotisierung • T5: Durchschnittsberechnung • T6: Selektion für Verkaufsprünge • T7: Projektion T7 Dispatcher Filter Dispatcher T6 T5 Dispatcher T4 Dispatcher T3 Dispatcher Filter T2 T1 Produkt Bestellung Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen – Aggregatoren • Zwei Bedingungen müssen gelten • Partition: Inputs können partitioniert werden, so dass jede Partition für genau ein Output verantwortlich ist. • Sei T(I) = {o1,..., on}. • ∀I existiert eine eindeutige, disjunkte Partitionierung I1, ..., In, so dass T(Ik) = ok für alle k. • Vollständig: Jeder Input ist an mindestens einem Output beteiligt • ∀I  , T(I)   • Lineage: T*(ok,I) = Ik Felix Naumann, VL Informationsintegration, WS 05/06

Vergleich: Dispatcher vs. Aggregator • Dispatcher • Jeder Input produziert unabhängig null oder mehr Outputs. • Aggregator • Jeder Input ist an mindestens einem Output beteiligt. • Inputs können partitioniert werden, so dass jede Partition für genau ein Output verantwortlich ist. • Transformationen, die zugleich Dispatcher und Aggregator sind • Identität • Projektion (ohne Duplikate) Felix Naumann, VL Informationsintegration, WS 05/06

Data Lineage – Beispiel Verkaufssprung • T1: Bestellungen (Produktlisten) aufspalten • Neues Schema: Bestellung(BID, KundenID, Datum, PID, Menge) • T2: Kategorie selektieren • Filter für Computer Kategorie • T3: Join (und Projektion) über Bestellungen und Produkte • Neues Schema: (BID, Datum, PID, Menge, Name, Preis, Gültig) • T4: Aggregation und Pivotisierung • Verkaufsmenge pro Quartal und Produkt • Neues Schema: (Name, Q1, Q2, Q3, Q4) • T5: Durchschnittsberechnung • Neues Schema: (Name, Q1, Q2, Q3, AVG3, Q4) • T6: Selektion für Verkaufsprünge • T7: Projektion • Neues Schema: Verkaufssprung(Name, AVG3, Q4) Aggregator Filter T7 Dispatcher T6 Aggregator T5 Dispatcher T4 Dispatcher Aggregator T3 T2 T1 Filter Produkt Bestellung Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen – Aggregatoren Potenzmenge • Tracing Prozedur • Aufwand: 2|I| Aufrufe von T • Zu viel! • Deshalb zwei Unterklassen • Kontextfreie Aggregatoren • Schlüsselerhaltende Aggregatoren Mindestens I* ist Lineage I* ist vollstän-diges Lineage Eingrenzung: I* ist größer Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen – Aggregatoren • Kontextfreie Aggregatoren • Input gehört zu einer Partition, unabhängig von den Werten andere Inputs in der Partition. • Alle bisherigen Aggregatoren sind kontextfrei. • Gegenbeispiel: Clustering und Durchschnittsbildung über Cluster • Mitgliedschaft in einem Cluster ist von anderen Werten abhängig. • Tracing Prozedur einfacher • Intuition: Bildung der Partitionen I ist linear. Danach nur noch Zugehörigkeit prüfen. Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen – Aggregatoren Finde für jedes i eine Partition Initialisierung der ersten Partition Prüfe ob i in eine vorhandene Partition passt Sonst erzeuge neue Partition |I²| Transformationen Suche Partitionen, die O* erzeugen. |I| Transformationen Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen – Aggregatoren • Schlüsselerhaltende Aggregatoren • Sei I partitioniert I1, ..., In, so dass T(I) = {o1,...on}. • ∀k, ∀I‘Ik : T(I‘) = {o‘k} und o‘k.key = ok.key • Beispiel: „Normale“ Gruppierung und Aggregation • Gegenbeispiel: Gruppierung, die Gruppierungsattribut nicht erhält. • Tracing Prozedur • Aufwand: |I| • Intuition: Schlüssel imTransformationsergebniswird verwendet, um Zugehörigkeit zu prüfen. Felix Naumann, VL Informationsintegration, WS 05/06

Transformationen – Black Boxes • Transformationen, die weder Dispatcher noch Aggregatoren sind, noch eine explizite Tracing Prozedur aufweisen. • Beispiel: • Sortierung und Einfügen der Ordnungszahl. • Kein Dispatcher, weil Output nicht unabhängig • Kein Aggregator, weil ein Output nur mittels aller Inputs erzeugt werden kann. • Lineage: • T*(o,I) = I • Tracing Prozedur: • Trivial, • aber nutzlos Felix Naumann, VL Informationsintegration, WS 05/06

MISOs – Multiple Input Single Output Transformationen • Exklusive MISOs • Unabhängige Transformation jeder Inputmenge • Beispiel: UNION • Lineage: • Teilen der Transformation in unabhängige Teile • Bestimmung der Eigenschaften der Teile • Lineage gemäß der Eigenschaften • Inklusive MISOs • Lineage • Teilen der Transformation in Einzelteile • Jeweils anderer Input als Konstante Felix Naumann, VL Informationsintegration, WS 05/06

Vorgegebene Tracing-Prozedur • Mit „Glück“ wird zusammen mit der Transformationsprozedur die entsprechende Tracing-Prozedur TP geliefert. • TP benötigt Inputdaten: TP(O*,I) = I* • TP benötigt keine Inputdaten: TP(O*) = I* • Aufwand: unbekannt Felix Naumann, VL Informationsintegration, WS 05/06

Inverse Transformation • Mit „Glück“ wird zusammen mit der Transformationsprozedur die entsprechende inverse Prozedur geliefert. • T ist invertierbar, falls T-1 existiert, so dass • ∀I, T-1(T(I)) = I und • ∀O, T(T-1(O)) = O • Kann nicht immer als Tracing-Prozedur verwendet werden. • Immer bei Aggregatoren: I* = T-1(O*) • Nur manchmal bei Dispatchern und Black Boxes • Beispiel gleich. • Aufwand: unbekannt Felix Naumann, VL Informationsintegration, WS 05/06

Inverse Transformation – Beispiel • T: List-Merging • I = {(1,a),(1,c),(2,b),(2,g),(2,h)} • O = {(1,“a,c“),(2,“b,g,h“)} • Aggregator => T-1 ist als TP verwendbar • T-1: List Splitting • wie T1 in Beispiel • T-1((2,“b,g,h“)) = {(2,b),(2,g),(2,h)} • Dispatcher • Aber: (T-1)-1 ist keine Tracing Prozedur für T-1. • (T-1)-1((2,b)) = {(2,“b“)} Korrekte lineage wäre: (2,“b,g,h“) Felix Naumann, VL Informationsintegration, WS 05/06

Transformationssequenzen • Bisher: Lineage und Tracing für einzelne Transformationen • Nun: Sequenzen von Transformationen • Sei I2* = T2*(o,I2) • Sei I* = T1*(I2*,I) • Dann gilt I* = (T1∘T2)*(o,I) • Beispiel: • (T1∘T2)*(3,I) = {1,3} Felix Naumann, VL Informationsintegration, WS 05/06

Informationsintegration ETL & Datenherkunft ( Lineage )