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.

Über die bimanu GmbH

Über bimanu bimanu, das sind motivierte und sympathische Digital Natives, die gemeinsam an einer Vision arbeiten: Unternehmen zu helfen bessere datengetriebene Entscheidung zu treffen.

Neben der klassischen IT – Beratung für Business Intelligence in den Bereichen SAP Analytics und Snowflake, bieten wir zusätzlich unsere bimanu Cloud, eine automatisierte All-In-One Plattform für Datenintegration und Analyse für kleine und mittelständige Unternehmen an.

Der Vorteil für Sie – entweder greifen Sie auf die Expertise einer Business Intelligence – Beratung zurück, wenn Sie diese benötigen oder Sie wählen einen Komplettservice mit überschaubaren Kosten, um Ihre Datenanforderungen im Zeitalter der Digitalisierung umzusetzen.

Neuigkeiten zum 01.04.2019
Die bimanu GmbH setzt ihren Wachstumskurs fort und hat zum 01.04.2019 die Tochter bimanu Cloud Solutions GmbH gegründet.

Die Fokussierung der Tochter GmbH liegt auf der Software bimanu Cloud, eine automatisierte All-In-One Plattform für Unternehmensdaten, IoT und Marketing Analytics vereint in einem Datawarehouse, die eine 360° Unternehmenssicht fördert.

Link: https://bimanu.de/…

Firmenkontakt und Herausgeber der Meldung:

bimanu GmbH
Bickenbachstraße 38
41462 Neuss
Telefon: +49 (2131) 74211-74
Telefax: +49 (2131) 4028-192
http://bimanu.de

Ansprechpartner:
Swen Göllner
E-Mail: swen.goellner@bimanu.de
Für die oben stehende Pressemitteilung ist allein der jeweils angegebene Herausgeber (siehe Firmenkontakt oben) verantwortlich. Dieser ist in der Regel auch Urheber des Pressetextes, sowie der angehängten Bild-, Ton-, Video-, Medien- und Informationsmaterialien. Die United News Network GmbH übernimmt keine Haftung für die Korrektheit oder Vollständigkeit der dargestellten Meldung. Auch bei Übertragungsfehlern oder anderen Störungen haftet sie nur im Fall von Vorsatz oder grober Fahrlässigkeit. Die Nutzung von hier archivierten Informationen zur Eigeninformation und redaktionellen Weiterverarbeitung ist in der Regel kostenfrei. Bitte klären Sie vor einer Weiterverwendung urheberrechtliche Fragen mit dem angegebenen Herausgeber. Eine systematische Speicherung dieser Daten sowie die Verwendung auch von Teilen dieses Datenbankwerks sind nur mit schriftlicher Genehmigung durch die United News Network GmbH gestattet.