Snowflake 101: Arbeiten mit Stored Procedures

Stored Procedures erweitern SQL um prozedurale Logik. Damit lassen sich Bedingungen, Schleifen und Funktionen umsetzen, die mit reinem SQL nicht möglich wären.

Neben Stored Procedures lassen sich in Snowflake auch User-Defined Functions (UDFs) anlegen. Den Unterschied zu kennen ist entscheidend, um die richtige Wahl zwischen UDFs und Stored Procedures (SP) zu treffen.

Stored Procedures kommen in Snowflake typischerweise für administrative Aufgaben zum Einsatz – etwa zum Bereinigen alter Daten, zum Löschen ungenutzter Tabellen oder für individuelle Daten-Backups. UDFs setzen Sie dagegen ein, wenn Sie innerhalb einer SELECT-Abfrage einen Wert berechnen und zurückgeben möchten, beispielsweise den Umsatz eines bestimmten Vertriebsmitarbeiters oder den Bonus eines Angestellten.

Stored Procedures werden mit dem Schlüsselwort CALL als eigenständige Anweisung aufgerufen:

1CALL my_stored_procedure(input_param);

UDFs hingegen werden innerhalb eines SELECT-Statements aufgerufen:

1SELECT column1, my_udf(input_parameter) FROM table1;

Ein weiterer Unterschied liegt im Rückgabewert: Stored Procedures müssen nichts zurückgeben – sie können einfach eine Aufgabe ausführen. UDFs liefern dagegen immer einen Wert zurück.

Schauen wir uns ein paar typische Anwendungsfälle an und prüfen, ob sich eher Stored Procedures oder UDFs eignen:

Anwendungsfall	UDF oder Stored Procedure?
Neuen Benutzer und dediziertes Warehouse anlegen	Stored Procedure
Alle temporären Tabellen löschen	Stored Procedure
Ungenutzte Tabellen aufräumen	Stored Procedure
Stadt anhand einer IP-Adresse ermitteln	UDF
Browsertyp aus einem User-Agent-String extrahieren	UDF
Rabatte für Bestellungen berechnen	UDF
Tabelle aus einer Stage laden	Stored Procedure

Snowflake unterstützt mehrere Sprachen für die Entwicklung von Stored Procedures. Welche die richtige ist, hängt von mehreren Faktoren ab:

• Persönliche Vorlieben und Sprachkenntnisse
• Verfügbarkeit der benötigten Bibliotheken
• Konsistenz mit vorhandenem Code in unterstützten Sprachen
• Ob der Code inline oder extern (als eigenständige Datei in einer Stage) liegen soll

Hier ein Überblick über die verfügbaren Sprachen samt Information, ob sie inline oder via Stage unterstützt werden:

Sprache	Handler-Ablage
Java	Inline oder Stage
JavaScript	Inline
Scala	Inline oder Stage
Snowflake SQL Scripting	Inline

Beide Varianten haben Vor- und Nachteile. Die Inline-Entwicklung ist oft einfacher: Sie können den Code direkt in der SP-Definition feinjustieren. Stage-Handler bieten sich für kompilierten Code (Java, Scala) an, weil sich so vorhandener kompilierter Code in Snowflake Stored Procedures wiederverwenden lässt.

Mit Snowflake SQL Scripting

Schauen wir uns die Syntax zum Anlegen von Stored Procedures (SP) mit Snowflake SQL Scripting an und klären die Bedeutung der einzelnen Schlüsselwörter. Wie in der Abbildung zu sehen, besteht eine Stored Procedure aus folgenden Bestandteilen:

1. Name der Stored Procedure
2. Eingabeparameter (Argumente) samt ihren Datentypen
3. Rückgabetyp der Stored Procedure
4. Sprachangabe
5. Der Rumpf der Stored Procedure, in dem die eigentliche Logik steckt

Die obige Stored Procedure hello_world gibt schlicht die Nachricht zurück, die als Argument übergeben wurde. Beim Aufruf sehen wir die Ausgabe:

Mit JavaScript

Wandeln wir das Beispiel hello_world ab und setzen es in JavaScript um. Hier der Code derselben Prozedur, nun in JavaScript:

create or replace procedure hello_world_javascript(message varchar)
returns varchar not null
language javascript
as
$$
    return message;
$$;

