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

DataPortal上でデイリーでのレポートを試してみたいけど、手元には1日分しかデータがないといった状況にありました。手段を色々と検討した末、とりあえず値の精度はさておき、複数日分のデータとして複製してみました。

#Google BigQuery

#Google Cloud (GCP)

haoyayoi

2022.01.18

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

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  |
..
..

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