本文为您介绍如何通过SQL语言定义函数(SQL Function)在SQL脚本中使用SQL定义的UDF。
背景信息
SQL语言定义函数作为一种用户自定义函数,弥补了MaxCompute只能用Java或Python创建UDF的不足,还扩展了函数类型的参数和匿名函数特性,提升表达业务逻辑的灵活性。您可以通过该函数实现简单功能,提高代码复用率。具体功能如下:
- 支持在SQL脚本中使用SQL定义的UDF,并调用UDF。
- 支持函数类型的参数,调用时可以传入内置函数、UDF或SQL语言定义函数。
- 函数类型的参数可以为匿名函数,调用时可以传入匿名函数。
您可以通过MaxCompute的SQL语言定义函数解决如下问题:
- 代码中通常会存在很多相似部分,维护不方便,且容易出错。如果引入UDF并编写代码后,您还需要进行代码编译(Java)、创建资源和创建函数操作,过程比较繁琐,且性能不如内建函数。示例如下。
select nvl(str_to_map(get_json_object(col, '$.key1')), 'default') as key1, nvl(str_to_map(get_json_object(col, '$.key2')), 'default') as key2, ... nvl(str_to_map(get_json_object(col, '$.keyN')), 'default') as keyN from t;
- 使用类似Java中Lambda表达式的功能,把函数作为参数传给另一个函数。
注意事项
在调用SQL语言定义函数时,为避免出现函数解析失败的问题,请确保读取的数据类型满足SQL语言定义函数的数据类型要求。
在创建、查询、调用、删除SQL自定义函数时,需要具备Function级别的权限。更多Function权限及授权操作,请参见MaxCompute权限。
创建永久SQL语言定义函数
创建永久SQL语言定义函数并存入Meta系统后,所有的查询操作都可以调用该函数,调用函数操作,请参见调用SQL语言定义函数。命令格式如下。
说明 创建永久SQL语言定义函数命令请使用ODPS Script节点运行,详情请参见创建ODPS Script节点。
create sql function <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...])
[returns @<parameter_out> <datatype>]
as [begin]
<function_expression>
[end];
- function_name:必填。新建SQL语言定义函数的名称。函数名称需要唯一,同名函数只能注册一次,不能与系统内建函数同名。您可以通过
list functions;
命令查看项目下的全部函数。 - parameter_in:必填。函数的输入参数。支持函数类型参数或匿名函数参数。函数类型参数,请参见函数类型参数。匿名函数参数,请参见匿名函数参数。
- datatype:必填。参数的数据类型,数据类型请参见2.0数据类型版本。
- returns:必填。函数返回值变量。如果不指定,默认返回function_name的同名变量。
- parameter_out:必填。函数返回参数。
- function_expression:必填。函数表达式。
create sql function my_add(@a BIGINT) as @a + 1;
@a + 1
为函数实现逻辑,可直接写为表达式,支持内置操作符、内建函数和UDF。如果逻辑复杂,可以在定义中使用begin和end,内部可以编写多条语句。returns指定返回值变量,如果不指定,默认返回function_name的同名变量。命令示例如下。
create sql function my_sum(@a BIGINT, @b BIGINT, @c BIGINT) returns @my_sum BIGINT
as begin
@temp := @a + @b;
@my_sum := @temp + @c;
end;
说明 写入多条语句时,需要使用脚本模式,详情请参见SQL脚本模式。
创建临时SQL语言定义函数
如果您不需要把SQL语言定义函数存入MaxCompute的Meta系统,可以使用临时SQL语言定义函数。临时SQL语言定义函数只在当前脚本有效。命令格式如下。
function <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...])
[returns @<parameter_out> <datatype>]
as [begin]
<function_expression>
[end];
详细参数说明,请参见创建永久SQL语言定义函数。说明 写入多条语句时,需要使用脚本模式,详情请参见SQL脚本模式。
命令示例如下。
function my_add(@a BIGINT) as @a + 1;
查询SQL语言定义函数的基本信息
查询SQL语言定义函数的方式与Java UDF或Python UDF保持一致。命令格式如下。
desc function <function_name>;
function_name:已创建的SQL语言定义函数的名称。命令示例如下。
desc function my_add;
返回结果如下。Name my_add
Owner ALIYUN$s***_****@**.aliyunid.com
Created Time 2021-05-08 11:26:02
SQL Definition Text CREATE SQL FUNCTION MY_ADD(@a BIGINT) AS @a + 1
说明 客户端版本需要升级至0.34.0以上。查看客户端版本及获取客户端操作,请参见使用客户端(odpscmd)连接。
删除SQL语言定义函数
删除SQL语言定义函数的方式与Java UDF或Python UDF保持一致。语法格式如下。
drop function <function_name>;
function_name:已创建的SQL语言定义函数的名称。命令示例如下。
drop function my_add;
调用SQL语言定义函数
调用SQL语言定义函数的方式和现有内建函数的调用方式一致。命令示例如下。
select <function_name>(<column_name>[,...]) from <table_name>;
- function_name:已创建的SQL语言定义函数的名称。
- column_name:待查询的目标表的列名称。列的数据类型必须与SQL语言定义函数定义的数据类型保持一致。
- table_name:待查询目标表的名称。
命令示例如下。
--创建目标表src。
create table src (c bigint, d string);
insert into table src values (1,100.1),(2,100.2),(3,100.3);
--调用my_add函数。
select my_add(c) from src;
--返回结果如下。
+------------+
| _c0 |
+------------+
| 2 |
| 3 |
| 4 |
+------------+
函数类型参数
调用SQL语言定义函数时,可以传入内建函数、UDF或SQL语言定义函数。命令示例如下。
function add(@a BIGINT) as @a + 1;
function op(@a BIGINT, @fun function (BIGINT) returns BIGINT) as @fun(@a);
select op(key, add), op(key, abs) from values (1),(2) as t (key);
--返回结果如下。
+------------+------------+
| _c0 | _c1 |
+------------+------------+
| 2 | 1 |
| 3 | 2 |
+------------+------------+
示例中,函数op
定义了2个输入参数。@a
定义一个BIGINT类型数值;@fun
定义一个函数,输入和输出均为BIGINT类型。函数op
将@a
传入@fun
函数,并传入ADD和ABS函数,对@a
进行操作。ABS函数详情请参见数学函数。匿名函数参数
函数类型的参数可以为匿名函数,SQL语言定义函数调用时可以传入匿名函数。命令示例如下。
function op(@a BIGINT, @fun function (BIGINT) returns BIGINT) as @fun(@a);
select op(key, function (@a) as @a + 1) from values (1),(2) as t (key);
示例中,function (@a) as @a + 1
为匿名函数。匿名参数的输入参数@a
不需要指定类型,编译器会根据OP函数的参数定义推导@a
的类型。典型示例
场景:将yyyy-mm-dd
格式的日期转换为yyyymmdd
格式。
假设待转换的日期为:2020-11-21、2020-1-01、2019-5-1和19-12-1。
处理方案如下:
- 方案1:通过创建SQL语言定义函数实现。推荐使用。命令示例如下:
返回结果如下:create sql function y_m_d2yyyymmdd(@y_m_d string) returns @yyyymmdd string as begin @yyyymmdd := concat(lpad(split_part(@y_m_d, '-', 1), 4, '0'), lpad(split_part(@y_m_d, '-', 2), 2, '0'), lpad(split_part(@y_m_d, '-', 3), 2, '0')) ; end; select y_m_d2yyyymmdd(d) from values('2020-11-21'),('2020-1-01'), ('2019-5-1'), ('19-12-1') t (d);
+------------+ | _c0 | +------------+ | 20201121 | | 20200101 | | 20190501 | | 00191201 | +------------+
- 方案2:该方案存在重复调用函数的问题,代码复用率较低,不推荐使用。命令示例如下:
select concat(lpad(split_part(d, '-', 1), 4, '0'), lpad(split_part(d, '-', 2), 2, '0'), lpad(split_part(d, '-', 3), 2, '0')) from values('2020-11-21'),('2020-1-01'), ('2019-5-1'), ('19-12-1') t (d);
说明 写入多条语句时,需要使用脚本模式,详情请参见SQL脚本模式。