Schreiben von gespeicherten Prozeduren in Scala

Sie können eine gespeicherte Prozedur in Scala schreiben. Sie können die Snowpark-Bibliothek innerhalb Ihrer gespeicherten Prozedur verwenden, um Abfragen, Aktualisierungen und andere Arbeiten an Tabellen in Snowflake auszuführen.

Unter diesem Thema wird erklärt, wie Sie die Logik für eine gespeicherte Prozedur schreiben. Sobald die Logik verfügbar ist, können Sie die Prozedur mit SQL erstellen und aufrufen. Weitere Informationen dazu finden Sie unter Erstellen einer gespeicherten Prozedur und Aufrufen einer gespeicherten Prozedur.

Unter diesem Thema:

Einführung

Sie können Ihre Datenpipeline in Snowflake erstellen und ausführen, wobei ein Snowflake-Warehouse als Compute-Framework dient. Für den Code Ihrer Datenpipeline verwenden Sie die Snowpark-API für Scala zum Schreiben der gespeicherten Prozeduren. Um die Ausführung dieser gespeicherten Prozeduren zu planen, verwenden Sie Aufgaben (Tasks).

Bemerkung

Um eine anonyme Prozedur sowohl zu erstellen als auch aufzurufen, verwenden Sie CALL (mit anonymen Prozeduren). Das Erstellen und Aufrufen einer anonymen Prozedur erfordert keine Rolle mit CREATE PROCEDURE-Schemaberechtigungen.

Voraussetzungen

Sie müssen Version 1.1.0 oder eine neuere Version der Snowpark-Bibliothek verwenden.

Wenn Sie eine gespeicherte Prozedur schreiben, deren Handler-Code in einen Stagingbereich kopiert wird, müssen Sie Ihre Klassen für die Ausführung in Java, Version 11.x kompilieren.

Einrichten Ihrer Entwicklungsumgebung für Snowpark

Richten Sie Ihre Entwicklungsumgebung für die Verwendung der Snowpark-Bibliothek ein. Siehe Einrichten Ihrer Entwicklungsumgebung für Snowpark Scala.

Strukturieren und Erstellen von Handler-Code

Sie können den Handler-Quellcode inline mit demSQL-Code aufbewahren, der die Prozedur erstellt, oder das kompilierte Ergebnis des Handlers an einem separaten Ort aufbewahren und von SQL aus referenzieren. Weitere Informationen dazu finden Sie unter Speichern von Handler-Code inline oder in einem Stagingbereich.

Weitere Informationen zum Erstellen von Handler-Quellcode zur Verwendung mit einer Prozedur finden Sie unter Packen des Handler-Codes.

Einschränkungen

In dieser Vorschau bestehen für gespeicherte Snowpark-Prozeduren die folgenden Einschränkungen:

  • Parallelität wird nicht unterstützt. So können Sie beispielsweise über Ihren Code keine Abfragen von mehreren Threads übermitteln. Code, der mehrere Abfragen gleichzeitig ausführt, führt zu einem Fehler.

  • Wenn Sie Ihre gespeicherte Prozedur von einer Aufgabe aus ausführen, müssen Sie beim Erstellen der Aufgabe ein Warehouse angeben. (Sie können für die Ausführung der Aufgabe keine von Snowflake verwalteten Computeressourcen verwenden).

  • Das Lesen und Schreiben von Dateien aus in Scala geschriebenem Handler-Code von gespeicherten Prozeduren wird noch nicht vollständig unterstützt. Das Lesen und Schreiben von Dateien kann zu instabilem Verhalten führen. Dazu zählt das Empfangen eines InputStream als Argument und die Verwendung von Methoden, die in der Klasse FileOperation verfügbar sind (auf die Sie normalerweise über die Methode Session.file zugreifen), einschließlich put und get.

  • Beachten Sie die folgenden Einschränkungen für die Verwendung von Snowpark-APIs in Ihrer gespeicherten Prozedur.

    • Wenn Sie APIs verwenden, die PUT- und GET-Befehle ausführen (einschließlich Session.sql("PUT ...") und Session.sql("GET ...")), dürfen Sie nur in das Verzeichnis „/tmp“ des speichergestützten Dateisystems schreiben, das für die Abfrage vorgesehen ist, das die Prozedur aufruft.

    • Verwenden Sie keine APIs für asynchrone Aktionen.

    • Verwenden Sie keine APIs, die neue Sitzungen erstellen (z. B. Session.builder().configs(...).create()).

    • Die Verwendung von session.jdbcConnection (und der zurückgegebenen Verbindung) wird nicht unterstützt, da dies zu unsicherem Verhalten führen kann.

  • Das Erstellen benannter temporärer Objekte wird in einer gespeicherten Prozedur mit Eigentümerrechten nicht unterstützt. Eine gespeicherte Prozedur mit Eigentümerrechten ist eine gespeicherte Prozedur, die mit den Berechtigungen des Eigentümers der gespeicherten Prozedur ausgeführt wird. Weitere Informationen dazu finden Sie unter Aufruferrechte oder Eigentümerrechte.

