Übersicht zu UDFs

Unter diesem Thema werden Konzepte und Verwendungsdetails beschrieben, die für alle Arten von UDFs (benutzerdefinierte Funktionen) gelten.

Unter diesem Thema:

Arten von benutzerdefinierten Funktionen

SQL

Eine SQL-UDF wertet einen beliebigen SQL-Ausdruck aus und gibt die Ergebnisse des Ausdrucks zurück. Die Funktionsdefinition kann ein SQL-Ausdruck sein, der entweder einen skalaren (d. h. einen einzelnen) Wert oder, wenn als Tabellenfunktion definiert, einen Satz von Zeilen zurückgibt.

Der Ausdruck kann ein Abfrageausdruck sein, aber für nicht tabellarische Funktionen muss garantiert sein, dass der Abfrageausdruck höchstens eine Zeile mit einer einzelnen Spalte zurückgibt.

Der Ausdruck, der eine UDF definiert, kann sich auf die Eingabeargumente der Funktion und auf Datenbankobjekte wie Tabellen, Ansichten und Sequenzen beziehen. Der Eigentümer der UDF muss über die entsprechenden Berechtigungen für alle Datenbankobjekte verfügen, auf die die UDF zugreift. Der definierende Ausdruck einer SQL-UDF kann sich auf andere benutzerdefinierte Funktionen beziehen, sie kann sich jedoch weder direkt noch über eine andere auf sie zurückgerufene Funktion rekursiv auf sich selbst beziehen.

JavaScript

Mit JavaScript-UDFs können Sie Daten mit der JavaScript-Programmiersprache und der Laufzeitumgebung bearbeiten. JavaScript-UDFs werden auf die gleiche Weise wie SQL-UDFs erstellt, der LANGUAGE-Parameter ist jedoch auf JAVASCRIPT gesetzt.

Ähnlich wie SQL-UDFs können JavaScript-UDFs abhängig von der Definition der UDF entweder ein Skalar- oder ein Tabellenergebnis zurückgeben.

Der definierende Ausdruck einer JavaScript-UDFkann sich rekursiv auf sich selbst beziehen, jedoch nicht auf andere benutzerdefinierte Funktionen.

Bemerkung

JavaScript-UDFs haben einige Anforderungen, Nutzungsdetails und Einschränkungen, die nicht für SQL-UDFs gelten. Weitere Details einschließlich Beispielen finden Sie unter JavaScript UDFs.

Skalare/Tabellen-UDFs

Standardmäßig ist eine UDF skalar, gibt höchstens eine Zeile zurück und besteht aus einer einzelnen Spalte/einem einzelnen Wert.

Eine UDF kann jedoch auch so definiert werden, dass ein Satz von Zeilen mit einer oder mehreren Spalten zurückgegeben wird, auf die dann in der FROM-Klausel einer Abfrage zugegriffen werden kann. Eine benutzerdefinierte Tabellenfunktion (UDTF) wird definiert, indem ein Rückgabetyp angegeben wird, der das Schlüsselwort TABLE enthält, und die Namen und Typen der Spalten in den Tabellenergebnissen angibt.

Weitere Informationen zum Erstellen und Verwenden von UDTFs, einschließlich Beispiele, finden Sie unter SQL-UDTFs (Benutzerdefinierte Tabellenfunktionen) und JavaScript-UDTFs (Benutzerdefinierte Tabellenfunktionen). Allgemeine Informationen zu den in Snowflake bereitgestellten systemdefinierten Tabellenfunktionen finden Sie unter Tabellenfunktionen.

Namenskonventionen für UDFs

UDFs sind Datenbankobjekte, d. h. sie werden in einer festgelegten Datenbank und einem festgelegten Schema erstellt. Sie verfügen daher über einen vollqualifizierten Namen, der durch ihren Namespace in Form von Datenbank.Schema.Funktionsname definiert wird. Beispiel:

SELECT temporary_db_qualified_names_test.temporary_schema_1.udf_pi();

Beim Aufrufen ohne den vollqualifizierten Namen werden UDFs für die aktuell in der Sitzung verwendete Datenbank und das aktuell verwendete Schema aufgelöst.

Dies steht im Gegensatz zu den eingebauten, systemdefinierten Funktionen von Snowflake, die keinen Namespace haben und daher von überall aufgerufen werden können.

Konflikte mit systemdefinierten Funktionen

Um Konflikte beim Aufruf von Funktionen zu vermeiden, lässt Snowflake die Erstellung von UDFs mit dem gleichen Namen wie bei einer der systemdefinierten Funktionen nicht zu.

Überladen von UDF-Namen

Snowflake unterstützt das Überladen von UDF-Namen. Das bedeutet, dass mehrere UDFs im selben Schema denselben Namen haben können, sofern sich ihre Argumentsignaturen unterscheiden, entweder durch die Anzahl der Argumente oder durch die Argumenttypen. Wenn eine überladene UDF aufgerufen wird, überprüft Snowflake die Argumente und ruft die korrekte Funktion auf.

Betrachten Sie die folgenden Beispiele, in denen zwei SQL-UDFs mit dem Namen add5 erstellt werden:

CREATE OR REPLACE FUNCTION add5 (n number)
  RETURNS number
  AS 'n + 5';

CREATE OR REPLACE FUNCTION add5 (s string)
  RETURNS string
  AS 's || ''5''';

Wichtig

