OGP

はじめに

こんにちは。ZOZOTOWN部サービスグロースチームでアナリティクスをしている井ノ口です。

この記事ではBigQueryで使える、ユーザー定義関数（UDF）という便利な武器をご紹介します。「UDFって何？」「何のために使うの？」という方に向けた記事のため、高度な分析などはこの記事では扱いません。

UDFとは

UDFとは、最初から用意されているSUMやCOUNTのような関数を、ユーザー自身が定義する関数です。

私のチームではGoogle Analyticsのデータから、ユーザーが閲覧したページを分類したり、日時を計算したりする際などにUDFを用いてます。利用法など詳細は公式のドキュメントに記述されているため、そちらをご参照ください。

参考：標準 SQL ユーザー定義関数 | BigQuery | Google Cloud

UDFを使うメリット

使い方によって様々なメリットを受けられますが、特に大きいと感じるメリットは以下の3つです。

1. コーディングのコストを低減できる

UDFは一度処理を記述するとUDFを呼び出すだけで処理を実行できるため、同じコードを繰り返し記述する必要がありません。UDFをチームで共有できるなら、時には自身が処理を記述する必要すらなくなるため、コーディングにかかる時間・労力のコストを低減できます。

また、作成したコードについてレビューを依頼する機会があれば、相手は既存のUDFについてレビューする必要がなくなるため、レビューのコスト低減にも繋がります。

2. コードを理解しやすくできる

処理が複雑で長いものであるほど、処理がどのようなものかを考える、あるいは理解するために大きなコストを要します。そこで処理の記述にUDFを用いると、コード中の複雑あるいは長い処理であっても役割として理解しやすくなります。UDFを用いた処理が複数回繰り返されるなら、それらが同じ役割であるとも容易に理解できます。

3. バグ混入のリスクを低減できる

UDFは自身以外が作ったものでも使い回せます。

一度バグがないか検証されたUDFを利用すると、新たに処理を記述してバグが混入するケースを避けられます。たとえUDFにバグが含まれていたとしても、UDFの中身を修正すれば全ての処理が修正されます。

例えばコピー&ペーストで同じ処理を複数箇所に記述していた場合、該当箇所を全て修正する手間や修正漏れによるバグ混入の恐れがあります。

デメリットとしてはUDFは結合条件など一部では使えなかったり、UDFの中でWITH句の内容が使えなかったりします。ただし、結合条件の代わりにWHERE句で指定したりWITH句の内容をUDFで宣言できるケースがあるなど、工夫でなんとかなることもあります。

UDFを使用したクエリ例

上記のようなメリットがあるため、サービスグロースチームでは以下の用途などで使用します。

IDの管理

覚えるのが大変なため、UDFを使用しないとバグを混入させたり見逃したりしがちです。
条件の管理

ページや流入元を分類・判定する正規表現などを扱うことが多いです。条件は取りたい値が、なんらかの事情で変更されることが少なくないため、UDFを用いると条件の確認や変更に重宝します。
日時の処理

頻繁に使うものの直感的に記述・理解しづらかったり、記述が長くなりがちな処理なため、UDFが活躍する場面です。

実際にこのようなUDFを使用したクエリの例をご紹介します。

ZOZOTOWNの仕様に合わせた独自の条件などが含まれるため、一部をマスクしてあります。このクエリでは、セッションのテーブルから閲覧した日付、ページカテゴリごとにUUを求め、ページカテゴリが”その他”のものだけ除外します。

------------------------------------ユーザー定義関数------------------------------------
-- visitStartTimeを日本時間の日付に変更する関数
CREATE TEMPORARY FUNCTION DATE_BY_VISITSTARTTIME(visitStartTime INT64)
    AS (
        DATE(TIMESTAMP_SECONDS(visitStartTime), "Asia/Tokyo"))
    );

-- ページに対応するIDを返す関数 --★ 複数回使っているUDFだが、この処理を変更すると全ての処理に変更が適応される
CREATE TEMPORARY FUNCTION PAGECATEGORY_ID_BY_PAGECATEGORY_NAME(pageCategoryName STRING)
    AS(
        CASE pageCategoryName
            WHEN "トップ" THEN 1
            WHEN "検索結果" THEN 2
            WHEN "商品詳細" THEN 3
            WHEN "その他" THEN 4
        END
    );

-- ページを判定し対応するページIDを返す関数
CREATE TEMPORARY FUNCTION CATEGORIZE_WEB_PAGECATEGORY_ID(pagePath STRING)
    AS (
        CASE
            WHEN REGEXP_CONTAINS(pagePath, r"[トップのページパスに該当する正規表現]")
                THEN PAGECATEGORY_ID_BY_PAGECATEGORY_NAME("トップ")
            WHEN REGEXP_CONTAINS(pagePath, r"[検索結果のページパスに該当する正規表現]")
                THEN PAGECATEGORY_ID_BY_PAGECATEGORY_NAME("検索結果")
            WHEN REGEXP_CONTAINS(pagePath, r"[商品詳細のページパスに該当する正規表現]")
                THEN PAGECATEGORY_ID_BY_PAGECATEGORY_NAME("商品詳細")
            ELSE
                PAGECATEGORY_ID_BY_PAGECATEGORY_NAME("その他")
        END
    );

--★ ここまではチーム内で共通して使っているUDFなので、レビュー不要
 
