UDF(SQL自定义函数)

MaxCompute支持直接通过SQL语言定义函数,简化部分简单UDF的定义使用流程。本文为您介绍如何通过SQL语言定义函数(SQL Function)并使用SQL定义的UDF。

背景信息

功能介绍

SQL语言定义函数作为一种用户自定义函数(UDF),弥补了MaxCompute只能用Java或Python创建UDF的不足,还扩展了UDF入参的参数类型,可支持函数类型的入参参数,提升表达业务逻辑的灵活性。您可以通过该函数实现简单功能,提高代码复用率。具体功能如下:

  • 支持在SQL脚本中使用SQL语言来定义UDF,并直接调用UDF。

    • 支持通过SQL定义永久UDF,即函数定义完成后,您可以在MaxCompute的函数列表中查询到此函数,并可在任意可使用UDF的环境中使用此UDF。详情请参见下文的定义SQL UDF:创建永久SQL UDF

    • 支持通过SQL定义临时UDF,即在SQL脚本模式中定义好此UDF后,函数不会注册至MaxCompute的函数列表中,仅支持在当前定义UDF的SQL脚本中直接调用,其他环境无法调用此UDF,详情请参见下文的定义SQL UDF:创建临时SQL UDF

  • 使用SQL定义UDF时,UDF的入参支持设置为函数类型的参数,函数类型的入参可包括MaxCompute内置函数、其他UDF或匿名函数。详情请参见下文的SQL UDF示例:入参为函数创建SQL UDF示例:入参为匿名函数

应用场景

您可以通过MaxCompute的SQL语言定义函数解决如下问题:

  • 代码中通常会存在很多相似部分,维护不方便,且容易出错。如果引入Java或Python开发的UDF,在完成编写代码后,您还需要进行代码编译(Java)、创建资源和创建函数操作,过程比较繁琐,且性能较差。

    例如如下的查询操作,您可通SQL语言定义一个UDF,可提高UDF定义应用的效率和灵活性。

    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;
  • 由于SQL定义的UDF支持使用函数作为UDF入参,因此可实现类似Lambda表达式的功能,把函数作为参数传给另一个函数。

    说明

    当前Lambda表达式在MaxCompute中的应用注意事项请参见Lambda函数

注意事项

  • 使用SQL定义UDF时,您需要使用SQL脚本模式进行操作,普通SQL编辑模式可能会导致定义报错的问题。

    说明

    MaxCompute的SQL脚本模式介绍及使用指导请参见SQL脚本模式

  • 使用SQL定义UDF时,UDF入参的参数类型需为MaxCompute支持的数据类型,支持的数据类型请参见2.0数据类型版本;在UDF创建完成后,在调用SQL UDF时,请确保调用时的入参与定义UDF的入参参数类型保持一致。

  • 在创建、查询、调用、删除SQL自定义函数时,操作的阿里云账号需要具备Function级别的权限。更多Function权限及授权操作,请参见MaxCompute权限

定义SQL UDF:创建永久SQL UDF

MaxCompute支持通过create sql function命令创建SQL UDF,且通过此命令创建的UDF为永久SQL UDF,即创建完成后会存入MaxCompute的Meta系统后(可在MaxCompute的函数列表中查询到此UDF),后续所有的查询操作都可以调用该函数。

  • 注意事项

    请使用SQL 脚本模式创建SQL UDF,否则可能会出现创建SQL UDF失败的情况。SQL脚本模式的介绍及使用详情请参见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:必填。函数的输入参数。入参支持函数类型参数(包括匿名函数)。入参为函数类型的示例详情请参见SQL UDF示例:入参为函数,匿名函数类型的入参示例详情请参见创建SQL UDF示例:入参为匿名函数

    • datatype:必填。定义函数的输入参数的数据类型,支持的数据类型请参见2.0数据类型版本

    • returns:可选。定义UDF的返回值变量。如果不指定,默认返回function_name的同名变量。

    • parameter_out:必填。定义UDF的返回参数。

    • function_expression:必填。定义UDF的表达式(实现逻辑)。

  • 示例代码

    • UDF逻辑简单时,示例如下。

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

      @a + 1为函数实现逻辑,可直接写为表达式,支持内置操作符、内建函数和UDF。

    • UDF逻辑复杂时,可以在定义中使用begin和end来标注UDF表达式的内容范围,在begin和end内可以编写多条语句作为UDF的表达式,示例如下。

      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;

      其中

      • returns指定返回值变量,如果不指定,默认返回function_name的同名变量。

      • begin和end内的两行表达式即为SQL UDF的函数实现逻辑。

定义SQL UDF:创建临时SQL UDF

MaxCompute支持通过function命令创建SQL UDF,且通过此命令创建的UDF为临时SQL UDF,即创建完成后不会存入MaxCompute的Meta系统,因此无法在MaxCompute的函数列表中查询到此UDF,后续仅支持在当前SQL脚本中调用此临时UDF,其他环境中无法直接调用此UDF。

  • 注意事项

    请使用SQL 脚本模式创建SQL UDF,否则可能会出现创建SQL UDF失败的情况。SQL脚本模式的介绍及使用详情请参见SQL脚本模式

  • 命令格式

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

    详细参数说明,请参见定义SQL UDF:创建永久SQL UDF

  • 示例代码

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

查询SQL UDF基本信息

查询SQL语言定义函数的方式与Java UDF或Python UDF保持一致。

  • 注意事项

    • 使用客户端查询时,客户端版本需要升级至0.34.0以上。查看客户端版本及获取客户端操作,请参见使用本地客户端(odpscmd)连接

    • 仅使用create sql function命令创建的永久SQL UDF会存储在MaxCompute中,临时SQL UDF不会存储,即无法查询到临时SQL 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 UDF

调用SQL语言定义函数的方式和现有内建函数的调用方式一致。

  • 注意事项

    • 调用永久SQL UDF时,函数已存储在MaxCompute,您可以在任何环节中调用。

    • 调用临时SQL UDF时,必须在定义SQL UDF的同个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语言定义函数的方式与Java UDF或Python UDF保持一致。

  • 语法格式

    drop function <function_name>;

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

  • 示例代码

    drop function my_add;

SQL UDF示例:入参为函数

创建SQL UDF时,可以设置UDF的传入参数类型为函数,包括MaxCompute的内建函数、UDF或SQL UDF。后续调用此SQL UDF时传入函数类型的入参即可,示例如下。

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

示例中定义了两个SQL UDF:

  • 函数add的入参为一个BIGINT类型的参数。

  • 函数op定义了2个输入参数。

    • 一个为BIGINT类型的入参@a

    • 另一个为函数类型的入参@fun,且@fun的输入和输出均为BIGINT类型。函数op@a传入@fun函数。

  • 调用函数op时,传入ADD(自定义SQL UDF)和ABS函数(MaxCompute内置函数),对@a进行操作。ABS函数详情请参见数学函数

创建SQL UDF示例:入参为匿名函数

当SQL UDF的入参为函数类型时,参数也可以为匿名函数。命令示例如下。

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为匿名函数,作为SQL UDF op的入参。匿名函数的入参为@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);