シンプルなパイプラインはBigQueryのスケジュールクエリにおまかせ
投稿者：中根
日本情報通信の中根です。
今回はBigQueryのスケジュールクエリ機能についてご紹介します。
スケジュールクエリはBigQueryのWeb UIから設定が可能な定期的にクエリを実行できるサービスです。
スケジュールしたいクエリを入力していくつかの設定をするだけで完了です。
スケジュールクエリの機能
スケジュールクエリはその名の通り定期的にクエリを実行することが出来ます。
実態はBigQuery Data Transfer Serviceです。
スケジュールは1時間単位から月次まで選択でき、設定によっては4半期毎といったこともできます。
実行できるクエリはBigQueryの標準SQLを使用します。
SELECT文はもちろんDML(INSERTTやDELETE、MERGE)も対応していますの普段ご利用されているクエリをだいたいそのまま実施できます。
スケジュールクエリのメリット
それではスケジュールクエリにはどんなメリットがあるのでしょうか。
いくつか上げてみました。
クエリジョブを実行するサービスが不要
私自身が一番のメリットだと思うのはクエリを実行するためのサービス、もしくはジョブスケジューラを用意する必要がないことです。
スケジュールクエリはBigQueryのWeb UI上から実行したいSQL文を入力してスケジュールをセットするだけ、とてもシンプルです。
管理画面が用意されている
クエリを実行するだけではなく過去の実行状況やステータスを確認できないと不便ですね。
スケジュールクエリは管理画面(Data Transfer Serviceと同じもの)が用意されているので現在設定されている一覧の参照から過去の実施履歴も見ることが出来ます。
過去の日付に基づきクエリを実行できる
スケジュールクエリはバックフィルという機能を持っています。
実行日/実行時間を代入できる[パラメータ](https://cloud.google.com/bigquery/docs/scheduling-queries#configuration_options)が用意されていますのでこれをクエリに使用することであたかも過去に実施したようにクエリを動的に作成して実行してくれます。
＊もちろんクエリ対象のデータは実行したタイミングとなります。[タイムトラベル](https://cloud.google.com/bigquery/docs/time-travel))とは異なります。
たとえば元データから日次集計した値を更新するといったパイプラインを考えてみます。
– クエリイメージ
MERGE `blog_demo.sales_summary` AS summary
USING (
SELECT
date,
SUM(price * order_num) as total_sales
FROM `blog_demo.sales`
WHERE date = @run_time
GROUP BY date
) AS sales
ON summary.date = sales.date
WHEN MATCHED THEN
UPDATE SET summary.total_sales = sales.total_sales
WHEN NOT MATCHED THEN
INSERT (date, total_sales) VALUES(sales.date, sales.total_sales)
上記クエリは売上テーブルから1日の売上を集計して結果を集計テーブルにマージを想定しています。
ポイントは売上テーブルから抽出するときの`@run_date`です。
これはスケジュールクエリを実行した日のDATEを返します。
同じようにTIMESTAMPを返す`@run_time`もあります。
ここをCURRENT_DATE()としてもよいのですがもし過去の売上データに修正があった場合はどうでしょうか。
修正を行った日付が入ってしまうので別の日が更新されてしまいます。
もちろんWHERE句の抽出部分を該当の日に修正して実行する方法もありますが、実行した結果は実施者のクエリ履歴に消えてしまいます。
そんなときにバックフィルが役に立ちます。
スケジュールクエリの画面からバックフィルのスケジュール構成を選択しましょう。
修正する期間を入力することでクエリがその期間で実行されます。
もちろんスケジュールクエリの管理画面でも同様に確認することができます。
GIFでは12/1と12/2の2日間のデータが修正されたとし、その2日に対してバックフィルをしています。
抽出する日付はバックフィルから渡された日付となっています。
またジョブとしても一覧に追加されているのでいつ修正ジョブを流したかもこの画面から確認することが可能です。
1つバックフィルとパラメータで気をつけなければいけない点があります。
BigQueryでは基本的にタイムゾーンはUTCとなります。
ですので日本時間との時差を考慮する必要があります。
例えば日本の0-9時の間に実行された場合はrun_dateは1日前となります。
修正するつもりが値の不整合を起こしてしまう可能性もあるので必ず確認することをおすすめします。
まとめ
スケジュールクエリはBigQueryだけで完結する便利なサービスです。
もちろん複雑なジョブネットが必要になるようなものはCloud Composerのようなジョブスケジューラが必要となってきます。
しかし単体のSQLで完結するような場合や、ジョブスケジューラを用意するほどではない場合などには非常に便利なサービスです。
ぜひ使ってみて頂ければと思います。
