Template MS Sync

datasqill Modul "Template SQL"

Steckbrief

Name Bedeutung
Basismodul Template SQL
Instanzen Insert from select
Sync Tables
SCD2
Typ Java
Programm DsModTemplateSQL
Verwendung Das Basismodul "Template SQL" ist ein generisches SQL Modul, das über eine SQL-Vorlage gesteuert wird. Es kann vielseitig verwendet werden, typische Anwendungsfälle sind Insert-Select, Merge-Select oder SCD2 Transformationen.

Basismodul

Das Basismodul "Template SQL" erzeugt aus einer SQL-Vorlage (SQL Template) und einer vom Entwickler vorgegebenen SQL-Abfrage zur Laufzeit eine effektives SQL-Anweisung, mit der Daten in eine Zieltabelle geschrieben werden. Dabei können Schlüsselwörter (Platzhalter) in der SQL-Vorlage angegeben werden, die bei der Ausführung für ein dynamisches Ersetzen von Inhalten verwendet werden.

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

INSERT INTO ${target_schema}.${target_table} (
    ${for(other_src_column)}${COLUMN}
    , ${end_for(other_src_column)}${for(REMOVE)}
    , ${end_for(REMOVE)}
    )
${action}

Die Schlüsselwörter sind mit einem führenden Dollarzeichen markiert und in geschweifte Klammern gesetzt. Das Modul verwendet sie bei der Ausführung, um eine geeignete Ersetzung von Texten durchzuführen. Der Entwickler muss in der datasqill GUI eine SQL-Abfrage, die sogenannte Action, definieren, die zur Laufzeit die Daten liefert. Sie könnte zum Beispiel so aussehen:

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

Damit generiert das Modul dann das folgende, effektive SQL:

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

Die Variablen "target_schema" und "target_table" wurden dabei durch "demo" bzw. "d_customer" ersetzt. Anstelle der Variablen "action" wird die gesamte Select-Abfrage verwendet und die Zielspalten werden für die Insert-Anweisung mit Hilfe der Ausdrücke "for(other_src_column)", "COLUMN", " end_for(other_src_column)", " for(REMOVE)" und " end_for(REMOVE)" aus den mit "AS" benannten Quellspalten erzeugt.

Datenobjekte

Datenquellen und Datenziele des Moduls sind Datenbanktabellen, die alle in derselben Datenbank liegen müssen. Das Modul unterstützt mehrere Quelltabellen aber nur eine Zieltabelle.

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 mit der 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 der datasqill GUI im grafischen Datenmodell mit dem Ausgang des Moduls verbunden werden.

Attribute

Das Basismodul besitzt folgende Attribute:

Name Typ Bedeutung
Truncate Before Boolean Das Modul löscht die Zieltabelle vor dem Beschreiben, wenn dieses Attribute auf 'Y' gesetzt ist. Das Löschen wird mit Hilfe einer Stored-Procedure versucht, die TRUNC_TABLE heißen muss. Ist die Stored-Procedure nicht vorhanden, wird der SQL-Befehl DELETE verwendet.

Schlüsselwörter

Das Modul unterstützt verschiedene Schlüsselwörter, die aus einer der folgenden Kategorien stammen:

  • Einzelvariablen,
  • Listen d.h. Aufzählung von Werten oder
  • Sprachelemente.

Folgende Schlüsselwörter werden vom Modul verarbeitet:

