Exasol und Python: Zwei gute Freunde
Entwicklung mit Python für die Exasol-DB
Folgende Umgebung wird verwendet:
- aktuelle Python-Version
- Exasol-DB
- beliebiger Editor
Es werden zwei Aspekte in (1) und (2) durchleuchtet:
(1) "Stored Procedures" innerhalb Exasol
(1.1) Python-"UDF" (=SQL-Function), mit einem Beispiel
(1.2) "Scripts" (=Stored-Procedure), mit einem Beispiel
(2) Exasol-Connection innerhalb Python (außerhalb der Exasol-DB)
(3) Fazit
(1) Python innerhalb der Exasol-DB
Es ist möglich, sogenannte UDF (=User-Defined-Functions) zu erstellen, die SQL-Funktionen darstellen, welche beim SQL-SELECT aufgerufen werden. Ferner gibt es neben UDF auch das Konzept von Skripten innerhalb der Exasol-DB, welche wie Prozedures funktionieren, um bspw. DDLs ausführen zu können.
Beide Themen werden im Folgenden näher beschrieben.
(1.1) Python-"UDF" (=SQL-Functions)
Exasol unterstützt mehrere Programmiersprachen für UDF-Entwicklung: Lua, R, Java und Python.
Bei UDF wird ein Wert oder ein Datensatz als Input übergeben und als Ergebnis ein einzelner Wert oder ein Tupel zurückgegeben.
Die Syntax ("Template") für ein Python-Script innerhalb der Exasol-DB lautet:
CREATE OR REPLACE PYTHON SCALAR SCRIPT <my_schema>.<my_udf>(param_1 <dtype>, param_2 <dtype>, ... , param_n <dtype>)
EMITS (output_column_1 <dtype>, output_column_2 <dtype>, ... , output_column_n <dtype>)
AS
import pandas as pd # beliebige Imports von Python-Modulen möglich
# der Name "run" ist vorgegeben und
# kann nicht verändert werden:
def run(ctx):
arg1 = ctx.param_1
arg2 = ctx.param_2
...
argn = ctx.param_n
df = <init and apply a pandas-dataframe>
for index, row in df.iterrows():
ctx.emit(row[0], row[1], ..., row[n]) # Tupel zurückgeben
Hinweis zu "ctx" bei "def run(ctx)":
dieses interne Objekt "ctx" wird systemseitig automatisch zur Verfügung gestellt, kann beliebig benannt werden und stellt die Verbindung zwischen der Exasol-Umgebung und Python-UDF her. Der Name der Funktion (="run") ist systemseitig vorgegeben und kann nicht geändert werden. Nach dem Aufruf von Python wird die run-Funktion automatisch ausgeführt.
Mögliche Rückgabewerte (am Ende der UDF) sind ein einzelner Wert per "RETURN" oder auch ein Tupel per "emit". Ist der Rückgabewert ein Tupel und ist die Tupellänge beliebig, kann eine Python-Liste erstellt und per "ctx.emit(*my_return_list)" zurückgegeben werden.
In diesem Fall darf die Klammer in "EMITS" auch nur aus drei Punkten bestehen, um eine beliebige Tupellänge als Rückgabewert festzulegen: "EMITS (...)" Die drei Punkte sind auch bei den Inputparametern erlaubt.
Hinweis zum "*" in "ctx.emit(*my_return_list)":
Der "*" in der Klammer löst die Liste in die einzelnen Elemente auf, als hätte man diese in der Klammer einzeln genannt.
Auf der SQL-Ebene erfolgt der Aufruf der Python-UDF in folgender Form:
SELECT <my_schema>.<my_udf>( ... ) EMITS(col1 <dtype>, col2 <dtype>, ... , coln <dtype>)
FROM tabelle_xy
Als Beispiel für eine Python-UDF wird folgender Use-Case kreiert:
Es gibt eine hierarchische Tabelle mit einer Spalte für parent und einer weiteren Spalte für child, d.h. einen Knoten mit dessen Unter-Knoten. Diese hierarchische parent-child-Beziehung soll durch eine Transformation mittels Pivotierung in Spalten nebeneinander dargestellt werden. Es sollen max. 7 Ebenen nebeneinander dargestellt werden. Wenn es weniger als 7 parent-child-Ebenen geben sollte, wird das letzte Element bis zur letzten siebten Ebene aufgefüllt (d.h. bis zum Blattelement kopiert).
Diese Aufgabe wird mit einer Python-UDF gelöst. Vorab werden die Beispieldaten in einer View aufgebaut:
create or replace view myschema.v_hierarchy_example as select 'ROOT' as parent_col, 'aaa' as child_col union select 'aaa' as parent_col, 'b' as child_col union select 'aaa' as parent_col, 'c' as child_col union select 'b' as parent_col, 'd' as child_col union select 'b' as parent_col, 'e' as child_col union select 'b' as parent_col, 'f' as child_col union select 'c' as parent_col, 'z' as child_col union select 'z' as parent_col, 'xy' as child_col ;
Die Beispieldaten der parent-child-View haben folgenden Aufbau:
PARENT_COL | CHILD_COL |
ROOT | aaa |
aaa | b |
aaa | c |
b | d |
b | e |
b | f |
c | z |
z | xy |
Eine weitere "Helperview" liefert den Pathstring zur Hierarchie, dieser wird in der Python-UDF gebraucht:
create or replace view myschema.v_read_hierarchy as select SYS_CONNECT_BY_PATH(child_col, '/') as PATH_STR, parent_col, child_col from myschema.v_hierarchy_example connect by parent_col = prior child_col start with parent_col = 'ROOT' ;
Der "Pathstr" in der "Helperview" wird für die Python-UDF bereitgestellt:
PATHSTR | PARENT_COL | CHILD_COL |
/aaa | ROOT | aaa |
/aaa/b | aaa | b |
/aaa/c | aaa | c |
/aaa/b/d | b | d |
/aaa/b/e | b | e |
/aaa/b/f | b | f |
/aaa/c/z | c | z |
/aaa/c/z/xy | z | xy |
Als gewünschtes Ergebnis soll folgender Aufbau abgebildet werden (gem. der Vorgabe bzw. Zusatzregel, dass bis zur letzten Ebene mit dem letzten Element (Knoten) aufgefüllt wird, dadurch wird sichergestellt, dass im "Blatt" immer ein Wert vorkommt, so dass Folgeprozesse sich darauf verlassen und zugreifen können):
LEVEL_1 | LEVEL_2 | LEVEL_3 | LEVEL_4 | LEVEL_5 | LEVEL_6 | LEVEL_7 |
aaa | aaa | aaa | aaa | aaa | aaa | aaa |
aaa | b | b | b | b | b | b |
aaa | c | c | c | c | c | c |
aaa | b | d | d | d | d | d |
aaa | b | e | e | e | e | e |
aaa | b | f | f | f | f | f |
aaa | c | z | z | z | z | z |
aaa | c | z | xy | xy | xy | xy |
Die folgende Python-UDF verteilt den Pathstring auf die einzelnen Spalten:
CREATE OR REPLACE PYTHON SCALAR SCRIPT MYSCHEMA.PIVOT_HIERARCHY_TO_COLUMNS (P_PATH_STR VARCHAR(500),
P_ANZ_EBENE INTEGER,
P_SPLIT_CHAR CHAR(1))
EMITS (...)
AS
def run(ctx):
path_str = ctx.P_PATH_STR
if ctx.P_PATH_STR[0] == "/":
path_str = ctx.P_PATH_STR[1:]
liste = path_str.split(ctx.P_SPLIT_CHAR)
liste_len = len(liste)
liste_last_element = liste[-1]
if liste_len < ctx.P_ANZ_EBENE:
# vervielfachen des letzten Elements:
liste_multiply_last_element = [liste_last_element] * (ctx.P_ANZ_EBENE - liste_len)
# Liste erweitern mit dem letzten Element:
liste = liste + liste_multiply_last_element
else:
# Listenobjekt beschraenken auf max. Elemente,
# falls mehr Elemente uebergeben wurden:
liste = liste[0:ctx.P_ANZ_EBENE]
ctx.emit(*liste)
;
Diese Python-UDF wird per SQL aufgerufen:
select myschema.pivot_hierarchy_to_columns(PATH_STR, 7, '/') emits (LEVEL_1 VARCHAR(200), LEVEL_2 VARCHAR(200), LEVEL_3 VARCHAR(200), LEVEL_4 VARCHAR(200), LEVEL_5 VARCHAR(200), LEVEL_6 VARCHAR(200), LEVEL_7 VARCHAR(200)) from myschema.v_read_hierarchy ;
.. dieses SQL-SELECT führt zum gewünschten Ergebnis, u.a. gem. der Vorgabe im Use-Case, dass bis zum Blattelement (Ebene 7) aufgefüllt wird, falls die Zwischenebenen fehlen:
LEVEL_1 | LEVEL_2 | LEVEL_3 | LEVEL_4 | LEVEL_5 | LEVEL_6 | LEVEL_7 |
aaa | aaa | aaa | aaa | aaa | aaa | aaa |
aaa | b | b | b | b | b | b |
aaa | c | c | c | c | c | c |
aaa | b | d | d | d | d | d |
aaa | b | e | e | e | e | e |
aaa | b | f | f | f | f | f |
aaa | c | z | z | z | z | z |
aaa | c | z | xy | xy | xy | xy |
(1.2) "Scripts" (=SQL-Procedures)
Neben einer UDF existiert das Konzept von Skripten in der Exasol-DB. Im Vergleich zur UDF können Skripte nur in der Sprache LUA implementiert werden.
Der Hauptunterschied zu UDFs liegt darin, dass in LUA-Skripten auch DDLs ausgeführt werden können. D.h. UDFs bewegen sich ausschließlich auf "Datenebene" innerhalb SQL-SELECTs und können nicht die "Infrastruktur" ändern.
Die Definition sieht folgendermaßen aus:
"CREATE OR REPLACE SCRIPT <schema_name>.<skript_name>() AS <LUA-Quellcode>..."
Auch hier wird ein Beispiel präsentiert, gem. folgendem Use-Case:
Es gibt eine View mit KPIs, d.h. eine Spalte enthält den Namen der KPI, eine weitere Spalte ein Datum und eine dritte Spalte den entsprechenden KPI-Wert. Diese KPIs sollen ebenfalls pivotiert werden und in einer neuen View abgebildet werden. In dieser sollen die KPIs nebeneinander als neue Felder, gruppiert nach Datum, dargestellt werden. Als Namen der neuen Spalten sollen die KPI verwendet werden. Hierbei kann nicht statisch von einer vorgegebenen Anzahl an KPIs ausgegangen werden. Die Spaltennamen für die neue View müssen dynamisch hergeleitet werden. Es entstehen damit genau so viele Spalten in der (neuen) pivotierten View, wie es KPIs in der Quell-View gibt.
Zunächst die KPI-Daten:
create or replace view myschema.v_kpi_view01 as select 'KENNZAHL_1' as kpi_name, sysdate as datum, 30 as kpi_wert union select 'KENNZAHL_2' as kpi_name, sysdate as datum, 55 as kpi_wert union select 'KENNZAHL_1' as kpi_name, sysdate+15 as datum, 10 as kpi_wert union select 'KENNZAHL_3' as kpi_name, sysdate+75 as datum, -99 as kpi_wert ;
Folgende Test-KPI's stehen damit zur Verfügung:
KPI_NAME | DATUM | KPI_WERT |
KENNZAHL_1 | 2019-10-18 | 30 |
KENNZAHL_2 | 2019-10-18 | 55 |
KENNZAHL_1 | 2019-11-02 | 10 |
KENNZAHL_3 | 2020-01-01 | -99 |
Gem. dem Use-Case soll folgendes Ergebnis entstehen, indem die KPIs nebeneinander, nach Datum gruppiert, stehen:
DATUM | KENNZAHL_1 | KENNZAHL_2 | KENNZAHL_3 |
2019-10-18 | 30 | 55 | NULL |
2019-11-02 | 10 | NULL | NULL |
2020-01-01 | NULL | NULL | -99 |
Zum Stichtag 18.10.2019 gibt es zwei KPI-Werte, diese werden nebeneinander dargestellt, zum 02.11.2019 gibt es eine KPI und zum 01.01.2020 eine weitere KPI. Die Spaltennamen entsprechen den Namen der KPIs.
Kommt die gleiche KPI für den gleichen (Stich-)Tag mehrfach vor, soll aufsummiert werden.
Um diese neue pivotierte View in der Exasol-DB zu generieren, wird ein Skript in LUA implementiert.
Das LUA-Script zu unserem Use-Case:
create or replace script myschema.lua_example() as
success, res = pquery([[select distinct kpi_name
from myschema.V_KPI_VIEW01]])
if not success then
output("### ERROR: "..res.error_message..", Caught while executing: "..res.statement_text)
return
end
output("Variable 'view_str' : ")
view_str = "CREATE OR REPLACE VIEW myschema.V_MERGE_ALL AS SELECT DATUM, \n"
for i=1,#res do
view_str = view_str.."SUM(CASE WHEN KPI_NAME='"..res[i].KPI_NAME.."' THEN KPI_WERT ELSE NULL END) AS "..res[i].KPI_NAME
if i<#res then
view_str = view_str..", \n"
end
output(view_str)
end
view_str = view_str.." \n"..
"FROM myschema.V_KPI_VIEW01 \n"..
"GROUP BY DATUM "
output("**********************************************************************************************")
output("FINAL =")
output(view_str)
query(view_str)
;
Mit "output" können Debuginfos ausgegeben werden, die Darstellung in Exasol erfolgt zeilenweise in Tabellenform. In der Variablen "res" wird die Ergebnismenge der SQL-Abfrage gespeichert. Mit dem Audruck "#res" in der for-Schleife wird auf die Anzahl der Zeilen zugegriffen.
Falls es im SQL zu einem Fehler kommt, verhalten sich die Statements "query()" und "pquery()" (=protected-query) unterschiedlich. "pquery()" gibt einen Tupel als Ergebnis zurück, wie in der Zeile "success, res = pquery([[select distinct kpi_name from myschema.V_KPI_VIEW01]])". Die Variable "success" enthält die Werte true oder false. Der Wert "true" bedeutet, es gab keinen Fehler und die Variable "res" enthält die Ergebnismenge des SQL-SELECTs. Der Wert "false" bedeutet, es gab einen Fehler im SQL und die Variable "res" enthält die Fehlermeldung.
Die Vorgehensweise im Skript:
Die Beispielview wird durchlaufen, dabei der KPI-Name gelesen und als neuer Spaltenname eingesetzt. Es wird ein String aufgebaut, der das DDL für die neue zu pivotierende View beinhaltet und am Ende per "query"-Statement ausführt.
execute script myschema.lua_example() with output ;
Der Zusatz "with output" ist optional und führt dazu, dass die "Output"-Meldungen angezeigt werden.
Es wird eine neue View mit dem Namen "V_MERGE_ALL" erstellt und in der Exasol-DB angelegt.
Diese neue View erfüllt den geforderten Aufbau mit den nebeneinander platzierten KPI's:
DATUM | KENNZAHL_1 | KENNZAHL_2 | KENNZAHL_3 |
2019-10-18 | 30 | 55 | NULL |
2019-11-02 | 10 | NULL | NULL |
2020-01-01 | NULL | NULL | -99 |
Eine Gegenüberstellung der beiden Konzepte: "UDF's vs. scripts"
Skripte | UDFs |
Umsetzen von ETL-Prozessen | nur auf Daten (=row, column) anwendbar |
Umsetzung nur mit Lua | Umsetzung möglich mit SQL, Lua, Java, Python, R |
Aufruf mit 'EXECUTE SCRIPT ...' |
Aufruf innerhalb SQL-SELECT, d.h. 'SELECT udf(col1, col2) FROM tabelle' |
Unterstützung für DDLs und SQL error handling |
Laden von weiteren Java / Python / R packages |
läuft nur auf einer Node im Cluster (single threaded) |
parallele Ausführung innerhalb des Clusters |
(2) Exasol-Connection innerhalb Python
Für eine Verbindung aus der Programmiersprache Python heraus in die Exasol-DB gibt es das Python-Modul "pyexasol". Es ist nicht in der Python-Standardinstallation vorhanden und muss im Nachhinein separat installiert werden, dies erfolgt auf der Kommandozeile mit folgendem Befehl: pip install pyexasol
Der erste Test mit "pyexasol":
import pyexasol
conn = pyexasol.connect(dsn='<host:port>',
user='<username>',
password='<pwd>')
# als Bsp.: SQL zum Lesen im Exasol-Katalog
stmt = conn.execute("SELECT * FROM EXA_ALL_USERS")
for row in stmt:
print(row)
conn.close()
Ist die Verbindung nach Exasol erfolgreich gesetzt, kann mit dem sogenannten Python-Modul "pandas" der nächste Schritt erfolgen, wenn man Analysen durchführen möchte. Es wird bei der Datenanalyse im Data Science-Umfeld eingesetzt. Das zentrale Objekt in "pandas" ist der Dataframe. Das Modul pyexasol beinhaltet die Methode "export_to_pandas()", um aus einer SQL-Abfrage ein Dataframe-Objekt aufzubauen.
Das Beispiel von oben, erweitert um die "pandas"-Dataframe, sieht folgendermaßen aus:
import pyexasol
import pandas as pd
conn = pyexasol.connect(dsn='<host:port>',
user='<username>',
password='<pwd>',
schema='<schemaname>',
compression=True)
# pandas dataframe:
df = pd.DataFrame
# SQL zum Lesen im Exasol-Katalog
df = conn.export_to_pandas("SELECT * FROM EXA_ALL_USERS")
# Mit Hilfe der pandas-dataframe sind aufwendige Daten-Analysen im Pythoncode möglich.
# Die ersten 20 Zeilen der dataframe ausgeben
print(df.head(20))
# pandas dataframe zurück nach Exasol persistieren:
conn.import_from_pandas(df, '<TARGET_TABLENAME>')
conn.close()
Ein Nachteil ist das "fest-verdrahtete" Passwort im Quellcode. Abhilfe schafft hier eine ini-Datei, mit dem Dateinamen "pyexasol.ini" (der Dateiname ist vorgegeben), in der die Parameter der connect-Methode aufgelistet werden:
[abschnitt_connect_parameter]
dsn = <host>
user = <username>
password = <pwd>
schema = <schemaname>
compression = True
Erneut mit dem Beispiel von oben ausprobiert:
import pyexasol conn = pyexasol.connect_local_config('abschnitt_connect_parameter') # als Bsp.: SQL zum Lesen im Exasol-Katalog stmt = conn.execute("SELECT * FROM EXA_ALL_USERS") for row in stmt: print(row) conn.close()
(3) Fazit
zu (1):
Python-Funktionen können über UDFs schnell in Exasol implementiert werden.
Für DDLs/ETLs werden hingegen LUA-Skripte benötigt.
Python eröffnet viele Möglichkeiten für die Exasol-DB,
die ansonsten mit SQL-Mitteln nicht umsetzbar sind,
Beispiele für Use-Cases mit Python-UDFs sind:
- Datei von einer URL laden und den Inhalt zeilenweise zurückgeben
- Parsen von JSON-Daten, d.h. den Aufbau zergliedern und den Inhalt zeilenweise zurückgeben
- xml-Verarbeitung
- Laden einer Datei von einem FTP-Server und die Zeilen zurückgeben
- Testen des Clusters, Erreichbarkeit von Nodes (Bsp.: https://www.exasol.com/support/browse/SOL-325)
- Web scraping (Bsp.: https://beatthebookie.blog/2019/01/20/exasol-python-udf-web-scraper-for-bundesliga-match-day-fixtures/)
- ...
zu (2):
Mit Hilfe des Python-Moduls "pyexasol" ist der Zugriff auf eine Exasol-DB sehr leicht möglich. Im Zusammenspiel mit dem Python-Modul "pandas" sind hochwertige Analysen (Data Science) der Daten möglich. Ferner erschließen sich weitere Möglichkeiten für Charts bzw. Graphiken aller Art mit dem Python-Modul "Matplotlib". Eine GUI, um Exasol-Tabellen oder -Views darzustellen, ist mit dem Modul "PyQt5" möglich.