Die Transformationen

Die Erfassung der Transformationen erfolgt in Arbeitsblättern (Sheets). Diese werden in Verzeichnissen organisiert.

Eine einzelne Transformation benötigt Quellen und Ziele und verwendet ein bestimmtes Modul.

Verzeichnisse

Neue Ordner legt man im Explorer unter Transformations über das Kontextmenü an (New Folder). Siehe Der Navigationsbaum.

Arbeitsblätter

In Arbeitsblättern werden Transformationen zusammengefasst. Arbeitsblätter sind die kleinste Deploymenteinheit.

Ein Arbeitsblatt kann beliebig viele Elemente enthalten. Es empfiehlt sich, Blätter schlank und themengranular zu halten: gleichzeitiges Bearbeiten durch mehrere Entwickler ist nicht möglich.

Arbeitsblätter erscheinen rechts im Editor als Reiter. Die Beschriftung enthält den Namen der Umgebung, den Namen des Blatts und ein vorangestelltes *, wenn das Blatt im Bearbeitungsmodus ist. Es können mehrere Reiter offen sein, aber nur eines im Bearbeitungsmodus.

Ein Doppelklick auf den Reiter verbirgt den Explorer und maximiert den Arbeitsbereich. Ein erneuter Doppelklick blendet den Explorer wieder ein.

Maximaler Arbeitsbereich

Kontextmenü des Reiters:

Close: schließt das gewählte Arbeitsblatt
Close Others: schließt alle anderen Arbeitsblätter
Close All: schließt alle Arbeitsblätter

Reiter schließen

Unterhalb des Explorers erscheint die Dokumentation des Arbeitsblatts. Sie lässt sich dort oder in einem größeren Dialog bearbeiten (Schaltfläche in der Documentation-Titelzeile). Dazu muss das Blatt im Bearbeitungsmodus sein. Die Dokumentation wird in Markdown geschrieben; siehe Markdown-Dokumentation.

Schaltflächenleiste

Edit (F4): startet den Bearbeitungsmodus
Save (F9): speichert Änderungen
Cancel/Reload (F5): verwirft Änderungen seit dem letzten Speichern, beendet den Bearbeitungsmodus und lädt den Stand aus der Datenbank
Export SQL (F12): exportiert die Metadaten des Blatts in eine Datei (für Deployment oder Versionsverwaltung)
Sheet Validation (F7): prüft alle Elemente des Blatts und zeigt das Ergebnis in einem Dialog
Run: sendet einen Ausführungsrequest für alle Aktionen auf dem Blatt an den Scheduler
New Object: neues Objekt per Drag & Drop
New Action: neue Transformation per Drag & Drop
New Action/Object/Action: Kette aus Quellobjekt, Transformation und Zielobjekt per Drag & Drop
Suchfeld: Suche innerhalb des Arbeitsblatts
Zoom: Schieberegler (0–200 %) oder Stufen im Dropdown
Username & Zeitstempel: wer das Blatt zuletzt geändert hat

Elemente auf dem Arbeitsblatt

Im Bearbeitungsmodus platziert man Transformationen und Objekte per Drag & Drop. Der Datenfluss verläuft von oben nach unten.

Transformationen sind Aktionen, meist SQL, die ein Modul ausführt.
Objekte sind Quellen und Ziele (Tabellen, Webservices, Dateien usw.).

Verknüpfungen zieht man mit gedrückter linker Maustaste von einem Anschlusspunkt zum nächsten; bei grüner Kennzeichnung rasten sie ein. Quellen führen von der Unterseite des Objekts zur Oberseite der Transformation, Ziele von der Unterseite der Transformation zur Oberseite des Zielobjekts. Remove Connection im Kontextmenü löst eine Verknüpfung.

Einzelne Transformationen startet man über Run im Kontextmenü. Markierte Elemente lassen sich kopieren (Strg+C), einfügen (Strg+V) und löschen (Entf), auch zwischen Arbeitsblättern.

Objekt und Transformation

Arbeitsblatt validieren und exportieren

Sheet Validation prüft, ob Modell und verwendete Objekte konsistent sind. Blätter mit Fehlern dürfen nicht deployed werden: für die Ablaufsteuerung müssen alle in der Transformation verwendeten Objekte als Quelle oder Ziel auf dem Blatt stehen.

Arbeitsblatt validieren

Export SQL erzeugt ein Skript mit den Metadaten des Blatts. Das File kann in einer anderen Transformationsdatenbank eingespielt oder in der Versionsverwaltung abgelegt werden. Siehe Deployment.

Objekte

