Tiger103 ˚₊‧🐯.𖥔 ݁

ETL

May 29, 20255 min read

Die Hauptaufgaben des ETL-Prozesses

Der ETL-Prozess dient dazu, Daten aus verschiedenen Quellen zu integrieren, zu bereinigen und in einer Form zu speichern, die für die Analyse geeignet ist. Da der Prozess nicht komplett im Hauptspeicher abläuft, ist ein Zwischenspeicher (die sogenannte Staging Area) notwendig.

Der ETL-Prozess besteht aus drei Phasen: Extrahieren, Transformieren und Laden.

1. Extrahieren

In dieser Phase werden die Daten aus verschiedenen Quellen in eine Staging Area überführt:

  • Die Daten werden aus den Quellen gefiltert.
  • Das benötigte Format, der Zeitpunkt und der Ort der Daten werden bereitgestellt.
  • Änderungen in den Quelldaten werden erkannt:
    • Dies kann entweder durch aktive Meldung der Quelle erfolgen oder durch die Ermittlung der Änderungen.
  • Ein Extraktor ist die Komponente, die die Daten aus den Quellen extrahiert:
    • Die Lieferzeitpunkte der Daten können periodisch, synchron oder ereignisgesteuert sein.
    • Die Daten können geliefert werden als:
      • Kompletter Datenbestand (Snapshot)
      • Alle Änderungen (Logfile)
      • Nettoänderungen zu festen Zeitpunkten (Snapshot-Differenz)

2. Transformieren

In dieser Phase werden die extrahierten Daten in ein einheitliches Format gebracht und für die Analyse vorbereitet:

2. Transformieren

In dieser Phase werden die extrahierten Daten in ein einheitliches Format gebracht und für die Analyse vorbereitet.

Transformationsschritte:

  • Selektion bestimmter Datensätze: Auswahl von Datensätzen basierend auf Bedingungen.

    • Beispiel: Attribute ‘A’ nicht NULL und ‘C’ > 10.

  • Projektion bestimmter Spalten: Reduktion auf relevante Spalten.

    • Beispiel: Auswahl von ‘B’, ‘C’, ‘E’.

  • Pivotisierung von Zeilen in Spalten: Zeilen werden zu Spalten aggregiert.

    • Beispiel: Bestellungen nach Kategorie und Status.

  • Automatisches Erkennen und Korrigieren: Fehlerhafte Daten werden angepasst.

    • Beispiel: Firmennamen wie “AG” erkannt und standardisiert.

  • Separieren/Zusammenfassen von Feldern: Aufteilen oder Zusammenführen von Feldern.

    • Beispiel: “Max Mustermann” → Vorname: Max, Nachname: Mustermann.

  • Disaggregation von Werten: Werte in separate Tabellen auslagern.

    • Beispiel: Sensorwerte und Zeiten getrennt speichern.

Weitere Transformationsaufgaben:

  • Vereinheitlichung von Formaten: Einheitliche Datenformate.

    • Beispiel: Datum → “DD.MM.YYYY”.

  • Konvertierung von Einheiten: Einheiten angleichen.

    • Beispiel: cm und inch → mm.

  • Matching und Deduplizieren: Duplikate erkennen und entfernen.

    • Beispiel: Gleicher Name + Geburtsdatum → 1 Eintrag.

  • Konsistenzprüfung: Wertevalidierung.

    • Beispiel: Bewertung nur “Rot”, “Gelb”, “Grün”.

  • Vereinheitlichung von Codierungen: Unterschiedliche Codierungen angleichen.

    • Beispiel: 1/0, “Male/Female” → “M/F”.

  • Berechnung abgeleiteter Werte: Zusätzliche Werte berechnen.

    • Beispiel: Dauer zwischen Start- und Endzeit.

  • Aggregation von Daten: Zusammenfassen auf höhere Granularität.

    • Beispiel: Gesamtwert eines Verkaufs berechnen.

Abgleich (Reconciliation):
Überprüfung der Konsistenz zwischen Quelle und Ziel, z.B. Stammdatenabgleich.

3. Laden

In der letzten Phase werden die transformierten Daten in das Data Warehouse (DWH) geladen:

  • Strategien hängen von den Anforderungen und dem verfügbaren Zeitfenster ab.
  • Die Aktualisierung kann:
    • Stündlich, täglich oder wöchentlich erfolgen.
    • Daten können entweder angehängt oder überschrieben werden.

Einfügen in mehreren Tabellen

  • Standard erlaubt mit einer INSERT-Anweisung nur das Einfügen in einer Tabelle

Erweiterung der INSERT

Diese ermöglicht das gleichzeitige Einfügen in mehrere Tabellen.

INSERT [ALL|FIRST]
{WHEN <Bedingung> THEN
    {INTO ... VALUES}}
[ELSE
    INTO ... VALUES}]
