Upsert

Das Modul generiert ein Datenbank-spezifisches Upsert (Update else Insert) Statement auf Basis der von der Entwickler eingegebenen SQL-Abfrage und führt das effektive Statement aus. Die Zieltabelle wird mit der SQL-Abfrage über definierte Schlüssel abgeglichen. Die Zeilen mit Schlüsseln die in der Zieltabelle als auch in der SQL-Abfrage vorhanden sind werden auf die Werte der SQL-Abfrage gesetzt (Update Funktionalität). Zeilen mit Schlüsseln, die in der SQL-Abfrage vorkommen aber nicht in der Zieltabelle, werden in die Zieltabelle eingefügt (Insert Funktionalität).

Beschreibung

Da nicht alle Datenbanken einen einheitlichen SQL MERGE Befehl haben wird abhängig von der beteiligten Datenbank ein Statement generiert, welches die Upsert Funktionalität durchführt.

/ Die SQL Abfrage und die Zieltabelle sind nach der Ausführung nicht identisch, da Zeilen, die nicht in der Quellabfrage sind, nicht aus der Zieltabelle gelöscht wurden. /

Das folgende Beispiel zeigt, wie man mit Hilfe des Moduls eine Upsert Anweisung aufbauen kann. Zunächst die verwendete SQL-Abfrage:

SELECT n.n_nationkey AS nation_key
     , n.n_name nation_name
     , r.r_regionkey AS region_key
     , r.r_name region_name
  FROM stage.region r
     , stage.nation n
 WHERE 1 = 1
   AND n.n_regionkey = r.r_regionkey

Als Primärschlüssel wird "nation_key" angegeben (siehe hierzu Attribute).

Heißen das Zielschema "data_mart" und die Zieltabelle "d_nation", generiert das Modul dazu unter Postgres das folgende, effektive SQL:

INSERT INTO data_mart.d_nation AS tgt (
       nation_key
     , nation_name
     , region_key
     , region_name
     )
SELECT nation_key
     , nation_name
     , region_key
     , region_name
  FROM (
SELECT n.n_nationkey AS nation_key
     , n.n_name nation_name
     , r.r_regionkey AS region_key
     , r.r_name region_name
  FROM stage.region r
     , stage.nation n
 WHERE 1 = 1
   AND n.n_regionkey = r.r_regionkey
       ) src
 ON CONFLICT (nation_key)
   DO UPDATE
      SET nation_name = excluded.nation_name
        , region_key = excluded.region_key
        , region_name = excluded.region_name
    WHERE NOT (excluded.nation_name IS NOT DISTINCT FROM tgt.nation_name
           AND excluded.region_key IS NOT DISTINCT FROM tgt.region_key
           AND excluded.region_name IS NOT DISTINCT FROM tgt.region_name
          )

Wie im Beispiel zu erkennen, müssen die Spaltennamen in der Quellabfrage als Aliase angegeben werden, damit sie den Spaltennamen der Zieltabelle entsprechen. Dieses entfällt natürlich, wenn die Abfragespalte bereits so heißt, wie die entsprechende Spalte im Ziel.

Im Falle vom Update prüft datasqill, ob sich etwas geändert hat. Diese Prüfung muss dabei berücksichtigen, dass Spalten, die sowohl in der Quelle als auch im Ziel NULL sind, als gleich gelten. Beim Postgres wurde in diesem Beispiel der Operator "IS NOT DISTINCT FROM" verwendet, um zu erkennen, dass die zwei Spalten identisch sind.

Bei einer Oracle Zieldatenbank sieht das generierte SQL so aus:

MERGE INTO data_mart.d_nation tgt
USING (
SELECT n.n_nationkey AS nation_key
     , n.n_name nation_name
     , r.r_regionkey AS region_key
     , r.r_name region_name
  FROM stage.region r
     , stage.nation n
 WHERE 1 = 1
   AND n.n_regionkey = r.r_regionkey
      ) src
   ON (src.nation_key = tgt.nation_key
      )
 WHEN MATCHED THEN
   UPDATE
      SET tgt.nation_name = src.nation_name
        , tgt.region_key = src.region_key
        , tgt.region_name = src.region_name
    WHERE NOT (DECODE(src.nation_name, tgt.nation_name, 1, 0) = 1
           AND DECODE(src.region_key, tgt.region_key, 1, 0) = 1
           AND DECODE(src.region_name, tgt.region_name, 1, 0) = 1
          )
 WHEN NOT MATCHED THEN
   INSERT (
          nation_key
        , nation_name
        , region_key
        , region_name
        )
   VALUES (
          src.nation_key
        , src.nation_name
        , src.region_key
        , src.region_name
        )

Man kann erkennen, dass sowohl der zugrunde liegende DML-Befehl sich unterscheidet (INSERT oder MERGE), als auch das Verfahren, wie geprüft wird, ob sich die Datenspalten geändert haben.

Datenquellen

