CREATE FUNCTION (SQL und Python)

2025-06-07

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 13.3 LTS und höher

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

Python UDFs erfordern Unity Catalog auf serverlosen oder pro SQL Warehouses oder eine Unity Catalog-fähige Computeressource.

Gilt für: Häkchen ja Databricks SQL Häkchen gesetzt ja Databricks Runtime 14.1 und höher

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

Gilt für: Ja, mit Häkchen markiert Databricks SQL Databricks Runtime 16.2 und höher

Verwenden Sie die ENVIRONMENT Klausel, um die Python-Umgebung für eine Funktion anzugeben, die mit LANGUAGE PYTHON deklariert wurde. Wird verwendet, um benutzerdefinierte Abhängigkeiten zu installieren und die Umgebungsversion festzulegen.

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] |
    DEFAULT COLLATION default_collation_name } |
    environment }

environment
  ENVIRONMENT ( { environment_key = environment_value } [, ...] )

Parameter

ODER ERSETZEN

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 oder eine Prozedur 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.
VORLÄUFIG

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.
WENN NICHT EXISTIERT

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.

Der Funktionsname muss für alle Routinen (Prozeduren und Funktionen) im Schema eindeutig sein.
function_parameter

Gibt einen Parameter der Funktion an.
- parameter_name
  
  Der Parametername muss innerhalb der Funktion eindeutig sein.
- Datentyp
  
  Jeder unterstützte Datentyp. Für Python wird data_type gemäß dieser Sprachzuordnung in einen Python-Datentyp umgewandelt.
  
  Bei einer STRINGdata_type erfolgt die Standardsortierung standardmäßig durch die Funktion default_collation_name.
- STANDARD 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.
- KOMMENTAR Kommentar
  
  Eine optionale Beschreibung des Parameters. comment muss ein STRING-Literal sein.
GIBT data_type zurück.

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.
- Spaltenname
  
  Der Spaltenname muss innerhalb der Signatur eindeutig sein.
- Datentyp
  
  Jeder unterstützte Datentyp.
- KOMMENTAR column_comment
  
  Eine optionale Beschreibung der Spalte. comment muss ein STRING-Literal sein.
RETURN {Ausdruck | Abfrage }

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$
```
charakteristisch

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.
- [NICHT] DETERMINISTISCH
  
  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.
- KOMMENTAR 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 beiden Klauseln angeben, wird die Eigenschaft vom Funktionskörper abgeleitet.
- STANDARDSORTIERUNG default_collation_name
  
  Gilt für: Databricks Runtime 17.0 und höher
  
  Definiert die standardmäßige Sortierung, die für Folgendes verwendet werden soll:
  - STRING Parameter und Datentyp RETURNS und RETURNS TABLE Felder der Funktion.
  - DEFAULT Ausdruck.
  - Der Körper der SQL-Funktion.
  Wenn nicht angegeben, ist UTF8_BINARY die Standardsortierung.
Umgebung

Gibt die Python-Umgebung für eine Funktion an, die mit LANGUAGE PYTHONdeklariert ist. Die ENVIRONMENT Klausel wird für SQL-Funktionen nicht unterstützt.
- abhängigkeiten
  
  Ein JSON-Array von Zeichenfolgen, die die erforderlichen Python-Pakete oder Raddateien für die Funktion angeben. Der dependencies Schlüssel ist nicht case-sensitiv. Unterstützte Formate:
  - PyPI-Paket mit optionaler Version, z. B.: "simplejson==3.19.3"
  - Absoluter Pfad zu einer Wheel-Datei, die in einem Unity Catalog-Volume gespeichert ist, z. B. "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl"
  - HTTPS-URL zu einer Wheel-Datei im externen Speicher, z. B.: "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=..."
- environment_version
  
  Eine Zeichenfolge, die die Python-Umgebungsversion angibt. Verwenden Sie None, um die Standard-Python-Umgebung zu nutzen. Wenn sie nicht angegeben wird, wird die Standardumgebung verwendet.
  - Derzeit wird nur der Wert None unterstützt.

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)]
$$

Standardmäßig sind Abhängigkeiten auf die Standardmäßige Python-Bibliothek und die folgenden Bibliotheken beschränkt:

Paket	Version
Bleichmittel	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
Sündenbock	0.5.2
protobuf	4.21.5
Pyarrow	7.0.0
Pyparsing	3.0.9
Python-dateutil	2.8.2
Pytz	2021.3
scikit-lernen	0.24.2”
SciPy	1.7.1”
setuptools	65.2.0
sechs	1.16.0
Threadpoolctl	3.1.0
Webkodierungen	0.5.1
Benutzeragenten	2.2.0
Kryptographie	38.0.4

Benutzerdefinierte Abhängigkeiten in Python UDFs

Wenn Sie zusätzliche Abhängigkeiten über die Standardbibliothek und die unterstützten integrierten Pakete hinaus verwenden möchten, geben Sie diese in der ENVIRONMENT Klausel an.

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
Definieren von benutzerdefinierten Abhängigkeiten in 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;
 1.0
 1.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
  $$

Definieren von benutzerdefinierten Abhängigkeiten in Python-Funktionen

-- Create a Python function with additional dependencies using the ENVIRONMENT clause.
> CREATE FUNCTION main.default.dump_json(data STRING)
    RETURNS STRING
    LANGUAGE PYTHON
    ENVIRONMENT (
      dependencies = '["simplejson==3.19.3", "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl", "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=2043167927&Signature=abcd"]',
      environment_version = 'None'
    )
    AS $$
      import simplejson as json
      import custom_package
      return json.dumps(custom_package.process(data))
    $$;

-- Use the Python function in a query.
> SELECT dump_json('{"foo": "bar"}');

Freigeben über