BigQueryのユーザー定義関数(UDF)を使ってみた

こんにちは!
アナリストのMです。

日々の業務でBigQueryに触れる機会が頻繁にあるのですが、 恥ずかしながら最近になってユーザー定義関数(UDF)なるものの存在を知りました(汗)

ちょっと使ってみて個人的にはかなり便利だと感じたので、 本記事ではUDFを使用したことがない人やそもそも何それ?状態の方向けにUDFの利便性や実装方法を簡潔に共有したいと思います。

ユーザー定義関数(UDF)とは?

ユーザー定義関数(UDF)は読んで字のごとく、ユーザー独自で定義する関数です。 頻繁に行うやや複雑な処理をSQLJavaScriptを用いて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をうまく使うことで、スムーズかつ正確なデータ処理が可能になるかと思います。
ぜひ試してみてください。