Ein Objekt ist Quelle oder Ziel einer Transformation, zum Beispiel eine Tabelle, ein Webservice, eine Datei oder eine Mailingliste. Die meisten Module erwarten mindestens eine Quelle und ein Ziel.

Ein Objekt definiert sich aus der Objektart und zusätzlichen Eigenschaften wie Connection, Name, Schema oder Pfad.

Auf Objektebene legt man fest, ob die Verbindung zu anderen Sheets als Abhängigkeit gilt. Im Feld Dependency steht Wait für „Auf Transformationen aus anderen Sheets warten“ und Ignore für „Nicht warten“. Innerhalb desselben Blatts gilt diese Einstellung nicht; siehe Abhängigkeiten.

Objekt anlegen

Ein Objekt entsteht per Drag & Drop des Objekticons auf das Raster. Doppelklick öffnet den Edit Object Dialog.

ID (nur Anzeige)
Objekttyp: abhängig von der Installation; Standard für Datenbankobjekte ist Table or View

Felder für Table or View:

Objekt

Dependency: Wait oder Ignore (siehe oben)
Connection: logische Verbindung
Schema: Schema zu dieser Connection
Table: Tabelle oder View; die Liste lässt sich per Texteingabe filtern
Partition: optionale Partition
Show Table Create Script: erzeugt DDL- und Select-Skripte zum Kopieren
Refresh Objects: aktualisiert den Objektkatalog und damit die Table-Liste

Refresh Objects

Refresh Objects liest die für datasqill sichtbaren Objekte aus den konfigurierten Schemata und schreibt sie in den Objektkatalog. Das ist initial nötig und nach jeder transformationsrelevanten Änderung an den Datenbankobjekten.

DDL-Skript

Show Table Create Script formatiert die erzeugten Skripte in einer Textbox (Kopieren per Strg+C oder Kontextmenü):

DDL-Skript

einfaches Create Table
Create Table als versionierte Tabelle mit Generierungsstatement
Tabellenkommentierung
einfaches Select

Welcher Code dabei generiert wird erfolgt an Hand eines freemarker Templates. Dieses Template ist in der Tabelle vv_sqts_config_global definiert:

Typ	config_key1	config_key2	config_key3	config_key4
Export	VARIABLE	Template	TABLE_SCRIPT	EXPORT

Es gibt jeweils zwei Sets von Variablen. Der erste Set bietet die unten angegebenen Variablen. Ein weiterer Set ist für die Basistabelle, falls es sich um ein Objekt handelt, welches auf genau ein anderes Objekt basiert (Für Views, die genau eine Tabelle als Quelle haben). Bei diesem Set haben alle Variablen den Prefix BASE_. Gibt es exakt ein Objekt auf den das Objekt basiert, dann wird dieser Set mit den Informationen der Basistabelle befüllt. Ansonsten entspricht dieser Set dem ersten Set.

Es werden im Freemarker folgende Variablen zur Verfügung gestellt:

Variable	Datentyp	Bedeutung
DATABASE_ID	Number	Die Connection des Objekts
SCHEMA_NAME	String	Der Name des Schemas
OBJECT_NAME	String	Der Name des Objekts
OBJECT_TYPE	String	Der Objekttyp. Dereit entweder TABLE oder VIEW
PK_NAME	String	Der Name des Primärschlüssels
OBJECT_COMMENT	String	Der Objektkommentar
SCHEMA_NAME_LOWER	String	Derzeit nicht unterstützt
OBJECT_NAME_LOWER	String	Derzeit nicht unterstützt
columns	Liste	Liste der Spalten des Objekts

Hierbei hat jedes Objekt eine Liste von Namen und deren Werten. Folgende Namen sind verfügbar:

Name	Datentyp	Bedeutung
DATABASE_ID	Number	Die Connection des Objekts
SCHEMA_NAME	String	Der Name des Schemas
OBJECT_NAME	String	Der Name des Objekts
COLUMN_NAME	String	Der Name der Spalte
COLUMN_POSITION	Number	Position der Spalte innerhalb des Objekts
COLUMN_TYPE	Number	Der Datentype
COLUMN_PK_POSITION	Number	Wenn nicht null Position im Primary Key
COLUMN_COMMENT	String	Der Spaltenkommentar
COLUMN_IS_NULLABLE	CHAR	"Y" Nulls sind erlaubt, "N" nicht
COLUMN_SOURCE_TYPE	String	Der Datentyp, wie im Quellsystem angegeben
SCHEMA_NAME_LOWER	String	Derzeit nicht unterstützt
OBJECT_NAME_LOWER	String	Derzeit nicht unterstützt
COLUMN_NAME_LOWER	String	Derzeit nicht unterstützt

Verwendung der Variablen im freemarker

