CREATE FUNCTION (SQL и Python)

Статья
04/18/2024

Область применения: проверка помечены да Databricks SQL Databricks Runtime

Создает SQL скалярную или табличную функцию, которая принимает набор аргументов и возвращает скалярное значение или набор строк.

Область применения: проверка помечены да Databricks SQL Databricks Runtime 13.3 LTS и выше

Создает скалярную функцию Python, которая принимает набор аргументов и возвращает скалярное значение.

Для определяемых пользователем пользователей Python требуется каталог Unity в бессерверных или профессиональных хранилищах SQL, а также общий или отдельный кластер каталога Unity.

Область применения: проверка помечены да Databricks SQL Databricks Runtime 14.1 и более поздних версий

Помимо вызова позиционного параметра можно также вызывать SQL и Python UDF с помощью вызова именованных параметров.

Синтаксис

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

Параметры

OR REPLACE

Если этот параметр задан, то функция с тем же именем и сигнатурой (число параметров и типы параметров) заменяется. Нельзя заменить существующую функцию другой сигнатурой. Это в основном полезно для обновления тела функции и типа возвращаемого значения функции. Этот параметр нельзя указать с помощью IF NOT EXISTS.
TEMPORARY

Область создаваемой функции. Если указан параметр TEMPORARY, то созданная функция является допустимой и отображается в текущем сеансе. В каталоге не выполняется постоянная запись.
IF NOT EXISTS

Если этот параметр указан, функция создается только в том случае, если она не существует. Функция успешно создается (ошибки не возникает), если указанная функция уже существует в системе. Этот параметр нельзя указать с помощью OR REPLACE.
function_name

Имя функции. Для постоянной функции можно дополнительно уточнить имя функции с помощью имени схемы. Если имя указано не полностью, таблица создается в текущей схеме.
function_parameter

Задает параметр функции.
- parameter_name
  
  Имя параметра должно быть уникальным в пределах функции.
- data_type
  
  Любой поддерживаемый тип данных. Для Python используется тип данных Python data_type в соответствии с этим сопоставлением языков.
- DEFAULT default_expression
  
  Область применения: Databricks SQL Databricks Runtime 10.4 LTS и выше
  
  Необязательное значение по умолчанию, используемое, когда при вызове функции параметру не присваивается аргумент. default_expression должен приводиться к data_type. Выражение не должно ссылаться на другой параметр или содержать подзапрос.
  
  При указании значения по умолчанию для одного параметра все следующие параметры также должны иметь значение по умолчанию.
  
  DEFAULT поддерживается только для LANGUAGE SQL .
- Комментарий COMMENT
  
  Необязательное описание параметра. comment должен быть литералом STRING .
RETURNS data_type

Тип возвращаемых данных скалярной функции. Для определяемых пользователем пользователей Python возвращаемые значения должны точно соответствовать типу данных, указанному в data_type. В противном случае, чтобы предотвратить непреднамеренные преобразования типов, функция завершится ошибкой.

Для UDF SQL это предложение является необязательным. Тип данных будет производным от тела функции, если он не указан.
RETURNS TABLE [ (column_spec [,...] ]

Это предложение обозначает функцию как функцию таблицы. При необходимости он также указывает сигнатуру результата функции таблицы. Если column_spec не указан, он будет производным от текста UDF SQL.

RETURNS TABLE поддерживается только для LANGUAGE SQL .
- column_name
  
  Имя столбца должно быть уникальным в пределах сигнатуры.
- data_type
  
  Любой поддерживаемый тип данных.
- COMMENT column_comment
  
  Необязательное описание столбца. comment должен быть литералом STRING .
RETURN { expression | query }

Текст функции. Для скалярной функции это может быть либо запрос, либо выражение. Для табличной функции это может быть только запрос. Выражение столбца не может содержать:
- Агрегатные функции
- Функции окна
- Ранжирующие функции
- Функции создания строк, такие как explode
В теле функции можно ссылаться на параметр по его неполному имени или путем уточнения параметра именем функции.
AS dollar_quoted_definition

dollar_quoted_definition — это функция body Python, заключенная в два сопоставления $[tag]$body$[tag]$. tag может быть пустой строкой.

Примеры:
```
$$
  return “Hello world”
$$

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

Все предложения характеристик являются необязательными. Можно указать любое количество предложений в любом порядке, но каждое предложение можно указать только один раз.
- ЯЗЫК SQL или ЯЗЫК PYTHON
  
  Язык реализации функции.
- [NOT] DETERMINISTIC
  
  Является ли функция детерминированной. Функция является детерминированной, если она возвращает только один результат для заданного набора аргументов. Вы можете пометить функцию так, как DETERMINISTIC если его тело не является и наоборот. Причиной этого может быть поощрение или запрет оптимизации запросов, например констант свертывания или кэширования запросов. Если параметр ths не указан, он является производным от тела функции.
- COMMENT function_comment
  
  Комментарий для функции. function_comment должен быть строковым литералом.
- CONTAINS SQL или READS SQL DATA
  
  Необходимо знать, выполняет ли функция чтение данных из таблицы или представления напрямую или косвенно. Если функция считывает данные SQL, нельзя указать CONTAINS SQL. Если ни одно из предложений не указано, свойство является производным от тела функции.

Поддерживаемые библиотеки в пользовательских файлах Python

Чтобы использовать все зависимости, используйте import <package> в теле функции. См. следующий пример:

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

Зависимости ограничены стандартной библиотекой Python и следующими библиотеками:

Пакет	Версия
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
во внешнем виде	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
six	1.16.0
threadpoolctl	3.1.0
webencodings	0.5.1
агенты пользователей	2.2.0
Криптографии	38.0.4

Примеры

Создание и использование скалярной функции SQL
Создание и использование функции, задействующей значения по умолчанию
Создание табличной функции SQL
Замена функции SQL
Описание функции SQL
Создание функций Python

Создание и использование скалярной функции 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

Создание и использование функции, задействующей значения по умолчанию

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

Создание табличной функции 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

Замена функции 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

Примечание.

Нельзя заменить существующую функцию другой сигнатурой.

Описание функции 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)

Создание функций 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
  $$