Template MS Sync

datasqill Modul "Template SQL"

Steckbrief

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:

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:

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 ':'.

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

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

• Insert from Select
• Sync Tables
• SCD2

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:

Instanz "Sync Tables"

Funktionsweise

Attribute

Instanz "SCD2"

Funktionsweise

Attribute