BigQueryのスケジューリングクエリで日付別シャード化テーブルを作成する

こんにちは！テクノロジー推進グループでエンジニアをやってるaboです。

この記事では、Google BigQueryのスケジューリングクエリを使って日付別シャード化テーブルを作成する方法を紹介します。

日付別シャード化テーブルはBigQueryにおけるテーブル分割の方法の1つです。弊社では費用と計算コスト減を目的にテーブル分割を行なっており、その中でも日付別シャード化テーブルは以下のようなメリットがあり、採用しています。

where句でテーブル名の絞り込みが出来るのでスキャン量が調整出来て便利
テーブルが日付ごとに分かれるので、バッチ失敗時のリランやテーブル再生成が気軽に行える

cloud.google.com

では実際に日付別シャード化テーブルを作成する手順に移ります。

手順

1. クエリを用意して「スケジュールされたクエリを新規作成」を選択

このクエリの結果がそのままテーブルの中身になります。下記の画像の例では、抽出対象となるテーブルが日付別シャード化テーブルになっており、前日分のデータを抽出しようとしています。

f:id:aboy_perry:20200422193705p:plain — クエリを書いて、スケジュールを新規作成

2. クエリ結果の書き込み先のテーブル名をシャード化したい日付にあわせて入力

ここが本題ですが、テーブル名の指定には、文字列のほか、run_date と run_time という2つのパラメータが使えます。特にrun_timeパラメータを使うと前日の日付でシャード化することができたりします。抽出対象とするデータが前日分だからシャード化する日付も前日のものにしたい、というケースで活躍します。

以下が入力例です。

例1）テーブルの日付をバッチ実行日にしたい

table名_{run_date}

例2）テーブルの日付をバッチ実行日の前日にしたい

table名_{run_time-24h|"%Y%m%d"}

f:id:aboy_perry:20200422203355p:plain — スケジュール設定画面

ハマりポイント

run_dateとrun_timeはUTCになるので注意が必要です！つまりスケジュールをJST 09:00以前にした場合、run_dateとrun_timeはJSTの実行日の前日を示します。例2ではそこからさらに-24hしているため最終的にJSTの実行日の前々日でシャード化されることになります ԅ( ˘ω˘ԅ)

他の入力例や詳細は下記をご覧ください。

cloud.google.com

3. 残りの入力欄に適当な値を設定して、「スケジュール」を実行

f:id:aboy_perry:20200422225501p:plain — スケジュールを実行

これでスケジューリングしたクエリが実行されたら、日付別シャード化テーブルができてるはずです！👏 下の画像のように、日付別シャード化テーブルはBigQueryのGUI上で日付ごとに選択できたり、見やすくなっています。

f:id:aboy_perry:20200422231227p:plain — 日付別シャード化テーブル

また、「スケジュールされたクエリ」画面では、スケジュールの詳細を見ることができ、次に実行される予定の時間を確認したり、クエリやスケジュールの変更を行うことができます。

f:id:aboy_perry:20200422224520p:plain — スケジュールされたクエリの一覧

日付別シャード化テーブルからデータを抽出する

できた日付別シャード化テーブルは、WHERE句で _table_suffix を用いて日付をまたいで検索することができます。

以下はhoge_events_{日付}という日付別シャード化テーブルから、過去3日分のデータを抽出する例です。

SELECT *
FROM
    `dataset_name.hoge_events_*` as events
WHERE
    _table_suffix BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 3 DAY)) AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY))

また、今月分のデータを指定する場合はLIKEを使って以下のようにもできます。

SELECT *
FROM
    `dataset_name.hoge_events_*` as events
WHERE
    _table_suffix LIKE FORMAT_DATE("%Y%m%%", CURRENT_DATE("Asia/Tokyo"))

おわりに

UTCである点はハマりポイントかなと思いますが、簡単に日付別シャード化テーブルを作ることができます。テーブル分割を取り入れ、コストを削減していきましょう〜。

コネヒトでは、データを活用してプロダクトを成長させたい！機械学習やりたい！といった方を募集中です！

コネヒト開発者ブログ