Wir haben JavaScript als Sprache angegeben und nutzen andere Zeichen, um den Rumpf zu umschließen ($$).

Der Aufruf der Prozedur bleibt identisch:

Mit Python

Im letzten Beispiel legen wir dieselbe hello_world-Prozedur in Python an:

create or replace procedure hello_world_python(message varchar)
returns varchar not null
language python
runtime_version = 3.11
handler = 'hello_world'
packages = ('snowflake-snowpark-python')
as
$$
def hello_world(session, message):
    return message;
$$;

Gegenüber SQL Scripting oder JavaScript fallen mehrere Unterschiede auf. Aktualisieren wir das Diagramm und heben die Änderungen hervor:

1. Bei Python muss die verwendete Python-Version angegeben werden.
2. Es muss ein handler definiert sein – die Python-Funktion, die in der Stored Procedure ausgeführt wird.
3. Es müssen alle Python-Pakete angegeben werden, die in der Stored Procedure genutzt werden.

Der Aufruf der Python-Stored-Procedure funktioniert wie in jeder anderen Sprache:

Als Nächstes klären wir, wie Sie eine Stored Procedure ausführen (bzw. "aufrufen") – insbesondere mit Argumenten.

Argumente in Stored Procedures nutzen

Stored Procedures unterstützen Argumente, die Sie von außen übergeben können. Im Beispiel hello_world haben wir das Argument message verwendet. Dort sehen Sie auch, wie der Wert beim Aufruf übergeben wird.

Argumente lassen sich optional gestalten, wenn in der SP-Definition ein Standardwert hinterlegt ist. In diesem Fall können Sie das Argument beim Aufruf weglassen, und der Standardwert wird verwendet. Passen wir unser Beispiel so an, dass es ein optionales Argument nutzt:

create or replace procedure hello_world(message varchar DEFAULT 'Hello from Stored procedure written in SQL scripting!')
returns varchar not null
language sql
as
begin
  return message;
end;

Optionale und Pflichtargumente kombinieren

Wenn Sie sowohl optionale als auch Pflichtargumente haben, müssen die Pflichtargumente zuerst stehen. Nun können wir die Prozedur ohne Argumentwert aufrufen, und Snowflake greift auf den Standardwert zurück.

Natürlich lässt sich der Standardwert beim Aufruf auch überschreiben:

Wie nutzt man Bind-Variablen in einem SQL-Statement?

Argumente werden häufig auch als Bind-Variablen in SQL-Abfragen innerhalb von Stored Procedures eingesetzt. So lässt sich die SQL-Abfrage dynamisch auf Basis der Argumente erzeugen. Typische Beispiele sind das Übergeben eines Datums, einer Kunden-ID oder eines Tabellennamens, der geleert werden soll. Bauen wir ein einfaches Beispiel, in dem wir ein SELECT-Statement dynamisch zusammensetzen und Werte aus einer Tabelle für eine bestimmte ID zurückgeben:

-- create a test table
create table SP_TEST (
    id number,
    value varchar
);

-- insert data
insert into SP_TEST values (1, 'value 1');
insert into SP_TEST values (2, 'value 2');

-- create a stored procedure
CREATE PROCEDURE DYNAMIC_QUERY(ID number)
RETURNS VARCHAR
language sql
as

Code einblenden

Der Code der Stored Procedure führt zwei neue Konzepte ein. Erstens das Binden von Eingabeargumenten an SQL-Statements: Dazu setzen wir vor den Argumentnamen einen Doppelpunkt, also :id. Außerdem deklariert der Code eine Ausgabevariable und schreibt das Ergebnis des SELECT-Statements direkt im SQL-Code in diese Variable. Das erledigt die Syntax :into <variable_name>.

Stored Procedures sind Datenbankobjekte wie Tabellen oder Views. Sie gehören also einer Rolle. Neben der Berechtigung OWNERSHIP gibt es auch die Berechtigung USAGE auf SPs, die anderen Rollen erteilt werden kann. Es gibt noch einen weiteren Aspekt rund um Berechtigungen und das Ausführen von Stored Procedures: Snowflake unterstützt zwei verschiedene Modi für den Umgang damit. Sie können entweder Caller’s Rights oder Owner’s Rights verwenden. Beim Anlegen einer Prozedur legen Sie fest, welcher Modus gilt. Standard sind Owner’s Rights. Erläutern wir beide Modi.

