Die Exa­sol Daten­bank kommt bei immer mehr Unter­neh­men zum Ein­satz als Daten­bank für ein Report­ing. Viele Mecha­nis­men der Exa­sol Daten­bank sind hier­bei dar­auf aus­ge­legt eine schnelle Daten­ver­ar­bei­tung zu ermög­li­chen und somit schnelle Berichte und Daten­auf­rufe zu ermög­li­chen. Als Bei­spiel kön­nen hier die auto­ma­ti­sier­ten Indi­zes, sowie Par­ti­tio­nie­rung von Tabel­len auf­ge­zählt wer­den. Außer­dem bie­tet die Exa­sol Daten­bank durch das Anle­gen von z.B. Lua oder Python Skrip­ten eine fle­xi­ble Mög­lich­keit direkt auf der Daten­bank ETL- oder ELT-Pro­zesse zu schrei­ben. Auch die Import Funk­tion der Daten­bank ist sehr per­for­mant und ermög­licht das Laden von gro­ßen Daten­men­gen in Sekun­den. Trotz die­ser Vor­teile hat die Daten­bank aber auch ein paar Nach­teile, die in bestimm­ten Situa­tio­nen den Anwen­der vor grö­ßere Pro­bleme set­zen könnte. Eines die­ser Pro­bleme ist das Feh­len von Daten­bank Triggern.

Daten­bank Trig­ger und In-memory Datenbanken

In-memory Daten­ban­ken unter­stüt­zen gene­rell keine Trig­ger Funk­tion, da die Trig­ger beim Auf­ruf der Tabelle mit­ge­la­den wer­den und somit den in-memory Spei­cher blo­ckie­ren. Gerade bei grö­ße­ren Tabel­len und meh­re­ren Trig­gern auf den Tabel­len kann dadurch viel Spei­cher unnö­tig blo­ckiert wer­den. Um die­ses Pro­blem zu umge­hen, muss man sich daher andere Mög­lich­kei­ten über­le­gen, mit denen man die Funk­tio­na­li­tä­ten von Trig­gern erhal­ten kann, ohne, dass die Per­for­mance dar­un­ter leidet.

Die Ide­al­vor­stel­lung in die­sem Sze­na­rio ist dann eine Rela­tio­nale Daten­bank, in der die Aufbereitung pas­siert, wobei anschlie­ßend die rele­van­ten Daten voll­stän­dig auf die In-memory Daten­bank über­tra­gen wer­den. Dies kann zu einer per­for­man­ten Lösung füh­ren, die aller­dings auch gleich­zei­tig Pro­bleme mit sich bringt. Die Tabel­len müss­ten ein­zeln gela­den wer­den, um zu ver­hin­dern, dass Berichte oder ein­zelne Ele­mente par­ti­ell nicht erreich­bar sind. Man kann schlech­ter Real-Time Aus­wer­tun­gen machen, da diese nach der Aus­wer­tung noch migriert wer­den müs­sen und abschlie­ßend macht einem häu­fig die bereits vor­han­dene Archi­tek­tur im Unter­neh­men indi­vi­du­elle Probleme.

Eine andere Mög­lich­keit ist es, die Bela­dung der Tabel­len per ETL ent­spre­chend anzu­pas­sen. In vie­len Unter­neh­men wer­den ETL-Tools ver­wen­det, um die Trig­ger dafür zu rea­li­sie­ren, aller­dings gibt es auch eine Mög­lich­keit dies per Lua-Skript direkt auf der Daten­bank umzusetzen. 

Ein all­ge­mei­nes ETL-Lua Skript

Wir betrach­ten im Fol­gen­den ein fik­ti­ves Bei­spiel von unter­schied­li­chen Tabel­len in einem unter­neh­men, die in regel­mä­ßi­gen Abstän­den gela­den wer­den. Die all­ge­meine Archi­tek­tur sieht dabei vor, dass wir fol­gende Struk­tur der Tabel­len haben:

Tabellenübersicht auf der Datenbank
Abbil­dung 1: Über­sicht über die all­ge­meine Struk­tur von Tabellen

Jede Tabelle in der Report­ing Schicht hat dem­nach eine Quell-Tabelle und eine tem­po­räre Tabelle. Die Bela­dung erfolgt dem­nach in 2 Schrit­ten von der Quell- in die Ziel-Tabelle. Da es viele Tabel­len gibt soll das Skript zur Bela­dung mög­lichst gene­risch geschrie­ben wer­den, damit die­ses uni­ver­sell ver­sen­det wer­den kann. Das Skript wird daher über Input Para­me­ter gesteu­ert. Das Skript erwar­tet fol­gende Input Para­me­ter: „DEST:Zieltabelle“, „SOUR:Quelltabelle“, „KEY:Schlüsselfeld“, „BDAT:Buchungstag“. Die Para­me­ter wer­den immer in zweier Pär­chen über­ge­ben, um eine ein­deu­tige Zuord­nung zu gewährleisten.

Auf­bau des Skriptes

Im ers­ten Schritt wer­den die Para­me­ter aus­ge­le­sen und loka­len Varia­blen zuge­ord­net. Dafür wird der loka­len Varia­ble lv_Paramter zunächst die Bezeich­nung der Varia­ble über­ge­ben, anschlie­ßend wird die­ser Bezeich­nung der Wert der Varia­ble über­ge­ben, z.B. lv_Paramerter[‚DEST‘]=‘PRODUCT‘. Anschlie­ßend liest das Skript aus dem Sys­tem­ka­ta­log die Spal­ten­na­men der Tabelle aus da diese für spä­tere Abglei­che und Updates genutzt werden.

--/ETL-Skript mit INSERT/UPDATE/DELETE Trigger
CREATE OR REPLACE LUA SCRIPT ETL_MIT_TRIGGER (ARRAY in_Parameter) RETURNS ROWCOUNT AS

--lokale Variable zum auslesen vorbereiten
local lv_Parameter = {}
local lv_Zaehler = 0

--auslesen der lokalen Parameter und Zuordnung
for i=1, #in_Parameter, 2 do
    lv_Parameter[in_Parameter[i]]=in_Parameter[i+1]
    lv_Zaehler = lv_Zaehler + 1
    output("Variable["..lv_Zaehler.."] "..in_Parameter[i]..": "..in_Parameter[i+1])
end

--schreiben der Werte in lokale Varianlen
local DEST=lv_Parameter['DEST']
local TEMP=lv_Parameter['DEST']..'_TEMP'
local SOUR=lv_Parameter['SOUR']
local KEY=lv_Parameter['KEY']
local BDAT=lv_Parameter['BDAT']

Nach dem Ein­le­sen der Para­me­ter wer­den die Spal­ten der Tabelle aus dem Sys­tem­ka­ta­log gele­sen. Außer­dem wer­den die Spal­ten direkt für das Merge State­ment vorbereitet.

--auslesen der Spalten
COLUMNS = query([[SELECT
                        LISTAGG(COLUMN_NAME||',') 
                  FROM
                        EXA_ALL_COLUMNS 
                  WHERE 
                        COLUMN_TABLE=:SOUR
                        ]],{SOUR=SOUR})

--entfernen des Kommas am Ende
COLUMNS_O_K = query([[SELECT 
                        SUBSTR(:COLUMNS,1,LEN(:COLUMNS)-1)
                        ]],{COLUMNS=COLUMNS[1][1]})

