Skalare SQL-UDFs¶

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. Sie können eine memoisierbare Funktion mit oder ohne Argumente erstellen. Durch die Verwendung von Argumenten haben Sie mehr Flexibilität bei der Definition der SQL-UDF. Wenn Sie eine Richtlinie schreiben, um die memoisierbare Funktion aufzurufen, haben Sie mehr Freiheit bei der Definition der Richtlinie.

Wenn Sie Argumente angeben, müssen die Argumente konstante Werte mit einem der folgenden Datentypen sein:

• VARCHAR und andere Zeichenfolgen-Datentypen.

• NUMBER und andere numerische Datentypen.

• TIMESTAMP und andere Datumsdatentypen.

• Ein BOOLEAN-Wert.

Nicht konstante Werte und deren Datentypen, wie semistrukturierte Daten und Tabellenspalten, werden nicht unterstützt.

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.

Grundlegende Beispiele 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'
  ;

SELECT pi_udf();   

Ausgabe:

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

Häufige SQL-Beispiele¶

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)
   ;

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

SELECT profit();

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

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 $$
  ;

WITH
    radii AS (SELECT diameter_to_radius(diameter) AS radius FROM circles)
  SELECT radius FROM radii
    ORDER BY radius
  ;

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

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');

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
  $$
  ;

SELECT store_profit();

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

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);

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);

CREATE OR REPLACE FUNCTION my_filter_function()
RETURNS TABLE (id int)
AS
$$
SELECT id FROM table1 WHERE id > $id_threshold
$$
;

Memoisierbare Funktionen¶

Weitere Beispiele finden Sie unter:

• Memoisierbare Funktion ohne Argumente in einer Zeilenzugriffsrichtlinie.

• Memoisierbare Funktion mit Argumenten in einer Maskierungsrichtlinie.