Kategorien:: DDL für benutzerdefinierte Funktionen, externe Funktionen und gespeicherte Prozeduren

CREATE FUNCTION¶

Erstellt eine neue UDF (benutzerdefinierte Funktion). Ein UDF kann eines der folgenden Elemente enthalten:

SQL-Ausdruck
JavaScript-code
Java-Code (Quellcode oder kompilierter Code)

Eine UDF kann entweder skalare oder tabellarische Ergebnisse zurückgeben.

Siehe auch:: ALTER FUNCTION, DROP FUNCTION, SHOW USER FUNCTIONS, DESCRIBE FUNCTION

Syntax¶

SQL

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'

JavaScript

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'

Java

Verwenden Sie die folgende Syntax, wenn der Quellcode inline ist:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] FUNCTION [ IF NOT EXISTS ] <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
    RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
    [ [ NOT ] NULL ]
    LANGUAGE JAVA
    [ RUNTIME_VERSION = <java_jdk_version> ]
    [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
    [ VOLATILE | IMMUTABLE ]
    [ COMMENT = '<string_literal>' ]
    [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] ) ]
    [ PACKAGES = ( '<package_name_and_version>' [, '<package_name_and_version>' ...] ) ]
    HANDLER = '<path_to_method>'
    [ TARGET_PATH = '<stage_path_and_file_name_to_write>' ]
    AS '<function_definition>'

Verwenden Sie die folgende Syntax, wenn der Quellcode vorkompiliert ist (d. h. wenn der Code als JAR-Datei bereitgestellt wird):

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] FUNCTION [ IF NOT EXISTS ] <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
    RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
    [ [ NOT ] NULL ]
    LANGUAGE JAVA
    [ RUNTIME_VERSION = <java_jdk_version> ]
    [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
    [ VOLATILE | IMMUTABLE ]
    [ COMMENT = '<string_literal>' ]
    IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] )
    HANDLER = '<path_to_method>'

Erforderliche Parameter¶

Alle Sprachen

Name ( [ Argumentname Argumentdatentyp ] [ , ... ] )

Gibt den Bezeichner (und optional ein oder mehrere Argumente/Eingaben) für die UDF an. Der Bezeichner muss für das Schema, in dem die UDF erstellt wird, nicht eindeutig sein, da UDFs durch ihren Namen und ihre Argumenttypen identifiziert und aufgelöst werden.

Der Bezeichner muss jedoch mit einem alphabetischen Zeichen beginnen und darf keine Leer- oder Sonderzeichen enthalten, es sei denn, die gesamte Bezeichnerzeichenfolge wird in doppelte Anführungszeichen gesetzt (z. B. "My object"). Bei Bezeichnern, die in doppelte Anführungszeichen eingeschlossen sind, ist auch die Groß-/Kleinschreibung zu beachten.

Weitere Details dazu finden Sie unter Anforderungen an Bezeichner.

RETURNS ...

Gibt die von der UDF zurückgegebenen Ergebnisse an, die den UDF-Typ bestimmen:

Ergebnisdatentyp: Erstellt eine skalare UDF, die einen Einzelwert mit dem angegebenen Datentyp zurückgibt.

TABLE ( Spaltenname Spaltendatentyp , ... ): Erstellt eine Tabellen-UDF, die tabellarische Ergebnisse mit den angegebenen Tabellenspalten und Spaltentypen zurückgibt.

Bemerkung

Für Java-UDFs:

Der Ergebnisdatentyp muss in der SQL Data Type-Spalte der SQL-Java-Typzuordnungstabelle enthalten sein.

AS Funktionsdefinition

Definiert den Code, der von der UDF ausgeführt wird. Der Inhalt hängt vom Typ der erstellten UDF ab:

SQL-UDF: Jeder gültige SQL-Ausdruck. Weitere Informationen zu SQL-UDFs einschließlich Beispielen finden Sie unter SQL-UDFs.

