BigQueryのスキーマ変更あたりのドキュメントを改めて読んだメモ

対象ドキュメント

スキーマの指定

• BigQueryのスキーマを指定する方法は以下のいずれか
  • GCPConsoleを使用する
  • 従来の BigQuery ウェブ UI を使用する
  • CLI を使用してインラインで指定する
    • JSON 形式のスキーマ ファイルを作成する
  • APIで作る

手動でのスキーマの指定

• UI, bq client, スクリプトでスキーマ設定できる
  • 運用が楽そうなのは、 bq client + jsonでのスキーマ管理
  • コード管理できるのが良い
  • スクリプト書いてもいいけど、bq clientの方が楽そう

JSON スキーマ ファイルの指定

bq client使って既存のテーブルからjsonとる

bq show --schema --format=prettyjson project_id:dataset.table > path_to_file

bq client使ってJSONのスキーマファイル使って空テーブル作成する

bq mk --table project_id:dataset.table path_to_schema_file

bq client使って、用意したJSONスキーマファイルのテーブルを作成し、csvファイルのデータをloadする

bq load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json

スキーマの自動検出

• データソース内のファイルをランダムに選出
• ファイルの最大100行をスキャンして推定する
• 大きいファイルだと結構失敗するので、個人的には、String, nullableでとりあえず、BQ tableに突っ込んで、後からparseしていくのがおすすめ

スキーマの変更

• モードが required -> nullalble は可能。
• 以下はできない
  • カラム名変更
  • データ型変更
  • モード変更(required -> nullable以外)
  • 列の削除
  • requiredの列の追加

nullableの列の追加などであれば、bq update でやるのが良さそう

まずは既存テーブルからJSONスキーマを取得し

bq show --schema --format=prettyjson mydataset.mytable > /tmp/myschema.json

手元でJSONスキーマに、追加した列を追加して、bq updateコマンドを叩く

bq update project_id:dataset.table schema

テーブルスキーマの手動変更

列の名前変更 & 列のモード変更 & 列の削除

bq update　ではできなかった、列の名前変更や、列のモード変更や、列の削除は、以下の2つの実現方法がある

1. SQLを使用する

• 簡単だが、金額的コストがかかる
  • カラム名を変更した結果となるSQLを書いて、自身のテーブルを上書きする
• bq --replace を使うのが楽そう
• 以下のようにすると、列の名前が変更できる
• 超巨大なサイズのテーブルでなければこれがいいと思う

bq query --destination_table mydataset.mytable --replace --use_legacy_sql=false \
'SELECT
  * EXCEPT(column_one, column_two),
  column_one AS newcolumn_one,
  column_two AS newcolumn_two
FROM
  mydataset.mytable'

2. GCSにexportしてから取り込む

• 面倒だが、金額的コストはかからない
• exportジョブも読み込みジョブも無料