BigQueryの定期的なクエリ実行機能について

メディアサービス開発部Webアプリケーション開発課の長田です
主にRuby on Railsを利用したサービス開発やBigQueryを利用した数値集計・解析のための基盤整備などをしています。

BigQueryには日時と実行したいクエリを設定することで定期的にクエリを実行する機能があります。
この機能では実行日時・実行間隔の設定から、実行結果をどのデータセットのテーブルに格納するかまで指定することができます。

今回はこの定期的なクエリ実行機能に関して利用例と一緒に紹介します。

BigQueryExportで同期したGA4イベントを利用した集計

前回投稿したGA4をBigQueryと連携する際に気をつけたいことの中でも触れましたが、GA4のイベントを日次でBigQuery上のテーブルに格納できます。これはBigQueryExportという機能を利用し、日次でevents_YYYYMMDDテーブルにGA4で発生したイベントの内容を格納するというものです。

では実際に格納されたGA4のイベントを利用して日次のアクティブユーザ数を集計、指定したテーブルに格納したい場合、日次でeventsテーブルから集計に必要なイベントを取り出すためのクエリを実行する必要があります。

毎日手動でクエリを実行しても良いのですが、それでは効率が悪いです。
定期的なクエリ実行機能を利用すれば、人の手を介さず自動でクエリ実行から集計用のテーブルにデータ格納までを行うことができます。

指定した時刻にクエリを実行する

では実際に定期的なクエリ実行設定についてみていきます。

実行する集計クエリ

以下は前日分のeventsテーブルからアクティブユーザ数を取得するためのクエリになります。

SELECT
  DATE_SUB(DATE(FORMAT_DATETIME('%Y-%m-%d',@run_time,'Asia/Tokyo')), INTERVAL 1 DAY) AS event_date,
  COUNT(DISTINCT(user_pseudo_id)) AS active_user_count,
FROM
  `analytics_xxxxxxx.events_*`
WHERE
  _TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", DATE_SUB(DATE(FORMAT_DATETIME('%Y-%m-%d',@run_time,'Asia/Tokyo')), INTERVAL 1 DAY));

通常のクエリと異なる点は、集計日時を指定する際に@run_timeというパラメータを使用しています。
このパラメータは定期実行する際に利用できるもので、実行タイミングの時刻をTIMESTAMP型で表します。
今回は@run_timeのみを利用しますが、DATE型を返す@run_dateパラメータも存在します。
どちらもタイムゾーンはUTCのため、利用する際は注意が必要です。

定期実行の設定

今回はBigQueryコンソール上から定期実行の設定をする場合の紹介になります。

エディタ上に実行させたいクエリを記載した状態で、スケジュール＞スケジュールされたクエリを新規作成をクリックします。

画面右側に定期実行の設定画面が表示されます。
実行間隔と、設定後すぐに設定したクエリを実行するか、初回の実行日時を指定して実行するのかを選択した後、定期実行を任意の日付で終了させたいあれば、終了時刻を設定します。
指定が終わると、最終的に設定した実行間隔と時刻が下に表示されます。
今回の場合は毎日15:30に実行されるように設定した状態になります。

クエリの書き込み先を指定します。
SELECT文を定期実行する場合は必須の設定になりますが、実行するクエリがUPDATEやINSERTの場合は指定の必要はありません。
実行結果を格納したいデータセットとテーブルIDを指定します。
また、実行結果をテーブルに追加するのか、実行結果でテーブルを上書きするのかを選択します。
これで定期実行の設定が完了しました。
設定内容はBigQueryの「スケジュールされたクエリ」から確認する事ができます。

BigQueryExportの実行タイミングとの調整

BigQueryExportを利用したGA4イベント同期タイミングは公表されておらず、公式のドキュメントにも「午後の早い時間帯」としか記載がありません。
そのため前日のデータを利用して集計しようとする場合、集計しようとするeventsテーブルが作成される前に設定したスケジュールが実行され、集計に失敗する可能性があります。
集計に失敗した場合は、後述するバックフィル機能を使ってデータの穴埋めを行う事もできますが、eventsテーブルを扱うスケジュールはできるだけ午後に実行するようにすることで失敗を回避できる確率を上げることができます。

任意の時刻を指定してクエリを実行する

何らかの理由で定期実行に失敗しデータが欠けてしまった、定期実行前の過去データを登録したいなど、定期実行で設定したクエリを任意の日付で実行したいケースはあると思います。
クエリ内で @run_time, または @run_date パラメータを利用している場合、BigQueryコンソール上から各パラメータに対して渡す日付を指定して実行する事が可能です。

BigQueryコンソールの「スケジュールされたクエリ」から実行したいスケジュールを選択し、右上にある「バックフィルのスケジュール構成」をクリックします。
ラジオボタンで現在時刻で実行する、または指定した時刻で実行するを選択しOKをクリックすると指定した時刻でクエリが実行されます。
日次実行のスケジュールの場合、指定期間を3日間にした場合、それぞれの日付で合計3回クエリが実行されます。