Schreiben des Scala-Codes für die gespeicherte Prozedur

Für die Logik der Prozedur schreiben Sie Handler-Code, der bei Aufruf der Prozedur ausgeführt wird. In diesem Abschnitt wird das Design eines Handlers beschrieben.

Sie können diesen Code in die SQL-Anweisung einfügen, mit der die Prozedur erstellt wird, oder Sie kopieren den Code in einen Stagingbereich und referenzieren ihn dort, wenn Sie die Prozedur erstellen. Weitere Informationen dazu finden Sie unter Speichern von Handler-Code inline oder in einem Stagingbereich.

Planen des Schreibens Ihrer gespeicherten Prozedur

Belegung des Arbeitsspeichers begrenzen

Snowflake belegt Methoden mit einer Begrenzung hinsichtlich der benötigten Menge an Arbeitsspeicher. Weitere Informationen dazu, wie Sie zu hohen Speicherverbrauch vermeiden können, finden Sie unter Entwerfen von Handlern unter Berücksichtigung der Snowflake-bedingten Einschränkungen.

Thread-sicheren Code schreiben

Stellen Sie sicher, dass Ihre Handler-Methode oder Funktion threadsicher ist.

Erläuterungen zu Sicherheitseinschränkungen

Da Ihr Handler-Code wird innerhalb einer eingeschränkten Engine ausgeführt wird, sollten Sie unbedingt die unter Sicherheitsverfahren für UDFs und Prozeduren beschriebenen Regeln befolgen.

Entscheiden Sie, ob Sie die Eigentümerrechte oder die Aufruferrechte nutzen möchten.

Außerdem müssen Sie bei der Planung Ihrer gespeicherten Prozedur berücksichtigen, ob Sie die gespeicherte Prozedur mit Aufruferrechten oder Eigentümerrechten ausführen möchten.

Timeout-Verhalten von gespeicherten Prozeduren berücksichtigen

Die Ausführung einer gespeicherten Prozedur wird zeitlich begrenzt, es sei denn, der Timer wird durch die Aktivität des Codes zurückgesetzt. Insbesondere wird der Timeout-Timer durch die Interaktionen des Codes mit Daten zurückgesetzt, einschließlich Dateioperationen, Abfragen und Iterationen durch ein Resultset.

Schreiben der Klasse oder des Objekts

Die Methode oder Funktion, die Sie definieren, muss Teil einer Klasse oder eines Objekts sein.

Beim Schreiben der Klasse oder des Objekts ist Folgendes zu beachten:

  • Die Klasse (oder das Objekt) und die Methode dürfen nicht geschützt oder privat sein.

  • Wenn die Methode nicht statisch ist und Sie einen Konstruktor definieren möchten, definieren Sie einen Null-Argument-Konstruktor für die Klasse. Snowflake ruft diesen Null-Argument-Konstruktor zur Initialisierungszeit auf, um eine Instanz Ihrer Klasse zu erstellen.

  • Sie können in derselben Klasse oder demselben Objekt verschiedene Methoden für verschiedene gespeicherte Prozeduren definieren.

Schreiben der Methode oder Funktion

Beachten Sie beim Schreiben der Methode oder Funktion für die gespeicherte Prozedur Folgendes:

  • Geben Sie das Snowpark-Session-Objekt als erstes Argument Ihrer Methode oder Funktion an.

    Wenn Sie Ihre gespeicherte Prozedur aufrufen, erstellt Snowflake automatisch ein Session-Objekt und übergibt dieses an Ihre gespeicherte Prozedur. (Sie können das Session-Objekt nicht selbst erstellen.)

  • Für die restlichen Argumente und den Rückgabewert werden die Scala-Typen verwendet, die den Snowflake-Datentypen entsprechen.

  • Ihre Methode oder Funktion muss einen Wert zurückgeben. Für gespeicherte Prozeduren in Scala ist ein Rückgabewert erforderlich.

  • Die Ausführung einer gespeicherten Prozedur wird zeitlich begrenzt, es sei denn, der Timer wird durch die Aktivität des Codes zurückgesetzt. Insbesondere wird der Timeout-Timer durch die Interaktionen des Codes mit Daten zurückgesetzt, einschließlich Dateioperationen, Abfragen und Iterationen durch ein Resultset.