<SELECT-Anweisung>;
  • INSERT ALL fügt jeden Datensatz der SELECT-Anweisung gemäß der formulierten Bedingungen ein
  • INSERT FIRSTfügt jeden Datensatz nur bei der ersten passenden Bedingung ein

Beispiel

  • Es sollen Tabellen für drei Kundenkategorien befüllt werden:
    • Kategorie III: Über 3000 €
    • Kategorie II: Über 3500 €
    • Kategorie I: Über 4000 €
  • Fall A: Kunden der höheren Kategorien sollen auch in den niedrigeren Kategorien erscheinen
  • Fall B: Kunden aus einer höheren Ka
  • Vorgehen
    • Zuerst Tabellen anlegen
    CREATE TABLE Kunde_Kat_III(
    ID NUMBER(10) PRIMARY KEY,
    Name VARCHAR2(50),
    Vorname VARCHAR2(50),
    Umsatz NUMBER(10,2));
    • Für die anderen beiden Kategorien analog
    • Fall A kann mit ALLgelöst werden
    INSERT ALL
    WHEN Umsatz>3000 THEN INTO Kunde_Kat_III
    WHEN Umsatz>3500 THEN INTO Kunde_Kat_II
    WHEN Umsatz>4000 THEN INTO Kunde_Kat_I
    SELECT * FROM Kunden_Extern_Fest;
    • Fall B kann FIRSTgelöst werden
    INSERT ALL
    WHEN Umsatz>3000 THEN INTO Kunde_Kat_III
    WHEN Umsatz>3500 THEN INTO Kunde_Kat_II
    WHEN Umsatz>4000 THEN INTO Kunde_Kat_I
    SELECT * FROM Kunden_Extern_Fest;

    • WHEN-Bedingung ist hier wichtig

Vergleich Performancemessung

Testbedingungen:

  • Datenmenge: 1.000.000 Datensätze
  • Messungen: Jeweils drei Durchläufe mit Mittelwertbildung
FälleGleichzeitiges Einfügen (min:sec)Standard-SQL (min:sec)
Fall A8:3926:04
Fall B9:1927:06

Das gleichzeitige Einfügen spart erheblich Zeit.

  • Die Zeiteinsparung entspricht in etwa der Anzahl der WHEN-Bedingungen Dies macht das gleichzeitige Einfügen besonders effizient für Szenarien mit komplexen Bedingungen oder großen Datenmengen.

MERGE INTO-Anweisung

ermöglicht kombinierte Anwendung

  • Syntax
MERGE INTO <Tabelle>
USING (<SELECT-Anweisung> oder <Tabelle> oder <View>)
ON (<Verbundbedingung>)
WHEN MATCHED THEN
    <Aktualisierungsanweisung>
WHEN NOT MATCHED THEN
    <Einfügeanweisung>;

Beispiel

  • Fakttabelle F, neue Daten sollen hinzugefügt werden:
    • Ist Datensatz neu, dann einfügen
    • Ist Datensatz schon vorhanden, dann Attribut valueerhöhen
MERGE INTO F           -- bestehende Tabelle
USING NeueDaten N      -- Neuetabele 
ON (F.ID1 = N.ID1 AND F.ID2 = N.ID2 AND F.ID3 = N.ID3)
WHEN MATCHED THEN
  UPDATE SET F.VALUE = F.VALUE + N.VALUE
WHEN NOT MATCHED THEN
  INSERT VALUES (N.ID1, N.ID2, N.ID3, N.VALUE);

MERGE INTO-Anweisung mit WHERE

Aktualisierungs- und Einfügeoperation dürfen WHERE-Klausel haben

  • Beispiel:
    • Tabelle N habe Zeitstempel
    • Aktualisierung nur bei jüngeren Datensätzen
MERGE INTO F          -- bestehende Tabelle
USING NeueDaten N     -- Neuetabele 
ON (F.ID1 = N.ID1 AND F.ID2 = N.ID2 AND F.ID3 = N.ID3)
WHEN MATCHED THEN
  UPDATE SET F.VALUE = F.VALUE + N.VALUE
     WHERE N.Timestamp > '01-02-2020' -- Hier WHERE
WHEN NOT MATCHED THEN
  INSERT VALUES (N.ID1, N.ID2, N.ID3, N.VALUE);

Bei INSERT-Anweisung analog möglich

MERGE INTO-Anweisung mit DELETE

Anstelle Aktualisierungsoperation darf DELETE-Operation ausgeführt werden

  • Beispiel:
    • Tabelle N habe Zeitstempel
    • Lösche alte Datensätze
MERGE INTO F  
USING NeueDaten N  
ON (F.ID1 = N.ID1 AND F.ID2 = N.ID2 AND F.ID3 = N.ID3)  
WHEN MATCHED THEN  
 
WHEN NOT MATCHED THEN  
INSERT VALUES (N.ID1, N.ID2, N.ID3, N.VALUE)

Bei INSERT-Anweisung nicht sinnvoll

Graph View

Backlinks

  • No backlinks found