<img height="1" width="1" style="display:none;" alt="" src="https://dc.ads.linkedin.com/collect/?pid=412841&amp;fmt=gif">
Erfahren Sie welche Möglichkeiten die neuen Stored Procedures in Snowflake bieten

Erfahren Sie welche Möglichkeiten die neuen Stored Procedures in Snowflake bieten

13.05.2019  •  ETL, Snowflake

Jon Nedelmann

Seit Kurzem gibt es in snowflake die Möglichkeit, Stored Procedures zu erstellen und natürlich auch auszuführen.

Analog zu den UDFs (User Defined Functions), die dem Entwickler schon seit längerer Zeit zur Verfügung stehen, wird als Basis-Programmiersprache JavaScript verwendet. Das mag dem einen oder anderen, der zum Beispiel die prozeduralen Erweiterungen von Oracle (PL/SQL) oder von Microsofts SQL Server (Transact-SQL) kennt, verwundern: Ist doch JavaScript eine Sprache, die eher von der Webanwendungsentwicklung bekannt ist, und bisher kaum mit nativer Datenbankprogrammierung in Verbindung gebracht wurde.

In diesem Blog möchte ich an einigen einfachen Beispielprozeduren zeigen, wie klassische prozedurale Themen in snowflake behandelt werden können, an anderen Beispielen aber auch, welche weiteren Möglichkeiten mit JavaScript zur Verfügung stehen.

Zur Vorbereitung lege ich zunächst zwei Tabellen an, die in einer Spalte ganze positive Zahlen und in einer zweiten Spalte das Pendant in römischer Schrift speichern:

CREATE TABLE NUMBERS(ARABIC_NUMBER INT, ROMAN_NUMBER VARCHAR);
CREATE TABLE NUMBERS_OF_DAY(ARABIC_NUMBER INT, ROMAN_NUMBER VARCHAR);

I. INSERTS in einer FOR-Schleife

Das erste Beispiel ist eine Prozedur, welche 2.995 Zeilen in der Tabelle NUMBERS erzeugt und in das Feld ARABIC_NUMBER jeweils die Zeilen von 1 bis 2.995 schreibt:

 

Eine Prozedur wird also in einem Rahmen

 

 

erzeugt. Die Zeile RETURNS VARCHAR… ist Teil der snowflake-Syntax für die Prozedur und sollte nicht so gelesen werden, dass diese Prozedur ein Ergebnis vom Typ VARCHAR zurückgibt (Prozeduren können aber tatsächlich Werte zurückgeben, die snowflake-Dokumentation gibt mehrere Beispiele).

 Die Prozedur kann durch ein einfaches CALL-Statement aufgerufen werden:

 call fill_numbers();

Der Block $$ … $$; umfasst den JavaScript-Code. In diesem Block wird 2.995 mal ein INSERT-Statement erzeugt, das dann auch innerhalb der Schleife ausgeführt wird. Das funktioniert, ist aber sehr, sehr langsam. Mit einem XS Warehouse lief die Prozedur 21 Minuten und 38 Sekunden. Das hat folgenden Grund: Der Aufruf

snowflake.execute ({sqlText: sql_command});

 führt das angegebene Statement in einer eigenen gekapselten Transaktion aus (deshalb wird auch kein COMMIT benötigt). Das sequentielle Abarbeiten von diesen vielen Transaktionen braucht halt seine Zeit.

II. Bulk-Load Statement in einer FOR-Schleife erzeugen

 Bevor nun aber das Warehouse nach oben skaliert wird, um die Performance zu steigern, gibt es einen anderen Weg. Die folgende Prozedur kommt zu demselben Ergebnis, aber auf eine andere Weise: in der FOR-Schleife wird ein INSERT-Statement für den BULK-Load zusammengestellt und im Anschluss ausgeführt. Die Prozedur benötigt in diesem Fall weniger als 2 Sekunden.

 

III. JSON-Objekte als Parameter

Da JSON-Objekte durch den Datentyp VARIANT Bürger erster Klasse sind, kann auch eine Liste übergeben werden, und aus dieser direkt das Statement ergänzt werden. Eine Variante der vorherigen Prozedur sieht dann so aus:

und kann so aufgerufen werden:

call fill_numbers_3(PARSE_JSON('[2996, 2997, 2999, 3000]'));

 Der Parameter war übrigens in der in JavaScript üblichen camel case Notation als moreNumbers deklariert. Innerhalb der Prozedur muss die Variable komplett in Großbuchstaben als MORENUMBERS geschrieben werden; more_numbers wäre also der bessere Parameternamen gewesen.