Name Typ Bedeutung
other_src_column Liste Enthält alle verbleibenden Quell-Spalten aus der Abfrage (SELECT), die nicht durch andere, benutzerdefinierte Spaltenlisten verwendet werden
other_tgt_column Liste Enthält alle verbleibenden Ziel-Spalten, die nicht durch andere, benutzerdefinierte Spaltenlisten verwendet werden
action Einzelvariable Enthält die vom datasqill Entwickler in der GUI definierte Query
target_schema Einzelvariable Enthält den Namen des Zielschemas
target_table Einzelvariable Enthält den Namen der Zieltabelle
target_partition Einzelvariable Enthält den Namen der Zielpartition
src_col_list Einzelvariable Enthält alle Quell-Spalten aus der Abfrage (SELECT) zur Verwendung in benutzerdefinierten Variablen
tgt_pk_list Einzelvariable Enthält die Namen der Primärschlüsselspalten in der Zieltabelle zur Verwendung in benutzerdefinierten Variablen
tgt_col_list Einzelvariable Enthält die Namen aller Spalten in der Zieltabelle zur Verwendung in benutzerdefinierten Variablen
COLUMN Einzelvariable Bezeichner für das aktuelle Listenelement in einer for-Iteration
REMOVE Liste Liste???
if_nullable Sprachelement Übernimmt den folgenden Block, wenn eine Spalte "Nullable" ist
else_nullable Sprachelement Übernimmt den folgenden Block, wenn eine Spalte nicht "Nullable" ist (optional)
end_nullable Sprachelement Beendet den "if_nullable"-Block
for Sprachelement Beginnt einen Iterationsblock über eine Liste
end_for Sprachelement Beendet einen Iterationsblock über eine Liste
if Sprachelement Übernimmt den folgenden Block, wenn eine Bedingung erfüllt ist
else Sprachelement Übernimmt den folgenden Block, wenn die zugehörige "if"-Bedingung nicht erfüllt ist (optional)
end_if Sprachelement Beendet einen "if"-Block

Daneben können für Instanzen des Moduls weitere benutzerdefinierte Variablen (Listen oder Einzelvariablen) angelegt werden (siehe benutzerdefinierte Variablen).

Details

other_src_column

Die Liste other_src_column enthält zunächst alle Spalten aus der SQL-Abfrage (SELECT). Sie kann in der Vorlage referenziert werden, um daraus zum Beispiel die Spalten für die Insert-Anweisung zu generieren.

other_tgt_column

xxx

action

Diese Variable enthält die vollständige SQL-Abfrage, die der datasqill Entwickler über die GUI hinterlegt hat. Mit ihr werden zur Laufzeit die Daten aus den Quelltabellen gelesen. Weiterhin wird sie beim Validieren verwendet, um die Quelltabellen des grafischen datasqill Modells dagegen zu prüfen.

target_schema, target_table und target_partition

Diese Variablen enthalten den Namen des Zielschemas, den Namen der Zieltabelle und den Namen der Zielpartition. Sie können verwendet werden, um beispielsweise eine Insert-Anweisung für die Zieltabelle zu erzeugen.

src_col_list

xxx

tgt_pk_list

xxx

tgt_col_list

xxx

COLUMN

xxx

REMOVE

xxx

if_nullable

Der folgende Codeausschnit demonstriert die Verwendung von "if_nullable". Eine Anweisung wie

t.${COLUMN} = ${if_nullable}${s.COLUMN}${else_nullable}NVL(s.${COLUMN}, -1)${end_nullable}

ergibt folgendes Ergebnis. Wenn die Spalte col1 Nullwerte zulässt (also "nullable" ist), lautet die generierte Zuweisung

t.col1 = s.col1

ansonsten wird folgendes erzeugt

t.col1 = NVL(s.col1, -1)
for

Ein Beispiel für die Verwendung von "for", "COLUMN" und "REMOVE" könnte so aussehen. Ist eine Liste von Spalte gegeben (im Beispiel "other_src_column"), so kann man über diese iterieren und dabei die Spaltennamen mit "COLUMN" einfügen lassen. Zusätzlich wird "REMOVE" verwendet, um abschließende Trennzeichen (im Beispiel Zeilenumbruch, Einrückung, Komma und Leerzeichen) hinter dem letzten Element wieder zu entfernen. Zunächst wieder die Anweisung

${for(other_src_column)}${COLUMN}
, ${end_for(other_src_column)}${for(REMOVE)}
, ${end_for(REMOVE)}

Enthält die Liste "other_src_column" die Spalten "col1", "col2" und "col3", so ergibt die Ersetzung

col1
, col2
, col3
if

Mit Hilfe von "if" können leere Listen bzw. leere Variablen erkannt werden und davon abhängig konditionale Blöcke eingefügt werden.

Die Anweisung