JavaScript-UDF: Jedes gültige JavaScript-Skript. Weitere Informationen zu JavaScript-UDFs einschließlich Beispielen finden Sie unter JavaScript UDFs.

Java-UDF: Jedes gültige Java-Skript. Weitere Informationen zu Java-UDFs einschließlich Beispielen finden Sie unter Java-UDFs.

Weitere Details dazu finden Sie unter Nutzungshinweise (unter diesem Thema).

Bemerkung

Eine Funktionsdefinition ist für vorkompilierte UDFs nicht erforderlich.

JavaScript

LANGUAGE JAVASCRIPT: Gibt an, dass der Code in der Sprache JavaScript vorliegt.

Java

LANGUAGE JAVA

Gibt an, dass der Code in der Sprache Java vorliegt.

RUNTIME_VERSION = Java-JDK-Version

Gibt die zu verwendende Java JDK-Laufzeitversion an. Folgende Java-Versionen werden unterstützt:

11.x

Wenn RUNTIME_VERSION nicht festgelegt ist, wird Java JDK 11 verwendet.

IMPORTS = (stage_path_and_file_name_to_read)

Der Speicherort (Stagingbereich), der Pfad und der Name der zu importierenden Datei(en).

Eine Datei kann eine JAR-Datei oder ein anderer Typ von Datei sein.

Wenn die Datei eine JAR-Datei ist, kann sie eine oder mehrere .class-Dateien und keine oder mehrere Ressourcendateien enthalten.

JNI (Java Native Interface) wird nicht unterstützt. Snowflake verbietet das Laden von Bibliotheken, die nativen Code (im Gegensatz zu Java-Bytecode) enthalten.

Java-UDFs können auch Nicht-JAR-Dateien lesen. Ein Beispiel finden Sie unter Lesen einer Datei aus einer Java-UDF heraus.

Wenn Sie vorhaben, eine Datei (JAR-Datei oder eine andere Datei) in einen Stagingbereich zu kopieren, empfiehlt Snowflake die Verwendung eines benannten internen Stagingbereichs, da der PUT-Befehl das Kopieren von Dateien in benannte interne Stagingbereiche unterstützt und der PUT-Befehl normalerweise die einfachste Möglichkeit ist, eine JAR-Datei in einen Stagingbereich zu verschieben.

Externe Stagingbereiche sind erlaubt, werden aber von PUT nicht unterstützt.

Jede Datei in der IMPORTS-Klausel muss einen eindeutigen Namen haben, auch wenn sich die Dateien in verschiedenen Unterverzeichnissen oder verschiedenen Stagingbereichen befinden.

Wenn sowohl die IMPORTS- als auch die TARGET_PATH-Klausel vorhanden ist, muss sich der Dateiname in der TARGET_PATH-Klausel von jedem Dateinamen in der IMPORTS-Klausel unterscheiden, auch wenn sich die Dateien in verschiedenen Unterverzeichnissen oder verschiedenen Stagingbereichen befinden.

Snowflake gibt einen Fehler zurück, wenn TARGET_PATH mit einer vorhandenen Datei übereinstimmt. Sie können TARGET_PATH nicht verwenden, um eine vorhandene Datei zu überschreiben.

Für eine vorkompilierte Java-UDF ist die IMPORTS-Klausel erforderlich, da sie den Speicherort der JAR-Datei angibt, die die UDF enthält.

Für eine Inline-Java-UDF wird die IMPORTS-Klausel nur dann benötigt, wenn die Inline-Java-UDF auf andere Dateien, wie z. B. Bibliotheken oder Textdateien, zugreifen muss.

Bei Snowflake-Systempaketen, wie dem Snowpark-Paket, können Sie das Paket mit der PACKAGES-Klausel angeben, anstatt seine JAR-Datei mit IMPORTS zu spezifizieren. Wenn Sie dies tun, muss die JAR-Paketdatei nicht in einem IMPORTS-Wert enthalten sein.

HANDLER = Pfad_zur_Methode

