WortmarkeTEDAMOH

Log in
updated 7:36 AM, May 6, 2020 Europe/Berlin
A+ A A-

Constraints im Data Vault

  • Published in Data Vault

Immer wieder kommt in Projekten die Frage auf, besser gesagt die Diskussion, ob Constraints in der Datenbank physisch sinnvoll sind oder nicht. Meist gibt es Vorgaben von DBAs oder durchsetzungsstarken ETLern, die eine generelle Abneigung gegen Constraints zu haben scheinen, dass Constraints nicht erwünscht sind. OK, diese Woche wurde mir wieder das Gegenteil bewiesen. Doch wie heißt es so schön: Ausnahmen ...

Auf dem #WWDVC und im Advanced Data Vault 2.0 Boot Camp haben wir ebenfalls über dieses Phänomen gesprochen. Das scheint weltweit zu existieren. Dazu hat kurz nach dem #WWDVC auch Kent Graziano einen Blogpost verfasst. Auf LinkedIn gab es dazu einige Kommentare.

Gut, wie argumentiert man am besten, bzw. was sind eigentlich die Vor- und Nachteile Constraints zu verwenden?

Jaja, ich weiß, eigentlich sind Constraints nur dazu da um ETLer daran zu hindern ihre Daten schnell in die Datenbank zu bekommen und um in Ladeketten für Probleme zu sorgen. Und natürlich werden alle Checks (die Constraints übernehmen sollten) von extra entwickelten Routinen in den ETL-Jobs gemacht. Also keine Notwendigkeit darüber zu schreiben? Alles geklärt, oder?

Ganz so sehe ich das nicht. In den Diskussionen sollte zuerst klar sein, über welche Art von Constraints eigentlich geredet wird:

  • NOT NULL Constraint (ja, das ist auch ein Constraint)
  • Check Constraint
  • Unique Constraint
  • Foreign Key Constraint
  • Primary Key Constraint

Hmm, und natürlich lieber erst gar nicht von Deletes on Cascade und Ähnlichem anfangen. Das kann richtig Ärger machen, wenn man nicht weiß was man tut, oder?

Was ist das Ziel eines Data Warehouse? Primär Daten zu laden oder auch Daten und Information bereitzustellen? Aus meiner Sicht ist eines der größten Ziele eines Data Warehouse Daten und Information so schnell wie möglich den Anwendern zu Verfügung zu stellen, im Sinne der Abfragegeschwindigkeit. Per SQL, Bericht, Dashboard oder wie auch immer. Wenn dem nicht so wäre, bräuchte man die vielen Varianten von InMemory Datenbanken nicht.

Gut, kommen wir zu den Vor- und Nachteilen:

  • Nachteil 1: Sie erzeugen je nach Variante mehr oder weniger Overhead in der Datenbank. Das kostet Zeit beim Laden der Daten.
  • Nachteil 2: Sie lassen ETL-Ketten abbrechen, wenn die Daten nicht passen. Sehr ärgerlich und bedeutet im Betrieb Aufwand.

     

  • Vorteil 1: Sie beschleunigen die Abfragegeschwindigkeit und helfen den Optimizern nicht unerheblich, siehe dazu das Beispiel unten.
  • Vorteil 2: Foreign Key Constraints sind bereits eine QS-Prüfung, die Datenbanken frei Haus liefern. Ich muss nicht aufwändig ETL-Routinen entwickeln, die diese vorhandenen Prüfungen nachbauen.
  • Vorteil 3: Direkte Rückmeldung, wenn Probleme in den Daten auftreten.
  • Vorteil 4: Speziell im Data Vault Datenmodell können so ETL-Prozesse einer QS unterzogen werden. Data Vault ETL Prozesse müssen durch ihre Natur immer so funktionieren, dass sie keine Foreign Key Constraint Verletzungen erzeugen.

Aus meiner Sicht überwiegen die Vorteile klar. Sollte beim Laden von großen Datenmengen Performanceprobleme auftreten, hat man in der Regel die Möglichkeit die Constraints zu deaktivieren und nach dem Laden wieder zu aktivieren.