Abhängigkeiten für Code zur Verfügung stellen

Wenn Ihr Handler-Code von Code abhängt, der außerhalb des Handlers selbst definiert ist (z. B. Klassen in einer JAR-Datei) oder von Ressourcendateien, können Sie diese Abhängigkeiten für Ihren Code zur Verfügung stellen, indem Sie ihn in einen Stagingbereich hochladen. Beim Erstellen der Prozedur können Sie diese Abhängigkeiten mithilfe der IMPORTS-Klausel referenzieren.

Weitere Informationen dazu finden Sie unter Abhängigkeiten für Code zur Verfügung stellen.

Zugriff auf Daten in Snowflake über Ihre gespeicherte Prozedur

Um auf Daten in Snowflake zuzugreifen, verwenden Sie die Snowpark-Bibliotheks-APIs.

Bei der Verarbeitung eines Aufrufs Ihrer gespeicherten Scala-Prozedur erstellt Snowflake ein Snowpark-Session-Objekt und übergibt das Objekt an die Methode oder Funktion für Ihre gespeicherte Prozedur.

Wie bei gespeicherten Prozeduren in anderen Sprachen wird der Kontext für die Sitzung (z. B. die Berechtigungen, die aktuelle Datenbank, das aktuelle Schema usw.) dadurch bestimmt, ob die gespeicherte Prozedur mit Aufruferrechten oder mit Eigentümerrechten ausgeführt wird. Weitere Details dazu finden Sie unter Zugriff auf und Einstellung des Sitzungsstatus.

Sie können dieses Session-Objekt verwenden, um APIs in der Snowpark-Bibliothek aufzurufen. Sie können zum Beispiel einen DataFrame für eine Tabelle erstellen oder eine SQL-Anweisung ausführen.

Weitere Informationen dazu finden Sie im Snowpark-Entwicklerhandbuch für Scala.

Bemerkung

Weitere Informationen zu Einschränkungen, einschließlich Einschränkungen beim Zugriff auf Daten, finden Sie unter Einschränkungen.

Beispiel für Datenzugriff

Es folgt ein Beispiel für eine Scala-Methode, die eine bestimmte Anzahl von Zeilen aus einer Tabelle in eine andere Tabelle kopiert. Der Methode werden die folgenden Argumente übergeben:

  • Ein Snowpark-Session-Objekt

  • Der Name der Tabelle, aus der die Zeilen kopiert werden sollen

  • Der Name der Tabelle, in der die Zeilen gespeichert werden sollen

  • Die Anzahl der zu kopierenden Zeilen

Die Methode in diesem Beispiel gibt eine Zeichenfolge zurück.

object MyObject
{
  def myProcedure(session: com.snowflake.snowpark.Session, fromTable: String, toTable: String, count: Int): String =
  {
    session.table(fromTable).limit(count).write.saveAsTable(toTable)
    return "Success"
  }
}
Copy

Im folgenden Beispiel wird statt einer Methode eine Funktion definiert:

object MyObject
{
  val myProcedure = (session: com.snowflake.snowpark.Session, fromTable: String, toTable: String, count: Int): String =>
  {
    session.table(fromTable).limit(count).write.saveAsTable(toTable)
    "Success"
  }
}
Copy

Zugriff auf andere Klassen und Ressourcendateien

Wenn Ihr Code von Klassen abhängt, die außerhalb der gespeicherten Prozedur definiert sind (z. B. Klassen in einer separaten JAR-Datei), oder von Ressourcendateien, können Sie diese Abhängigkeiten dem Handler zur Verfügung stellen, indem Sie die Dateien in einen Stagingbereich hochladen. Weitere Informationen dazu finden Sie unter Abhängigkeiten für Code zur Verfügung stellen.

Wenn Sie später die CREATE PROCEDURE-Anweisung ausführen, verwenden Sie die IMPORTS-Klausel, um auf diese Dateien zu verweisen.

