Skalare SQL-UDFs

Unter diesem Thema werden Konzepte und Nutzungsdetails behandelt, die für SQL-UDFs (benutzerdefinierte Funktionen) spezifisch sind.

Unter diesem Thema:

Allgemeine Nutzung

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. Hier ist ein einfaches Beispiel einer skalaren UDF, die die Fläche eines Kreises berechnet:

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  AS
  $$
    pi() * radius * radius
  $$
  ;
Copy
SELECT area_of_circle(1.0);
Copy

Ausgabe:

SELECT area_of_circle(1.0);
+---------------------+
| AREA_OF_CIRCLE(1.0) |
|---------------------|
|         3.141592654 |
+---------------------+
Copy

Der Ausdruck kann ein Abfrageausdruck sein (ein SELECT-Ausdruck). Beispiel:

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold)
        FROM purchases
  $$
  ;
Copy

Wenn Sie einen Abfrageausdruck in einer SQL-UDF verwenden, müssen Sie kein Semikolon in den UDF-Funktionsrumpf einschließen, um den Abfrageausdruck zu beenden.

Sie können nur einen Abfrageausdruck einschließen. Der Ausdruck kann UNION [ALL] enthalten.

Bemerkung

Obwohl der Textteil einer UDF eine vollständige SELECT-Anweisung enthalten kann, darf er keine DDL-Anweisung oder DML-Anweisung außer SELECT enthalten.

Bemerkung

Skalare Funktionen (UDFs) haben einen Grenzwert von 500 Eingabeargumenten.

Memoisierbare UDFs

Eine skalare SQL-UDF kann memoisierbar sein. Mit einer memoisierbaren Funktion kann das Ergebnis des Aufrufs einer skalaren SQL-UDF zwischengespeichert und das zwischengespeicherte Ergebnis zurückgegeben werden, wenn die Ausgabe zu einem späteren Zeitpunkt benötigt wird. Die Verwendung von memoisierbaren Funktionen verbessert die Performance bei komplexen Abfragen, z. B. bei Suchvorgängen auf mehreren Spalten von Zuordnungstabellen, auf die in Zeilenzugriffsrichtlinien oder Maskierungsrichtlinien verwiesen wird.

Eigentümer von Richtlinien (z. B. die Rolle mit OWNERSHIP-Berechtigung für die Zeilenzugriffsrichtlinie) können ihre Richtlinienbedingungen aktualisieren, um Unterabfragen, die Zuordnungstabellen aufweisen, durch eine memoisierbare Funktion zu ersetzen. Wenn Benutzer später in einer Abfrage auf die richtliniengeschützte Spalte verweisen, können bei Bedarf die zwischengespeicherten Ergebnisse der memoisierbaren Funktion genutzt werden.

Memoisierbare Funktion erstellen

Sie können eine skalare SQL-UDF in der CREATE FUNCTION-Anweisung durch Angabe des Schlüsselworts MEMOIZABLE als memoisierbare Funktion definieren. Memoisierbare Funktionen enthalten keine Argumente. Wenn Sie eine memoisierbare Funktion schreiben:

  • Geben Sie BOOLEAN oder andere skalare Datentypen als result_data_type an.

    Bei der Verwendung von ARRAY als result_data_type ist Vorsicht geboten, da die Cache-Größe begrenzt ist.

  • Geben Sie keine anderen Datentypen, wie OBJECT und VARIANT an.

  • Verweisen Sie in keiner Weise auf eine andere memoisierbare Funktion.

Memoisierbare Funktion aufrufen

Eine memoisierbare Funktion kann in einer SELECT-Anweisung aufgerufen oder in eine Richtliniendefinition aufgenommen werden, die dann die memoisierbare Funktion auf der Grundlage der Richtlinienbedingungen aufruft.

Beachten Sie beim Aufrufen einer memoisierbaren Funktion Folgendes:

  • Für SQL-UDFs, die den Datentyp ARRAY zurückgeben oder einen nicht skalaren Wert angeben, verwenden Sie die memoisierbare Funktion als Argument in der Funktion ARRAY_CONTAINS.

  • Beschränkung der Cache-Größe:

    Jede memoisierbare Funktion hat eine Beschränkung von 10 KB für die aktuelle Snowflake-Sitzung.

    Wenn die memoisierbare Funktion diese Grenze für den Resultset-Cache überschreitet, speichert Snowflake das Ergebnis des Aufrufs der memoisierbaren Funktion nicht. Stattdessen verhält sich UDF wie eine normale skalare UDF, je nachdem, wie die Funktion geschrieben ist.

  • Cache-Nutzung:

    Memoisierbare Funktionen haben einen wiederverwendbaren Ergebnis-Cache für verschiedene SQL-Anweisungen, wenn sich die Abfrageumgebung und der Kontext nicht ändern. Im Allgemeinen bedeutet dies, dass der Ergebnis-Cache für verschiedene SQL-Anweisungen gilt, sofern Folgendes gilt:

    • Die Autorisierung der Zugriffssteuerung von Objekten und Spalten, auf die in einer Abfrage verwiesen wird, bleibt unverändert.

    • Die Objekte, auf die in der Abfrage verwiesen wird, werden nicht geändert (z. B. durch DML-Anweisungen).

    In der Spalte CHILD_QUERIES_WAIT_TIME der Account Usage-Ansicht QUERY_HISTORY wird die Zeit (in Millisekunden) erfasst, die bei Aufruf einer memoisierbaren Funktion bis zum Abschluss der zwischengespeicherten Suche benötigt wird.

  • Die zwischengespeicherten Ergebnisse von memoisierbaren Funktionen werden in folgenden Fällen nicht wiederverwendet:

    • Die Funktion verweist auf eine Tabelle oder ein anderes Objekt, und es erfolgt eine Aktualisierung der referenzierten Tabelle.

    • Es gibt eine Änderung in der Zugriffssteuerung der Tabelle.

    • Die Funktion ruft eine nicht deterministische Funktion auf.

    • Die Funktion ruft eine externe Funktion oder eine UDF auf, die keine SQL-UDF ist.

