MaxCompute:UDF (SQL ユーザー定義関数)

基本概念

前提条件

開始する前に、次のことを確認してください：

• SQL スクリプトモードで実行されている環境。詳細については、「スクリプトモードでの SQL」をご参照ください。

• 使用予定の入力パラメーターのデータ型が MaxCompute でサポートされていること。完全なリストについては、「MaxCompute V2.0 データ型のエディション」をご参照ください。

• ご利用の Alibaba Cloud アカウントに必要な関数レベルの権限。詳細については、「MaxCompute の権限」をご参照ください。

永続的 SQL UDF の作成

永続的 SQL UDF は MaxCompute メタデータシステムに格納されます。作成後、関数リストに表示され、どのフェーズからでも呼び出すことができます。

構文

CREATE SQL FUNCTION <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...])
[RETURNS @<parameter_out> <datatype>]
AS [BEGIN]
<function_expression>
[END];

パラメーター

例

単純な関数 — BIGINT 型の入力に 1 を加算します：

CREATE SQL FUNCTION my_add(@a BIGINT) AS @a + 1;

BEGIN / END を使用した複数ステートメントの関数：

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 UDF の作成

一時的 SQL UDF は MaxCompute には格納されません。定義された SQL スクリプト内にのみ存在し、他のセッションやスクリプトでは呼び出すことができません。

構文

FUNCTION <function_name>(@<parameter_in1> <datatype>[, @<parameter_in2> <datatype>...])
[RETURNS @<parameter_out> <datatype>]
AS [BEGIN]
<function_expression>
[END];

パラメーターは CREATE SQL FUNCTION のものと同じです。CREATE SQL を省略すると、一時的 UDF が作成されます。

例

FUNCTION my_add(@a BIGINT) AS @a + 1;

SQL UDF のクエリ

クエリできるのは永続的 SQL UDF のみです。一時的 UDF は MaxCompute に格納されません。

MaxCompute クライアント (odpscmd) から DESC FUNCTION を実行するには、クライアントをバージョン 0.34.0 以降にアップグレードする必要があります。インストールとアップグレードの手順については、「MaxCompute クライアント (odpscmd)」をご参照ください。

構文

DESC FUNCTION <function_name>;

例

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 は、ビルトイン関数を呼び出すのと同じ方法で呼び出します。

• 永続的 SQL UDF はどのフェーズからでも呼び出すことができます。

• 一時的 SQL UDF は、定義されたスクリプト内でのみ呼び出すことができます。

渡す引数のデータ型は、UDF で定義されたデータ型と一致する必要があります。

構文

SELECT <function_name>(<column_name>[, ...]) FROM <table_name>;

例

-- テーブルを作成し、サンプルデータを挿入します。
CREATE TABLE src (c BIGINT, d STRING);
INSERT INTO TABLE src VALUES (1, '100.1'), (2, '100.2'), (3, '100.3');

-- c 列で my_add を呼び出します。
SELECT my_add(c) FROM src;

出力：

+------------+
| _c0        |
+------------+
| 2          |
| 3          |
| 4          |
+------------+

SQL UDF の削除

構文

DROP FUNCTION <function_name>;

例

DROP FUNCTION my_add;

関数をパラメーターとして渡す

SQL UDF は関数型パラメーターをサポートしています。パラメーターを FUNCTION (<input_type>) RETURNS <output_type> として宣言すると、呼び出し元は互換性のある任意の関数 (ビルトイン関数、他の UDF (Java、Python、または SQL)、または匿名関数) を渡すことができます。

例

-- SQL UDF を定義します。
FUNCTION add(@a BIGINT) AS @a + 1;

-- 引数として関数を受け入れる高階 UDF を定義します。
FUNCTION op(@a BIGINT, @fun FUNCTION (BIGINT) RETURNS BIGINT) AS @fun(@a);

-- 2番目の引数として異なる関数を渡して op を呼び出します。
-- add は SQL UDF で、abs は MaxCompute のビルトイン関数です。
SELECT op(key, add), op(key, abs) FROM VALUES (1), (2) AS t(key);

出力：

+------------+------------+
| _c0        | _c1        |
+------------+------------+
| 2          | 1          |
| 3          | 2          |
+------------+------------+

_c0 は add(key) (1 を加算) の結果です。_c1 は abs(key) (絶対値) の結果です。ABS 関数の詳細については、「数学関数」をご参照ください。

MaxCompute でラムダ式を使用する際の注意事項については、「ラムダ関数」をご参照ください。

匿名関数の使用

関数型パラメーターを持つ UDF を呼び出す際に、名前付き関数の代わりにインライン匿名関数を渡すことができます。コンパイラは、UDF 署名から匿名関数のパラメーター型を推論します。

例

FUNCTION op(@a BIGINT, @fun FUNCTION (BIGINT) RETURNS BIGINT) AS @fun(@a);

-- 2番目の引数として匿名関数を渡します。
SELECT op(key, FUNCTION (@a) AS @a + 1) FROM VALUES (1), (2) AS t(key);

FUNCTION (@a) AS @a + 1 が匿名関数です。そのパラメーター型は op の署名から推論されるため、明示的な型宣言は不要です。

例：繰り返し使用される SQL ロジックの簡略化

シナリオ：日付文字列を yyyy-mm-dd 形式から yyyymmdd 形式に変換します。入力日付は 2020-11-21、2020-1-01、2019-5-1、19-12-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') AS t(d);

出力：

+------------+
| _c0        |
+------------+
| 20201121   |
| 20200101   |
| 20190501   |
| 00191201   |
+------------+

SQL UDF を使用しない場合

すべての呼び出し元で完全な式をインライン化します。これは、ロジックの変更が必要になった場合に、保守が困難でエラーが発生しやすくなります：

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') AS t(d);

制限事項

次のステップ

• スクリプトモードでの SQL

• MaxCompute V2.0 データ型のエディション

• ラムダ関数

• 数学関数

• LIST FUNCTIONS