02.12.2019 | von Yildirim Icen

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.