Pfad und Name der statischen Methode innerhalb dieser .jar-Datei. Dies geschieht typischerweise in der folgenden Form:

com.my_company.my_package.my_class.my_method

Wobei:

com.my_company.my_package

entspricht der package-Deklaration am Anfang der Java-Quellcodedatei. Beispiel:

package com.my_company.my_package;

Wenn die JAR-Datei außerhalb eines Pakets erstellt wurde, dann darf der Pfad_zur_Methode keinen Paketnamen enthalten.

Inline-Java

Funktionsdefinition: Inline-Java-UDFs erfordern eine Funktionsdefinition.

Optionale Parameter¶

Alle Sprachen

SECURE

Gibt an, dass die Funktion sicher ist. Weitere Informationen zu sicheren Funktionen finden Sie unter Sichere UDFs.

[ [ NOT ] NULL ]

Gibt an, ob die Funktion auch NULL-Werte oder nur NON-NULL-Werte zurückgeben kann. Der Standardwert ist NULL (d. h. die Funktion kann NULL zurückgeben).

Bemerkung

Derzeit wird die NOT NULL-Klausel bei SQL-UDFs nicht erzwungen. Als NOT NULL deklarierte SQL-UDFs können NULL-Werte zurückgeben. Snowflake empfiehlt, NOT NULL nicht für SQL-UDFs zu verwenden, es sei denn, der Code in der Funktion wurde geschrieben, um sicherzustellen, dass niemals NULL-Werte zurückgegeben werden.

CALLED ON NULL INPUT oder . RETURNS NULL ON NULL INPUT | STRICT

Gibt das Verhalten der UDF an, wenn sie mit Null-Eingaben aufgerufen wird. Im Gegensatz zu systemdefinierten Funktionen, die immer Null zurückgeben, wenn eine Eingabe Null ist, können UDFs Null-Eingaben verarbeiten und Nicht-Null-Werte zurückgeben, auch wenn eine Eingabe Null ist:

CALLED ON NULL INPUT ruft immer die UDF mit Null-Eingaben auf. Es liegt an der UDF, mit solchen Werten angemessen umzugehen.

RETURNS NULL ON NULL INPUT (oder sein Synonym STRICT) ruft UDF nicht auf, wenn eine Eingabe Null ist. Stattdessen wird immer ein Null-Wert für diese Zeile zurückgegeben. Beachten Sie, dass die UDF bei Nicht-Null-Eingaben immer noch Null zurückgeben kann.

Standard: CALLED ON NULL INPUT

VOLATILE | IMMUTABLE

Gibt das Verhalten der UDF bei der Rückgabe des Ergebnisses an:

VOLATILE: Die UDF kann für verschiedene Zeilen unterschiedliche Werte zurückgeben, auch bei gleicher Eingabe (z. B. aufgrund von Nichtdeterminismus und Statefullness).

IMMUTABLE: UDF geht davon aus, dass die Funktion, wenn sie mit den gleichen Eingabewerten aufgerufen wird, immer das gleiche Ergebnis liefert. Diese Garantie wird nicht geprüft. Die Angabe von IMMUTABLE für eine UDF, die unterschiedliche Werte für dieselbe Eingabe zurückgibt, führt zu einem undefinierten Verhalten.

Standard: VOLATILE

COMMENT = 'Zeichenfolgenliteral'

Gibt einen Kommentar für die UDF an, der in der Spalte DESCRIPTION in der Ausgabe SHOW FUNCTIONS und SHOW USER FUNCTIONS angezeigt wird.

Standard: user-defined function

Java

PACKAGES = (Name_und_Version_des_Pakets)

Der Name und die Versionsnummer der Snowflake-Systempakete, die als Abhängigkeiten benötigt werden. Der Wert sollte die Form Paketname:Versionsnummer haben, wobei Paketname das Snowflake_Domäne:-Paket ist. Beachten Sie, dass Sie latest als Versionsnummer angeben können, sodass Snowflake die neueste auf dem System verfügbare Version verwendet.

