新機能 Amazon Redshift SQLによるスカラーユーザー定義関数（UDF）を試してみました

#Amazon Redshift

#AWS

石川覚

2017.09.04

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

これまでスカラーユーザー定義関数（UDF）はPythonで定義していましたが、PostgreSQLのSQL構文で作成できるようになりました。使い勝手やパフォーマンスについて検証してみたいと思います。

Using PostgreSQL syntax, you can create custom SQL scalar functions and execute them in parallel across your clusters. For faster performance, we replace the SQL function name with the SQL function code on the leader node, and these are then run as regular SQL in parallel on each node of your cluster. Once defined, you can call these scalar UDFs in any SQL statement.

PostgreSQL構文を使用して、カスタムSQLスカラ関数を作成し、クラスタ全体で並列に実行できます。パフォーマンスを向上させるために、SQL関数名をリーダ・ノード上の　SQLファンクション・コードに置き換えます。これらは、クラスタの各ノードで通常のSQLとして並列に実行されます。一度定義されると、これらのスカラーUDFを任意のSQLステートメントで呼び出すことができます。

引用：Amazon Redshift Introduces SQL Scalar User-Defined Functions

簡単なUDF定義と実行

Pythonで定義したスカラーユーザー定義関数（UDF）の例

まずは、従来のPythonで定義したスカラーユーザー定義関数（UDF）の例です。

create function f_py_greater (a float, b float)
returns float
stable
as $$
if a > b:
return a
return b
$$ language plpythonu;

UDFの作成するにはlanguage plpythonuのUSAGE権限が必要です。

SQLで定義したスカラーユーザー定義関数（UDF）の例

同じ関数をSQL構文で定義すると以下のようになります。

create function f_sql_greater (float, float)
returns float
stable
as $$
select case when $1 > $2 then $1
else $2
end
$$ language sql;

language sqlのUSAGE権限がPUBLICに付与されているので、一般ユーザーでも特に権限付与することなく定義できます。（PUBLICって、何だ？という方は、Amazon Redshift 全てユーザが持つデフォルトのpublicスキーマに対する権限を剥奪するを御覧ください。）

UDFのより詳細なセキュリティや権限の取扱については、UDF Security and Privilegesを御覧ください。

UDFの実行

PythonのUDFとSQLのUDFの結果です。計測値は実行プランの作成やコンパイル時間の影響を受けないように、２回目以降の実行結果です。

cmdb=> select f_py_greater(a1, a2)
cmdb-> from (select 10.0::float as a1, 20.0::float as a2);
f_py_greater
--------------
20
(1 row)

Time: 289.988 ms
cmdb=>
cmdb=> select f_sql_greater(a1, a2)
cmdb-> from (select 10.0::float as a1, 20.0::float as a2);
f_sql_greater
---------------
20
(1 row)

Time: 184.325 ms

何度実行しても、PythonのUDFよりもSQLのUDFの方が速いようです。もう少し詳しく調べてみたいと思います。

パフォーマンスの比較

PythonのUDFよりもSQLのUDFの方が速いことが確認できましたが、より大きなデータではどれくらい差が出るか計測してみます。

リージョン：us-east-1
ノード：dc1.large x 2 (2ノード、4スライス)
約1000万件のレコード

cmdb=> insert into lineorder_7z select * from lineorder limit 10000000;
INSERT 0 10000000
Time: 37887.697 ms
cmdb=> analyze lineorder_7z;
ANALYZE
Time: 6351.700 ms

計測用UDF

UDFの呼び出しオーバーヘッドを計測したいので、シンプルな奇数判定のUDFを作成しました。SQLのUDFの中では、SQL関数を利用してUDFを作成しています。

cmdb=# create function f_py_isodd (a integer)
cmdb-# returns integer
cmdb-# stable
cmdb-# as $$
cmdb$# if a % 2 > 0 :
cmdb$# return 1
cmdb$# return 0
cmdb$# $$ language plpythonu;
CREATE FUNCTION

cmdb=# create function f_sql_isodd (integer)
cmdb-# returns integer
cmdb-# stable
cmdb-# as $$
cmdb$# select case when mod($1,2) > 0 then 1
cmdb$# else 0
cmdb$# end
cmdb$# $$ language sql;
CREATE FUNCTION

cmdb=# select f_py_isodd(100);
f_py_isodd
------------
0
(1 row)

Time: 284.187 ms
cmdb=# select f_sql_isodd(100);
f_sql_isodd
-------------
0
(1 row)

Time: 178.344 ms

Pythonのスカラーユーザー定義関数（UDF）の実行結果

PythonのUDFに対して実行します。

cmdb=# select sum(f_py_isodd(lo_orderkey)) from ssbgz.lineorder_7z;
sum
---------
5005013
(1 row)

Time: 161190.789 ms

SQLのスカラーユーザー定義関数（UDF）の実行結果

では、SQLのUDFに対して実行します。

cmdb=# select sum(f_sql_isodd(lo_orderkey)) from ssbgz.lineorder_7z;
sum
---------
5005013
(1 row)

Time: 249.694 ms

SQLをそのままの実行した結果

「SQLのスカラーユーザー定義関数（UDF）」の中に記載したSQLをそのまま実行したとも比較します。

cmdb=# select sum(case when mod(lo_orderkey,2) > 0 then 1 else 0 end) from ssbgz.lineorder_7z;
sum
---------
5005013
(1 row)

Time: 248.684 ms

計測結果

今回の検証では、PythonのUDFよりもSQLのUDFの方が約650倍速いことが確認できました。なお、SQLのUDFとSQLをそのまま実行した場合はほぼ同じ結果でした。また、それぞれの実行プランに違いはありませんでした。

PythonのUDFはCPUがボトルネックになる傾向があります。下記のグラフの通り、実行するとコンピュートノードのCPU使用率が100%になりますが、他のリソースは大きな変化は見られません。

20170904-amazon-redshift-python-scala-udf-metrics

スカラーユーザー定義関数（UDF）のベストプラクティス

RedshiftのSQL構文とSQL関数の組み合わせで、UDFが作成可能ならSQLで作成した方がベター
Python標準ライブラリやPythonモジュールを利用した演算は従来通り、PythonでUDFを作成する
UDFを適用する箇所やレコード数が少ないユースケースでは、どちらでもかまわない
UDFを利用して可読性を上げたいユースケースではSQLのスカラーユーザー定義関数（UDF）が性能低下のリスクがなく効果的

最後に

SQLのスカラーユーザー定義関数（UDF）の導入によって、SQL構文とSQL関数を組み合わせて簡単かつパフォーマンスの優れたUDFが作成できることが可能になりました。SQLをそのまま実行したときとパフォーマンスが殆ど変わらないので、ワークアラウンドとしてSQLを組み合わせてクエリーを作成したクエリーは容易に移行できるはずです。パフォーマンス低下を懸念してUDFの導入を諦めていた人にはぜひ再検討していただくことをおすすめします。

執筆時点では、公式ドキュメントは日本語化されていませんので、英語版をご覧ください。

公式ドキュメント（英語） - Creating User-Defined Functions