こんにちは！
アナリストのMです。
日々の業務でBigQueryに触れる機会が頻繁にあるのですが、
恥ずかしながら最近になってユーザー定義関数(UDF)なるものの存在を知りました(汗)
ちょっと使ってみて個人的にはかなり便利だと感じたので、
本記事ではUDFを使用したことがない人やそもそも何それ？状態の方向けにUDFの利便性や実装方法を簡潔に共有したいと思います。
ユーザー定義関数(UDF)とは？
ユーザー定義関数(UDF)は読んで字のごとく、ユーザー独自で定義する関数です。
頻繁に行うやや複雑な処理をSQLかJavaScriptを用いてUDFとしてあらかじめ関数化しておくことで、
以下のようなメリットが見込めるようになります。
・コードがすっきりする
・コーディングミスが減る
UDFには、1つのクエリで一時的に利用できる一時UDFと複数のクエリで再利用可能な永続UDFが存在します。
次節以降ではSQLを用いたそれぞれのUDFの実装方法を紹介します。
デモ用テーブルの準備
下記クエリを実行し、日付が3つのカラム([date_year],[date_month],[date_day])に分かれて整数型として格納されているテーブル”demo”を作成しておきます。
-- 空テーブルを作成 CREATE OR REPLACE TABLE `[プロジェクト名].[データセット名].demo` ( date_year INT64, date_month INT64, date_day INT64 ); -- 空テーブルにデータをインサート INSERT INTO `[プロジェクト名].[データセット名].demo`(date_year, date_month, date_day) VALUES (2021, 1, 1), (2021, 1, 2), (2021, 1, 3)
一時UDFの作成方法
今回は年,月,日のカラムをもとに日付を生成するUDFとしてmake_date関数を実装し、”demo”テーブルに適用していきます。
下記がそのクエリです。
-- 一時UDFを作成 CREATE TEMPORARY FUNCTION make_date(year INT64, month INT64, day INT64) AS ( DATE(year, month, day) ); -- "sales"テーブルで適用 SELECT *, make_date(date_year, date_month, date_day) AS date_ymd FROM `[プロジェクト名].[データセット名].demo`
“CREATE TEMPORARY FUNCTION”と表記することで一時UDFを作成することができます。
make_date関数の引数には”year”,”month”,”day”を設定し、それをもとにDATE関数を用いて日付を返すといった仕組みです。
実行すると、下記結果が返ってきます。
無事日付カラムが追加されていることが確認できました。
今回はSELECT文でUDFを使用しましたが、WHERE句から呼び出すことも可能なので、
例えば
1カ月前の日付を生成するUDFを用意
→WHERE句から呼び出して1カ月前のログを抽出
なんてことも簡単にできます。
また、下記のようにUDFから別のUDFを呼び出すことも可能です。
-- 一時UDFを作成 CREATE TEMPORARY FUNCTION make_date(year INT64, month INT64, day INT64) AS ( DATE(year, month,day) ); CREATE TEMPORARY FUNCTION make_date_yesterday(year INT64, month INT64, day INT64) AS ( DATE_SUB( make_date(year, month, day), INTERVAL 1 DAY ) ); -- "sales"テーブルで適用 SELECT *, make_date(date_year,date_month,date_day) AS date_ymd, make_date_yesterday(date_year,date_month,date_day) AS date_ymd_yesterday FROM `[プロジェクト名].[データセット名].demo`
make_date_yesterday関数ではmake_date関数を呼び出し、1日引くことで前日の日付を返しています。
実行すると、下記結果が返ってきます。
永続UDFの作成方法
一時UDFを作成する際は”CREATE TEMPORARY TABLE”と表記しましたが、
永続UDFを作成する際は”CREATE TABLE”と表記します。
試しに先ほど作成したmake_date関数を永続UDFとして保存してみます。
下記クエリを実行すると指定したデータセット内にmake_date関数が永続UDFとして生成されます。
CREATE FUNCTION [データセット名].make_date(year INT64, month INT64, day INT64) AS ( DATE(year, month,day) )
後は以下のように[データセット名].[関数名]といった感じで記述することで永続UDFを呼び出すことが可能になります。
SELECT *, [データセット名].make_date(date_year, date_month, date_day) AS date_ymd FROM `[プロジェクト名].[データセット名].demo`
さいごに
いかがでしょうか？
UDFをうまく使うことで、スムーズかつ正確なデータ処理が可能になるかと思います。
ぜひ試してみてください。