--auslesen der Spalten, die für den Update Vergleich notwendig sind
COLUMNS_UPDATE = query([[SELECT 
                                LISTAGG('
                                AND DEST.'||COLUMN_NAME||'=TEMP.'||COLUMN_NAME||'') 
                        FROM 
                                EXA_ALL_COLUMNS
                        WHERE 
                                COLUMN_TABLE=:SOUR
                                ]],{SOUR=SOUR})

Nun wird die tem­po­räre Tabelle gene­riert und auto­ma­ti­siert bela­den. Durch die Syn­tax in der Exa­sol Daten­bank ist es mög­lich die Tabelle mit einem CREATE OR REPLACE direkt neu auf­zu­bauen. Durch das AS SELECT * FROM SOURCE wird die Tabelle auto­ma­tisch gefüllt. Die Tem­po­räre Tabelle ent­hält neben den Daten auch den Buchungs­tag, der über­ge­ben wird.

--Erstellen und beladen der Temporären Tabelle
TEMPTABLE = query([[CREATE OR REPLACE TABLE ::TEMP AS
                        SELECT 
                                CD.INSERT_DATE,
                                SC.*
                        FROM 
                                ::SOUR SC
                        JOIN 
                                (SELECT 
                                        :BDAT INSERT_DATE) CD
                        ON 
                                1=1
                                ]],{TEMP=TEMP,SOUR=SOUR,BDAT=BDAT})

Der vierte Schritt der Bela­dung ent­hält den Merge der Daten. Daten­sätze, die bis­her noch nicht vor­ge­kom­men sind oder eine Anpas­sung in den Spal­ten erhal­ten haben, wer­den in die Ziel­ta­belle gemer­ged mit dem ent­spre­chen­den Buchungstag.

--Insert von neuen oder veränderten Datensätzen
ETL_INSERT = query([[MERGE INTO ::DEST AS DEST 
                        USING ::TEMP AS TEMP 
                        ON 
                                DEST.::KEY=TEMP.::KEY
                        AND 
                                DEST.END_DATE='9999-12-31'
                        ]]..COLUMNS_UPDATE[1][1]..[[

                     WHEN NOT MATCHED THEN INSERT

                                (]]..COLUMNS[1][1]..[[ START_DATE)
                        VALUES
                                (]]..COLUMNS[1][1]..[[ INSERT_DATE)
                                ]],{TEMP=TEMP,DEST=DEST,KEY=KEY})

Im fünf­ten Schritt wer­den nun die Daten­sätze iden­ti­fi­ziert, die nicht mehr aktu­ell sind und ent­spre­chend ungül­tig gesetzt.

--Update von veralteten Datensätzen
ETL_UPDATE = query([[UPDATE ::DEST DEST
                        SET 
                                DEST.END_DATE=SOUR.MAXIMUM
                        FROM (
                                SELECT 
                                        MINIMUM, 
                                        MAXIMUM, 
                                        MIN.::KEY
                                FROM (
                                        SELECT 
                                                MIN(START_DATE) MINIMUM, 
                                                ::KEY 
                                        FROM 
                                                ::DEST
                                        WHERE 
                                                ::KEY IN (
                                                        SELECT 
                                                                ::KEY
                                                        FROM 
                                                                ::DEST
                                                        WHERE 
                                                                START_DATE<=:BDAT
                                                        AND 
                                                                END_DATE>=:BDAT
                                                        GROUP BY 
                                                                ::KEY
                                                        HAVING 
                                                                COUNT(*)>1)
                                        AND 
                                                START_DATE<=:BDAT
                                        AND 
                                                END_DATE>=:BDAT
                                        GROUP BY 
                                                ::KEY) MIN
                                JOIN (
                                        SELECT 
                                                MAX(START_DATE) MAXIMUM, 
                                                ::KEY 
                                        FROM 
                                                ::DEST
                                        WHERE 
                                                ::KEY IN (
                                                        SELECT 
                                                                ::KEY
                                                        FROM 
                                                                ::DEST
                                                        WHERE 
                                                                START_DATE<=:BDAT
                                                        AND 
                                                                END_DATE>=:BDAT
                                                        GROUP BY 
                                                                ::KEY
                                                        HAVING 
                                                                COUNT(*)>1)
                                        AND 
                                                START_DATE<=:BDAT
                                        AND 
                                                END_DATE>=:BDAT
                                        GROUP BY 
                                                ::KEY) MAX
                                ON 
                                        MIN.::KEY=MAX.::KEY) SOUR
                        JOIN 
                                ::DEST DEST
                        ON 
                                DEST.::KEY=SOUR.::KEY
                        AND 
                                DEST.START_DATE=SOUR.MINIMUM
                                ]],{BDAT=BDAT,DEST=DEST,KEY=KEY})

Nach dem Deak­ti­vie­ren der alten Daten­sätze wer­den die Daten­sätze iden­ti­fi­ziert, zu denen es keine neuen Daten­sätze gibt. Diese wer­den eben­falls ungül­tig geschrieben.

--Inaktivieren von gelöschten Datensätzen
ETL_DELETE = query([[UPDATE ::DEST
                        SET 
                                END_DATE=:BDAT
                        WHERE 
                                ::KEY IN (
                                        SELECT 
                                                DEST.::KEY 
                                        FROM 
                                                ::TEMP TEMP
                                        RIGHT JOIN 
                                                ::DEST DEST
                                        ON 
                                                DEST.::KEY=TEMP.::KEY
                                        WHERE 
                                                DEST.END_DATE='9999-12-31'
                                        AND 
                                                TEMP.::KEY IS NULL)
                                        AND 
                                                END_DATE='9999-12-31'
                                                ]],{BDAT=BDAT,DEST=DEST,KEY=KEY,TEMP=TEMP})

Durch den Auf­bau des Skrip­tes kann die­ses Skript für jede Tabelle genutzt wer­den, die eine fach­li­che Key Spalte besitzt. Um das Skript zu tes­ten, gene­rie­ren wir uns 2 Quell und 2 Ziel­ta­bel­len PRODUCT und CUSTOMER mit den jeweils dazu­ge­hö­ri­gen Quelltabellen.

--Erstellen von Zieltabellen
CREATE OR REPLACE TABLE CUSTOMER
        (ID                     INTEGER IDENTITY,
         START_DATE             DATE DEFAULT(CURRENT_DATE),
         END_DATE               DATE DEFAULT('9999-12-31'),
         CUSTOMER_NR            VARCHAR(255),
         FIRST_NAME             VARCHAR(255),
         LAST_NAME              VARCHAR(255),
         ADRESS                 VARCHAR(255),
         CITY                   VARCHAR(255));

CREATE OR REPLACE TABLE PRODUCT
        (ID                     INTEGER IDENTITY,
         START_DATE             DATE DEFAULT(CURRENT_DATE),
         END_DATE               DATE DEFAULT('9999-12-31'),
         PRODUCT_NR             VARCHAR(255),
         PRODUCT_NAME           VARCHAR(255),
         PRODUCT_CATEGORIE      VARCHAR(255),
         PRODUCT_PRICE          DECIMAL(10,2));

--Erstellen von Quelltabellen
CREATE OR REPLACE TABLE CUSTOMER_SOURCE
        (CUSTOMER_NR            VARCHAR(255),
         FIRST_NAME             VARCHAR(255),
         LAST_NAME              VARCHAR(255),
         ADRESS                 VARCHAR(255),
         CITY                   VARCHAR(255));
CREATE OR REPLACE TABLE PRODUCT_SOURCE
        (PRODUCT_NR             VARCHAR(255),
         PRODUCT_NAME           VARCHAR(255),
         PRODUCT_CATEGORIE      VARCHAR(255),
         PRODUCT_PRICE          DECIMAL(10,2));

Jede Quell­ta­belle wird mit initia­len Daten gefüllt. Anschlie­ßend wird das Skript für den Buchungs­tag ‚2023–02-23‘ ausgeführt.

--Insert von Startdatensätzen
DELETE FROM PRODUCT_SOURCE;
         
INSERT INTO PRODUCT_SOURCE
        (PRODUCT_NR, PRODUCT_NAME,PRODUCT_CATEGORIE,PRODUCT_PRICE)
VALUES
        ('1.01','Apple','Fruit',1.29),
        ('1.02','Pear','Fruit',0.69),
        ('2.01','Tomato','Vegetable',0.59);

DELETE FROM CUSTOMER_SOURCE;

INSERT INTO CUSTOMER_SOURCE
        (CUSTOMER_NR, FIRST_NAME, LAST_NAME, ADRESS, CITY)
VALUES
        ('1234','John','Doe','Main Street 1','New York'),
        ('1111','Jimmy','Garner','Golden Gate Ave 23','San Francisco'),
        ('5555','Brian','Marshall','Park Street 95','New York'),
        ('4321','Sally','Novak','Parkway Drive 5','Los Angeles');

Nach der Aus­füh­rung ste­hen die Werte aus der Quell­ta­belle in der Ziel­ta­belle. Die Daten­sätze haben als Start­da­tum den Buchungs­tag ‚2023–02-23‘ und sind alle gül­tig. Dies gilt für beide Zieltabellen.

Abfrage auf die Tabelle PRODUCT nach Initialbeladung
Abbil­dung 2: Initiale Daten­sätze in der Tabelle PRODUCT
Abfrage auf die Tabelle CUSTOMER nach Initialbeladung
Abbil­dung 3: Initiale Daten­sätze in der Tabelle CUSTOMER

Anschlie­ßend pas­sen wir ein­zelne Daten­sätze an und füh­ren das Skript erneut aus.

--Insert neue Datensätze
DELETE FROM PRODUCT_SOURCE;
         
INSERT INTO PRODUCT_SOURCE
        (PRODUCT_NR, PRODUCT_NAME,PRODUCT_CATEGORIE,PRODUCT_PRICE)
VALUES
        ('1.01','Apple','Fruit',1.29),
        --('1.02','Pear','Fruit',0.69),
        ('2.01','Tomato','Vegetable',0.79); --0.59

DELETE FROM CUSTOMER_SOURCE;

INSERT INTO CUSTOMER_SOURCE
        (CUSTOMER_NR, FIRST_NAME, LAST_NAME, ADRESS, CITY)
VALUES
        ('1234','John','Doe','Main Street 12','New York'),             --Main Street 1
        --('1111','Jimmy','Garner','Golden Gate Ave 23','San Francisco'),
        ('5555','Brian','Marshall','Townstreet 2','New jersey'),       --Park Street 95, New York
        ('4321','Sally','Novak','Parkway Drive 5','Los Angeles');

In der Tabelle CUSTOMER ist der Daten­satz mit der COSTUMER_NR=1111‘ auf ungül­tig gesetzt. Die Daten­sätze mit den Num­mern ‚5555‘ und ‚1234‘ haben jeweils eine Anpas­sung in der Adresse und erhal­ten somit das neue End­da­tum. In der Tabelle PRODUCT hat ‚Tomato‘ einen neuen Preis erhal­ten und ‚Pear‘ ist aus dem Sor­ti­ment ent­fernt. Für beide Tabel­len wurde jeweils das glei­che Skript verwendet

Abfrage auf die Tabelle PRODUCT nach Deltabeladung
Abbil­dung 4: Ange­passte Daten­sätze in der Tabelle PRODUCT
Abfrage auf die Tabelle CUSTOMER nach Deltabeladung
Abbil­dung 5: Ange­passte Daten­sätze in der Tabelle CUSTOMER

Fazit

Abschlie­ßend lässt sich sagen, dass Lua eine gute Mög­lich­keit für die ETL-Ver­ar­bei­tung ist. Klas­si­sche Daten­bank­t­rig­ger kön­nen so zwar nicht ersetzt wer­den, aller­dings gibt es die Mög­lich­keit per­for­mante ETL-Ver­ar­bei­tung zu schrei­ben, die zeit­gleich einen klas­si­schen INSERT/UPDATE/DELETE Trig­ger erset­zen. Durch das Lua Skript und die Logik reicht es aus ein Skript zu erstel­len, mit dem viele Tabel­len ver­ar­bei­tet wer­den können.