SELECT 
    DATE_BY_VISITSTARTTIME(visitStartTime) AS visitDate --★ 日時の変換処理が直感的でバグが混入しづらい
    , CATEGORIZE_WEB_PAGECATEGORY_ID(hits.page.pagePath) AS pageCategoryId --★ 記述すると長くなる分類処理が短く済む
    , COUNT(DISTINCT fullVisitorId) AS UU
FROM
    `[セッション情報からなるテーブル]`, UNNEST(hits) AS hits
WHERE
    pageCategoryId <> PAGECATEGORY_ID_BY_PAGECATEGORY_NAME("その他") --★ <> 4 だと4の意味がわからない、誤りがあっても気づけない
GROUP BY
    visitDate
    , pageCategoryId

クエリ全体で見ると行数が多く感じるかもしれませんが、書く・読む作業が発生するのはほぼSELECT文のみで行数は少ないです。UDF部分はすでに用意してあるものを使っているため、書く・読む手間はほぼありません。

今回はUDFを一時的なUDFとして扱っていますが、永続的なUDFとして予め用意していると記述する必要もなくなります。

参考：標準 SQL ユーザー定義関数 | BigQuery | Google Cloud

このクエリでは日時の処理、条件の管理、IDの管理にあたるUDFをそれぞれ使っています。UDFの使用によりSELECT文中の記述量は減り、馴れもありますが処理の内容を理解しやすくなっているかと思います。

また、クエリ例の中ではIDの管理にあたる PAGECATEGORY_ID_BY_PAGECATEGORY_NAME() は別のUDFやWHERE句で複数回使われており、条件を変更したい場合にはこのUDF一箇所で済みます。

加えてコード中にIDの数値をそのまま書いていると、そのIDが何を指し正しいのかどうかが理解しづらいですが、IDを管理するUDFによって理解しやすくなっています。このようなマジックナンバーを避ける働きもできます。

UDFの例

最後に、このクエリに含まれていないUDFも含め、処理の説明を併せた例を紹介します。

セッション固有のIDを返す

CREATE TEMPORARY FUNCTION SESSION_ID_BY_FULLVISITORID_VISITID (fullVisitorId STRING, visitId INT64)
    AS(
        CONCAT(fullVisitorId, "-", CAST(visitId AS STRING))
    );

fullVisitorIdとvisitIdをハイフンでつなぎ、セッション固有のIDを生成します。セッションを比較し、同じものかどうかを判定する際などに用います。

参考：BigQuery Export のスキーマ - アナリティクスヘルプ

visitStartTimeを日本時間のTIMESTAMP型に変更する

CREATE TEMPORARY FUNCTION TIMESTAMPDT_BY_VISITSTARTTIME (visitStartTime INT64)
    AS (
        CAST(DATETIME(TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS TIMESTAMP)
    );

visitStartTimeを日本時間のTIMESTAMP型に変換する関数です。セッションが始まった日時を把握するために用います。

参考：BigQuery Export のスキーマ - アナリティクスヘルプ

yyyymmdd型の日付をDATE型に変換する

CREATE TEMPORARY FUNCTION DATE_BY_yyyymmdd(yyyymmdd STRING)
    AS (
        DATE(CAST(SUBSTR(yyyymmdd, 1, 4) AS INT64), CAST(SUBSTR(yyyymmdd, 5, 2) AS INT64), CAST(SUBSTR(yyyymmdd, 7, 2) AS INT64))
    );

"20200202"（2020年2月2日）のようにyyyymmddの形で書かれた日付をDATE型へ変換する関数です。

ワイルドカードテーブルを使用する際に、対象テーブルを指定するためにyyyymmddの形で日付を指定します。このyyyymmddを、DATE型でも使いたい際に用います。

例えば、他のテーブルとジョインしたいが、そのテーブルが持つ日時はyyyymmddでなくDATE型のため同じ形に合わせる必要があるというときに使用します。

参考：ワイルドカードテーブルを使用した複数テーブルに対するクエリ | BigQuery | Google Cloud

yyyymmdd型の日付をx日前にずらす

CREATE TEMPORARY FUNCTION DATE_SUB_yyyymmdd(yyyymmdd STRING, sub_date INT64)
    AS (
        REPLACE(SAFE_CAST(DATE_SUB(DATE(CAST(SUBSTR(yyyymmdd, 1, 4) AS INT64), CAST(SUBSTR(yyyymmdd, 5, 2) AS INT64), CAST(SUBSTR(yyyymmdd, 7, 2) AS INT64)), INTERVAL sub_date DAY) AS STRING), "-", "")
    );

"20200202"（2020年2月2日）のようにyyyymmddの形で書かれた日付をSUB_DATE日前へずらす関数です。yyyymmddに加え、数日前のテーブルもデータ抽出の対象とする際などで用います。

yyyymmdd型の日付をx日後にずらす

CREATE TEMPORARY FUNCTION DATE_ADD_yyyymmdd(yyyymmdd STRING, add_date INT64)
    AS (
        REPLACE(SAFE_CAST(DATE_ADD(DATE(CAST(SUBSTR(yyyymmdd, 1, 4) AS INT64), CAST(SUBSTR(yyyymmdd, 5, 2) AS INT64), CAST(SUBSTR(yyyymmdd, 7, 2) AS INT64)), INTERVAL add_date DAY) AS STRING), "-", "")
    );