BigQueryのパーティション分割テーブルでカラムの型変更を行う

サマリ

• BigQueryには、RDBMSでよくある、ALTER TABLE等でのカラムの型変更の機能がありません(2020年12月現在)
• そのため、カラムの型の変更を行う場合は多少工夫が必要になります。
• 具体的には、該当カラムが変更後のテーブル(table_b)を別途用意し、元のテーブル（table_a)からtable_bにInsertする形になります。
• ただ、パーティショニングテーブルの場合には、Insert時に_PARTITIONTIMEを指定するなどの工夫が必要です。
• このやり方で、型の変更だけでなく、当然カラム追加、削除も可能。

具体例

• 要件
  • テーブルAのvalueカラム(INTEGER)をSTRINGにしたい。
  • テーブルAは日付でパーティショニングされたテーブルである。
    • timePartitioning
      • requirePartitionFilter : true
      • type : DAY
• やりかた
  • STEP1. valueカラムをSTRINGにした（それ以外のスキーマ構造はtable_aと同じの）table_bを作成する
  • STEP2. table_aをSELECTして、table_bにINSERT
  • STEP3. table_aをrenameしてcopyないしremove、table_bをtable_aと同じ名前に変更。

STEP1. insert先のtable_bの作成

table_a

[
  {
    "mode": "NULLABLE",
    "name": "id",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "value",
    "type": "INTEGER"
  }
]

table_b.json

[
  {
    "mode": "NULLABLE",
    "name": "id",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "value",
    "type": "STRING"
  }
]

table_bの作成

bq mk --table --schema table_b.json --require_partition_filter --time_partitioning_type=DAY 'foo-project:bar-dataset.table_b'

STEP2. table_aをSELECTして、table_bにINSERT

• ポイント
  • INSERT INTO で _PARTITIONTIME を指定してあげることにより、元テーブルと同じパーティションにINSERTすることができる。
  • INSERT INTO ~ のところで、 _PARTITIONTIME の代わりに、 _PARTITIONDATE などは使えない。 _PARTITIONTIME のみ。

INSERT INTO
  `foo-project:bar-dataset.table_b` (_PARTITIONTIME,
    id,
    value)
SELECT
  _PARTITIONTIME,
  id,
  CAST(value as STRING)
 FROM
  `foo-project:bar-dataset.table_a`
 WHERE
  _PARTITIONTIME >= TIMESTAMP("2020-01-01")

STEP3. table_aをrenameないし削除、table_bをtable_aと同じ名前に変更。

table_aはrenameしてbackupするなり、削除するなりする。

bq cp "foo-project:bar-dataset.table_a" "foo-project:bar-dataset.backup_table_a"
bq rm -f "foo-project:bar-dataset.table_a"

table_bをtable_aとしてcopyし、table_bは削除。

bq cp "foo-project:bar-dataset.table_b" "foo-project:bar-dataset.table_a"
bq rm -f "foo-project:bar-dataset.table_b"

ここまでやってやっとおしまい。