BigQueryの取り込み時間パーティションテーブルのスキーマ更新手順メモ

背景

取り込み時間パーティションテーブルにおけるスキーマ変更作業は非常に面倒。
クエリを使った削除や、GCSを経由するLoadジョブだとパーティション情報は失われてしまうので使えない。
テーブル スキーマからの列の削除

今回はINSERT(..., _PARTITIONTIME) を使ってパーティション情報を維持したまま、スキーマを更新する手順をメモしていく。

手順流れ

1. 一時テーブル作成
2. insert用SQLの作成 & 実行
3. テーブルの置き換え & バックアップ

一時テーブルの作成

更新後のスキーマとなる一時テーブルを作成していく。

まずは、対象のテーブルのスキーマを取得

bq show --schema --format=prettyjson dataset.target_table > schema.json

次に、schema.jsonを開いて更新したいテーブル定義に編集する。schema_updating_columnというカラムをSTRING -> INTEGERに変更する想定する。

schema_edit_sample.json

[
# ~略~
{
        "type": "INTEGER", # STRINGから変更
        "name": "schema_updating_column",
        "mode": "NULLABLE"
    },
# ~略~
]

編集したschema.jsonで一時テーブルを作成する。

bq mk --time_partitioning_type=DAY --table dataset.tmp_target_table ./schema.json

insert用のSQLの作成&実行

まずは対象テーブルのカラム一覧を取得する。
カラム数が多い場合は以下のようなSQLでカラム一覧を取得してもいい。
この時更新対象のカラム(schema_updating_column)を一覧から外しておくと便利。

SELECT
  ARRAY_TO_STRING(ARRAY_AGG(column_name), ', ')
FROM dataset.INFORMATION_SCHEMA.COLUMNS
WHERE table_name="target_table"
AND column_name != 'schema_updating_column'

これを元にINSERT用のSQLを作成。schema_updating_columnはINSERTの最後尾に追加。SELECT側でcastしておく。

INSERT INTO dataset.tmp_target_table (column1, column2, ... ,_PARTITIONTIME, schema_updating_column)
SELECT column1, column2, ... _,PARTITIONTIME, cast(schema_updating_column as INT64) FROM dataset.target_table

これを実行すると、一時テーブルにschema更新されたテーブルがパーティション付きでインサートされる。

テーブルの置き換え & バックアップ

target_table と tmp_target_table でデータに差異がないかを確認する。
例えば、テーブル全体件数や、パーティションごとのデータ件数が同じか確認する。
問題なければ、テーブルの置き換え作業に入る。

まず念のため、target_tableをバックアップとっておく。これはBigQueryのコピー機能を使って別名で保存しておけばいい。

バックアップをとったら、置き換えのためにtarget_tableを削除する。

最後に、tmp_target_tableをtarget_tableに置き換える。これも、コピー機能を使って、tmp_target_tableをtarget_tableとしてコピーして、tmp_target_tableを削除すればいい。

参照

BigQueryの取り込み時間パーティションテーブルのカラム削除