CREATE FUNCTION (SQL und Python)

Artikel
04/18/2024

Gilt für: durch Häkchen mit „Ja“ markiert Databricks SQL Häkchen gesetzt ja Databricks Runtime

Erzeugt eine SQL-Skalar- oder Tabellenfunktion, die eine Reihe von Argumenten berücksichtigt und einen Skalarwert oder eine Menge von Zeilen zurückgibt.

Gilt für: Häkchen gesetzt ja Databricks SQL Databricks Runtime ab Version 13.3 LTS

Erstellt eine Python-Skalarfunktion, die einen Satz von Argumenten akzeptiert und einen Skalarwert zurückgibt.

Python-UDFs erfordern Unity Catalog für serverlose oder pro SQL-Warehouses oder einen freigegebenen oder einzelnen Unity Catalog-Cluster.

Gilt für: Häkchen ja Databricks SQL Häkchen gesetzt ja Databricks Runtime ab Version 14.1

Zusätzlich zum Aufrufen von Positionsparametern können Sie SQL und Python UDF auch mit dem Aufrufen benannter Parameter aufrufen.

Syntax

CREATE [OR REPLACE] [TEMPORARY] FUNCTION [IF NOT EXISTS]
    function_name ( [ function_parameter [, ...] ] )
    { [ RETURNS data_type ] |
      RETURNS TABLE [ ( column_spec [, ...]) ] }
    [ characteristic [...] ]
    { AS dollar_quoted_string | RETURN { expression | query } }

function_parameter
    parameter_name data_type [DEFAULT default_expression] [COMMENT parameter_comment]

column_spec
    column_name data_type [COMMENT column_comment]

characteristic
  { LANGUAGE { SQL | PYTHON } |
    [NOT] DETERMINISTIC |
    COMMENT function_comment |
    [CONTAINS SQL | READS SQL DATA] }

Parameter

OR REPLACE

Falls angegeben, wird die Funktion mit demselben Namen und derselben Signatur (Anzahl der Parameter und Parametertypen) ersetzt. Sie können eine vorhandene Funktion nicht durch eine andere Signatur ersetzen. Dies ist hauptsächlich nützlich, um den Funktionsrumpf und Rückgabetyp der Funktion zu aktualisieren. Dieser Parameter kann nicht zusammen mit IF NOT EXISTS angegeben werden.
TEMPORARY

Der Bereich der Funktion, die erstellt wird. Wenn Sie TEMPORARY angeben, ist die erstellte Funktion gültig und in der aktuellen Sitzung sichtbar. Es erfolgt kein beständiger Eintrag im Katalog.
IF NOT EXISTS

Falls angegeben, wird die Funktion nur erstellt, wenn sie nicht vorhanden ist. Die Erstellung der Funktion ist erfolgreich (es wird kein Fehler ausgelöst), wenn die angegebene Funktion bereits im System vorhanden ist. Dieser Parameter kann nicht zusammen mit OR REPLACE angegeben werden.
function_name

Ein Name für die Funktion. Für eine permanente Funktion können Sie optional den Funktionsnamen mit einem Schemanamen qualifizieren. Wenn der Name nicht qualifiziert ist, wird die permanente Funktion im aktuellen Schema erstellt.
function_parameter

Gibt einen Parameter der Funktion an.
- parameter_name
  
  Der Parametername muss innerhalb der Funktion eindeutig sein.
- data_type
  
  Jeder unterstützte Datentyp. Für Python wird data_type gemäß dieser Sprachzuordnung in einen Python-Datentyp umgewandelt.
- DEFAULT default_expression
  
  Gilt für: Databricks SQL Databricks Runtime 10.4 LTS und höher
  
  Ein optionaler Standardwert, der verwendet wird, wenn ein Funktionsaufruf dem Parameter kein Argument zuweist. default_expression muss umwandelbar in data_type sein. Der Ausdruck darf nicht auf einen anderen Parameter verweisen oder eine Unterabfrage enthalten.
  
  Wenn Sie einen Standardwert für einen Parameter angeben, müssen alle folgenden Parameter ebenfalls einen Standardwert haben.
  
  DEFAULT wird nur für LANGUAGE SQL unterstützt.
- COMMENT comment
  
  Eine optionale Beschreibung des Parameters. comment muss ein STRING-Literal sein.
RETURNS data_type

