bqコマンドでschema_update_optionを指定すると、カラム追加の運用が楽な話

サマリ

• schema_update_option に ALLOW_FIELD_ADDITION を指定すると、 カラムが追加された場合でも、別途tableのschema update作業をしなくても良いという話。
• 日付別パーティショニングテーブルに、日次でCSVデータを投入していくようなシステムを想定している。
• 公式ドキュメントの記載はこちら
• 今回BQコマンドでやっているが、当然APIとして提供されているので、各種SDKを使ったLoadでも同じことができます（Embulkとかでも）
  • Embulkの例はこちら

実験した環境

テーブル名

• foobar

テーブル作成コマンド

bq mk --table --require_partition_filter --time_partitioning_type=DAY 'project_id:dataset_id.foobar'

Day 1

通常以下のようにLoadされているとします。

テーブルスキーマ

foobar.schema1.json

[
  {
    "mode": "NULLABLE",
    "name": "a",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "b",
    "type": "INTEGER"
  }
]

投入データ

foobar1.csv

"a","b"
"abc",1
"xyz",9

Loadコマンド

bq load \
--source_format=CSV --skip_leading_rows 1 \
'project_id:dataset_id.foobar$20200610' ./foobar1.csv ./foobar.schema1.json

途中からカラムが増える

途中から読み込むCSVのカラムが増えたと想定します。
この場合、

• schemaファイルの変更
• bq update（table schemaのupdate）

の2つが必要に思いますが、schema_update_option に ALLOW_FIELD_ADDITION を指定することによって、 bq update（table schemaのupdate） が不要になります。

Day 2

テーブルスキーマ

• cというカラムが追加されている

foobar.schema2.json

[
  {
    "mode": "NULLABLE",
    "name": "a",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "b",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "c",
    "type": "STRING"
  }
]

投入データ

• cというカラムが追加されている

foobar2.csv

"a","b","c"
"abc",1,"zzz"
"xyz",9,"zzz"

Loadコマンド

• 新スキーマを指定している

bq load \
--source_format=CSV --skip_leading_rows 1 \
--schema_update_option ALLOW_FIELD_ADDITION \
'project_id:dataset_id.foobar$20200611' ./foobar2.csv ./foobar.schema2.json

load結果

期待したとおり、Day2で増えたカラムに、過去分にはnullが入っています。

再実行時の注意点

rerunする場合、loadする前にパーティションを削除しないと2重にデータが入ってしまうので注意

再実行前にパーティションを消す

bq rm --force 'project_id:dataset_id.foobar$20200611'

と、これを書いたとに、 --replace オプションで同様のことができることがわかりました。Day2のクエリに --replace をつけるだけで良さそうです。

bq load \
--replace \
--source_format=CSV --skip_leading_rows 1 \
--schema_update_option ALLOW_FIELD_ADDITION \
'project_id:dataset_id.foobar$20200611' ./foobar2.csv ./foobar.schema2.json

まとめ

というわけで、実際の運用上は、日次で以下のようなbqコマンドを回せばやりたいことができそうです。

export LOAD_FILE="./foobar2.csv"
export TARGET_TABLE="'project_id:dataset_id.foobar$20200611'"
export SCHEMA="./foobar.schema2.json"
bq load \
--replace \
--source_format=CSV --skip_leading_rows 1 \
--schema_update_option ALLOW_FIELD_ADDITION \
${TARGET_TABLE} ${LOAD_FILE} ${SCHEMA}