SELECT ${if(target_partition)}0${else(target_partition)}1${end_if(target_partition)} AS indicator

erzeugt

SELECT 0 AS indicator

wenn die Variable "target_partition" leer ist, ansonsten wird

SELECT 1 AS indicator

erzeugt.

Analog kann mit

${if(liste)}

überprüft werden, ob eine Liste leer ist, um einen Block einzufügen.

Benutzerdefinierte Variablen

Für den Aufbau von Modulinstanzen aus dem Basismodul können weitere, benutzerdefinierte Variablen verwendet werden. Diese Variablen werden als Attribute einer Modulinstanz definiert. Sie können Listen oder Einzelvariablen sein.

Für die Definiton der Werte von benutzerdefinierten Variablen gibt es verschiedene Optionen

  • Verwendung von Literalen,
  • Verwendung von vorhandenen Listen oder
  • Verwendung von Spaltennamen.

Für die Fälle 2 und 3 der obigen Liste muss bei der Definition einer benutzerdefinierte Variablen zunächst angegeben werden, ob diese Variable erforderlich ist (O oder R). Danach folgt die Angabe des Typs (C oder L) gefolgt von einem Doppelpunkt ':'.

Bezeichner Bedeutung Beschreibung
O optional optionale Variable
R required erforderlicher Variable
C column Einzelvariable
L list Liste

"OC:" kennzeichnet also beispielsweise eine optionale Einzelvariable, während "RL:" zum Beispiel eine erforderliche Liste definiert.

Benutzerdefinierte Variablen können für einzelne Modulinstanzen angelegt werden. Dazu müssen entsprechende Einträge in die Modulattributtabelle gemacht werden, nachfolgend einige Beispiele für die Definition von benutzerdefinierten Variablen:

action_type attr_name attr_label attr_data_type default_value attr_description attr_position
??? eternity Date of last SCD2-Record CHAR CAST(''9999-12-31'' AS DATE) Value of end date/time for open SCD2 periods -1
??? key Merge Key Columns CHAR RL:${tgt_pk_list} Column list to be used for "ON" section in merge statement. At least one column must be defined. 2
??? del_ind Deleted Indicator CHAR OC:deleted_record_ind Name of column which indicates that a record is deleted. -3

Modulinstanzen

Sowohl in der GUI als auch im Server verwendet datasqill Instanzen der Basismodule. Die Instanzen werden aus einem Basismodulen erzeugt, indem entsprechde Einträge in die Modultabelle und die Modulattributtabelle eingefügt werden. Damit liefert das Basismodul die Funktionaliät in Form des Programmcodes während die Instanzen über die möglichen Attribute ihr unterschiedliches Verhalten festlegen.

Es gibt folgende Instanzen des Basismoduls "Template SQL":

Die Instanzen verbergen die SQL-Vorlage vor dem datasqill Entwickler. Diese ist fest definiert und wird in die GUI nicht angezeigt.

Instanz "Insert from Select"

Funktionsweise

Die Modulinstanz "Insert from Select" verwendet die SQL-Vorlage, um damit eine Insert-Select-Anweisung aufzubauen.

In der SQL-Vorlage werden das Zielschema und die Zieltabelle, die Zielspalten (Aliase in der SQL Abfrage) und die SQL-Abfrage mit Platzhaltern angegeben. Daraus wird dann zur Laufzeit die effektive SQL-Anweisung erzeugt.

Aus einer vom datasqill vorgegebener SQL-Abfrage wie

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

erzeugt das Modul zum Beispiel folgendes effektive SQL

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

Attribute

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

Name Typ Bedeutung
Truncate Before Boolean Das Modul löscht die Zieltabelle vor dem Beschreiben, wenn dieses Attribute auf 'Y' gesetzt ist. Das Löschen wird mit Hilfe einer Stored-Procedure versucht, die TRUNC_TABLE heißen muss. Ist die Stored-Procedure nicht vorhanden, wird der SQL-Befehl DELETE verwendet.

Instanz "Sync Tables"

Funktionsweise

Attribute

Instanz "SCD2"

Funktionsweise

Attribute