Der Rückgabedatentyp der Skalarfunktion. Für Python-UDFs müssen Rückgabewerte genau mit dem in data_typeangegebenen Datentyp übereinstimmen. Andernfalls schlägt die Funktion fehl, um unerwartete Typkonvertierungen zu verhindern.

Für SQL-UDFs ist diese Klausel optional. Der Datentyp wird vom Funktionstext abgeleitet, wenn er nicht bereitgestellt wird.
RETURNS TABLE [ (column_spec [,…] ) ]

Diese Klausel markiert die Funktion als Tabellenfunktion. Optional wird auch die Signatur des Ergebnisses der Tabellenfunktion angegeben. Wenn kein „column_spec” angegeben wird, wird dies aus dem Text der SQL-UDF abgeleitet.

RETURNS TABLE wird nur für LANGUAGE SQL unterstützt.
- column_name
  
  Der Spaltenname muss innerhalb der Signatur eindeutig sein.
- data_type
  
  Jeder unterstützte Datentyp.
- COMMENT column_comment
  
  Eine optionale Beschreibung der Spalte. comment muss ein STRING-Literal sein.
RETURN { expression | query }

Der Rumpf der Funktion. Bei einer Skalarfunktion kann es sich entweder um eine Abfrage oder einen Ausdruck handeln. Bei einer Tabellenfunktion kann es sich nur um eine Abfrage handeln. Folgendes darf nicht Ausdruck enthalten sein:
- Aggregatfunktionen
- Fensterfunktionen
- Rangfolgefunktionen
- Zeilen erzeugende Funktionen wie explode
Innerhalb des Rumpfs der Funktion können Sie auf den Parameter anhand seines unqualifizierten Namens oder durch Qualifizieren des Parameters mit dem Funktionsnamen verweisen.
AS dollar_quoted_definition

dollar_quoted_definition ist die Python-Funktion body, die von zwei übereinstimmenden $[tag]$body$[tag]$eingeschlossen wird. tag kann eine leere Zeichenfolge sein.

Beispiele:
```
$$
  return “Hello world”
$$

$py$
  return "Hello World"
$py$
```
characteristic

Alle characteristic-Klauseln sind optional. Sie können beliebig viele von ihnen in beliebiger Reihenfolge angeben, allerdings jede Klausel nur einmal.
- LANGUAGE SQL oder LANGUAGE PYTHON
  
  Die Sprache der Funktionsimplementierung.
- [NOT] DETERMINISTIC
  
  Gibt an, ob die Funktion deterministisch ist. Eine Funktion ist deterministisch, wenn sie nur ein Ergebnis für eine bestimmte Menge von Argumenten zurückgibt. Sie können eine Funktion als DETERMINISTIC markieren, wenn der Funktionstext nicht deterministisch ist (und umgekehrt). Ein Grund dafür kann darin bestehen, Abfrageoptimierungen wie konstante Faltung oder Abfragezwischenspeicherung zu fördern oder zu vermeiden. Wenn Sie keine Option angeben, wird dies vom Funktionstext abgeleitet.
- COMMENT function_comment
  
  Ein Kommentar für die Funktion. function_comment muss ein STRING-Literal sein.
- CONTAINS SQL oder READS SQL DATA
  
  Gibt an, ob eine Funktion Daten direkt oder indirekt aus einer Tabelle oder Sicht liest. Wenn die Funktion SQL-Daten liest, können Sie CONTAINS SQL nicht angeben. Wenn Sie keine der Klauseln angeben, wird die Eigenschaft aus dem Funktionsrumpf abgeleitet.

Unterstützte Bibliotheken in Python-UDFs

Um Abhängigkeiten zu verwenden, verwenden Sie import <package> innerhalb des Funktionstexts. Siehe zum Beispiel Folgendes:

CREATE FUNCTION […]
AS $$
   import json
   [... (rest of function definition)]
$$

Abhängigkeiten sind auf die Python-Standardbibliothek und die folgenden Bibliotheken beschränkt:

