云原生大数据计算服务 MaxCompute：SQL自定义函数

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语言定义函数并存入Meta系统后，所有的查询操作都可以调用该函数，调用函数操作，请参见调用SQL语言定义函数。命令格式如下。

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语言定义函数存入MaxCompute的Meta系统，可以使用临时SQL语言定义函数。临时SQL语言定义函数只在当前脚本有效。命令格式如下。

function <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...]) 
[returns @<parameter_out> <datatype>] 
as [begin] 
<function_expression> 
[end];

详细参数说明，请参见创建永久SQL语言定义函数。

命令示例如下。

function my_add(@a BIGINT) as @a + 1;

查询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

删除SQL语言定义函数的方式与Java UDF或Python UDF保持一致。语法格式如下。

drop function <function_name>;

function_name：已创建的SQL语言定义函数的名称。

命令示例如下。

drop function my_add;

调用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的类型。

处理方案如下：

• 方案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);