CREATE FUNCTION（SQL 和 Python）

项目
12/11/2023

适用于： check marked yes Databricks SQL Databricks Runtime 9.1 及更高版本

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

适用于： check marked yes Databricks SQL Databricks Runtime 13.2 及更高版本

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

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

适用于： check marked yes 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 及更高版本
  
  函数调用未向参数分配参数时使用的可选默认值。 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 }

函数的主体。对于标量函数，它可以是查询或表达式。对于表函数，它只能是查询。表达式不能包含：
- 聚合函数
- 开窗函数
- 排名函数
- 行生成函数，例如 explode
在函数的主体中，可以通过参数的非限定名称或用函数名称限定参数来引用参数。
AS dollar_quoted_definition

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

示例：
```
$$
  return "Hello world"
$$

$py$
  return "Hello World"
$py$
```
特征

所有特性子句都是可选的。可按任意顺序指定任意数量的子句，但每个子句只能指定一次。
- LANGUAGE SQL 或 LANGUAGE 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
  $$