Beispiele

Grundlegendes Beispiel für skalare SQL-UDF

Dieses Beispiel gibt eine hart codierte Approximation der mathematischen Konstante Pi zurück.

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

Ausgabe:

SELECT pi_udf();
+-------------+
|    PI_UDF() |
|-------------|
| 3.141592654 |
+-------------+
Copy

Häufige SQL-Beispiele

Abfrageausdruck mit SELECT-Anweisung

Erstellen Sie die zu verwendende Tabelle und Daten:

CREATE TABLE purchases (number_sold INTEGER, wholesale_price NUMBER(7,2), retail_price NUMBER(7,2));
INSERT INTO purchases (number_sold, wholesale_price, retail_price) VALUES 
   (3,  10.00,  20.00),
   (5, 100.00, 200.00)
   ;
Copy

Erstellen Sie die UDF:

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold)
        FROM purchases
  $$
  ;
Copy

Rufen Sie die UDF in einer Abfrage auf:

SELECT profit();
Copy

Ausgabe:

SELECT profit();
+----------+
| PROFIT() |
|----------|
|   530.00 |
+----------+
Copy

UDF in einer WITH-Klausel

CREATE TABLE circles (diameter FLOAT);

INSERT INTO circles (diameter) VALUES
    (2.0),
    (4.0);

CREATE FUNCTION diameter_to_radius(f FLOAT) 
  RETURNS FLOAT
  AS 
  $$ f / 2 $$
  ;
Copy
WITH
    radii AS (SELECT diameter_to_radius(diameter) AS radius FROM circles)
  SELECT radius FROM radii
    ORDER BY radius
  ;
Copy

Ausgabe:

+--------+
| RADIUS |
|--------|
|      1 |
|      2 |
+--------+
Copy

JOIN-Operation

In diesem Beispiel wird eine komplexere Abfrage verwendet, die eine JOIN-Operation enthält:

Erstellen Sie die zu verwendende Tabelle und Daten:

CREATE TABLE orders (product_ID varchar, quantity integer, price numeric(11, 2), buyer_info varchar);
CREATE TABLE inventory (product_ID varchar, quantity integer, price numeric(11, 2), vendor_info varchar);
INSERT INTO inventory (product_ID, quantity, price, vendor_info) VALUES 
  ('X24 Bicycle', 4, 1000.00, 'HelloVelo'),
  ('GreenStar Helmet', 8, 50.00, 'MellowVelo'),
  ('SoundFX', 5, 20.00, 'Annoying FX Corporation');
INSERT INTO orders (product_id, quantity, price, buyer_info) VALUES 
  ('X24 Bicycle', 1, 1500.00, 'Jennifer Juniper'),
  ('GreenStar Helmet', 1, 75.00, 'Donovan Liege'),
  ('GreenStar Helmet', 1, 75.00, 'Montgomery Python');
Copy

Erstellen Sie die UDF:

CREATE FUNCTION store_profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
  SELECT SUM( (o.price - i.price) * o.quantity) 
    FROM orders AS o, inventory AS i 
    WHERE o.product_id = i.product_id
  $$
  ;
Copy

Rufen Sie die UDF in einer Abfrage auf:

SELECT store_profit();
Copy

Ausgabe:

SELECT store_profit();
+----------------+
| STORE_PROFIT() |
|----------------|
|         550.00 |
+----------------+
Copy

Weitere Beispiele finden Sie unter dem Thema CREATE FUNCTION.

Verwenden von UDFs in verschiedenen Klauseln

Ein skalare UDF kann an jeder Stelle verwendet werden, an der ein Skalarausdruck verwendet werden kann. Beispiel:

-- ----- These examples show a UDF called from different clauses ----- --

select MyFunc(column1) from table1;

select * from table1 where column2 > MyFunc(column1);
Copy

Verwenden von SQL-Variablen in einer UDF

In diesem Beispiel wird gezeigt, wie Sie eine SQL-Variable festlegen und diese Variable in einer UDF verwenden:

SET id_threshold = (SELECT COUNT(*)/2 FROM table1);
Copy
CREATE OR REPLACE FUNCTION my_filter_function()
RETURNS TABLE (id int)
AS
$$
SELECT id FROM table1 WHERE id > $id_threshold
$$
;
Copy