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

8831 ワード
gcp BigQuery BigQuery テキストリンク

サマリ

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