CREATE FUNCTION (SQL i Python)

  • Artykuł

Dotyczy:zaznacz pole wyboru oznaczone jako tak Databricks SQL zaznacz pole wyboru oznaczone jako tak Databricks Runtime

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

Dotyczy:zaznacz pole wyboru oznaczone jako tak Databricks SQL zaznacz pole wyboru oznaczone jako tak 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 języka Python wymagają wykazu aparatu Unity w magazynach bezserwerowych lub pro SQL Albo w klastrze wykazu unity współużytkowanego lub pojedynczego użytkownika.

Dotyczy:zaznacz pole wyboru oznaczone jako tak Databricks SQL zaznacz pole wyboru oznaczone jako tak 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 nazwanego parametru.

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

Parametry

  • LUB ZASTĄP

    Jeśli zostanie określona, funkcja o tej samej nazwie i podpisie (liczba parametrów i typów parametrów) zostanie zamieniona. Nie można zastąpić istniejącej funkcji innym podpisem. 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 EXISTSpolecenia .

  • TYMCZASOWE

    Zakres tworzonej funkcji. Po określeniu TEMPORARYparametru utworzona funkcja jest prawidłowa i widoczna w bieżącej sesji. W wykazie nie jest wprowadzany żaden trwały wpis.

  • 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 REPLACEpolecenia .

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

  • function_parameter

    Określa parametr funkcji.

    • parameter_name

      Nazwa parametru musi być unikatowa w ramach funkcji.

    • Data_type

      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.

    • DEFAULT_EXPRESSION DOMYŚLNE

      Dotyczy:zaznacz pole wyboru oznaczone jako tak Databricks SQL zaznacz pole wyboru oznaczone jako tak 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 program jest obsługiwany tylko w przypadku LANGUAGE SQL programu .

    • Komentarz komentarza

      Opcjonalny opis parametru. comment musi być literałem STRING .

  • ZWRACA data_type

    Zwracany typ danych funkcji skalarnej. W przypadku funkcji UDF języka Python zwracane wartości muszą być dokładnie zgodne z typem danych określonym w pliku data_type. W przeciwnym razie, aby zapobiec nieprzewidzianym konwersjom typów, funkcja zakończy się niepowodzeniem.

    W przypadku funkcji zdefiniowanej przez użytkownika SQL ta klauzula jest opcjonalna. Typ danych będzie pochodzić z treści funkcji, jeśli nie zostanie podany.

  • ZWRACA TABELĘ [ (column_spec [,...] ) ]

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

    RETURNS TABLE program jest obsługiwany tylko w przypadku LANGUAGE SQL programu .

    • Column_name

      Nazwa kolumny musi być unikatowa w podpisie.

    • Data_type

      Dowolny obsługiwany typ danych.

    • COLUMN_COMMENT KOMENTARZ

      Opcjonalny opis kolumny. comment musi być literałem STRING .

  • RETURN { zapytanie wyrażenia | }

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

    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 dwie pasujące $[tag]$body$[tag]$wartości . tag może być pustym ciągiem.

    Przykłady:

    $$
  return “Hello world”
$$

$py$
  return "Hello World"
$py$

  • Charakterystyczne

    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ę tak, jak DETERMINISTIC wtedy, gdy jej treść nie jest i na odwrót. 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 opcji ths, pochodzi ona z treści funkcji.

    • FUNCTION_COMMENT KOMENTARZ

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

    • ZAWIERA DANE SQL lub READS 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.

Obsługiwane biblioteki w funkcjach zdefiniowanych przez użytkownika języka Python

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

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

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

Pakiet Wersja
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
stan obiektu grpcio 1.47.0
jmespath 0.10.0
joblib 1.1.0
Numpy 1.20.3
Opakowania 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
Sześć 1.16.0
threadpoolctl 3.1.0
webencodings 0.5.1
agentów użytkowników 2.2.0
Kryptografii 38.0.4

Przykłady

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

Tworzenie i używanie funkcji korzystającej z funkcji DEFAULTs

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