In der zweiten ADD5-Funktion werden einfache Anführungszeichen verwendet, um das Zeichenfolgenliteral '5' zu umgehen. Alle einfachen Anführungszeichen, die in einer UDF-Definition verwendet werden, müssen mit einfachen Anführungszeichen als Escape-Zeichen eingeschlossen werden.

Wenn add5 mit einem numerischen Argument aufgerufen wird, wird die erste Implementierung ausgewählt, während ein Zeichenfolgenargument die zweite Implementierung verwendet. Wenn das Argument weder eine Zahl noch eine Zeichenfolge ist, hängt die Implementierung von den impliziten Typkonvertierungsregeln von Snowflake ab. Beispielsweise wird ein Argument mit Datumstyp in eine Zeichenfolge konvertiert und die Implementierung der Zeichenfolge wird ausgewählt, da die Konvertierung von DATE in NUMBER nicht unterstützt wird.

Beispiel:

select add5(1);

+---------+
| ADD5(1) |
|---------|
|       6 |
+---------+

select add5('1');

+-----------+
| ADD5('1') |
|-----------|
| 15        |
+-----------+

select add5('hello');

+---------------+
| ADD5('HELLO') |
|---------------|
| hello5        |
+---------------+

select add5(to_date('2014-01-01'));

+-----------------------------+
| ADD5(TO_DATE('2014-01-01')) |
|-----------------------------|
| 2014-01-015                 |
+-----------------------------+

Seien Sie vorsichtig, wenn Sie die Überladung verwenden. Durch die Kombination aus automatischer Typkonvertierung und Überladung kann ein kleiner Benutzerfehler schnell zu unerwarteten Ergebnissen führen. Ein Beispiel finden Sie unter Überladen von Funktionsnamen.

Sicherheits-/Berechtigungsanforderungen für UDFs

Wenn sich eine Funktionsdefinition auf eine nicht qualifizierte Tabelle bezieht, wird diese Tabelle in dem Schema aufgelöst, das die Funktion enthält. Ein Verweis auf ein anderes Schemaobjekt (z. B. Tabelle, Ansicht oder andere Funktion) erfordert, dass der Eigentümer der Funktion über Berechtigungen zum Zugriff auf dieses Schemaobjekt verfügt. Der Aufrufer der Funktion muss keinen Zugriff auf die Objekte haben, auf die in der Funktionsdefinition verwiesen wird, sondern nur die Berechtigung zur Verwendung der Funktion.

Ein Administrator besitzt beispielsweise eine Tabelle mit dem Namen users, die vertrauliche Daten enthält, auf die allgemein nicht zugegriffen werden kann. Der Administrator kann jedoch die Gesamtzahl der Benutzer über eine Funktion verfügbar machen, für die andere Benutzer Zugriffsrechte haben:

use role dataadmin;

desc table users;

+-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------+
| name      | type         | kind   | null? | default | primary key | unique key | check  | expression | comment |
|-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------|
| USER_ID   | NUMBER(38,0) | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
| USER_NAME | VARCHAR(100) | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
  ...
  ...
  ...
+-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------+

create function total_user_count() returns number as 'select count(*) from users';

grant usage on function total_user_count() to role analyst;

use role analyst;

-- This will fail because the role named "analyst" does not have the
-- privileges required in order to access the table named "users".
select * from users;

FAILURE: SQL compilation error:
Object 'USERS' does not exist.

-- However, this will succeed.
select total_user_count();

+--------------------+
| TOTAL_USER_COUNT() |
|--------------------+
| 123                |
+--------------------+

Weitere Informationen zur Verwendung von Rollen und Berechtigungen zum Verwalten der Zugriffssteuerung finden Sie unter Zugriffssteuerung in Snowflake.

Beispiele

Überladen von Funktionsnamen

Wie unter Überladen von UDF-Namen (unter diesem Thema) beschrieben, können Sie gespeicherte Prozedurnamen überladen.

Das nächste Beispiel für Überladung zeigt, wie durch die Kombination von Überladung und automatischer Typkonvertierung unerwartete Ergebnisse auftreten können:

Erstellen Sie eine Funktion mit einem FLOAT-Parameter:

CREATE FUNCTION add_pi(PARAM_1 FLOAT)
    RETURNS FLOAT
    LANGUAGE SQL
    AS $$
        PARAM_1 + 3.1415926::FLOAT
    $$;

Rufen Sie die Funktion zweimal auf: Übergeben Sie beim ersten Mal einen FLOAT-Wert. Übergeben Sie beim zweiten Mal eine VARCHAR-Wert. Der VARCHAR-Wert wird in einen FLOAT-Wert konvertiert, und die Ausgabe jedes Aufrufs ist identisch:

SELECT add_pi(1.0), add_pi('1.0');
+-------------+---------------+
| ADD_PI(1.0) | ADD_PI('1.0') |
|-------------+---------------|
|   4.1415926 |     4.1415926 |
+-------------+---------------+

Erstellen Sie nun eine überladene Funktion mit einem VARCHAR-Parameter:

CREATE FUNCTION add_pi(PARAM_1 VARCHAR)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS $$
        PARAM_1 || ' + 3.1415926'
    $$;

Verwenden Sie jetzt genau dieselben CALLs wie zuvor. Beachten Sie den Unterschied in der Ausgabe zwischen diesen beiden CALLs und den beiden vorherigen CALLs.

SELECT add_pi(1.0), add_pi('1.0');
+-------------+-----------------+
| ADD_PI(1.0) | ADD_PI('1.0')   |
|-------------+-----------------|
|   4.1415926 | 1.0 + 3.1415926 |
+-------------+-----------------+