Insert

Das Modul generiert ein Insert from Select Statement auf Basis der von der Entwickler eingegebenen SQL-Abfrage und führt das effektive Statement aus. Die Zuordnung der Spalten zwischen SQL-Abfrage und der Zieltabelle erfolgt über Namen.

Beschreibung

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

SELECT c_custkey AS key
     , c_name AS name
  FROM staging.customer

Heißen das Zielschema "demo" und die Zieltabelle "d_customer", generiert das Modul dazu das folgende, effektive SQL:

INSERT INTO demo.d_customer (
       key
     , name
     )
SELECT key
     , name
  FROM (
SELECT c_custkey AS key
     , c_name AS name
  FROM staging.customer
       ) src

Wie im Beispiel zu erkennen, müssen die Spaltennamen der Zieltabelle als Aliase angegeben werden, wenn die Quellspalten anders heißen. Im Beispiel hat die Zieltabelle "d_customer" also die Spalten "key" und "name". Das gilt insbesondere dann, wenn man Literale oder Funktionen verwendet, um Spaltenwerte zu erzeugen wie in diesem Beispiel:

SELECT c_custkey AS key
     , c_name AS name
     , 'de' AS country
     , CURRENT_DATE() AS createdate
 FROM staging.customer

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

Beispiele

Ein komplexeres Beispiel könnte wie folgt aussehen. Mit Hilfe der Abfrage

SELECT nation_key, part_key, supplier_key, ps_supplycost AS supplycost
     , ps_availqty AS availqty, CASE WHEN MIN(ps_supplycost) OVER (PARTITION BY region_key, part_key) = ps_supplycost THEN 'Y' ELSE 'N' END is_min_supply_cost
FROM demo.d_supplier, staging.partsupp, demo.d_nation, demo.d_part
WHERE ps_partkey = part_key
AND ps_suppkey = supplier_key
AND supplier_nation_key = nation_key

werden Daten aus den Tabellen demo.d_supplier, staging.partsupp, demo.d_nation und demo.d_part in einem Join verknüpft und gelesen.

Das Modul erzeugt daraus die effektive SQL-Anweisung:

INSERT into demo.f_supplier_rank (
       nation_key
     , part_key
     , supplier_key
     , supplycost
     , availqty
     , is_min_supply_cost
     )
SELECT nation_key
     , part_key
     , supplier_key
     , supplycost
     , availqty
     , is_min_supply_cost
  FROM (
SELECT nation_key, part_key, supplier_key, ps_supplycost AS supplycost
     , ps_availqty AS availqty, CASE WHEN MIN(ps_supplycost) OVER (PARTITION BY region_key, part_key) = ps_supplycost THEN 'Y' ELSE 'N' END is_min_supply_cost
FROM demo.d_supplier, staging.partsupp, demo.d_nation, demo.d_part
WHERE ps_partkey = part_key
AND ps_suppkey = supplier_key
AND supplier_nation_key = nation_key
       ) src

Wie man erkennen kann ist die originale SQL-Abfrage exakt und im gleichen Format in der generierten enthalten.

Variationen

Bei der Installation des Modules kann man weitere Attribute einstellen

Insert 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 zum "Truncate Before" 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.

Hierzu ein Beispiel für die monatsweise Verarbeitung von Daten der letzten 6 Monate mit Oracle SQL.

Loop-Query:

SELECT DISTINCT TO_CHAR(TRUNC(day, 'MM'), 'DD.MM.YYYY')  monat
  FROM <source-table>
 WHERE day >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -5)

und die Haupt-Query dazu:

SELECT day, col1, col2, ... , coln
  FROM <source-table>
 WHERE TRUNC(day, 'MM') = TO_DATE('${monat}', 'DD.MM.YYYY')

datasqill ersetzt in der Haupt-Query pro Schleifendurchlauf der Loop-Query die Variable ${monat} mit dem Wert der Spalte monat von der Loop-Query. Da der generierte Spaltenwert als Text übertragen wird, muss (wie in diesem Beispiel) eine Datentypenkonvertierung erfolgen.

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 "demo.d_customer" und fügt die technischen Spalten ta_inserted_ts, ta_inserted_by, ta_modified_ts und ta_modified_by hinzu, so wird unter Postgres folgendes generiert:

INSERT INTO demo.d_customer (
       key
     , name
     , ta_modified_by
     , ta_modified_ts
     , ta_inserted_by
     , ta_inserted_ts
     )
SELECT key
     , name
     , 2423589
     , DATE_TRUNC('second', CURRENT_TIMESTAMP)
     , 2423589
     , DATE_TRUNC('second', CURRENT_TIMESTAMP)
  FROM (
SELECT c_custkey AS key
     , c_name AS name
  FROM staging.customer
       ) src

Unter Oracle wäre das Resultat:

INSERT INTO demo.d_customer (
       key
     , name
     , ta_modified_by
     , ta_modified_ts
     , ta_inserted_by
     , ta_inserted_ts
     )
SELECT key
     , name
     , 2423589
     , SYSDATE
     , 2423589
     , SYSDATE
  FROM (
SELECT c_custkey AS key
     , c_name AS name
  FROM staging.customer
       ) src