Beispielmodul

Dieses Kapitel zeigt den gesamten Prozess um ein einfachen Templates für ein Insert von einer SQL Abfrage zu erstellen.

Zielsetztung: Der Anwender gibt in der Transformation ein SQL an und basierend auf dieses SQL, wird ein effektives SQL generiert, dass in einem Statement die Daten in die angegebene Zieltabelle einfügt. Zusätzlich soll der Entwickler für Oracle Datenbanken die Möglichkeit haben über Optimizer Hints das Vorgehen der Datenbank beim Einfügen zu beeinflussen (nicht das Verhalten beim Select, da man dieses bereits im Transformationscode mit angeben kann)

Auch wenn es hier eine spezifische Anforderung für eine Oracle Datenbank gibt, funktioniert das Modul für alle SQL Datenbanken. Es wird lediglich der Optimizer Hint von anderen Datenbanken ignoriert.

Erstellung eines Templates

Nehmen wir für die Abfrage wieder das Beispiel aus der Einleitung:

SELECT vertrag_datum.id
     , vertrag_datum.datum
     , vertrag_zeit.uhrzeit
  FROM vertrag_datum
     , vertrag_zeit
 WHERE vertrag_datum.id = vertrag_zeit.id

Als Ziel wird wieder die Tabelle vertrag_datum_zeit im Schema SOFTQUADRAT angegeben.

Wir nun kein Optimizer hint gegeben, dann soll das folgende effektive SQL generiert werden:

INSERT INTO SOFTQUADRAT.vertrag_datum_zeit (
       id
     , datum
     , uhrzeit
     )
SELECT vertrag_datum.id
     , vertrag_datum.datum
     , vertrag_zeit.uhrzeit
  FROM vertrag_datum
     , vertrag_zeit
 WHERE vertrag_datum.id = vertrag_zeit.id

Bei der Angabe des Hints "append" soll der Code so generiert werden:

INSERT /*+append*/ INTO SOFTQUADRAT.vertrag_datum_zeit (
       id
     , datum
     , uhrzeit
     )
SELECT vertrag_datum.id
     , vertrag_datum.datum
     , vertrag_zeit.uhrzeit
  FROM vertrag_datum
     , vertrag_zeit
 WHERE vertrag_datum.id = vertrag_zeit.id

Das FreeMarker Template ohne die Unterstützumg von einem Optimizer Hint könnte dann so aussehen:

