bqコマンドで日付パーティションに対応した複数日分のダミーデータを作成してみた

BigQuery上で日毎にパーティショニングを切りつつ、複数テーブルを元にDataPortal上でデイリーのレポートを試しに作っていました。問題は、テーブルのうち一つのデータが一日分しかないところです。単純に日数分複製をすることに決めましたが、なるだけコストを抑えつつ操作の手間を省きたいためにマネージドサービスを使わずローカルで bq にてやってみました。

デイリー用のテーブルを作成する

ベースとなるデータが入っているテーブルを元にして、パーティショニング用のdateフィールドを追加したテーブルを作成します。

まずは元のテーブルからスキーマをdumpします。

bq show --schema --format=prettyjson 'project:dataset.table'> source.json

念の為source.jsonの中身を確認しておきます。エラーの場合はJSONデータではなくエラーメッセージになっているためです。

次にdateフィールドをJSON定義して結合します。

vim date.json

[
  {
    "name": "date",
    "type": "DATE"
  }
]

jq -s add date.json source.json > schema.json

そしてデイリー用のテーブルを作成します。

bq mk --table \
--schema ./schema.json \
--time_partitioning_field date \
--require_partition_filter \
dataset.daily

デイリー用テーブルにデータを追加する

元のデータに日付を追加した状態にて、必要日数分Insertし続けます。

vim insert.sh

#! /bin/sh
d=$1
end=$2
while [ "$d" != "$end" ]; do 
  echo $d
  query="insert into \`dataset.daily\` select DATE('${d}') as date, id, name, mail, enabled, registeredAt from \`dataset.table\`;"
  echo $query
  bq query --use_legacy_sql=false $query
  d=$(gdate --date "$d + 1 day" +%Y-%m-%d)
done

% sh insert.sh 2021-12-01 2021-12-31
2021-12-01
insert into `dataset.daily` select DATE('2021-12-01') as date, id, name, mail, enabled, registeredAt from `dataset.table`;
Waiting on bqjob_xxxxxxxxxxxxxx_000000000000000000_1 ... (1s) Current status: DONE
Number of affected rows: 6630

2021-12-02
insert into `dataset.daily` select DATE('2021-12-02') as date, id, name, mail, enabled, registeredAt from `dataset.table`;
Waiting on bqjob_xxxxxxxxxxxxxx_000000000000000000_1 ... (1s) Current status: DONE
Number of affected rows: 6630

2021-12-03
insert into `dataset.daily` select DATE('2021-12-03') as date, id, name, mail, enabled, registeredAt from `dataset.table`;
Waiting on bqjob_xxxxxxxxxxxxxx_000000000000000000_1 ... (1s) Current status: DONE
Number of affected rows: 6630
..
..

実行し終えたら、正常にデータが追加されているかを確認します。

まずは件数。

bq query --use_legacy_sql=false 'select count(*) from `dataset.daily` where date >= "2021-12-01"'
Waiting on bqjob_xxxxxxxxxxxxxx_000000000000000000_1 ... (0s) Current status: DONE
+--------+
|  f0_   |
+--------+
| 205530 |
+--------+

そして実データ。

% bq query --use_legacy_sql=false 'select * from `dataset.daily` where date >= "2021-12-01" limit 10'
+----------+--+----+----------------+-------+------------+
|date      |id|name|mail            |enabled|registeredAt|
+----------+--+----+----------------+-------+------------+
|2021-12-01|1 |test|test@example.com|1      |2021-10-30  |
|2021-12-01|2 |test|test@example.com|1      |2021-10-30  |
|2021-12-01|3 |test|test@example.com|1      |2021-10-30  |
..
..

値は完全に同じものばかりですが、デイリーでのレポート構成用には仕上がりました。

あとがき

当初は1日分のみのデータであるため月単位のデータとして結合を試みていました。が、結合先はデイリー構成で日付フィールドは YYYY-MM-DD 構成。月指定向けとしてMONTHでTRUNCを掛けることも考えましたが、本来各日分のデータがある想定のため今回のやり方に切り替えました。

bqコマンドへqueryを渡す際にtable名は必ずバッククォートで囲む必要があり、シェルスクリプト内で書く場合はバッククォートに対してエスケープを入れないとエラーになる点だけ注意が必要です。