vorheriger Artikel
Was leistet Snowflake als Cloud-DB?
Snowflake ist eine native Cloud-DB und läuft auf AWS und inzwischen auch auf Azure. Die Internetverbindung vom Client zur Cloud und die Daten innerhalb der DB sind verschlüsselt. Dabei kann es während der Ausführung beliebig und automatisch hochskalieren und am Ende wieder herunterschalten. Da für das (Speicher-)Volumen und die Ausführungszeit gezahlt wird, können so durch die geringen Zeiten Kosten gespart werden. Eine ausführliche Online-Dokumentation ist unter folgender URL verfügbar: https://docs.snowflake.net/manuals/index.html
Man muss übrigens kein AWS-Kunde sein, um Snowflake verwenden zu können. Snowflake selbst bietet als Cloud-DB-Service keine eigenen ETL-Tools an, sondern überlässt dies den Herstellern von ETL-, Reporting- oder Self-Service-BI-Tools. Diese bieten meist native Treiber und Connections an, um ihre Tools mit Snowflake verwenden zu können. Soll im Unternehmen kein separates ETL-Tool eingesetzt werden, gibt es verschiedene Möglichkeiten, die Daten zu laden und die ETL-Strecken zu realisieren. Das Umsetzen der Logik in SQL und die Orchestrierung über Python ist dabei ein möglicher Weg.
Snowflake bietet Treiber für Python, Spark, ODBC und JDBC an
Gemäß eigener Umsetzung bietet Snowflake Treiber für Python, Spark, ODBC und JDBC an. Das bedeutet, dass ein sogenannter Python-Connector für Snowflake existiert, um vom eigenen Client aus die DB-Verbindung herstellen zu können.
In diesem Artikel wird genau dieser Python-Connector anhand von Beispiel-Snippets vorgestellt: Mit Hilfe des Python-Connectors (= DB-Verbindung) können ETL-Ladestrecken (manuell) realisiert werden. Scheduling kann beispielsweise per „Cronjob“ erfolgen, Quellen (csv, xml, Tabellen, xlsx etc.) können importiert und sogar weitere SQL-Skripte aufgerufen werden.
Vorgehensweise
In Python werden mit Hilfe von "pip" neue Packages installiert:
"pip install snowflake-connector-python"
In Python-Code wird folgender Modulimport möglich:
"import snowflake.connector"
Damit steht der Python-Connector zur Verfügung, und die Umsetzung kann starten. Im Folgenden ein erstes Python-Snippet:
(Test der Verbindung)
# *****************************************************************************
import snowflake.connector
conn = snowflake.connector.connect(host=<hostname>,
user=<DB-User>,
password=<DB-Password>,
account=<Snowflake-Account>)
cur = conn.cursor()try:
cur.execute("SELECT current_version()") # Snowflake-SQL
one_row = cur.fetchone()
print(one_row[0])finally:
cur.close()
# =======================================================
# Eine Variante davon ist der Zugriff per Column-Name
# statt Indexposition:
#
# (ein Beispiel)
# =======================================================
cur_dict = conn.cursor(snowflake.connector.DictCursor) # Cursor mit Parametertry:
cur_dict.execute("SELECT current_version() as c1, current_client() as c2, current_date() as c3")
for r in cur_dict:
print(f"{r['c1']} -- {r['c2']} -- {r['c3']}")finally:
cur_dict.close()
conn.close()
# ******************************************************************************Nach diesem ersten Python-Snippet wird im nächsten Schritt versucht, Stored Procedures innerhalb von Snowflake aufzurufen. Snowflake Stored Procedures werden in **JavaScript** (!) implementiert.
Da staunt der ETL-Architekt, Data Engineer, und der SQL-Entwickler ...
Man stellt sich spontan die Frage: JavaScript und DBMS? Wie soll das gehen? Innerhalb von Stored Procedures werden drei Snowflake-eigene Objekte zur Verfügung gestellt:
"snowflake", "Statement", "ResultSet"
Sie befähigen JavaScript innerhalb von Snowflake zu DDL + DML. Stored Procedures werden, unabhängig davon, ob in Snowflake oder in Python, per "Callprocname()" aufgerufen.
Hinweis am Rande:
Ein **Template** für JavaScript Stored Procedures mit SQL-Zugriff in Snowflake:
create or replace procedure my_proc1()
returns string
language javascript
as
$$
try {
var stmt1 = snowflake.createStatement( { sqlText: "select * from Tabelle" } );
var rs1 = stmt1.execute();
while (rs1.next()) {
-- read from ResultSet "rs1" via
--
-- column name : rs1.column_name1
-- or
-- indexpos. : rs1.getColumnValue(1)
...
...
}
return "SUCCESS";
catch (err) {
return "FAILED (Exception): " + err;
}
$$
;
Es wird aufgezeigt, wie man in Python diese Stored Procedure "my_proc1()" von oben aufrufen kann. Hierbei werden weitere Snowflake-spezifische SQLs für die Umgebung gebraucht; diese sind "mandatory" und werden hier exemplarisch aufgezeigt und nicht näher behandelt, da es den Rahmen dieses Artikels sprengen würde:
...
cur.execute("use warehouse warehousename")
cur.execute("alter warehouse warehousename resume")
cur.execute("alter session set timestamp_type_mapping = 'TIMESTAMP_NTZ'")
cur.execute("alter session set timezone = 'Europe/Berlin'")
cur.execute("alter session set TIME_OUTPUT_FORMAT = 'HH24:MI:SS.FF'")
cur.execute("use database dbname")
cur.execute("use schema schemaname")
# *********************************************
# Aufruf der Stored Procedure:
# =============================
cur.execute("call my_proc1()")
# *********************************************
cur.execute("alter warehouse warehousename suspend")
...Wie steht es nun um die Performance?
Welche Ausführungszeiten werden mit verschiedenen Varianten in Python erreicht?
Die Varianten in Python sind die folgenden (Werte aus einem „POC“-Projekt ermittelt):
(1) Aus Python heraus werden Stored Procedures in Snowflake aufgerufen.
(2) Die Stored Procedures werden in Python re-implementiert bzw. nachgebaut und in Python ausgeführt.
(3) Keine Stored Procedures, sondern:
aus Python heraus werden SQL-Statements (= SELECT) direkt in Snowflake ausgeführt,
ohne den Umweg über Stored Procedures.
Hierbei gab es mehrere Durchläufe bzw. Testaufrufe:
zu (1): python calling javascript stored proc.:
Ausführungszeiten (sec):
(1) 8.61, (2) 7.28, (3) 6.00, (4) 6.70, (5) 8.64,
(6) 14.49, (7) 7.27, (8) 7.77, (9) 8.85, (10) 8.55
zu (2): python re-implementing stored proc.:
Ausführungszeiten (sec):
(1) 6.83, (2) 4.76, (3) 6.57, (4) 5.27, (5) 5.19,
(6) 4.81, (7) 5.58, (8) 5.38, (9) 12.54, (10) 5.83
zu (3): python sql processing:
* select from (arbitrary) Snowflake table
* insert into another Snowflake table
==> inkl. „TCL“ (= begin, rollback, commit)
Ausführungszeiten (sec):
(1) 4.42, (2) 6.52, (3) 35.08, (4) 3.91, (5) 3.69,
(6) 4.95, (7) 4.52, (8) 18.99, (9) 4.94, (10) 3.89
Man kann sehen, dass es „Ausreißer“ gibt, die auch nicht durch Snowflake erklärt werden können.
Hinweis:
Eine weitere Variante ist der Aufruf von SQL-Dateien bzw. Skripten, die mehrere SQL-Statements beinhalten und mit Hilfe von "execute_stream()" Statement für Statement ausgeführt werden können, gemäß folgendem Python-Snippet. Hierbei fehlen allerdings Messwerte für die Ausführungszeiten:
try:
# --------------------------------------------------------
# EXECUTE SQL-File with Snowflake-SQL-Statements:
# --------------------------------------------------------
ret_values_list = [] # Init.: list for return values of sql execution
with open("sqlskript_dateiname", 'r', encoding='utf-8') as f:
for c in conn.execute_stream(f): # executes all SQL-Statements LINE BY LINE!!
for ret in c:
# save return values,
# from each SQL-Statement:
ret_values_list.append(ret)
...
...
except snowflake.connector.errors.ProgrammingError as e:
# default error message
print(e)
...Fazit
Es gibt einen nativen Python-Connector von Snowflake, mit dem manuelle ETL-Prozesse auf eine bequeme Art und Weise möglich sind. Python stellt trotzdem eine Option unter vielen dar, grafische ETL-Tools bieten mittlerweile ebenfalls native Konnektoren an.
Je nach (eigenem) Wunsch und Rahmenbedingungen sind Use Cases auch unter Python implementierbar. Wenn Python bereits „im eigenen Hause“ eingesetzt wird, sind Importvorgänge schnell eingebaut. Man kann modular implementieren und Packages aufbauen. Diese Möglichkeit bietet Snowflake mit den Stored Procedures nicht, es sind keine Frameworks in JavaScript möglich. Man hat am Ende eine Menge von Procedures, die nebeneinander existieren, und keine Modularität.
Da man sich in der Cloud befindet, gibt es für ein Logging kein Filesystem und man muss auf Tabellen ausweichen. JavaScript in Snowflake stellt für SQL-Entwickler eine neue Umgebung bzw. eine neue „Low-Level“-Sprache dar, d. h., es gibt eine Lernkurve, die man nicht unterschätzen darf. Das kann man mit Python „umgehen“ und in Python „bleiben“, statt Stored Procedures in JavaScript zu verwenden.
Die Ausführungszeiten für Python sind akzeptabel, weil es den nativen „Snowflake-Connector“ für Python gibt. Dieser tunt das SQL bereits intern auf dem Weg zu Snowflake – das ist beim JDBC-Treiber nicht der Fall.
Meine Empfehlung
ETL mit Python im Kontext Snowflake wird empfohlen, wenn bereits Python-Kenntnisse „im eigenen Hause“ existieren. Es können damit auch schnell Testszenarien umgesetzt werden.