Vorbereiten einer gespeicherten Prozedur mit einem Staging-Handler

Wenn Sie vorhaben, eine gespeicherte Prozedur zu erstellen, deren Handler kompiliert und in einen Stagingbereich kopiert wird (anstatt als Quelle inline aufbewahrt zu werden), müssen Sie Ihre Klassen in eine JAR-Datei kompilieren und verpacken, und Sie müssen die JAR-Datei in den Stagingbereich hochladen.

Kompilieren und Packen Ihres Scala-Codes

Um das Einrichten Ihrer gespeicherten Prozedur zu vereinfachen, erstellen Sie eine JAR-Datei, die alle für Ihre gespeicherte Prozedur benötigten Abhängigkeiten enthält. Später müssen Sie die JAR-Datei in einen Stagingbereich hochladen und in Ihrer CREATE PROCEDURE-Anweisung auf die JAR-Datei verweisen. Dieser Vorgang ist einfacher, wenn Sie weniger JAR-Dateien hochladen und auf sie verweisen müssen.

Verwenden von SBT zum Erstellen einer JAR-Datei mit Abhängigkeiten

Wenn Sie SBT zum Erstellen und Packen Ihres Codes verwenden, können Sie mithilfe des sbt-assembly-Plugins eine JAR-Datei erstellen, die alle Abhängigkeiten enthält. Weitere Informationen dazu finden Sie unter Packen von Scala-Handler-Code mit SBT.

Verwenden von Maven zum Erstellen einer JAR-Datei mit Abhängigkeiten

Wenn Sie Maven zum Erstellen und Packen Ihres Codes verwenden, können Sie das Maven Assembly Plugin verwenden, um eine JAR-Datei zu erstellen, die alle Abhängigkeiten enthält. Weitere Informationen dazu finden Sie unter Packen von Java- oder Scala-Handler-Code mit Maven.

Verwenden anderer Tools zum Erstellen einer JAR-Datei mit Abhängigkeiten

Wenn Sie weder SBT noch Maven verwenden, finden Sie in der Dokumentation Ihres Erstellungstools eine Anleitung zum Erstellen einer JAR-Datei mit allen Abhängigkeiten.

Wenn Sie beispielsweise ein IntelliJ IDEA-Projekt (kein SBT-Projekt in IntelliJ) verwenden, finden Sie die erforderlichen Informationen in der Anleitung zum Einrichten einer Artefakt-Konfiguration.

Hochladen von Dateien in einen Stagingbereich

Um die Logik Ihrer Prozedur (und ggf. andere Abhängigkeiten) für die Prozedur zur Verfügung zu stellen, müssen Sie die erforderlichen Dateien in einen Stagingbereich hochladen. Weitere Informationen dazu finden Sie unter Abhängigkeiten für Code zur Verfügung stellen.

Erstellen der gespeicherten Prozedur

Informationen zum Erstellen einer gespeicherten Prozedur mit SQL finden Sie unter Erstellen einer gespeicherten Prozedur.

Zurückgeben von tabellarischen Daten

Sie können eine Prozedur schreiben, die Daten in tabellarischer Form zurückgibt. Gehen Sie wie folgt vor, um eine Prozedur zu schreiben, die Tabellendaten zurückgibt:

  • Geben Sie in der CREATE PROCEDURE-Anweisung TABLE(...) als Rückgabetyp der Prozedur an.

    Als TABLE-Parameter können Sie die Spaltennamen und Typen der zurückgegebenen Daten angeben, sofern Sie diese kennen. Wenn Sie beim Definieren der Prozedur die zurückgegebenen Spalten nicht kennen – z. B. wenn diese zur Laufzeit angegeben werden – können Sie die TABLE-Parameter weglassen. Wenn Sie dies tun, werden die Spalten des Rückgabewerts der Prozedur aus den Spalten des Datenframes konvertiert, der vom Handler zurückgegeben wird. Spaltendatentypen werden gemäß der unter Zuordnung von Datentypen zwischen SQL und Scala angegebenen Zuordnung in SQL konvertiert.

  • Schreiben Sie den Handler so, dass er das tabellarische Ergebnis in einem Snowpark-Datenframe zurückgibt.

    Weitere Informationen zu Datenframes finden Sie unter Verwenden von DataFrames in Snowpark Scala.

Bemerkung

