CREATE FUNCTION(SQL 和 Python)

适用于:勾选“是” Databricks SQL 勾选“是” Databricks Runtime

创建 SQL 标量或表函数,该函数采用一组参数并返回标量值或一组行。

适用于:勾选“是” Databricks SQL 勾选“是” Databricks Runtime 13.3 LTS 及更高版本

创建一个 Python 标量函数,它采用一组参数并返回一个标量值。

Python UDF 要求在无服务器或 pro SQL 仓库或者共享或单用户 Unity Catalog 群集上使用 Unity Catalog。

适用于:勾选“是” 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,data_type 根据此语言映射转换为 Python 数据类型。

    • DEFAULT default_expression

      适用于:勾选“是” Databricks SQL 勾选“是” Databricks Runtime 10.4 LTS 及更高版本

      函数调用未向参数分配参数时使用的可选默认值。 default_expression 必须可转换data_type。 表达式不得引用另一个参数或包含子查询。

      为一个参数指定默认值时,以下所有参数也必须具有默认值。

      LANGUAGE SQL 仅支持 DEFAULT

    • COMMENT 注释

      参数的可选说明。 comment 必须是 STRING 文本。

  • RETURNS data_type

    标量函数的返回数据类型。 对于 Python UDF,返回值必须与 data_type 中指定的数据类型完全匹配。 否则,该函数将无法阻止意外的类型转换。

    对于 SQL UDF,此子句是可选的。 如果未提供数据类型,将从函数体派生该数据类型。

  • RETURNS TABLE [ (column_spec [,…] ) ]

    此子句将函数标记为表函数。 (可选)它还指定表函数的结果的签名。 如果未指定 column_spec,它将派生自 SQL UDF 的主体。

    LANGUAGE SQL 仅支持 RETURNS TABLE

    • column_name

      列名在签名中必须是唯一的。

    • data_type

      支持的任何数据类型。

    • COMMENT column_comment

      列的可选描述。 comment 必须是 STRING 文本。

  • RETURN { expression | query }

    函数的主体。 对于标量函数,它可以是查询或表达式。 对于表函数,它只能是查询。 表达式不能包含:

    在函数的主体中,可以通过参数的非限定名称或用函数名称限定参数来引用参数。

  • AS dollar_quoted_definition

    dollar_quoted_definition 是由两个匹配的 $[tag]$body$[tag]$ 括起来的 Python 函数 bodytag 可以为空字符串。

    示例:

    $$
      return "Hello world"
    $$
    
    $py$
      return "Hello World"
    $py$
    
  • 特征

    所有特性子句都是可选的。 可按任意顺序指定任意数量的子句,但每个子句只能指定一次。

    • LANGUAGE SQLLANGUAGE PYTHON

      函数实现的语言。

    • [NOT] DETERMINISTIC

      函数是否具有确定性。 如果某函数仅为一组给定参数返回一个结果,则该函数具有确定性。 当函数体不为 DETERMINISTIC 时,可以将函数标记为 DETERMINISTIC,反之亦然。 这样做的原因可以是为了鼓励或阻止查询优化,例如常量折叠或查询缓存。 如果未指定此选项,则它派生自函数体。

    • COMMENT function_comment

      函数的注释。 function_comment 必须是字符串文字。

    • CONTAINS SQL 或 READS SQL DATA

      函数直接或间接从表或视图中读取数据。 当函数读取 SQL 数据时,不能指定 CONTAINS SQL。 如果未指定任一子句,则属性派生自函数主体。

Python UDF 中支持的库

若要使用任何依赖项,请在函数体中使用 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
6 1.16.0
threadpoolctl 3.1.0
webencodings 0.5.1
user-agents 2.2.0
密码系统 38.0.4

示例

创建和使用 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

创建和使用使用 DEFAULT 的函数

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