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
指定函数的参数。
-
参数名称在函数中必须是唯一的。
-
支持的任何数据类型。 对于 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
。-
列名在签名中必须是唯一的。
-
支持的任何数据类型。
COMMENT column_comment
列的可选描述。
comment
必须是STRING
文本。
-
RETURN { expression | query }
函数的主体。 对于标量函数,它可以是查询或表达式。 对于表函数,它只能是查询。 表达式不能包含:
在函数的主体中,可以通过参数的非限定名称或用函数名称限定参数来引用参数。
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
$$