こんにちは。データサイエンティスト、兼データ基盤エンジニアのshobyです。
今回は、データ抽出業務を効率化するために、RedShiftにユーザー定義関数を追加する方法をご紹介します。
RedShiftのユーザー定義関数
RedShiftでは、SQLもしくはPythonを使用し、任意のユーザー定義関数を定義する事ができます。
ただし現状*1ではPostgreSQLのユーザー定義関数とは異なり、制限があります。
引数と返り値に使用できる型は通常のデータ型のみであり、 引数にテーブルの列を渡したり、返り値にテーブルを返す事ができません。 また、関数内で別テーブルを参照することもできないため、集合に対する処理は不可能になっています。
INTEGERの引数を受け取ってVARCHARを返す、といった関数しか書けないため、用途が限定されることにはご注意ください。
SQLでのユーザー定義関数
SQLでのユーザー定義関数には制限があり、クエリに以下の要素を含める事ができません。
- FROM
- INTO
- WHERE
- GROUP BY
- ORDER BY
- LIMIT
基本的には、SELECTのみを書くような形で関数を定義します。
関数名に関しては、システム定義関数とぶつからないよう、 f_ のprefixをつける事が推奨されています。
例として、timestampから曜日の文字列を取得する関数は以下のような実装になります。
CREATE FUNCTION f_extract_day_of_week(TIMESTAMP) RETURNS VARCHAR(3) STABLE AS $$ SELECT CASE EXTRACT(DOW FROM $1) WHEN 0 THEN '日' WHEN 1 THEN '月' WHEN 2 THEN '火' WHEN 3 THEN '水' WHEN 4 THEN '木' WHEN 5 THEN '金' WHEN 6 THEN '土' ELSE '' END; $$ LANGUAGE SQL;
Pyhtonでのユーザー定義関数
Pythonでユーザー定義関数を書く際には、デフォルトnumpy、pandas、scipyなどのライブラリが使用でき、 カスタムでライブラリをインポートできます。
ただし、現状では集合に対しての操作を行う事ができず、統計的な処理を行うことが難しいため、*2 SQLではなくPythonのユーザー定義関数を使うメリットはそれほど多くありません。
また、パフォーマンスもSQLを使用した方が良好という調査結果があります。
現状、Pythonのユーザー定義関数を使った複雑な処理が必要なケースでは、 事前にLambdaやAWS batch等でバッチ処理をし、カラムに値を格納しておく方法が使えないか、検討するのをおすすめします。
TVISION INSIGHTSでの活用方法
TVISION INSIGHTSでは、ユーザー定義関数を、TIMESTAMP型の値を任意の形式に変換する用途などに活用しています。
具体的には、上記例に出したTIMESTAMP型から曜日を文字列として取得する関数や、 視聴時刻をテレビ業界の慣習に合わせた日時に変換する関数などを定義しています。*3
これらのユーザー定義関数を活用する事で、データ抽出業務の効率化を行なっています。
まとめ
RedShiftではSQLとPythonでユーザー定義関数を実装することができます。 ただし、現状では集合に対して操作を行う事ができず、用途が限られるため、SQL側を使うことをおすすめします。
TVISION INSIGHTSでは、TIMESTAMP型の値を任意の形式に変換する用途などで、データ抽出業務の効率化を行なっています。
