Stored Procedures in snowflake
Jon Nedelmann, Mai 2019
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);
1. 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:
CREATE OR REPLACE PROCEDURE fill_numbers()
RETURNS VARCHAR LANGUAGE JAVASCRIPT
AS
$$
for(var i = 1; i 3000 || n < 1) return "NN";
else {
var result = "";
if(n > 999) {
result = times(Math.floor(n / 1000), 'M');
n = n % 1000;
}
if(n > 99) {
result = result + parts(Math.floor(n / 100), 'C', 'D', 'M');
n = n % 100;
}
if(n > 9) {
result = result + parts(Math.floor(n / 10), 'X', 'L', 'C');
n = n % 10;
}
result = result + parts(n, 'I', 'V', 'X');
}
return result;
}
var selectArabic = "SELECT ARABIC_NUMBER FROM NUMBERS";
var result = snowflake.execute({sqlText: selectArabic});
while(result.next()) {
var arabicNumber = result.getColumnValue(1);
var updateStatement = "UPDATE NUMBERS SET ROMAN_NUMBER = '" + toRoman(arabicNumber) + "' WHERE ARABIC_NUMBER = " + arabicNumber;
snowflake.execute ({sqlText: updateStatement});
}
$$;
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.
2. 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…
CREATE OR REPLACE PROCEDURE copy_by_day()
RETURNS VARCHAR LANGUAGE JAVASCRIPT
AS
$$
var dayOfWeek;
var result = snowflake.execute({sqlText: "select dayofweek(current_date) from dual"});
if(result.next()) dayOfWeek = result.getColumnValue(1);
var copyStatement = "INSERT INTO NUMBERS_OF_DAY SELECT * FROM NUMBERS where ARABIC_NUMBER % ? = 0";
snowflake.execute ({sqlText: copyStatement, binds:[dayOfWeek]});
$$;
3. 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.
Jon Nedelmann, Mai 2019
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);
1. 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:
CREATE OR REPLACE PROCEDURE fill_numbers()
RETURNS VARCHAR LANGUAGE JAVASCRIPT
AS
$$
for(var i = 1; i 3000 || n < 1) return "NN";
else {
var result = "";
if(n > 999) {
result = times(Math.floor(n / 1000), 'M');
n = n % 1000;
}
if(n > 99) {
result = result + parts(Math.floor(n / 100), 'C', 'D', 'M');
n = n % 100;
}
if(n > 9) {
result = result + parts(Math.floor(n / 10), 'X', 'L', 'C');
n = n % 10;
}
result = result + parts(n, 'I', 'V', 'X');
}
return result;
}
var selectArabic = "SELECT ARABIC_NUMBER FROM NUMBERS";
var result = snowflake.execute({sqlText: selectArabic});
while(result.next()) {
var arabicNumber = result.getColumnValue(1);
var updateStatement = "UPDATE NUMBERS SET ROMAN_NUMBER = '" + toRoman(arabicNumber) + "' WHERE ARABIC_NUMBER = " + arabicNumber;
snowflake.execute ({sqlText: updateStatement});
}
$$;
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.
2. 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…
CREATE OR REPLACE PROCEDURE copy_by_day()
RETURNS VARCHAR LANGUAGE JAVASCRIPT
AS
$$
var dayOfWeek;
var result = snowflake.execute({sqlText: "select dayofweek(current_date) from dual"});
if(result.next()) dayOfWeek = result.getColumnValue(1);
var copyStatement = "INSERT INTO NUMBERS_OF_DAY SELECT * FROM NUMBERS where ARABIC_NUMBER % ? = 0";
snowflake.execute ({sqlText: copyStatement, binds:[dayOfWeek]});
$$;
3. 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.