Beispiel:

-- Use version 1.2.0 of the Snowpark package.
packages=('com.snowflake:snowpark:1.2.0')

-- Use the latest version of the Snowpark package.
packages=('com.snowflake:snowpark:latest')

Sie können die Liste der unterstützten Systempakete ermitteln, indem Sie in Snowflake den folgenden SQL-Befehl ausführen:

select * from information_schema.packages where language ='java';

Für eine Abhängigkeit, die Sie mit PACKAGES angeben, müssen Sie die zugehörige JAR-Datei nicht zusätzlich in einer IMPORTS-Klausel angeben.

Inline-Java

TARGET_PATH = stage_path_and_file_name_to_write

Die TARGET_PATH-Klausel gibt den Speicherort an, in den Snowflake den kompilierten Code (JAR-Datei) nach der Kompilierung des in der Funktionsdefinition angegebenen Quellcodes schreiben soll.

Wenn diese Klausel enthalten ist, sollte der Benutzer die JAR-Datei manuell entfernen, wenn sie nicht mehr benötigt wird (typischerweise, wenn die Java-UDF gelöscht wird).

Wenn diese Klausel weggelassen wird, kompiliert Snowflake den Quellcode jedes Mal neu, wenn der Code benötigt wird. Die JAR-Datei wird nicht dauerhaft gespeichert, und der Benutzer muss die JAR-Datei nicht bereinigen.

Nutzungshinweise¶

Alle Sprachen

Funktionsdefinition hat Größenbeschränkungen. Die maximal zulässige Größe kann sich ändern.
Die Trennzeichen um die Funktionsdefinition können einfache Anführungszeichen oder doppelte Dollarzeichen sein.

Eine Verwendung von $$ als Trennzeichen vereinfacht das Schreiben von gespeicherten Prozeduren, die einfache Anführungszeichen enthalten.

Wenn als Trennzeichen für den Textköper der Funktion das einfache Anführungszeichen verwendet wird, müssen alle Anführungszeichen innerhalb von Funktionsdefinition (z. B. Zeichenfolgenliterale) mit einfachen Anführungszeichen als Escape-Zeichen versehen werden.
Wenn Sie eine UDF- in einer Maskierungsrichtlinie verwenden, stellen Sie sicher, dass die Datentypen von UDF-Spalte und Maskierungsrichtlinie übereinstimmen. Weitere Informationen dazu finden Sie unter Benutzerdefinierte Funktionen in einer Maskierungsrichtlinie.
Metadaten:

Achtung

Kunden müssen sicherstellen, dass bei der Nutzung des Snowflake-Dienstes keine personenbezogenen Daten (außer für ein Objekt „Benutzer“), sensible Daten, exportkontrollierte Daten oder andere regulierte Daten als Metadaten eingegeben werden. Weitere Informationen dazu finden Sie unter Metadatenfelder in Snowflake.

SQL

Derzeit wird die NOT NULL-Klausel bei SQL-UDFs nicht erzwungen.

JavaScript

Snowflake validiert JavaScript-Code nicht zum Zeitpunkt der UDF-Erstellung (d. h. die Erstellung der UDF ist erfolgreich, unabhängig davon, ob der Code gültig ist). Wenn der Code nicht gültig ist, werden bei Aufruf der UDF zur Abfragezeit Fehler zurückgegeben.

Java