Beispiel mit Hub und Satellite

Ich möchte euch ein konkretes Beispiel zeigen, wie sich ein Foreign Key Constraint auf der Teradata V14 auf den Explain (Select) auswirkt.

Dafür habe ich einen Hub mit zwei Satelliten (mit und ohne FK-Constraint) angelegt:

CREATE TABLE HUB_SomeHub(
    SEQ_SomeHub INTEGER NOT NULL,
    BK_SomeHub    INTEGER,
    LoadDate    DATE,
    RCSRC        VARCHAR(128),
    CONSTRAINT HubPK
    PRIMARY KEY (SEQ_SomeHub)
    );

 

CREATE TABLE SAT_WithFK(
    SEQ_SomeHub INTEGER NOT NULL,
    LoadDate    DATE    NOT NULL,
    ColNumberA    INTEGER,
    ColNumberB    INTEGER,
    LoadEndDate    DATE,
    RCSRC        VARCHAR(128),
    CONSTRAINT SatPK
    PRIMARY KEY (SEQ_SomeHub,LoadDate),
    CONSTRAINT RefInt
    FOREIGN KEY (SEQ_SomeHub) REFERENCES WITH NO CHECK OPTION HUB_SomeHub(SEQ_SomeHub)
    );

 

CREATE TABLE SAT_WithNoFK(
    SEQ_SomeHub INTEGER NOT NULL,
    LoadDate    DATE    NOT NULL,
    ColNumberA    INTEGER,
    ColNumberB    INTEGER,
    LoadEndDate    DATE,
    RCSRC        VARCHAR(128),
    CONSTRAINT SatPK
    PRIMARY KEY (SEQ_SomeHub,LoadDate)
    );

 

Sample with Constraints in Data Vault

Der folgende Explain zeigt den Query Plan für ein vereinfachten SELECT mit einem Join zwischen dem Hub HUB_SomeHub und dem Satelliten SAT_WithNoFK. Beachtet dabei die Schritte 3-5.

EXPLAIN
    SELECT HUB_SomeHub.SEQ_SomeHub,
        SAT_WithNoFK.ColNumberA,
        MAX(SAT_WithNoFK.ColNumberB)
        FROM HUB_SomeHub,
            SAT_WithNoFK
        GROUP BY HUB_SomeHub.SEQ_SomeHub,
            SAT_WithNoFK.ColNumberA
        WHERE HUB_SomeHub.SEQ_SomeHub = SAT_WithNoFK.SEQ_SomeHub
        --AND      SAT_WithNoFK.LoadEndDate = '2999-12-31'    
        ORDER BY 1;

Expl.

1) First, we lock a distinct DLERNER.pseudo table for read on a
     RowHash to prevent global deadlock for DLERNER.TableWithNoFK.
  2) Next, we lock a distinct DLERNER.pseudo table for read on a
     RowHash to prevent global deadlock for DLERNER.SomeTable.
  3) We lock DLERNER.TableWithNoFK for read, and we lock
     DLERNER.SomeTable for read.
  4) We do an all-AMPs RETRIEVE step from DLERNER.TableWithNoFK by way
     of an all-rows scan with a condition of (NOT
     (DLERNER.TableWithNoFK.ColNumberB IS NULL)) into Spool 4
     (all_amps), which is redistributed by the hash code of (
     DLERNER.TableWithNoFK.ColNumberB) to all AMPs.  Then we do a SORT
     to order Spool 4 by row hash.  The size of Spool 4 is estimated
     with low confidence to be 2 rows (50 bytes).  The estimated time
     for this step is 0.01 seconds.
  5) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a
     RowHash match scan, which is joined to DLERNER.SomeTable by way of
     a RowHash match scan with no residual conditions.  Spool 4 and
     DLERNER.SomeTable are joined using a merge join, with a join
     condition of (DLERNER.SomeTable.ColNumberPK = ColNumberB).  The
     result goes into Spool 3 (all_amps), which is built locally on the
     AMPs.  The size of Spool 3 is estimated with index join confidence
     to be 2 rows (54 bytes).  The estimated time for this step is 0.05
     seconds.
  6) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
     way of an all-rows scan , grouping by field1 (
     DLERNER.SomeTable.ColNumberPK ,DLERNER.TableWithNoFK.ColNumberA).
     Aggregate Intermediate Results are computed locally, then placed
     in Spool 5.  The size of Spool 5 is estimated with low confidence
     to be 2 rows (82 bytes).  The estimated time for this step is 0.04
     seconds.
  7) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs.  Then we do a SORT to order Spool 1 by the sort key
     in spool field1 (DLERNER.SomeTable.ColNumberPK).  The size of
     Spool 1 is estimated with low confidence to be 2 rows (66 bytes).
     The estimated time for this step is 0.04 seconds.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.15 seconds.

