CREATE FUNCTION (SQL i Python)

Dotyczy: Databricks SQL Databricks Runtime

Tworzy funkcję skalarną lub tabelę SQL, która przyjmuje zestaw argumentów i zwraca wartość skalarną lub zestaw wierszy.

Dotyczy: Databricks SQL Databricks Runtime 13.3 LTS i nowsze

Tworzy funkcję skalarną języka Python, która przyjmuje zestaw argumentów i zwraca wartość skalarną.

Funkcje zdefiniowane przez użytkownika w języku Python wymagają Unity Catalog na bezserwerowych lub profesjonalnych magazynach SQL, albo zasobie obliczeniowym z obsługą Unity Catalog.

Dotyczy: Databricks SQL Databricks Runtime 14.1 i nowsze

Oprócz wywołania parametru pozycyjnego można również wywołać funkcję UDF SQL i Python przy użyciu wywołania parametru nazwanego.

Dotyczy: Databricks SQL Databricks Runtime 16.2 lub nowsze

Użyj klauzuli ENVIRONMENT, aby określić środowisko Pythona dla funkcji zadeklarowanej przy użyciu LANGUAGE PYTHON. Służy do instalowania niestandardowych zależności i ustawiania wersji środowiska.

Składnia

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 } [, ...] )

Parametry

• LUB ZASTĄP

  Jeśli zostanie określona, funkcja o tej samej nazwie i sygnaturze (liczbie i typach parametrów) zostanie zamieniona. Nie można zastąpić istniejącej funkcji innym podpisem lub procedurą. Jest to głównie przydatne do aktualizowania treści funkcji i zwracanego typu funkcji. Nie można określić tego parametru za pomocą IF NOT EXISTS.

• TYMCZASOWY

  Zakres tworzonej funkcji. Po określeniu TEMPORARYparametru utworzona funkcja jest prawidłowa i widoczna w bieżącej sesji. W katalogu nie wprowadza się trwałych wpisów.

• JEŚLI NIE ISTNIEJE

  Jeśli zostanie określony, tworzy funkcję tylko wtedy, gdy nie istnieje. Utworzenie funkcji powiedzie się (nie zostanie zgłoszony błąd), jeśli określona funkcja już istnieje w systemie. Nie można określić tego parametru za pomocą OR REPLACE.

• function_name

  Nazwa funkcji. W przypadku funkcji trwałej można opcjonalnie zakwalifikować nazwę funkcji przy użyciu nazwy schematu. Jeśli nazwa nie jest kwalifikowana, funkcja stała zostanie utworzona w bieżącym schemacie.

  Nazwa funkcji musi być unikatowa dla wszystkich procedur (procedur i funkcji) w schemacie.

• function_parameter

  Określa parametr funkcji.

  • parameter_name

    Nazwa parametru musi być unikatowa w ramach funkcji.

  • typ_danych

    Dowolny obsługiwany typ danych. W przypadku języka Python data_type jest rzutowane na typ danych języka Python zgodnie z tym mapowaniem języka.

    W przypadku elementu STRINGdata_typesortowanie domyślne to funkcja default_collation_name.

  • DOMYŚLNE default_expression

    Dotyczy: Databricks SQL Databricks Runtime 10.4 LTS i nowsze

    Opcjonalna wartość domyślna, która ma być używana, gdy wywołanie funkcji nie przypisuje argumentu do parametru. default_expression musi być rzutowalny na data_type. Wyrażenie nie może odwoływać się do innego parametru lub zawierać podzapytania.

    Po określeniu wartości domyślnej dla jednego parametru wszystkie następujące parametry muszą mieć również wartość domyślną.

    DEFAULT obsługiwane jest tylko dla LANGUAGE SQL.

  • Komentarz komentarz

    Opcjonalny opis parametru. comment musi być wartością literałową STRING.

• ZWRACA data_type

  Zwracany typ danych funkcji skalarnej. W przypadku funkcji użytkownika w Pythonie zwracane wartości muszą dokładnie odpowiadać typowi danych określonemu w data_type. W przeciwnym razie, aby zapobiec nieprzewidzianym konwersjom typów, funkcja zakończy się niepowodzeniem.

  Dla funkcji SQL UDF ta klauzula jest opcjonalna. Typ danych będzie pochodzić z treści funkcji, jeśli nie zostanie podany.

• ZWRACA TABLE [ (column_spec [,...] ) ]

  Ta klauzula oznacza funkcję jako funkcję tabeli. Może również określać sygnaturę wyniku funkcji tabelarycznej. Jeśli nie określono column_spec, zostanie on uzyskany z treści funkcji zdefiniowanej przez użytkownika SQL.

  RETURNS TABLE obsługiwane jest tylko dla LANGUAGE SQL.

  • nazwa_kolumny

    Nazwa kolumny musi być unikatowa w podpisie.

  • typ_danych

    Dowolny obsługiwany typ danych.

  • KOMENTARZ column_comment

    Opcjonalny opis kolumny. comment musi być wartością literałową STRING.

• RETURN { wyrażenie | zapytanie }

  Treść funkcji. W przypadku funkcji skalarnej może to być zapytanie lub wyrażenie. W przypadku funkcji tabeli może to być tylko zapytanie. Wyrażenie nie może zawierać:

  • Funkcje agregujące
  • funkcje okna
  • Funkcje klasyfikowania
  • Funkcje tworzenia wierszy, takie jak explode

  W treści funkcji można odwołać się do parametru za pomocą jego niekwalifikowanej nazwy lub kwalifikując parametr z nazwą funkcji.

• AS dollar_quoted_definition

  dollar_quoted_definition to funkcja body języka Python ujęta w dwa pasujące $[tag]$body$[tag]$. tag może być pustym ciągiem.

  Przykłady:

  $$
    return “Hello world”
  $$
  
  $py$
    return "Hello World"
  $py$
  