Paket	Version
bleach	4.0.0
chardet	4.0.0
charset-normalizer	2.0.4
defusedxml	0.7.1
googleapis-common-protos	1.56.4
grpcio	1.47.0
grpcio-status	1.47.0
jmespath	0.10.0
joblib	1.1.0
numpy	1.20.3
Packen	21,3
Pandas	1.3.4
patsy	0.5.2
protobuf	4.21.5
pyarrow	7.0.0
pyparsing	3.0.9
Python-dateutil	2.8.2
pytz	2021.3
scikit-learn	0.24.2”
scipy	1.7.1”
setuptools	65.2.0
sechs	1.16.0
threadpoolctl	3.1.0
webencodings	0.5.1
user-agents	2.2.0
cryptography	38.0.4

Beispiele

Erstellen und Verwenden einer SQL-Skalarfunktion
Erstellen und Verwenden einer Funktion, die DEFAULT-Werte verwendet
Erstellen einer SQL-Tabellenfunktion
Ersetzen einer SQL-Funktion
Beschreiben einer SQL-Funktion
Erstellen von Python-Funktionen

Erstellen und Verwenden einer SQL-Skalarfunktion

> CREATE VIEW t(c1, c2) AS VALUES (0, 1), (1, 2);

-- Create a temporary function with no parameter.
> CREATE TEMPORARY FUNCTION hello() RETURNS STRING RETURN 'Hello World!';

> SELECT hello();
  Hello World!

-- Create a permanent function with parameters.
> CREATE FUNCTION area(x DOUBLE, y DOUBLE) RETURNS DOUBLE RETURN x * y;

-- Use a SQL function in the SELECT clause of a query.
> SELECT area(c1, c2) AS area FROM t;
 0.0
 2.0

-- Use a SQL function in the WHERE clause of a query.
> SELECT * FROM t WHERE area(c1, c2) > 0;
 1  2

-- Compose SQL functions.
> CREATE FUNCTION square(x DOUBLE) RETURNS DOUBLE RETURN area(x, x);

> SELECT c1, square(c1) AS square FROM t;
  0  0.0
  1  1.0

-- Create a non-deterministic function
> CREATE FUNCTION roll_dice()
    RETURNS INT
    NOT DETERMINISTIC
    CONTAINS SQL
    COMMENT 'Roll a single 6 sided die'
    RETURN (rand() * 6)::INT + 1;
-- Roll a single 6-sided die
> SELECT roll_dice();
 3

Erstellen und Verwenden einer Funktion, die DEFAULT-Werte verwendet

-- Extend the function to support variable number of sides and dice.
-- Use defaults to support a variable number of arguments
> DROP FUNCTION roll_dice;
> CREATE FUNCTION roll_dice(num_dice  INT DEFAULT 1 COMMENT 'number of dice to roll (Default: 1)',
                            num_sides INT DEFAULT 6 COMMENT 'number of sides per die (Default: 6)')
    RETURNS INT
    NOT DETERMINISTIC
    CONTAINS SQL
    COMMENT 'Roll a number of n-sided dice'
    RETURN aggregate(sequence(1, roll_dice.num_dice, 1),
                     0,
                     (acc, x) -> (rand() * roll_dice.num_sides)::int,
                     acc -> acc + roll_dice.num_dice);

-- Roll a single 6-sided die still works
> SELECT roll_dice();
 3

-- Roll 3 6-sided dice
> SELECT roll_dice(3);
 15

-- Roll 3 10-sided dice
> SELECT roll_dice(3, 10)
 21

-- Roll 3 10-sided dice using named parameter invocation
> SELECT roll_dice(10 => num_sides, num_dice => 3)
 17

-- Create a SQL function with a scalar subquery.
> CREATE VIEW scores(player, score) AS VALUES (0, 1), (0, 2), (1, 2), (1, 5);

> CREATE FUNCTION avg_score(p INT) RETURNS FLOAT
    COMMENT 'get an average score of the player'
    RETURN SELECT AVG(score) FROM scores WHERE player = p;

> SELECT c1, avg_score(c1) FROM t;
 0  1.5
 1  3.5

Erstellen einer SQL-Tabellenfunktion

-- Produce all weekdays between two dates
> CREATE FUNCTION weekdays(start DATE, end DATE)
    RETURNS TABLE(day_of_week STRING, day DATE)
    RETURN SELECT extract(DAYOFWEEK_ISO FROM day), day
             FROM (SELECT sequence(weekdays.start, weekdays.end)) AS T(days)
                  LATERAL VIEW explode(days) AS day
             WHERE extract(DAYOFWEEK_ISO FROM day) BETWEEN 1 AND 5;