Eine Prozedur generiert zur Laufzeit einen Fehler, wenn eine der folgenden Bedingungen zutrifft:

  • TABLE ist als Rückgabetyp deklariert, aber sein Handler gibt keinen Datenframe zurück.

  • Der Handler gibt einen Datenframe zurück, aber die Prozedur deklariert TABLE nicht als Rückgabetyp.

Beispiel

Die Beispiele in diesem Abschnitt veranschaulichen die Rückgabe von Tabellenwerten aus einer Prozedur, die Zeilen herausfiltert, in denen eine Spalte mit einer Zeichenfolge übereinstimmt.

Definieren der Daten

Der Code im folgenden Beispiel erstellt eine Tabelle der Mitarbeiter.

CREATE OR REPLACE TABLE employees(id NUMBER, name VARCHAR, role VARCHAR);
INSERT INTO employees (id, name, role) VALUES (1, 'Alice', 'op'), (2, 'Bob', 'dev'), (3, 'Cindy', 'dev');
Copy

Deklarieren einer Prozedur zum Filtern von Zeilen

Der Code in den folgenden beiden Beispielen erstellt eine gespeicherte Prozedur, die den Tabellennamen und die Rolle als Argumente verwendet und die Zeilen der Tabelle zurückgibt, deren Rollenspaltenwert mit der als Argument angegebenen Rolle übereinstimmt.

Angeben von Namen und Typen der Rückgabespalten

In diesem Beispiel werden Spaltennamen und -typen in der RETURNS TABLE()-Anweisung angegeben.

CREATE OR REPLACE PROCEDURE filter_by_role(table_name VARCHAR, role VARCHAR)
RETURNS TABLE(id NUMBER, name VARCHAR, role VARCHAR)
LANGUAGE SCALA
RUNTIME_VERSION = '2.12'
PACKAGES = ('com.snowflake:snowpark:latest')
HANDLER = 'Filter.filterByRole'
AS
$$
import com.snowflake.snowpark.functions._
import com.snowflake.snowpark._

object Filter {
    def filterByRole(session: Session, tableName: String, role: String): DataFrame = {
        val table = session.table(tableName)
        val filteredRows = table.filter(col("role") === role)
        return filteredRows
    }
}
$$;
Copy

Bemerkung

Derzeit können Sie in der RETURNS TABLE(...)-Klausel von CREATE PROCEDURE den Datentyp GEOGRAPHY nicht als Spaltentyp angeben.

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE(g GEOGRAPHY)
...
Copy

Wenn Sie dies tun, führt der Aufruf der gespeicherten Prozedur zu einem Fehler:

CALL test_return_geography_table_1();
Copy
Stored procedure execution error: data type of returned table does not match expected returned table type
Copy

Um dieses Problem zu umgehen, können Sie die Spaltenargumente und -typen in RETURNS TABLE() weglassen.

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE()
...
Copy
Weglassen von Namen und Typen der Rückgabespalten

Der Code im folgenden Beispiel deklariert eine Prozedur, mit der Spaltennamen und -typen des Rückgabewerts aus den im Rückgabewert des Handlers enthaltenen Spalten extrapoliert werden können. Die Spaltennamen und -typen aus der RETURNS TABLE()-Anweisung werden weggelassen.

CREATE OR REPLACE PROCEDURE filter_by_role(table_name VARCHAR, role VARCHAR)
RETURNS TABLE()
LANGUAGE SCALA
RUNTIME_VERSION = '2.12'
PACKAGES = ('com.snowflake:snowpark:latest')
HANDLER = 'Filter.filterByRole'
AS
$$
import com.snowflake.snowpark.functions._
import com.snowflake.snowpark._

object Filter {
    def filterByRole(session: Session, tableName: String, role: String): DataFrame = {
        val table = session.table(tableName)
        val filteredRows = table.filter(col("role") === role)
        return filteredRows
    }
}
$$;
Copy

Aufrufen der Prozedur

Im folgenden Beispiel wird die gespeicherte Prozedur aufgerufen:

CALL filter_by_role('employees', 'dev');
Copy

Der Prozeduraufruf erzeugt die folgende Ausgabe:

+----+-------+------+
| ID | NAME  | ROLE |
+----+-------+------+
| 2  | Bob   | dev  |
| 3  | Cindy | dev  |
+----+-------+------+
Copy

Aufrufen Ihrer gespeicherten Prozedur

Informationen zum Aufrufen einer gespeicherten Prozedur aus SQL finden Sie unter Aufrufen einer gespeicherten Prozedur.