MaxCompute:SQL UDF

機能の説明

SQL UDFは、MaxComputeがJavaまたはPythonのみを使用してUDFを作成し、関数型の入力パラメーターをサポートできるという問題を解決するのに役立ちます。 SQL UDFは、ビジネスロジックの表現の柔軟性を向上させます。 SQL UDFを使用して、単純な機能を実装し、コードの再利用率を向上させることができます。 SQL UDFには次の機能があります。

• SQLスクリプトを使用してUDFを定義し、呼び出すことができます。

  • SQL文を使用して、永続的なSQL UDFを定義できます。 永続的なSQL UDFを定義した後、MaxComputeの関数リストでUDFを照会できます。 UDFを使用できる環境でもUDFを使用できます。 詳細については、「永続的なSQL UDFの作成」をご参照ください。

  • SQL文を使用して、一時的なSQL UDFを定義できます。 SQLスクリプトモードでUDFを定義した後、UDFはMaxComputeの関数リストに登録されません。 UDFが定義されているSQLスクリプトでのみ、UDFを直接呼び出すことができます。 他の環境ではUDFを呼び出すことはできません。 詳細については、「一時的なSQL UDFの作成」をご参照ください。

• SQL文を使用してUDFを定義する場合は、UDFの関数型の入力パラメーターを使用できます。 関数型の入力パラメーターには、MaxComputeの組み込み関数、その他のUDF、および匿名関数が含まれます。 詳細については、「入力パラメーターが関数型であるSQL UDFを作成する方法の例」および「入力パラメーターが匿名関数であるSQL UDFを作成する方法の例」をご参照ください。

シナリオ

MaxComputeのSQL UDFを使用して、次の問題を解決できます。

• ほとんどの場合、大量の同様のコードが存在し、これは保守が不便であり、エラーが発生しやすい。 JavaまたはPython UDFを使用する場合は、コードをコンパイルし (Java UDFに必要) 、リソースを作成し、コードを記述した後に関数を作成する必要があります。 このプロセスは複雑であり、パフォーマンスは高くありません。

  たとえば、次の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は、関数型の入力パラメーターをサポートします。 したがって、Lambda式に似た機能は、関数をパラメーターとして別の関数に渡すためにサポートされています。

  説明

  MaxComputeでLambda式を使用する場合の注意事項の詳細については、「Lambda関数」をご参照ください。

MaxComputeでは、CREATE SQL FUNCTIONステートメントを実行してSQL UDFを作成できます。 このステートメントを使用して作成されるUDFは、永続的なSQL UDFです。 CREATE SQL FUNCTIONステートメントを使用してUDFを作成すると、UDFはMaxComputeのメタデータシステムに保存されます。 MaxComputeの関数リストでUDFを照会できます。 以降のすべてのクエリ操作でUDFを呼び出すことができます。

• 注意事項

  SQLスクリプトモードでSQL UDFを作成します。 そうしないと、SQL UDFの作成に失敗する可能性があります。 SQLスクリプトモードの詳細については、「SQL in script mode」をご参照ください。

• 構文

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

  • function_name: 必須です。 このパラメーターには、作成するSQL UDFの名前を指定します。 関数名はプロジェクト内で一意である必要があり、組み込み関数の名前と同じにすることはできません。 同じ名前の関数は1回だけ登録できます。 LIST FUNCTIONSステートメントを実行して、プロジェクト内のすべての関数を表示し、既存の関数が作成する関数と同じ名前であるかどうかを確認できます。

  • parameter_in: 必須です。 このパラメーターには、作成するSQL UDFの入力パラメーターを指定します。 入力パラメータは、匿名関数を含む関数型とすることができる。 入力パラメーターが関数型であるSQL UDFを作成する方法の詳細については、「入力パラメーターが関数型であるSQL UDFを作成する方法の例」をご参照ください。 入力パラメーターが匿名関数であるSQL UDFを作成する方法の詳細については、「入力パラメーターが匿名関数であるSQL UDFを作成する方法の例」をご参照ください。

  • datatype: 必須です。 このパラメーターは、UDFの入力パラメーターのデータ型を指定します。 MaxComputeでサポートされているデータ型の詳細については、「MaxCompute V2.0データ型エディション」をご参照ください。

  • returns: オプション。 UDFの戻り値を指定します。 戻り値は変数です。 このパラメーターを指定しない場合、デフォルトでfunction_nameパラメーターの値が返されます。

  • parameter_out: 必須です。 このパラメーターは、UDFの応答パラメーターを指定します。

  • function_expression: 必須です。 UDFの式 (実装ロジック) を指定します。