Nun dieselbe Query, jedoch mit dem Satelliten SAT_WithFK und einen Foreign Key Constraint. Der Optimizer erkennt jetzt, dass die Schritte 3 – 5 überflüssig sind und optimiert entsprechend den Query Plan. Die geschätzte Zeit des Optimizer zur Ausführung der Query beträgt jetzt nur noch 50% der Query ohne Foreign Key Constraint. Selbst in meiner Virtual Machine war diese einfach Query mit Foreign Key Constraint bei jedem Test schneller als ohne.

EXPLAIN
    SELECT HUB_SomeHub.SEQ_SomeHub,
        SAT_WithFK.ColNumberA,
        MAX(SAT_WithFK.ColNumberB)
        FROM HUB_SomeHub,
            SAT_WithFK
        GROUP BY HUB_SomeHub.SEQ_SomeHub,
            SAT_WithFK.ColNumberA
        WHERE HUB_SomeHub.SEQ_SomeHub = SAT_WithFK.SEQ_SomeHub
        --AND      SAT_WithFK.LoadEndDate = '2999-12-31'    
        ORDER BY 1;

Expl.

1) First, we lock a distinct DLERNER.pseudo table for read on a
     RowHash to prevent global deadlock for DLERNER.TableWithFK.
  2) Next, we lock DLERNER.TableWithFK for read.
  3) We do an all-AMPs SUM step to aggregate from DLERNER.TableWithFK
     by way of an all-rows scan with a condition of (NOT
     (DLERNER.TableWithFK.ColNumberB IS NULL)) , grouping by field1 (
     DLERNER.TableWithFK.ColNumberB ,DLERNER.TableWithFK.ColNumberA).
     Aggregate Intermediate Results are computed locally, then placed
     in Spool 3.  The size of Spool 3 is estimated with low confidence
     to be 2 rows (82 bytes).  The estimated time for this step is 0.04
     seconds.
  4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs.  Then we do a SORT to order Spool 1 by the sort key
     in spool field1 (DLERNER.TableWithFK.ColNumberB).  The size of
     Spool 1 is estimated with low confidence to be 2 rows (66 bytes).
     The estimated time for this step is 0.04 seconds.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.08 seconds.

Und solltet Ihr den Oracle SQL Developer verwenden, gibt es noch einen guten Grund für Foreign Key Constraints: Lest dazu den Blogpost von ThatJeffSmith.

So long,

Euer Dirk

 

Kommentare   

# Mattick 2014-04-11 14:49
Grundsätzlich mag es so aussehen, als ob FK-Constraints als QS genutzt werden könnten. Aber gerade Ihr Beispiel zeigt, dass dies nicht notwendig ist. Werden PKs durch Sequenzen oder Identities oder AI im Hub gebildet, würde es zu keinem inkonsistenten Eintrag kommen, da der LKP in den HUB nicht den gewünschten Schlüssel liefern würde. Würde die PK-Bildung auf HASHs basieren, gebe ich Ihnen recht. Ohne eine implementierte Prüfung, gäbe es im Zweifel Satelliteneintr äge, die keine HUB-Entsprechun g hätten. Das wäre aber nicht weiter schlimm, da die Quelle des Satelliten eigentlich auch die Quelle des HUBs beinhalten müsste. Dieser Fall kann also nur auftreten, wenn es zu einer asynchronen Ladung kommt und würde selbst dann bei der nächsten Ladung korrigiert.
Was die Abfrage betrifft. Der DV ist grundsätzlich nicht für anwenderorienti erte Abfragen gedacht. I.d.R. wird immer ein Mart diese Schicht basierend auf dem Vault repräsentieren und so keinen Performancenach teil nach sich ziehen.
Antworten | Antworten mit Zitat | Zitieren
# Dirk Lerner 2014-04-11 19:29
Hallo Herr Mattick,

