CREATE FUNCTION (SQL и Python)

Область применения: 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 с помощью вызова именованных параметров.

применяется: Databricks SQL Databricks Runtime 16.2 и выше

Используйте оператор ENVIRONMENT, чтобы указать среду Python для функции, объявленной с помощью LANGUAGE PYTHON. Используется для установки пользовательских зависимостей и задания версии среды.

Синтаксис

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

Параметры

• ИЛИ ЗАМЕНИТЬ

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

• ВРЕМЕННЫЙ

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

• ЕСЛИ НЕ СУЩЕСТВУЕТ

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

• function_name

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

  Имя функции должно быть уникальным для всех подпрограмм (процедур и функций) в схеме.

• function_parameter

  Задает параметр функции.

  • parameter_name

    Имя параметра должно быть уникальным в пределах функции.

  • тип_данных

    Любой поддерживаемый тип данных. Для Python data_type преобразуется в тип данных Python в соответствии с этим языковым отображением.

    Для STRINGdata_type используются параметры сортировки по умолчанию функции default_collation_name.

  • ПО УМОЛЧАНИЮ default_expression

    Область применения: Databricks SQL Databricks Runtime 10.4 LTS и выше

    Необязательное значение по умолчанию, используемое, когда при вызове функции параметру не присваивается аргумент. default_expression должен приводиться к data_type. Выражение не должно ссылаться на другой параметр или содержать подзапрос.

    При указании по умолчанию для одного параметра все следующие параметры также должны иметь значение по умолчанию.

    DEFAULT поддерживается только для LANGUAGE SQL .

  • Комментарий COMMENT

    Необязательное описание параметра. comment должен быть литералом STRING.

• ВОЗВРАЩАЕТ data_type

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

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

• ВОЗВРАЩАЕТ TABLE [ (column_spec [,...] ) ]

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

  RETURNS TABLE поддерживается только для LANGUAGE SQL .

  • название_столбца

    Имя столбца должно быть уникальным в сигнатуре.

  • тип_данных

    Любой поддерживаемый тип данных.

  • КОММЕНТАРИЙ column_comment

    Необязательное описание столбца. comment должен быть литералом STRING.

• RETURN { выражение | запрос }

  Текст функции. Для скалярной функции это может быть либо запрос, либо выражение. Для функции таблицы это может быть только запрос. Выражение не может содержать:

  • Агрегатные функции
  • Функции окна
  • Ранжирующие функции
  • Функции создания строк, такие как explode

  В теле функции можно ссылаться на параметр по его неполному имени или путем уточнения параметра именем функции.

• AS dollar_quoted_definition

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

  Примеры:

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

• характеристика

  Все предложения характеристик являются необязательными. Можно указать любое количество предложений в любом порядке, но каждое предложение можно указать только один раз.

  • ЯЗЫК SQL или ЯЗЫК PYTHON

    Язык реализации функции.

  • [НЕ] ДЕТЕРМИНИРОВАННО

    Является ли функция детерминированной. Функция детерминирована, если она возвращает только один результат для заданного набора аргументов. Вы можете пометить функцию как DETERMINISTIC, когда ее тело не является таковым, и наоборот. Причиной этого может быть поощрение или запрет оптимизаций запросов, таких как свертывание констант или кэширование запросов. Если параметр ths не указан, он является производным от тела функции.

  • КОММЕНТАРИЙ function_comment

    Комментарий к функции. function_comment должен быть строковым литералом.

  • СОДЕРЖИТ SQL или ЧИТАЕТ ДАННЫЕ SQL

    Функция считывает данные напрямую или косвенно из таблицы или представления. Если функция считывает данные SQL, нельзя указать CONTAINS SQL. Если вы не указываете ни одно предложение, свойство является производным от тела функции.

  • СОРТИРОВКА ПО УМОЛЧАНИЮ default_collation_name

    Область применения: Databricks Runtime 17.0 и выше

    Определяет параметры сортировки по умолчанию, которые следует использовать для:

    • STRING параметры, RETURNS тип данных и RETURNS TABLE поля функции.
    • DEFAULT выражение.
    • Текст функции SQL.

    Если не указано, сортировка по умолчанию – UTF8_BINARY.

• среда

  Указывает среду Python для функции, объявленной с помощью LANGUAGE PYTHON. Использование предложения ENVIRONMENT не поддерживается для функций SQL.

  • Зависимости

    Массив строк JSON, указывающий необходимые пакеты Python или файлы колесика для функции. Ключ dependencies не учитывает регистр. Поддерживаемые форматы:

    • Пакет PyPI с необязательной версией, например "simplejson==3.19.3"
    • Абсолютный путь к wheel-файлу, хранящемуся в томе Unity Catalog, например: "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl".
    • URL-адрес HTTPS для файла колеса во внешнем хранилище, например "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=..."

  • environment_version

    Строка, указывающая версию среды Python. Используется None для использования среды Python по умолчанию. Если опущено, используется среда по умолчанию.

    • В настоящее время поддерживается только значение None .

Поддерживаемые библиотеки в Python UDFs

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

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

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

Пользовательские зависимости в определяемых пользователем серверах Python

Чтобы использовать дополнительные зависимости за пределами стандартной библиотеки и поддерживаемых встроенных пакетов, укажите их в предложении ENVIRONMENT .

Примеры

• Создание и использование скалярной функции SQL
• Создание и использование функции, задействующей значения по умолчанию
• Создание функции таблицы SQL
• Замена функции SQL
• Описание функции SQL
• Создание функций Python
• Определение пользовательских зависимостей в функциях 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;
 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

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

-- 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:         [email protected]
 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
  $$

Определение пользовательских зависимостей в функциях 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"}');

Связанные статьи

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