• charakterystyczny

  Wszystkie klauzule charakterystyczne są opcjonalne. Można określić dowolną liczbę z nich w dowolnej kolejności, ale można określić każdą klauzulę tylko raz.

  • JĘZYK SQL lub JĘZYK PYTHON

    Język implementacji funkcji.

  • [NIE] DETERMINISTYCZNY

    Czy funkcja jest deterministyczna. Funkcja jest deterministyczna, gdy zwraca tylko jeden wynik dla danego zestawu argumentów. Możesz oznaczyć funkcję jako DETERMINISTIC, gdy jej treść nie jest oznaczona, i odwrotnie. Przyczyną może być zachęcanie lub zniechęcanie do optymalizacji zapytań, takich jak stałe składanie lub buforowanie zapytań. Jeśli nie określisz tej opcji, zostanie ona wyprowadzona z treści funkcji.

  • FUNCTION_COMMENT KOMENTARZ

    Komentarz dla funkcji. function_comment musi być literałem ciągu znaków.

  • ZAWIERA SQL lub CZYTA DANE SQL

    Określa, czy funkcja odczytuje dane bezpośrednio, czy pośrednio z tabeli, czy widoku. Gdy funkcja odczytuje dane SQL, nie można określić elementu CONTAINS SQL. Jeśli nie określisz żadnej klauzuli, właściwość pochodzi z treści funkcji.

  • DOMYŚLNE SORTOWANIE default_collation_name

    Dotyczy: Databricks Runtime 17.0 lub nowszy

    Definiuje sortowanie domyślne do użycia w następujących celach:

    • STRING parametry i RETURNS typ danych oraz RETURNS TABLE pola funkcji.
    • DEFAULT wyrażenie.
    • Treść funkcji SQL.

    Jeśli nie zostanie określony, sortowanie domyślne to UTF8_BINARY.

• środowisko

  Określa środowisko Python dla funkcji deklarowanej za pomocą LANGUAGE PYTHON. Klauzula ENVIRONMENT nie jest obsługiwana w przypadku funkcji SQL.

  • Zależności

    Tablica JSON ciągów określająca wymagane pakiety języka Python lub pliki wheel dla funkcji. Klucz dependencies jest niewrażliwy na wielkość liter. Obsługiwane formaty:

    • Pakiet PyPI z opcjonalną wersją, na przykład: "simplejson==3.19.3"
    • Ścieżka bezwzględna do pliku wheel przechowywanego w woluminie Unity Catalog, na przykład: "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl"
    • Adres URL HTTPS do pliku wheel w magazynie zewnętrznym, na przykład: "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=..."

  • environment_version

    Ciąg określający wersję środowiska języka Python. Użyj None polecenia , aby użyć domyślnego środowiska języka Python. W przypadku pominięcia zostanie użyte środowisko domyślne.

    • Obecnie obsługiwana jest tylko wartość None .

Obsługiwane biblioteki w funkcjach zdefiniowanych przez użytkownika Pythona

Aby użyć dowolnych zależności, użyj import <package> w treści funkcji. Zobacz na przykład następujące kwestie:

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

Domyślnie zależności są ograniczone do standardowej biblioteki języka Python i następujących bibliotek:

Pakiet	Wersja
wybielacz	4.0.0
chardet	4.0.0
normalizator zestawu znaków	2.0.4
defusedxml (biblioteka zabezpieczająca przetwarzanie XML)	0.7.1
googleapis-common-protos	1.56.4
grpcio	1.47.0
status grpcio	1.47.0
jmespath	0.10.0
joblib	1.1.0
numpy	1.20.3
opakowanie	21,3
Pandas	1.3.4
Patsy	0.5.2
Protobuf (w skrócie od Protocol Buffers)	4.21.5
pyarrow	7.0.0
pyparsing – biblioteka do przetwarzania tekstu w Pythonie	3.0.9
python-dateutil (biblioteka Pythona do zarządzania datami)	2.8.2
pytz (biblioteka Pythona do obliczeń stref czasowych)	2021.3
scikit-learn	0.24.2”
scipy (biblioteka naukowa dla Pythona)	1.7.1”
setuptools	65.2.0
Sześć	1.16.0
threadpoolctl	3.1.0
kodowania sieciowe	0.5.1
agentów użytkowników	2.2.0
kryptografia	38.0.4

Niestandardowe zależności w funkcjach użytkownika języka Python

Aby korzystać z dodatkowych zależności poza biblioteką standardową i obsługiwanymi wbudowanymi pakietami, określ je w klauzuli ENVIRONMENT .

Przykłady

• Tworzenie i używanie funkcji skalarnej SQL
• Tworzenie i używanie funkcji korzystającej z funkcji DEFAULTs
• Tworzenie funkcji tabeli SQL
• Zastępowanie funkcji SQL
• Opisywanie funkcji SQL
• Tworzenie funkcji języka Python
• Definiowanie niestandardowych zależności w funkcjach języka Python

Tworzenie i używanie funkcji skalarnej SQL

> 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

Tworzenie i używanie funkcji korzystającej z wartości domyślnych

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

Tworzenie funkcji tabeli SQL

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

Zastępowanie funkcji SQL

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

Uwaga

Nie można zastąpić istniejącej funkcji innym podpisem.

Opisywanie funkcji SQL

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

Tworzenie funkcji języka Python

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

Definiowanie niestandardowych zależności w funkcjach języka Python

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

Powiązane artykuły

• DROP FUNCTION
• SHOW FUNCTIONS
• DESCRIBE FUNCTION
• GRANT
• REVOKE