vielen Dank für Ihren Kommentar zu meinem Blogpost!

Ich stimme zu, dass Data Vault Prozesse, wenn sie richtig implementiert sind, keine Verletzung der RI erzeugen. Das liegt in ihrer Natur, wie geschrieben. Doch gerade zu Beginn der ETL-Prozess Entwicklung für Data Vault Patterns ist diese QS sinnvoll, da hier die meisten Fehler passieren und wenn es „nur“ das vertauschen von Attributen ist oder die falsche Ladereihenfolge der Entitäten.

Korrekturen von asynchronen Ladefehlern finden nur dann statt, wenn Sie die Daten nicht volatil der Staging Area speichern, sondern einen bestimmten Zeitraum persistieren.

Richtig, Data Vault sollte nicht den Anwendern direkt zur Verfügung stehen. Jedoch sind virtuelle Data Marts auf eine hohe Abfrageperforma nce angewiesen. Eine Virtualisierung der Abfrageschicht durch Views ist aus meiner Sicht sehr effizient, sowohl aus Platz- als auch aus Gründen der Flexibilität und agilen Vorgehensweisen.

Beste Grüße,
Ihr Dirk
Antworten | Antworten mit Zitat | Zitieren
# Markus Weber 2014-06-21 16:37
Hallo, ein von Ihnen aufgeführtes Argument ist die Unterstützung des Datenbank-Optim izers bei der Berechnungs seines Ausführungsplan es. Dies ist jedoch auch möglich durch sogenannte deklarative RI. Bei Oracle und DB2 kann man bspw. die RI mit der Option "NOT ENFORCED" definieren. Der Optimizer erhält somit unterstützende Informationen über Datenbeziehunge n, die auch für ein Query Rewriting von Nutzen sind - ohne Performance-Nac hteile beim Laden durch RI-Prüfungen.
cheers,
Markus Weber
Antworten | Antworten mit Zitat | Zitieren
-1 # Dirk Lerner 2015-01-19 20:07
Hallo Herr Weber,
ja, das ist richtig. Diese Option gibt es auch auf der Teradata. In diesem Fall müssen (sollten :-) ) die ETL-Prozesse die RI sicherstellen. Sonst kann es bei der Abfrage der Daten zu interessanten Phänomenen kommen.
Hier sollte man generell abwägen, was mehr Zeit kostet beim Laden der Daten. Die RI der Datenbank oder die RI der ETL-Prozesse.
Beste Grüße
Dirk
Antworten | Antworten mit Zitat | Zitieren
# Christian Gräfe 2015-01-19 09:56
Hallo,
wir betreiben ein DWH mit DV 1.0 und aktiven PKs und FKs. Persönlich kann ich auf Basis Oracle keinen signifikanten Unterschied feststellen, wenn ohne FKs in die Hubs, Links und Satelliten geladen wird. Wir haben es ständig mit FK-Verletzungen zu tun, weil die Quellsysteme doch andere Daten liefern, als vereinbart. In diesen Fällen bin ich froh "ordentlichen" FKs zu haben, die mir diese Probleme bereits in der Entwicklungs- oder Abnahme-Umgebun g aufzeigen.

Gruß Christian
Antworten | Antworten mit Zitat | Zitieren
-1 # Dirk Lerner 2015-01-19 20:02
Hallo Christian,
genau so sollte es sein. Ziel eines DWH ist es Daten schnell an den Fachbereich zu liefern. Wie sind hier deine Erfahrungen mit und ohne aktivierte RI?
Viele Grüße
Dirk
Antworten | Antworten mit Zitat | Zitieren

Like what you see?

Hit the buttons below to follow us, you won't regret it...