IV. Daten mit einem Cursor auslesen und zeilenweise verarbeiten

 Eine typische Verarbeitung in klassischen prozeduralen Erweiterungen von SQL ist es, einen Cursor zu definieren und die mit diesem Cursor erhaltenen Daten zeilenweise zu verarbeiten. Eine Prozedur könnte dann ungefähr so aussehen:

  

Auch das ist im Prinzip in snowflake möglich, wird aber ein wenig anders gehandhabt. Zunächst schreibe ich eine Prozedur, welche die Zahlen, die wir gerade ergänzt haben, wieder ausliest:

 

Das Ergebnis der Abfrage wird hier in die Variable result geschrieben, über result.next() kann jeweils der nächste Datensatz gelesen und über result.getColumnValue(n) der Wert der n-ten Spalte ausgelesen werden – die klassische Cursor-Verarbeitung. Nun soll zu jeder Zahl die römische Zahldarstellung ermittelt werden. Wie kann nun an welchen Stellen der passende JavaScript-Code ergänzt werden? Die snowflake-Dokumentation gibt dazu folgende Auskunft:

The JavaScript code must define a single literal JavaScript object for the stored procedure to be valid.

Der Prozedurcode kann wie ein einziges Objekt betrachtet werden. Das bedeutet unter anderem, dass nach Belieben Funktionalitäten in separaten Funktionen ergänzt werden können. Zur Berechnung der römischen Ziffern ergänze ich also zwei Hilfsfunktionen times und parts und die Funktion to_roman, welche dann die eigentliche Umrechnung durchführt (zumindest bis 3.000). In der Schleife ergänze ich dann einfach einen Aufruf an to_roman und erstelle mit dem Ergebnis ein UPDATE-Statement. Der fertige Code sieht dann folgendermaßen aus:

 

Ein Blick in die Tabelle NUMBERS zeigt, dass wir das gewünschte Ergebnis erhalten haben. Die Performance dieser Prozedur ist aber wieder alles andere als akzeptabel gewesen: alle 3.000 UPDATE-Statements werden separat durchgeführt, das braucht seine Zeit. Hier sehen wir die aktuellen Begrenzungen der CURSOR-Verarbeitung in snowflake: Innerhalb der Schleife sollten keine DML-Statements ausgeführt werden. In unserem Fall könnten wird die Prozedur schnell umschreiben, dass arabische und römische Ziffern in einem einzigen BULK-INSERT geladen werden.

 

V. Dynamisches SQL ausführen

 Eine weitere typische Anwendung von Datenbank-Prozeduren besteht darin, dynamisch eine SQL-Anweisung zusammenzustellen und sie dann innerhalb der Prozedur auszuführen. Dies haben wir in allen Beispielen bereits gemacht, wenn wir ein Statement sql_command erstellt haben und dann das snowflake-Objekt mit der execute-Methode aufgerufen haben:

snowflake.execute ({sqlText: sql_command});

 Dieser Befehl hat noch weitere Optionen. Wir können in dem SQL-Befehl für noch nicht bekannte Parameter ein Fragezeichen setzen und dann bei der Befehlsausführung diesen Parameter binden. Die folgende Prozedur zeigt das Vorgehen. Sie kopiert montags alle Zahlen von der Tabelle NUMBERS in die Tabelle NUMBERS_OF_DAY, dienstags alle durch 2 teilbaren Zahlen, mittwochs alle durch 3…

 

VI. Fazit

Mit der Einführung der stored procedures hat snowflake eine große Lücke geschlossen. Für Entwickler, die aus der Oracle oder SQL Server-Ecke kommen, ist ein wenig Umdenken gefragt, um sich auf JavaScript als Programmiersprache einzulassen. Es lohnt sich aber, denn mit wenigen Zeilen können dann elegante Prozeduren erstellt werden. Hilfreich bei diesem Umstieg ist die wirklich gute snowflake-Dokumentation zu diesem Thema.

Schwachstelle ist bisher noch, dass jeder Aufruf eines DML-Statements in der Prozedur - sowie jeder Prozeduraufruf selbst – als eine Transaktion behandelt wird, und dann keine gute Performance zu erwarten ist. Die „klassische Cursor-Verarbeitung“ sollte dann anders gestaltet werden. Aber noch sind Prozeduren ja ein ganz neues Thema für snowflake, und es wird sich sicherlich in der nächsten Zeit weiterentwickeln.