Redshift の UDF（user-defined scalar function）機能の作成手順と使用方法についてまとめました。
UDFとは
UDF（user-defined scalar function）はユーザー定義関数といい、Python プログラムに基づいて SQL 以外の処理を作成することができます。作成した関数は既存の Redshift 関数（SUM や AVG）と同様に使用することも可能です。
Python 2.7 の標準ライブラリや numpy、pandas、scipy も使用できます。独自のカスタム Python モジュールをインポートしてライブラリとして使用することもできるので、UDF を使いこなせれば Redshift だけで出来る事も増えてとても便利に使用できます。
UDFの作成方法
UDFの作成における基本構文は以下の通りです。
CREATE [ OR REPLACE ] FUNCTION f_function_name ( [arg_name data_type, ... ] ) RETURNS data_type {VOLATILE | STABLE | IMMUTABLE } AS $$ python_program $$ LANGUAGE plpythonu;
| パラメータ | 説明 |
|---|---|
| f_function_name | UDFの名前です。f_は予約されているため全ての関数名と競合しないことが保証されているのでプレフィックスとしての使用が推奨されます。 UDF の命名 |
| arg_name data_type, ... | UDFの引数の名前とデータ型（Redshift、Pythonどちらのデータ型でもOK）をセットとしてカンマ区切りで記述します。最大32の引数を指定可能です。 UDF のデータ型 |
| RETURNS data_type | UDFの戻り値のデータ型（Redshiftのデータ型）を指定します。 |
| VOLATILE | STABLE | IMMUTABLE | 関数の変動率について VOLATILE（変動性）、STABLE（安定性）、IMMUTABLE（不変）を表し、クエリ最適化に影響します。 |
| python_program | 値を返すPythonプログラムを記述します。AS $$ ~ $$ LANGUAGE plpythonuで囲まれている必要があります。 |
サンプルとしてひとつ UDF を登録してみます。UDF を使いたいと思ったきっかけが JSON を扱う関数が少なかったからなので、JSON 文字列を受け取って値の数値を集計する関数を作成しました。 JSON 関数 - Amazon Redshift
CREATE FUNCTION f_sum_json_value (val VARCHAR) RETURNS REAL STABLE AS $$ import json try: return sum(json.loads(val).values()) except: return 0 $$ LANGUAGE plpythonu;
実行すると次のような結果になります。例外の補足も出来てますね。
test=# select f_sum_json_value('{"hoge": 123.456, "fuga": 500}'); f_sum_json_value ------------------ 623.456 test=# select f_sum_json_value('hoge'), f_sum_json_value(null); f_sum_json_value | f_sum_json_value ------------------+------------------ 0 | 0
UDF を変更したい場合はOR REPLACEオプションを付けます。同じ名前で引数と戻り値とも同じデータ型である必要があります。以下は戻り値を 0 から -1 に変更しました。
CREATE OR REPLACE FUNCTION f_sum_json_value (val VARCHAR) RETURNS REAL STABLE AS $$ import json try: return sum(json.loads(val).values()) except: return -1 $$ LANGUAGE plpythonu;
test=# select f_sum_json_value(null); f_sum_json_value ------------------ -1
データ型の対応は以下の通りです。
| Redshiftデータ型 | Pythonデータ型 |
|---|---|
| smallint integer bigint short long |
int |
| decimal numeric |
decimal |
| double real |
float |
| boolean | ブール |
| char varchar |
文字列 |
| timestamp | datetime |
UDFの削除
UDFの削除における基本構文は以下の通りです。
DROP FUNCTION f_function_name ( [arg_name] data_type, ...] ) [ CASCADE | RESTRICT ]
| パラメータ | 説明 |
|---|---|
| f_function_name | 削除するUDFの名前です。 |
| arg_name data_type, ... | UDFの引数の名前とデータ型をカンマ区切りで記述します。データ型のみで関数の識別をするため引数の名前は省略可能です。 |
| CASCADE | RESTRICT | CASCADEは関数に依存するオブジェクトを自動的に削除します。RESTRICTがデフォルトです。 |
削除するにはUDFの名前＋引数のデータ型も付ける必要があります。
test=# drop function f_sum_json_value(varchar); DROP FUNCTION
登録済みUDFの確認
登録済みの UDF はpg_procテーブルで確認できます。
test=# select * from pg_proc where proname like 'f_%' and proowner != 1; proname | pronamespace | proowner | prolang | proisagg | prosecdef | proisstrict | proretset | provolatile | pronargs | prorettype | proargtypes | proargnames | prosrc | probin | proacl -------------------+--------------+----------+---------+----------+-----------+-------------+-----------+-------------+----------+------------+-------------+-------------+------------------------------------------------------+----------+-------- f_sum_json_value | 2200 | 100 | 100057 | f | f | f | f | s | 1 | 700 | 1043 | {val} | +| -1:-1 | | | | | | | | | | | | | | import json +| | | | | | | | | | | | | | | try: return sum(json.loads(val).values()) +| | | | | | | | | | | | | | | except: return -1 +| | | | | | | | | | | | | | | | |
proargtypesが引数のデータ型でprorettypeが戻り値のデータ型です。pg_typeテーブルでデータ型は確認できます。
test=# select * from pg_type where typelem in (700,1043); typname | typnamespace | typowner | typlen | typbyval | typtype | typisdefined | typdelim | typrelid | typelem | typinput | typoutput | typreceive | typsend | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typdefaultbin | typdefault ----------+--------------+----------+--------+----------+---------+--------------+----------+----------+---------+----------+-----------+------------+------------+------------+----------+------------+------------+-------------+-----------+----------+---------------+------------ _varchar | 11 | 1 | -1 | f | b | t | , | 0 | 1043 | array_in | array_out | array_recv | array_send | - | i | x | f | 0 | -1 | 0 | | _float4 | 11 | 1 | -1 | f | b | t | , | 0 | 700 | array_in | array_out | array_recv | array_send | - | i | x | f | 0 | -1 | 0 | |