• サンプルコード

  • 次のサンプルコードは、単純なロジックを持つUDFの例を示しています。

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

    上記の例では、@ a + 1はSQL UDFのロジックを示します。 式として書くことができます。 式には、組み込み演算子、組み込み関数、またはUDFを使用できます。

  • UDFロジックが複雑な場合は、SQL文で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は、UDFの戻り値を指定します。 戻り値は変数です。 このパラメーターを指定しない場合、デフォルトでfunction_nameパラメーターの値が返されます。

    • beginとendの式は、SQL UDFの関数実装ロジックです。

MaxComputeでは、FUNCTIONステートメントを実行してSQL UDFを作成できます。 このステートメントを使用して作成されるUDFは、一時的なSQL UDFです。 FUNCTION文を使用してUDFを作成した後、UDFはMaxComputeのメタデータシステムに保存されません。 MaxComputeの関数リストでUDFを照会することはできません。 SQL UDFは、UDFが定義されているスクリプトでのみ呼び出すことができ、他のクエリ環境では呼び出すことができません。

• 注意事項

  SQLスクリプトモードでSQL UDFを作成します。 そうしないと、SQL UDFの作成に失敗する可能性があります。 SQLスクリプトモードの詳細については、「SQL in script mode」をご参照ください。

• 構文

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

  パラメーターの詳細については、「永続SQL UDFの作成」をご参照ください。

• サンプルコード

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

SQL UDFは、JavaまたはPython UDFをクエリするのと同じ方法でクエリできます。

• 注意事項

  • MaxComputeクライアントの情報を照会する場合は、クライアントのバージョンが0.34.0以降にアップグレードされていることを確認してください。 MaxComputeクライアントのバージョンを表示し、MaxComputeクライアントをインストールおよび設定する方法の詳細については、「MaxComputeクライアント (odpscmd) 」をご参照ください。

  • CREATE SQL FUNCTIONステートメントを使用して作成された永続的なSQL UDFのみがMaxComputeに格納されます。 一時SQL UDFはMaxComputeに保存されません。 したがって、一時的なSQL UDFに関する情報は照会できません。

• 構文

  desc function <function_name>;

  function_name: 作成されたSQL UDFの名前。

• サンプルコード

  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 UDFはMaxComputeに保存され、任意のフェーズで呼び出すことができます。

  • 一時的なSQL UDFは、UDFが定義されているスクリプトでのみ呼び出すことができ、他のクエリ環境では呼び出すことができません。

• 構文

  select <function_name>(<column_name>[,...]) from <table_name>;

  • function_name: 作成されたSQL UDFの名前。

  • column_name: データを照会するテーブルの列名。 列のデータ型は、SQL UDFで定義されているデータ型と同じである必要があります。

  • table_name: データを照会するテーブルの名前。

• サンプルコード

  -- Create a table named src. 
  create table src (c bigint, d string);
  insert into table src values (1,100.1),(2,100.2),(3,100.3);
  -- Call the my_add function. 
  select my_add(c) from src;
  -- The following result is returned: 
  +------------+
  | _c0        |
  +------------+
  | 2          |
  | 3          |
  | 4          |
  +------------+

SQL UDFは、JavaまたはPython UDFをドロップするのと同じ方法でドロップできます。

• 構文

  drop function <function_name>;

  function_name: 作成されたSQL UDFの名前。

• サンプルコード

  drop function my_add;

SQL UDFを作成するときは、MaxComputeの組み込み関数、他のUDF、またはSQL UDFなど、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);

-- The following result is returned: 
+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 2          | 1          |
| 3          | 2          |
+------------+------------+

上記の例では、2つのSQL UDFが定義されています。

• add: BIGINT型の入力パラメーターが定義されています。

• op: 2つの入力パラメータが定義されています。

  • 入力パラメーター @ aはBIGINT型です。

  • 入力パラメーター @ funは関数型です。 @ fun関数の入力パラメーターと出力パラメーターは両方ともBIGINT型です。 関数opは、入力パラメーター @ aを @ fun関数に渡します。

• op関数を呼び出すと、ADD関数とABS関数が渡され、@ aの操作が実行されます。 ADDはSQL UDFであり、ABSはMaxComputeの組み込み関数です。 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 UDFを使用する。 この方法を使用することをお勧めします。 サンプルコード：

  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);