Informationsintegration ETL

1
InformationsintegrationETL Datenherkunft
(Lineage)

• 2.2.2006
• Felix Naumann

2
Ü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

3
ETL Schritte
Data Transformation
Data Extraction
Daten quellen
Data Warehouse
Data Loading
Similarity Functions
Data scrubbing
Data cleansing
Object Fusion
4
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
• gt Großes Forschungspotenzial!

Quelle VL Data Warehouse Technologien
Sattler/Saake
5
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
6
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
7
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
8
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
9
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
10
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
11
Laden

• Satzbasiert
• Benutzung von Standard-Schnittstellen PROSQL,
  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

Quelle VL Data Warehouse Technologien
Sattler/Saake
12
Ü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
13
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
14
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

15
Data Lineage Motivation
A B
a -1
a 2
b 0
A B
a 2
b 0
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)
• ...

16
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

17
Data Lineage Beispiel

• Produkt(PID, Name, Kategorie, Preis, Gültig)
• Bestellung(BID, KundenID, Datum, Produkte)

Quelle CW03
18
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

19
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
Bestellung
Produkt
Produkt(PID, Name, Kategorie, Preis,
Gültig) Bestellung(BID, KundenID, Datum, Produkte)
20
Data Lineage Beispiel
Data Lineage für Verkaufssprung Tupel (Sony VAIO,
11250, 39600)
1950
Quelle CW03
21
Ü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
22
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 T1T2 T(I) T2(T1(I))
• Assoziativ (T1T2)T3 T1(T2T3)

23
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.

24
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?OT(o,I)
• O ist der interessante Output

25
Data Lineage Motivation
A B
a -1
a 2
b 0
A B
a 2
b 0
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)

26
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

27
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
28
Ü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
29
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)

30
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
Bestellung
Produkt
31
Transformationen Dispatcher

• Tracing Prozedur
• Definiert für Outputmengen, deshalb geeignet für
  Kompositionen
• Aufwand
• Vollständiger Scan des Input
• Transformationsaufruf für jeden Inputwert

32
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

33
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
Filter
Dispatcher
Dispatcher
T6
T5
Dispatcher
T4
Dispatcher
T3
Filter
Dispatcher
T2
T1
Bestellung
Produkt
34
Ü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
35
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

36
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)

37
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
Bestellung
Produkt
38
Transformationen Aggregatoren
Potenzmenge

• Tracing Prozedur
• Aufwand 2I 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
39
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.

40
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
41
Transformationen Aggregatoren

• Schlüsselerhaltende Aggregatoren
• Sei I partitioniert I1, ..., In, so dass T(I)
  o1,...on.
• ?k, ?I?Ik T(I) ok und ok.key ok.key
• Beispiel Normale Gruppierung und Aggregation
• Gegenbeispiel Gruppierung, die
  Gruppierungsattribut nicht erhält.
• Tracing Prozedur
• Aufwand I
• Intuition Schlüssel imTransformationsergebniswi
  rd verwendet, um Zugehörigkeit zu prüfen.

42
Ü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
43
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

44
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

45
Ü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
46
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

47
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

48
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 gt 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)
49
Ü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
50
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 (T1T2)(o,I)
• Beispiel
• (T1T2)(3,I) 1,3

51
Transformationssequenzen

• Naive Tracing Prozedur für Sequenzen T1... Tn
• Speicherung aller Zwischenergebnisse Ik
• Tracing Prozedur rückwärts für jeden
  Transformationsschritt.
• Nicht effizient
• Hoher Speicherbedarf
• Viele Transformationsschritte
• Besser Explizite Kombination von Transformationen

52
Transformationssequenzen

• Gegeben eine Transformationssequenz
• Normalisiere Sequenz durch geeignete
  Kombinationen
• Bestimme für Tracing benötigte Zwischenergebnisse
• Bei Transformation, speichere diese
  Zwischenergebnisse
• Iteratives Tracing durch normalisierte Sequenz

53
Transformationssequenzen Normalisierung

• Prinzipiell können jede und alle Transformationen
  zu einer einzigen Transformation kombiniert
  werden.
• Aber Erwünschte Eigenschaften gehen verloren.
• Transformationseigenschaften
• Klasse (Dispatcher, Aggregator, Filter, BlackBox)
• Vollständigkeit
• Jeder Input erzeugt ein Output
• Tracing Prozedur / Inverse
• weitere
• Kombination von Eigenschaften mit AND
• Bestimmung, welche Eigenschaften erwünscht sind,
  und wann Normalisierung sich lohnt, ist ein
  komplexes Problem.
• Kostenmodell nötig
• Hier nur
• Greedy Algorithmus

54
Transformationssequenzen Normalisierung
Index der geeignetsten Kombination
Kombiniere Transformationspaar
Alle Transformationen nach der ersten Black Box
vereinen.
55
Transformationssequenzen Normalisierung

• BestCombo(T1...Tn) Prozedur
• Heuristiken
• Kombiniere nie Black Boxes mit anderen
  Transformationen.
• Kombiniere keine Transformationen, wenn
  Tracing-Cost wesentlich schlechter wird.
• Kombiniere keine Transformationen, wenn
  Tracing-Genauigkeit wesentlich schlechter wird.
• Gruppierung aller (11) Eigenschaften in 5 Klassen
• Zuordnung jeder Transformation und Kombination in
  eine Klasse (gemäß bester Eigenschaft)
• Kombination ist schlecht, wenn neue Klasse
  erreicht würde.

56
Rückblick
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

Aggregator
T7
Filter
Dispatcher
T6
Aggregator
T5
Dispatcher
T4
Dispatcher
Aggregator
T3
Filter
T2
T1
Bestellung
Produkt
57
Rückblick
Definition
Beispiel
Tracing Prozedur
Tracing Aufwand
58
Rückblick

• Transformationssequenzen
• Normalisierungsalgorithmus

59
Literatur

• CW03 Yingwei Cui, Jennifer Widom Lineage
  tracing for general data warehouse
  transformations. VLDB J. 12(1) 41-58 (2003)
• Ergänzend
• BKT01 Peter Buneman, Sanjeev Khanna, Wang Chiew
  Tan Why and Where A Characterization of Data
  Provenance. ICDT 2001 316-330