BigQueryでスケジュールされたクエリを使ってみた②
投稿者：松井 大樹
前回のブログ（リンク）でBigQueryのスケジュールについて解説したので
今回はその応用編、スケジュールクエリの月末実行について試したいと思います
今回の利用方法
今回の利用想定としては
「月末に各テーブルの利用量について1回で取得する」ということを目的に実装していきます。
問題点①
前回のブログを含め、ここまでスケジュールされたクエリについて説明してきましたが一つ注意点があります。
今まで説明したスケジュールされたクエリは、月末ごとに使用量を確認するクエリを投げてみたいっといった要求に関して、対応することができません。
月毎に30日と31日（2月28日）、など月末の日付が変わってしまうためこのままではうまく設定できません。
なのでGoogleのサポートに問い合わせを行ってみました。
Googleからの回答
現状、月末に1回で設定する方法はありません。
28日~31日を設定して各月で手動で変えてみてください。
同様のリクエストは上がっていますが、改修の予定については未定となっております。
とのことでした。
参考までに、Stackoverflowでの回答を貼っておきます。
https://stackoverflow.com/questions/61816095/bigquery-ui-how-to-schedule-a-query-to-run-on-the-last-day-of-the-month
1回の設定で実装するには？
では月末に一回の設定で実装できる方法を考えてみましょう。
月によって月末の日付は異なりますが、月初の1日という日付は同じなので月初の1日の0時0分という日付に設定をすると、月末の日付での実行と同じ動作が可能となります。
実装
では実装していきます
まずはクエリ文を書きます
SELECT
table_catalog,
table_schema,
table_name,
total_rows,
total_billable_bytes/1000/1000 AS total_billable_MB,
total_billable_bytes/1000/1000/1000 AS total_billable_GB,
CURRENT_DATE AS date
FROMdt_portal.INFORMATION_SCHEMA.PARTITIONS;
取得行について
table_catalog（プロジェクト名）
table_schema（データセット名）
table_name（テーブル名）
total_rows（テーブル行数）
total_billable_MB（MB）
total_billable_GB（GB）
date（日付）
※今回は1MB＝1000Byteで計算を行います
クエリまで設定の一連の流れ
- 画面上のスケジュールを選択
「スケジュールされたクエリを新規作成」を押します
- スケジュールされたクエリを新規作成
「詳細とスケジュール」にクエリの名前を入力します。
※任意の時刻に開始と終了日を設定する事が可能です（今回は毎日で設定します）
「スケジュールオプション」で実行スケジュールを選択します。
※希望の実行スケジュールがない場合はカスタムを選択しましょう。
※カスタムスケジュールの書式についてはこちらを参照。
問題点②
カスタムスケジュールで設定した日付はUTC（協定世界時）になるので注意
UTC-9（日本標準時）ではありません。日本時間と9時間ずれます。
時間がずれる例
カスタムスケジュールを下記の画像の様に設定します。
UI出の実行スケジュールは0時00分ですが次回の実行予定日は9時00分となっています。
実際に実行されるのは日本時間の9時なので注意しましょう。
カスタムスケジュールに以下の設定を行います。
※日本時間とのずれがあるので前日の15時に時刻を設定します。
前月が30日で終わる場合、
30 of april, june, september, november 15:00
前月が31日で終わる場合、
31 of january, march, may, july, august, october, december 15:00
を設定します。
※毎月行いたい場合は同じクエリを2つ用意して30日分と31日分で使い分ける
※今回は2月の28日、29日について割愛します（後述の問題点③にて記載）
「クエリ結果」の書き込み先を指定します
全て記入し、「設定は有効です」の文言を確認したら「保存」を選択します。
問題点③
上記でカスタムスケジュールの設定を行いましたたが、カスタムスケジュールに
BigQueryの仕様上30日で設定した場合31日がある月には1日ではなく31日に実行されます。
また2月の分は別途で28日と29日を使い分ける必要があります。
こちらもGoogleに確認をしたところ以下の回答が来ました。
今のところ30日設定で31日に動作してしまう問題の解決策はありません。
修正を行う予定ですが、修正に関する ETA (推定完了時間) はありません。
新バージョンのリリースで修正する予定です。
回答をまとめると現状としては30日と31日の実行クエリを分ける以外の方法はないとのことでした
次回以降のBigqueryの改修に期待という形になります
Googleの方に回答をいただいたのですが、来年の2月頃までに改修が入るとのことでしたので、2月の日付指定に関しては今回は割愛させていただきました。
参考までに公式リリースのリンクを貼っておきます
https://cloud.google.com/bigquery/docs/release-notes
まとめ
これでBigQueryのスケジュールされたクエリの月末実行は完了となります。
いろいろと問題点はありますが一度設定をすると次回から定期的にクエリを実行してくれるのはとても便利です。皆様も是非お試しください。
最後に今回の方法以外にも実装方法はいくつかあるので、もっと良い実装方法があるよといった方は是非コメントいただけると助かります。