Caller’s Rights

Wenn Sie eine SP mit Caller’s Rights ausführen, läuft sie mit den Berechtigungen des Aufrufers. Die Prozedur hat zudem Zugriff auf die Session-Informationen des Aufrufers – sie kann Session-Variablen lesen, in Abfragen verwenden oder verändern. Solche Session-Änderungen bleiben auch nach dem SP-Aufruf bestehen. Die SP nutzt die Datenbankberechtigungen des Aufrufers und kann damit auf dieselben DB-Objekte zugreifen wie er. Enthält die SP eine Anweisung oder ein DB-Objekt, für die der Aufrufer keine Berechtigung hat, löst die SP einen Berechtigungsfehler aus.

Owner’s Rights

In diesem Fall läuft die Prozedur mit den Rechten des Owners und ist vom Aufrufer entkoppelt. Die SP kann also Dinge tun, die der Aufrufer direkt nicht darf. Das ist ein gutes Beispiel dafür, wie sich Aufgaben an andere Rollen delegieren lassen, ohne ihnen die erforderlichen Berechtigungen direkt zu erteilen. Sie können etwa eine SP anlegen, die alte Daten aus Tabellen löscht, und sie Nutzern bereitstellen, ohne ihnen die DELETE-Berechtigung auf diesen Tabellen einzuräumen. Eine Prozedur mit Owner’s Rights hat keinen Zugriff auf die Session-Informationen des Aufrufers und kann sie auch nicht verändern. Ebenso hat sie keinen Zugriff auf Variablen, die außerhalb der SP angelegt wurden – diese Werte müssen Sie bei Bedarf als Eingabeargument übergeben.

Welcher Modus passt?

Das hängt von Ihren Anforderungen ab. Wollen Sie etwas an andere Nutzer delegieren, sind Owner’s Rights die richtige Wahl. Gleiches gilt, wenn dieser Modus die Funktionsweise der SP nicht behindert oder Sie den Code nicht gegenüber Aufrufern offenlegen möchten. Caller’s Rights sind dagegen passend, wenn Sie Zugriff auf aktuelle Session-Informationen brauchen oder die Prozedur ausschließlich Objekte verwendet, die dem Aufrufer gehören oder die er nutzen darf. Fassen wir beide Optionen in einer Tabelle zusammen:

Caller’s Rights	Owner’s Rights
Läuft mit den Berechtigungen des Aufrufers	Läuft mit den Berechtigungen des SP-Owners
Hat Zugriff auf die aktuelle Session des Aufrufers	Eingeschränkter Zugriff auf die Session des Aufrufers
Session-Änderungen bleiben nach Ende des SP-Aufrufs bestehen	Kann den Session-Zustand nicht verändern
Kann Session-Variablen und -Parameter des Aufrufers lesen, setzen und zurücksetzen	Kann Session-Variablen und -Parameter des Aufrufers weder lesen noch setzen oder zurücksetzen
Kann nur Objekte und Operationen nutzen, die der Aufrufer ebenfalls nutzen darf	Entkoppelt von den Berechtigungen des Aufrufers

Prozeduren können einen Wert zurückgeben (Zahl, String, Boolean). Sehr häufig liefern SPs aber auch gar nichts zurück, weil sie nur Code ausführen, ohne ein Ergebnis weitergeben zu müssen.

Ein besonderer Rückgabetyp einer SP ist eine Tabelle. Schauen wir uns dazu zwei Beispiele an: eine Prozedur ohne Rückgabewert und eine, die eine Tabelle zurückgibt.

create or replace procedure no_return_value()
returns varchar null
language sql
as
begin
    select 1;
    select 2;
    select 3;
    select 4;
end;

Wenn Sie sich das CREATE PROCEDURE-Statement in der Dokumentation ansehen, stellen Sie fest, dass das Schlüsselwort RETURNS verpflichtend ist. Um eine Stored Procedure ohne Rückgabewert zu definieren, müssen Sie also einen Rückgabedatentyp angeben und das Schlüsselwort return im SP-Codeblock weglassen. Den Rückgabetyp müssen Sie nicht zwingend mit NULL definieren – der Code funktioniert auch mit RETURNS VARCHAR.

Beim Aufruf einer solchen Prozedur erhalten Sie als Ausgabe einen Nullwert:

Um eine Tabelle zurückzugeben, muss der Rückgabetyp als Tabelle deklariert werden. Wenn Sie die Datentypen der Spalten bereits kennen, können Sie sie zusammen mit den Spaltennamen direkt angeben. Andernfalls geht das auch zur Laufzeit.

Für die Rückgabe einer Tabelle führen wir einen weiteren Datentyp ein: RESULTSET. Dieser Datentyp kann das Ergebnis einer SQL-Abfrage aufnehmen. Wollen Sie es weiterverarbeiten, können Sie über die Zeilen iterieren – oder das Ergebnis einfach direkt zurückgeben, so wie in diesem Beispiel:

create or replace procedure returning_table()
returns table(id number, name varchar)
language sql
as
declare
    result RESULTSET DEFAULT (SELECT 1 id, 'test value' name);
begin
    return table(result);
end;

In diesem Beispiel weisen wir der Result-Variable einen Standardwert zu und geben sie dann innerhalb der Funktion table() zurück. Beim Aufruf dieser SP erhalten Sie eine Tabelle mit zwei Spalten als Ausgabe:

Wir haben die Grundlagen zum Schreiben von Stored Procedures in Snowflake behandelt. Bringen wir all diese Konzepte nun zusammen und bauen praxisnahe Anwendungsfälle, die zeigen, wie sich Stored Procedures für reale Aufgaben einsetzen lassen. Eine typische administrative Aufgabe ist das Ausführen mehrerer SQL-Statements, die dynamisch aus den Ergebnissen einer anderen Abfrage entstehen. So könnten Sie etwa alle leeren Tabellen bereinigen wollen. Dafür müssen Sie zunächst alle Tabellen mit 0 Datensätzen finden, dann darüber iterieren und sie löschen. Mit folgender Stored Procedure lässt sich das lösen:

create or replace procedure clean_empty_tables(db_name varchar)
returns varchar
language sql
as
declare
    table_list RESULTSET DEFAULT (SELECT TABLE_NAME, TABLE_TYPE, TABLE_SCHEMA FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES WHERE table_catalog = :db_name and row_count = 0 and deleted is null);
    c1 CURSOR FOR table_list;
    drop_statement varchar;
begin
    for record in c1 do
        drop_statement := 'DROP ' || record.table_type || ' ' || :db_name || '.' || record.table_schema || '.' || record.table_name || ';';
        execute immediate drop_statement;
    end for;
return 'cleaning done;';
end;

Gehen wir den Code Schritt für Schritt durch:

1. Wir übergeben den Datenbanknamen als Parameter, damit nur Tabellen und Views in der angegebenen DB bereinigt werden.
2. Wir deklarieren eine RESULTSET-Variable table_list, die als Ergebnis der Abfrage die leeren Tabellennamen enthält.
3. Außerdem definieren wir einen Cursor, um über die Ergebnisse zu iterieren, sowie eine Variable für das DROP-Statement, das in der Schleife dynamisch erzeugt und ausgeführt wird.
4. Der Rumpf der Prozedur arbeitet das Abfrageergebnis Zeile für Zeile ab und baut auf Basis der Cursor-Werte ein Drop-Statement zusammen. Wir berücksichtigen sowohl Views als auch Tabellen; wer nur Tabellen bereinigen möchte, passt die Eingangsabfrage entsprechend an.
5. Sobald das Drop-Statement steht, führen wir es über EXECUTE IMMEDIATE aus.

Beim Aufruf der Stored Procedure sollte die Meldung 'cleaning done' zurückkommen. Die gelöschten Tabellen und Views finden Sie in der Query-History – oder Sie passen die Rückgabemeldung an, sodass auch die Namen der gelöschten Objekte mit zurückgegeben werden.

Eine Stored Procedure ist Code – und sollte daher sauber kommentiert sein, damit Wartung und spätere Nutzung leichter fallen. Dafür gibt es mehrere Wege: Entweder Sie nutzen ein externes Tool wie einen Data Catalog oder ein internes Wiki und beschreiben die Stored Procedure dort. Zwei Aspekte sollten dabei immer im Fokus stehen:

• Dokumentation für Nutzer und Aufrufer
• Dokumentation für Entwickler

Einige Beispiele dafür, was in die Dokumentation gehört:

