読者です 読者をやめる 読者になる 読者になる

RedshiftでUDFを使用する方法

AWS Redshift
スポンサーリンク

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