Die Quellen des Moduls sind Tabellen, die über die vom datasqill Entwickler definierte Abfrage gelesen werden. Für diese Tabellen muss der datasqill Laufzeit-Benutzer Leserechte besitzen. Alle in der SQL-Abfrage verwendeten Quelltabellen müssen über die datasqill GUI im grafischen Datenmodell mit dem Eingang des Moduls verbunden werden.

Datenziel

Das Ziel des Moduls ist eine einzelne Tabelle. Für diese muss der datasqill Laufzeit-Benutzer Schreibrechte besitzen. Die Zieltabelle muss mit Hilfe der datasqill GUI im grafischen Datenmodell mit dem Ausgang des Moduls verbunden werden.

Attribute

Im Auslieferungszustand bietet das Modul nur ein Attribut in der GUI für den datasqill Entwickler an:

Variationen

Bei der Installation des Modules kann man weitere Attribute einstellen

Upsert mit Schleife

Das Modul lässt sich derart erweitern, dass man das Attribut "Loop Query" aktiviert (siehe Einstellen von Modul-Attributen im Anhang):

Dann hat man zusätzlich ein weiteres Attribut in dem man eine Query eingeben kann. Wenn man eine Loop-Query erfasst, dann führt das Modul pro gefundenen Datensatz die Hauptquery aus. Hierbei kann man die Spaltenergebnisse als Variablen verwenden.

Da die Hauptquery pro Ergebniszeile von der Loop-Query ausgeführt wird, sollte man die Ergebnismenge der Loop-Query gering halten. Sonst verliert man die Performanz von Mengenoperation und die Ausführung verhält sich prozedural.

Sehr sinnvoll kann man die Loop-Query auch als IF-Query verwenden. Z.B:

SELECT DISTINCT 1
  FROM <source-table>
 WHERE <last-change-date> >= <today>-7

Man bekommt eine Zeile, wenn sich seit 7 Tagen etwas geändert hat ansonsten keine. Somit wird die Haupt-Query einmal oder kein Mal ausgeführt.

In der Haupt-Query kann man die Spaltennamen der Loop-Query als Variablen verwenden. Es ist jedoch zu beachten, dass die Variablen jeweils als Text (String) eingefügt werden.

Siehe hierzu Beispiele beim Insert Modul.

Technische Spalten

Man kann technische Spalten definieren, die es ermöglichen, im Nachhinein zu sehen wann welche Zeilen eingefügt oder geändert wurden:

Die verwendeten Spaltennamen (unter Spaltendefinition) kann man ändern, sodass sie z.B. nicht mehr ta_inserted_ts heißt sondern insert_date. Siehe auch hierzu das Einstellen von Modul-Attributen im Anhang.

Enthät die Zieltabelle einige oder mehrere dieser technischen Spalten, so werden sie entsprechend der obigen Tabelle gefüllt. Ändert man im allerersten Beispiel die Zieltabelle "data_mart.d_nation" und fügt die technischen Spalten ta_inserted_ts, ta_inserted_by, ta_modified_ts, ta_modified_by, ta_deletion_ts, ta_deletion_by und ta_is_deleted hinzu, so wird unter Exasol folgendes generiert:

MERGE INTO data_mart.d_nation tgt
USING (
SELECT n.n_nationkey AS nation_key
     , n.n_name nation_name
     , r.r_regionkey AS region_key
     , r.r_name region_name
  FROM stage.region r
     , stage.nation n
 WHERE 1 = 1
   AND n.n_regionkey = r.r_regionkey
      ) src
   ON (src.nation_key = tgt.nation_key
      )
 WHEN MATCHED THEN
   UPDATE
      SET tgt.nation_name = src.nation_name
        , tgt.region_key = src.region_key
        , tgt.region_name = src.region_name
        , tgt.ta_modified_by = 1804
        , tgt.ta_modified_ts = DATE_TRUNC('second', CURRENT_TIMESTAMP)
        , tgt.ta_is_deleted = 'N'
        , tgt.ta_deletion_by = NULL
        , tgt.ta_deletion_ts = NULL
    WHERE NOT (DECODE(src.nation_name, tgt.nation_name, 1, 0) = 1
           AND DECODE(src.region_key, tgt.region_key, 1, 0) = 1
           AND DECODE(src.region_name, tgt.region_name, 1, 0) = 1
           AND tgt.ta_is_deleted = 'N'
           AND tgt.ta_deletion_by IS NULL
           AND tgt.ta_deletion_ts IS NULL
          )
 WHEN NOT MATCHED THEN
   INSERT (
          nation_key
        , nation_name
        , region_key
        , region_name
        , ta_modified_by
        , ta_modified_ts
        , ta_inserted_by
        , ta_inserted_ts
        , ta_is_deleted
        , ta_deletion_by
        , ta_deletion_ts
        )
   VALUES (
          src.nation_key
        , src.nation_name
        , src.region_key
        , src.region_name
        , 1804
        , DATE_TRUNC('second', CURRENT_TIMESTAMP)
        , 1804
        , DATE_TRUNC('second', CURRENT_TIMESTAMP)
        , 'N'
        , NULL
        , NULL
        )