Update

Das Modul generiert ein Update 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. Zeilen mit Schlüsseln, die in der SQL-Abfrage vorkommen aber nicht in der Zieltabelle, werden ignoriert (hierzu muss man das Upsert Modul verwenden).

Beschreibung

Beim SQL-Befehl UPDATE nach ANSI Definition kann nur die Zieltabelle als direkte beteiligte Tabelle angegeben werden. Um mehrere Spalten mit dem Update nach ANSI zu verändern, muss man in der SET-Clause pro Spalte ein Subselect angeben und zusätzlich eine weiteres Subselect (z.B. mit NOT IN oder NOT EXISTS) um die Menge der zu verändernden Daten einzuschränken.

Somit lässt sich mit dem UPDATE Befehl nach ANSI Standart nicht erreichen, dass man eine Tabelle durch eine Quelletable oder -abfrage lesbar und performant abgleicht.

Jedoch bieten die Datenbanken unterschiedliche Möglichkeiten, um ein UPDATE FROM zu realisieren. Abhängig von der beteiligten Datenbank wird ein SQL-Befehl generiert, der den UPDATE FROM Anforderungen genügt.

Das folgende Beispiel zeigt, wie man mit Hilfe des Moduls eine Update 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:

UPDATE data_mart.d_nation AS tgt
   SET nation_name = src.nation_name
     , region_key = src.region_key
     , region_name = src.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
       ) AS src
 WHERE src.nation_key = tgt.nation_key
   AND NOT (src.nation_name IS NOT DISTINCT FROM tgt.nation_name
        AND src.region_key IS NOT DISTINCT FROM tgt.region_key
        AND src.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.

datasqill generiert ein Statement, das nur die Zeilen ändert, bei denen sich auch 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
          )

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

Update 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:

UPDATE data_mart.d_nation AS tgt
   SET tgt.nation_name = src.nation_name
     , tgt.region_key = src.region_key
     , tgt.region_name = src.region_name
     , tgt.ta_modified_by = 1703
     , tgt.ta_modified_ts = DATE_TRUNC('second', CURRENT_TIMESTAMP)
     , tgt.ta_is_deleted = 'N'
     , tgt.ta_deletion_by = NULL
     , tgt.ta_deletion_ts = NULL
  FROM data_mart.d_nation AS tgt
     , (
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
       ) AS src
 WHERE DECODE(src.nation_key, tgt.nation_key, 1, 0) = 1
   AND 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 ta_is_deleted = 'N'
        AND ta_deletion_by IS NULL
        AND ta_deletion_ts IS NULL
       )