• Logik und Zielsetzung der Stored Procedure beschreiben
• Autoren benennen
• Ablageort beschreiben – Datenbank und Schema
• Eingabeparameter – Namen, Datentypen und Bedeutung
• Rückgabewerte, mögliche Fehler und Ausnahmen
• Voraussetzungen
• Welche Berechtigungen benötigt werden

Sie können Kommentare auch direkt in den Quellcode einfügen und den Algorithmus erläutern. Mein letzter Tipp: Versionieren Sie den Quellcode Ihrer Stored Procedures (z. B. mit Git). Denken Sie daran, dass Stored Procedures in Snowflake nicht Teil von Time Travel sind. Eine frühere Version des Codes lässt sich in Snowflake selbst nicht direkt wiederherstellen.

Fassen wir alle in diesem Beitrag gezeigten Beispiele für Stored Procedures zur schnellen Referenz zusammen:

Hello World

SQL Scripting

create or replace procedure hello_world(message varchar)
returns varchar not null
language sql
AS
begin
    return message;
end;

JavaScript

create or replace procedure hello_world_javascript(message varchar)
returns varchar not null
language javascript
as
$$
    return message;
$$;

Python

create or replace procedure hello_world_python(message varchar)
returns varchar not null
language python
runtime_version = 3.11
handler = 'hello_world'
packages = ('snowflake-snowpark-python')
as
$$
def hello_world(session, message):
    return message;
$$;

SQL-Abfrage mithilfe von Argumenten dynamisch erzeugen

-- create a test table
create table SP_TEST (
    id number,
    value varchar
);

-- insert data
insert into SP_TEST values (1, 'value 1');
insert into SP_TEST values (2, 'value 2');

-- create a stored procedure
CREATE PROCEDURE DYNAMIC_QUERY(ID number)
RETURNS VARCHAR
language sql
as

Code einblenden

Tabelle aus einer SQL-Abfrage zurückgeben

create or replace procedure returning_table()
returns table(id number, name varchar)
language sql
as
declare
    result RESULTSET DEFAULT (SELECT 1 id, 'test value' name);
begin
    return table(result);
end;

Mehrere SQL-Statements ausführen

create or replace procedure clean_empty_tables(db_name varchar)
returns varchar
language sql
as
declare
    table_list RESULTSET DEFAULT (SELECT TABLE_NAME, TABLE_TYPE, TABLE_SCHEMA FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES WHERE table_catalog = :db_name and row_count = 0 and deleted is null);
    c1 CURSOR FOR table_list;
    drop_statement varchar;
begin
    for record in c1 do
        drop_statement := 'DROP ' || record.table_type || ' ' || :db_name || '.' || record.table_schema || '.' || record.table_name || ';';
        execute immediate drop_statement;
    end for;
return 'cleaning done;';
end;

Benachrichtigungen für pausierte Tasks versenden

Hier ein Beispiel aus einem früheren Blogbeitrag. Die Stored Procedure task_state_monitor erhält einen task_name als Parameter und prüft den Status des Tasks. Lautet der Status "suspended", verschickt sie einen E-Mail-Alert und gibt eine Meldung zurück. Andernfalls meldet sie, dass mit dem Task alles in Ordnung ist.

create or replace procedure task_state_monitor(task_name string)
returns varchar not null
language SQL
AS
$$
DECLARE
    task_state string;
    c CURSOR FOR SELECT "state" from table(result_scan(last_query_id())) where "name" = ?;
BEGIN
    show tasks;
    open c USING (task_name);
    fetch c into task_state;
    IF(task_state = 'suspended') THEN
        CALL SYSTEM$SEND_EMAIL(
            'my_email_int',

Code einblenden

Tomáš Sobotík · Senior Data Engineer & Snowflake SME bei Norlys

Tomas ist langjähriger Snowflake Data SuperHero und ausgewiesener Snowflake-Experte. Seine Erfahrung in der Datenwelt reicht über mehr als ein Jahrzehnt zurück – als Snowflake Data Engineer, Architekt und Admin in zahlreichen Projekten unterschiedlichster Branchen und Technologien. Tomas ist ein zentrales Community-Mitglied, teilt sein Wissen aktiv und inspiriert andere. Außerdem ist er O’Reilly-Instructor und leitet Live-Online-Trainings.