Ein Beispiel, um den vollen Namen des Objekts name für die weitere Verwendung bereitzustellen:

[#if SCHEMA_NAME?has_content]
  [#assign schema=SCHEMA_NAME?lower_case + "."]
[#else]
  [#assign schema=""]
[/#if]
[#assign object=OBJECT_NAME?lower_case]
[#assign name=schema+object]

Analog für das Basisobjekt:

[#if BASE_SCHEMA_NAME?has_content]
  [#assign base_schema=BASE_SCHEMA_NAME?lower_case + "."]
[#else]
  [#assign base_schema=""]
[/#if]
[#assign base_object=BASE_OBJECT_NAME?lower_case]
[#assign base_name=base_schema + base_object]

Versionierte Tabellen erkennen:

[#-- base_tv and base_nv are placeholder for the versioned / non versioned table name with schema --]
[#assign base_tv=base_schema +base_object]
[#assign base_nv=base_schema +base_object]
[#if base_name?starts_with("tv_")]
  [#assign base_nv=base_schema + base_object[3..]]
[#else]
  [#assign base_tv=base_schema + "tv_" + base_object]
[/#if]

Die Spalten erkennen, die nicht zur Historisierung / Versionierung gehören

[#-- list of historize columns --]
[#assign hist_cols = ["upd_by", "ins_by", "is_latest_period","last_changed_dt", "valid_from_dt", "invalid_from_dt", "is_current_and_active", "is_deleted"]]
[#-- generate the list data_cols with all columns which are not sqdv columns --]
[#assign data_cols = []]
[#list baseColumns as col]
  [#if !(col["COLUMN_NAME"]?lower_case?starts_with("sqdv_")) && !hist_cols?seq_contains(col["COLUMN_NAME"]?lower_case)]
    [#assign data_cols += [col] ]
  [/#if]
[/#list]

Aus den Primary Key Spalten, die Spalten herausfltern, die nicht Teil der versionierung sind

[#-- generate the list pkCols with all primary key columns which are not sqdv columns --]
[#assign pkCols = []]
[#list baseColumns as col]
  [#if col["COLUMN_PK_POSITION"]?? && !(col["COLUMN_NAME"]?lower_case?starts_with("sqdv_")) && !hist_cols?seq_contains(col["COLUMN_NAME"]?lower_case)]
    [#assign pkCols += [col] ]
  [/#if]
[/#list]
[#assign pkCols = pkCols?sort_by("COLUMN_PK_POSITION")]

Eine Funktion, um sich die Spaltentypen für die Zielumgebung aufzubereiten

[#function toTargetType columType]
  [#local colType = columType?upper_case]
  [#local pos = colType?index_of("(")]
  [#if pos != -1]
    [#local colTypeFirst = colType?substring(0, pos)?trim]
    [#local colTypeLast = colType?substring(pos)?trim]
    [#local colTypeLast = colTypeLast?replace(" CHAR", "")?replace(" BYTE", "")]
  [#else]
    [#local colTypeFirst = colType?trim]
    [#local colTypeLast = ""]
  [/#if]
  [#if ["BIGINT", "INTEGER", "SMALLINT", "TINYINT"]?seq_contains(colTypeFirst)]
    [#local colTypeFirst = "INT"]
  [#elseif ["TIMESTAMP", "TIMESTAMP WITHOUT TIME ZONE"]?seq_contains(colTypeFirst)]
    [#local colTypeFirst = "DATETIME"]
  [#elseif ["VARCHAR2", "CHARACTER VARYING"]?seq_contains(colTypeFirst)]
    [#local colTypeFirst = "VARCHAR"]
  [#elseif ["CHARACTER"]?seq_contains(colTypeFirst)]
    [#local colTypeFirst = "CHAR"]
  [#elseif ["NUMBER", "NUMERIC"]?seq_contains(colTypeFirst) && colTypeLast=""]
    [#local colTypeFirst = "DECIMAL(15,2)"]
  [#elseif ["NUMBER", "NUMERIC"]?seq_contains(colTypeFirst)]
    [#local colTypeFirst = "DECIMAL"]
  [#elseif ["CLOB", "TEXT"]?seq_contains(colTypeFirst)]
    [#local colTypeFirst = "VARCHAR(MAX)"]
    [#local colTypeLast = ""]
  [/#if]
  [#return colTypeFirst + colTypeLast]
[/#function]

Die Ausgabe der versionierten Tabelle für nicht SQDV-versionierten Tabellen

DROP TABLE IF EXISTS ${base_tv};

-- Versioned table with data types of target database
CREATE TABLE ${base_tv} (
    [#list data_cols as col]${col["COLUMN_NAME"]?lower_case} ${toTargetType(col["COLUMN_TYPE"])}[#if col["COLUMN_IS_NULLABLE"] == 'N'] NOT NULL[/#if][#sep]
  , [/#list]
  , is_latest_period CHAR(1) NOT NULL
  , is_current_and_active CHAR(1) NOT NULL
  , is_deleted CHAR(1) NOT NULL
  , last_changed_dt DATETIME NOT NULL
  , valid_from_dt DATETIME NOT NULL
  , invalid_from_dt DATETIME NOT NULL [#if pkCols?size > 0]
  , PRIMARY KEY([#list pkCols as pk]${pk["COLUMN_NAME"]?lower_case}, [/#list]invalid_from_dt)[/#if]
);

Transformation

Eine Transformation enthält den SQL-Code und wird zur Laufzeit durch das gewählte Modul ergänzt. Typische Module sind Insert from Select oder Upsert/Merge.

Der Entwickler erfasst zum Beispiel:

SELECT SYSDATE AS zeitpunkt_der_reporterstellung
     , TO_CHAR(TRUNC(order_date,'MM'), 'YYYY.MM') AS monat_der_bestellungen
     , COUNT(*)  AS anzahl_der_bestellungen
     , SUM(order_total) AS umsatzsumme_der_bestellungen
     , ROUND(AVG(order_total),1) AS average_umsatz_pro_bestellung
  FROM oe.orders
 GROUP BY TRUNC(order_date,'MM')
 ORDER BY monat_der_bestellungen;

Insert from Select erzeugt daraus zur Laufzeit:

INSERT INTO oe.order_report(
       zeitpunkt_der_reporterstellung
     , monat_der_bestellungen
     , anzahl_der_bestellungen
     , umsatzsumme_der_bestellungen
     , average_umsatz_pro_bestellung)
SELECT SYSDATE AS zeitpunkt_der_reporterstellung
     , TO_CHAR(TRUNC(order_date,'MM'), 'YYYY.MM') AS monat_der_bestellungen
     , COUNT(*)  AS anzahl_der_bestellungen
     , SUM(order_total) AS umsatzsumme_der_bestellungen
     , ROUND(AVG(order_total),1) AS average_umsatz_pro_bestellung
  FROM oe.orders
 GROUP BY TRUNC(order_date,'MM')
 ORDER BY monat_der_bestellungen

Transformation anlegen

Eine Transformation entsteht per Drag & Drop des Transformationsicons (Zahnrad) auf das Raster. Oft legt man gleich die Kette Quellobjekt–Transformation–Zielobjekt an. Doppelklick öffnet den Edit Action Dialog: Parameter, Basisabfrage und Validierung.

Links oben:

ID (nur Anzeige)
Name
Type: das Modul; die verfügbaren Typen sind installationsabhängig, siehe Module

Transformation anlegen

Weitere Parameter kommen vom gewählten Modul und sind über Tooltips beschrieben. Im Bereich Documentation hinterlegt man analog zum Arbeitsblatt eine Beschreibung in Markdown; siehe Markdown-Dokumentation. Doppelklick auf die Reiter Action oder Validate blendet Parameter- und Dokumentationsbereich aus bzw. ein.

SQL-Code

Im Reiter Action steht die Basisabfrage. Der Editor bietet Syntaxhervorhebung und Reformat SQL im Kontextmenü. Es gelten die SQL-Konstrukte der Transformationsdatenbank. Verwendete Objekte müssen schemaqualifiziert angegeben werden.

Modul

Im Feld Type stehen die in der Umgebung registrierten Module. Die Basisabfrage wird zur Laufzeit um die Modulfunktionalität ergänzt, zum Beispiel um den Insert-Anteil bei Insert from Select.

Damit ein Modul als Type erscheint, muss es implementiert, installiert und in der Transformationsdatenbank registriert sein. Die Parameter im Edit-Action-Dialog und ihre Tooltips stammen aus der Moduldefinition.

Validierung

Der Reiter Validate übergibt die Basisabfrage an die Validate-Funktion des Moduls:

die Abfrage wird in das effektive Statement überführt
Query und Modell auf dem Arbeitsblatt werden auf Konsistenz geprüft

Action Validation

Resulting Action to be performed zeigt das SQL, das der Lauf später ausführt. Dieses Statement wird nur zur Laufzeit gebaut und nicht persistiert.

Result of Validation vergleicht die in der Query verwendeten Objekte mit den auf dem Blatt modellierten Quellen und Zielen. Alle in der Query verwendeten Objekte sollten im Modell stehen, sonst greift die Ablaufsteuerung nicht.