[#include "dsmod_template.ftl"]
[#--
    Tatata mein erstes Modul
--]
INSERT INTO ${target} (
       [#list all_src as col]${col.name}[#sep]
     , [/#list]
     )
${interpret(action)}

• datasqill hat bereits ein (konfigurierbares) Template, in dem einige Funktionen zur Verfügung gestellt werden. Dieses wird in der ersten Zeile eingebunden, da es später die in diesem Template definierte Funktion interpret verwendet.
• Die Variable target wird vom Java Modul zur Verfügung gestellt und liefert Schema und Tabellennamen der Zieltabelle.
• Die Variable all_src ist eine Liste aller Quellspalten, in der Reihenfolge wie sie im Select angegeben wurden. Diese Liste wird mit dem [#list] in einen mehrezeilige kommaseparierten Text umgewandelt. Duch die [#sep] Anweisung, wird das letzte Komma in der Liste unterdrückt
• Die letzte Zeile gibt einfach nur die Abfrage wieder, jedoch wird das Transfomation SQL noch einmal interpretiert, damit dort auch Variablen verwendet werden können.

Jetzt erweitern wir das FreeMarker Template noch um den optionalen Optimizerhint:

[#include "dsmod_template.ftl"]
[#--
    Tatata mein erstes Modul
--]
INSERT [#if optimizer_hint?has_content]/*+${optimizer_hint}*/ [/#if]INTO ${target} (
       [#list all_src as col]${col.name}[#sep]
     , [/#list]
     )
${interpret(action)}

• Wenn der Optimizer Hint gegeben wurde (has_content) dann wir der Hint als SQL-Kommentar mit führendem + eingefügt

Verwendung in datasqill

Zum Testen des Moduls wird folgendes datasqill Sheet verwendet:

Die Transformation sieht folgendermaßen aus:

Als Ziel wird die Tabelle vertrag_datum_zeit im Schema SOFTQUADRAT angegeben.

In der GUI wird im Attribut optimizer_hint der Text append eingegeben.

Das generierte SQL sieht beim Validate der Transformation dann so aus:

• Die eingesetzte request_id ändert sich mit jedem Validate

Komplette Moduldefinition im datasqill Repository Format

Und hier jetzt die gesamte HJson-Datei mod_my_ins.dsdb, die dieses Modul definiert.

{
  objectList:
  [
    {
      dsdbFormat: 1
      deploymentType: version
      current:
      {
        version: 4.0.2
        data:
        [
          {
            target: vv_sqts_module
            columns: [ "module", "module_name", "module_type_id", "description", "module_command", "module_action_data_type" ]
            rows: [
              [ "template_my_ins", "My Insert (T)", 2, "Insert from Select (T)", "DsModTemplateSQL", "SQL" ]
            ]
          }
          {
            target: vv_datasqill_module_trigger
            columns: [ "module", "trigger_name", "trigger_level" ]
            rows: [
              [ "template_my_ins", "After Database Open", "Connection" ]
              [ "template_my_ins", "Instead of Truncate", "Connection" ]
              [ "template_my_ins", "Before Commit", "Connection" ]
              [ "template_my_ins", "After Commit", "Connection" ]
              [ "template_my_ins", "Before Validate", "Connection" ]
              [ "template_my_ins", "After Validate", "Connection" ]
            ]
          }
          {
            target: vv_sqts_action_attr_definition
            columns: [ "action_type", "attr_name", "attr_label", "attr_data_type", "default_value", "attr_description", "attr_position" ]
            rows: [
              [ "template_my_ins", "Truncate Before", "Truncate Before", "BOOLEAN", "N", "Empty target table before", 1 ]
              [ "template_my_ins", "optimizer_hint", "Optimzer hint", "CHAR", "", "Testmodul for me", 3 ]
            ]
          }
          {
            target: vv_sqts_config_global
            columns: ["config_key1", "config_key2", "config_key3", "config_key4", "config_value", "config_sort", "config_comment"]
            rows:
            [
              [ "VARIABLE", "Template", "MODULE", "template_my_ins"
                '''
                [#include "dsmod_template.ftl"]
                [#--
                    Tatata mein erstes Modul
                --]
                INSERT [#if optimizer_hint?has_content]/*+${optimizer_hint}*/ [/#if]INTO ${target} (
                       [#list all_src as col]${col.name}[#sep]
                     , [/#list]
                     )
                ${interpret(action)}
                '''
                null, "template for insert"
              ]
            ]
          }
        ]
      }
    }
  ]
}

Die Erstellung der Moduldefinition für dieses Beispiel erfolgte in weniger als 30 Minuten. Komplexere Module, die zum Beispiel Rücksicht auf die eingesetzte Datenbank nehmen müssen (siehe hierzu mod_upsert.dsdb), dauern natürlich etwas länger.

Das Deployment in das datasqill Repository erfolgt mit dem Befehl

deploySchema.sh --version 0 --dbid SQTS_DB_0 < mod_my_ins.dsdb

Anhang

Im Folgenden die Skripte für die Tabellen zur Demonstration des Modules:

CREATE TABLE vertrag_datum (
    id NUMBER
  , datum DATE
);

INSERT INTO vertrag_datum SELECT rownum, TRUNC(SYSDATE) - rownum FROM dual CONNECT BY rownum <= 10;

CREATE TABLE vertrag_zeit (
    id NUMBER
  , uhrzeit VARCHAR2(8)
);

INSERT INTO vertrag_zeit SELECT rownum, '10:00:' || LPAD(rownum, 2, '0') FROM dual CONNECT BY rownum <= 10;

CREATE TABLE vertrag_datum_zeit (
    id NUMBER
  , datum DATE
  , uhrzeit VARCHAR2(8)
  , etl_run_id NUMBER
);