In Java lassen primitive Datentypen keine NULL-Werte zu, sodass die Übergabe eines NULL-Wertes für ein Argument eines solchen Typs zu einem Fehler führt.
In der HANDLER-Klausel wird beim Methodennamen zwischen Groß- und Kleinschreibung unterschieden.
In den Klauseln IMPORTS und TARGET_PATH:
- Bei Paket-, Klassen- und Dateinamen wird Groß-/Kleinschreibung unterschieden.
- Bei Stagingbereichsnamen wird Groß-/Kleinschreibung nicht unterschieden.
Sie können die PACKAGES-Klausel verwenden, um Paketname und Versionsnummer für systemdefinierte Snowflake-Abhängigkeiten, wie z. B. die von Snowpark, anzugeben. Für andere Abhängigkeiten geben Sie die JAR-Abhängigkeitsdateien mit der Klausel IMPORTS an.
Snowflake validiert Folgendes:
- Die im HANDLER der CREATE FUNCTION-Anweisung angegebene JAR-Datei existiert und enthält die angegebene Klasse und Methode.
- Die in der UDF-Deklaration angegebenen Eingangs- und Ausgangstypen sind mit den Eingangs- und Ausgangstypen der Java-Methode kompatibel.
Die Validierung kann zur Erstellungszeit oder zur Ausführungszeit erfolgen.
- Wenn der Benutzer zum Zeitpunkt der Ausführung der CREATE FUNCTION-Anweisung mit einem aktiven Snowflake-Warehouse verbunden ist, wird die UDF zum Erstellungszeitpunkt validiert.
- Andernfalls wird die UDF erstellt, aber nicht sofort validiert, und Snowflake gibt die folgende Meldung zurück: Function <Name> created successfully, but could not be validated since there is no active warehouse.

Beispiele¶

SQL

Erstellen Sie eine einfache skalare SQL-UDF, die eine hart codierte Approximation der mathematischen Konstante Pi zurückgibt:

CREATE FUNCTION pi_udf()
  RETURNS FLOAT
  AS '3.141592654::FLOAT'
  ;

Erstellen Sie eine einfache SQL-Tabellen-UDF, die hartcodierte Werte zurückgibt:

CREATE FUNCTION simple_table_function ()
  RETURNS TABLE (x INTEGER, y INTEGER)
  AS
  $$
    SELECT 1, 2
    UNION ALL
    SELECT 3, 4
  $$
  ;

SELECT * FROM TABLE(simple_table_function());

Ausgabe:

SELECT * FROM TABLE(simple_table_function());
+---+---+
| X | Y |
|---+---|
| 1 | 2 |
| 3 | 4 |
+---+---+

Erstellen Sie eine UDF, die mehrere Parameter akzeptiert:

CREATE FUNCTION multiply1 (a number, b number)
  RETURNS number
  COMMENT='multiply two numbers'
  AS 'a * b';

Erstellen Sie eine SQL-Tabellen-UDF namens get_countries_for_user, die die Ergebnisse einer Abfrage zurückgibt:

CREATE OR REPLACE FUNCTION get_countries_for_user ( id number )
  RETURNS TABLE (country_code char, country_name varchar)
  AS 'select distinct c.country_code, c.country_name
      from user_addresses a, countries c
      where a.user_id = id
      and c.country_code = a.country_code';

JavaScript

Erstellen Sie eine JavaScript-UDF mit dem Namen js_factorial:

CREATE OR REPLACE FUNCTION js_factorial(d double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  STRICT
  AS '
  if (D <= 0) {
    return 1;
  } else {
    var result = 1;
    for (var i = 2; i <= D; i++) {
      result = result * i;
    }
    return result;
  }
  ';

Java

Hier ist ein grundlegendes Beispiel für eine CREATE FUNCTION-Inline-Anweisung:

create or replace function echo_varchar(x varchar)
returns varchar
language java
called on null input
handler='TestFunc.echoVarchar'
target_path='@~/testfunc.jar'
as
'class TestFunc {
  public static String echoVarchar(String x) {
    return x;
  }
}';

Hier ist ein einfaches Beispiel einer skalaren CREATE FUNCTION-Anweisung:

create function my_decrement_udf(i numeric(9, 0))
    returns numeric
    language java
    imports = ('@~/my_decrement_udf_package_dir/my_decrement_udf_jar.jar')
    handler = 'my_decrement_udf_package.my_decrement_udf_class.my_decrement_udf_method'
    ;

Weitere Beispiele für Java-UDFs finden Sie unter Beispiele.