MaxCompute：UDF（SQL自訂函數）

功能介紹

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函數。

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的函數實現邏輯。

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語言定義函數的方式與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語言定義函數的方式和現有內建函數的調用方式一致。

• 注意事項

  • 調用永久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語言定義函數的方式與Java UDF或Python UDF保持一致。

• 文法格式

  drop function <function_name>;

  function_name：已建立的SQL語言定義函數的名稱。

• 範例程式碼

  drop function my_add;

建立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的入參為函數類型時，參數也可以為匿名函數。命令樣本如下。

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的類型。

處理方案如下：

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