-- Return all weekdays
> SELECT weekdays.day_of_week, day
    FROM weekdays(DATE'2022-01-01', DATE'2022-01-14');
  1     2022-01-03
  2     2022-01-04
  3     2022-01-05
  4     2022-01-06
  5     2022-01-07
  1     2022-01-10
  2     2022-01-11
  3     2022-01-12
  4     2022-01-13
  5     2022-01-14

-- Return weekdays for date ranges originating from a LATERAL correlation
> SELECT weekdays.*
    FROM VALUES (DATE'2020-01-01'),
                (DATE'2021-01-01'),
                (DATE'2022-01-01') AS starts(start),
         LATERAL weekdays(start, start + INTERVAL '7' DAYS);
  3     2020-01-01
  4     2020-01-02
  5     2020-01-03
  1     2020-01-06
  2     2020-01-07
  3     2020-01-08
  5     2021-01-01
  1     2021-01-04
  2     2021-01-05
  3     2021-01-06
  4     2021-01-07
  5     2021-01-08
  1     2022-01-03
  2     2022-01-04
  3     2022-01-05
  4     2022-01-06
  5     2022-01-07

Ersetzen einer SQL-Funktion

-- Replace a SQL scalar function.
> CREATE OR REPLACE FUNCTION square(x DOUBLE) RETURNS DOUBLE RETURN x * x;

-- Replace a SQL table function.
> CREATE OR REPLACE FUNCTION getemps(deptno INT)
    RETURNS TABLE (name STRING)
    RETURN SELECT name FROM employee e WHERE e.deptno = getemps.deptno;

-- Describe a SQL table function.
> DESCRIBE FUNCTION getemps;
 Function: default.getemps
 Type:     TABLE
 Input:    deptno INT
 Returns:  id   INT
           name STRING

Hinweis

Sie können eine vorhandene Funktion nicht durch eine andere Signatur ersetzen.

Beschreiben einer SQL-Funktion

> DESCRIBE FUNCTION hello;
 Function: hello
 Type:     SCALAR
 Input:    ()
 Returns:  STRING

> DESCRIBE FUNCTION area;
 Function: default.area
 Type:     SCALAR
 Input:    x DOUBLE
           y DOUBLE
 Returns:  DOUBLE

> DESCRIBE FUNCTION roll_dice;
 Function: default.roll_dice
 Type:     SCALAR
 Input:    num_dice  INT
           num_sides INT
 Returns:  INT

> DESCRIBE FUNCTION EXTENDED roll_dice;
 Function:      default.roll_dice
 Type:          SCALAR
 Input:         num_dice  INT DEFAULT 1 'number of dice to roll (Default: 1)'
                num_sides INT DEFAULT 6 'number of sides per dice (Default: 6)'
 Returns:       INT
 Comment:       Roll a number of m-sided dice
 Deterministic: false
 Data Access:   CONTAINS SQL
 Configs:       ...
 Owner:         the.house@always.wins
 Create Time:   Sat Feb 12 09:29:02 PST 2022
 Body:          aggregate(sequence(1, roll_dice.num_dice, 1),
                      0,
                      (acc, x) -> (rand() * roll_dice.num_sides)::int,
                      acc -> acc + roll_dice.num_dice)

Erstellen von Python-Funktionen

—- Hello World-like functionality using Python UDFs
> CREATE FUNCTION main.default.greet(s STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  AS $$
    def greet(name):
      return "Hello " + name + "!"

    return greet(s) if s else None
  $$

—- Can import functions from std library and environment
> CREATE FUNCTION main.default.isleapyear(year INT)
  RETURNS BOOLEAN
  LANGUAGE PYTHON
  AS $$
    import calendar
    return calendar.isleap(year) if year else None
  $$

—- Must return the correct type. Otherwise will fail at runtime.
> CREATE FUNCTION main.default.a_number()
  RETURNS INTEGER
  LANGUAGE PYTHON
  AS $$
    # does not work: return "10"
    # does not work: return 3.14
    return 10
  $$

—- Deal with exceptions.
> CREATE FUNCTION main.default.custom_divide(n1 INT, n2 INT)
  RETURNS FLOAT
  LANGUAGE PYTHON
  AS $$
    try:
      return n1/n2
    except ZeroDivisionException:
    # in case of 0, we can return NULL.
    return None
  $$