Die Exasol Datenbank kommt bei immer mehr Unternehmen zum Einsatz als Datenbank für ein Reporting. Viele Mechanismen der Exasol Datenbank sind hierbei darauf ausgelegt eine schnelle Datenverarbeitung zu ermöglichen und somit schnelle Berichte und Datenaufrufe zu ermöglichen. Als Beispiel können hier die automatisierten Indizes, sowie Partitionierung von Tabellen aufgezählt werden. Außerdem bietet die Exasol Datenbank durch das Anlegen von z.B. Lua oder Python Skripten eine flexible Möglichkeit direkt auf der Datenbank ETL- oder ELT-Prozesse zu schreiben. Auch die Import Funktion der Datenbank ist sehr performant und ermöglicht das Laden von großen Datenmengen in Sekunden. Trotz dieser Vorteile hat die Datenbank aber auch ein paar Nachteile, die in bestimmten Situationen den Anwender vor größere Probleme setzen könnte. Eines dieser Probleme ist das Fehlen von Datenbank Triggern.
Datenbank Trigger und In-memory Datenbanken
In-memory Datenbanken unterstützen generell keine Trigger Funktion, da die Trigger beim Aufruf der Tabelle mitgeladen werden und somit den in-memory Speicher blockieren. Gerade bei größeren Tabellen und mehreren Triggern auf den Tabellen kann dadurch viel Speicher unnötig blockiert werden. Um dieses Problem zu umgehen, muss man sich daher andere Möglichkeiten überlegen, mit denen man die Funktionalitäten von Triggern erhalten kann, ohne, dass die Performance darunter leidet.
Die Idealvorstellung in diesem Szenario ist dann eine Relationale Datenbank, in der die Aufbereitung passiert, wobei anschließend die relevanten Daten vollständig auf die In-memory Datenbank übertragen werden. Dies kann zu einer performanten Lösung führen, die allerdings auch gleichzeitig Probleme mit sich bringt. Die Tabellen müssten einzeln geladen werden, um zu verhindern, dass Berichte oder einzelne Elemente partiell nicht erreichbar sind. Man kann schlechter Real-Time Auswertungen machen, da diese nach der Auswertung noch migriert werden müssen und abschließend macht einem häufig die bereits vorhandene Architektur im Unternehmen individuelle Probleme.
Eine andere Möglichkeit ist es, die Beladung der Tabellen per ETL entsprechend anzupassen. In vielen Unternehmen werden ETL-Tools verwendet, um die Trigger dafür zu realisieren, allerdings gibt es auch eine Möglichkeit dies per Lua-Skript direkt auf der Datenbank umzusetzen.
Ein allgemeines ETL-Lua Skript
Wir betrachten im Folgenden ein fiktives Beispiel von unterschiedlichen Tabellen in einem unternehmen, die in regelmäßigen Abständen geladen werden. Die allgemeine Architektur sieht dabei vor, dass wir folgende Struktur der Tabellen haben:
Jede Tabelle in der Reporting Schicht hat demnach eine Quell-Tabelle und eine temporäre Tabelle. Die Beladung erfolgt demnach in 2 Schritten von der Quell- in die Ziel-Tabelle. Da es viele Tabellen gibt soll das Skript zur Beladung möglichst generisch geschrieben werden, damit dieses universell versendet werden kann. Das Skript wird daher über Input Parameter gesteuert. Das Skript erwartet folgende Input Parameter: „DEST:Zieltabelle“, „SOUR:Quelltabelle“, „KEY:Schlüsselfeld“, „BDAT:Buchungstag“. Die Parameter werden immer in zweier Pärchen übergeben, um eine eindeutige Zuordnung zu gewährleisten.
Aufbau des Skriptes
Im ersten Schritt werden die Parameter ausgelesen und lokalen Variablen zugeordnet. Dafür wird der lokalen Variable lv_Paramter zunächst die Bezeichnung der Variable übergeben, anschließend wird dieser Bezeichnung der Wert der Variable übergeben, z.B. lv_Paramerter[‚DEST‘]=‘PRODUCT‘. Anschließend liest das Skript aus dem Systemkatalog die Spaltennamen der Tabelle aus da diese für spätere Abgleiche 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 Einlesen der Parameter werden die Spalten der Tabelle aus dem Systemkatalog gelesen. Außerdem werden die Spalten direkt für das Merge Statement 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 temporäre Tabelle generiert und automatisiert beladen. Durch die Syntax in der Exasol Datenbank ist es möglich die Tabelle mit einem CREATE OR REPLACE direkt neu aufzubauen. Durch das AS SELECT * FROM SOURCE wird die Tabelle automatisch gefüllt. Die Temporäre Tabelle enthält neben den Daten auch den Buchungstag, der übergeben 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 Beladung enthält den Merge der Daten. Datensätze, die bisher noch nicht vorgekommen sind oder eine Anpassung in den Spalten erhalten haben, werden in die Zieltabelle gemerged mit dem entsprechenden 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ünften Schritt werden nun die Datensätze identifiziert, die nicht mehr aktuell sind und entsprechend ungültig 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 Deaktivieren der alten Datensätze werden die Datensätze identifiziert, zu denen es keine neuen Datensätze gibt. Diese werden ebenfalls ungültig 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 Aufbau des Skriptes kann dieses Skript für jede Tabelle genutzt werden, die eine fachliche Key Spalte besitzt. Um das Skript zu testen, generieren wir uns 2 Quell und 2 Zieltabellen PRODUCT und CUSTOMER mit den jeweils dazugehörigen 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 Quelltabelle wird mit initialen Daten gefüllt. Anschließend wird das Skript für den Buchungstag ‚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 Ausführung stehen die Werte aus der Quelltabelle in der Zieltabelle. Die Datensätze haben als Startdatum den Buchungstag ‚2023–02-23‘ und sind alle gültig. Dies gilt für beide Zieltabellen.
Anschließend passen wir einzelne Datensätze an und führen 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 Datensatz mit der COSTUMER_NR=‚1111‘ auf ungültig gesetzt. Die Datensätze mit den Nummern ‚5555‘ und ‚1234‘ haben jeweils eine Anpassung in der Adresse und erhalten somit das neue Enddatum. In der Tabelle PRODUCT hat ‚Tomato‘ einen neuen Preis erhalten und ‚Pear‘ ist aus dem Sortiment entfernt. Für beide Tabellen wurde jeweils das gleiche Skript verwendet
Fazit
Abschließend lässt sich sagen, dass Lua eine gute Möglichkeit für die ETL-Verarbeitung ist. Klassische Datenbanktrigger können so zwar nicht ersetzt werden, allerdings gibt es die Möglichkeit performante ETL-Verarbeitung zu schreiben, die zeitgleich einen klassischen INSERT/UPDATE/DELETE Trigger ersetzen. Durch das Lua Skript und die Logik reicht es aus ein Skript zu erstellen, mit dem viele Tabellen verarbeitet werden können.
