BigQueryのテーブルスキーマ変更方法について

こんにちは、香田です。

今回はBigQueryのテーブルスキーマ変更方法について紹介していきます。

内容としては、列の追加、列の削除、列名の変更、列のデータ型の変更方法について、サンプルのテーブルを利用しながら手順を説明していきます。

検証用のテーブルを用意する

事前準備として、スキーマ変更検証用にBigQueryの一般公開データセットよりテーブルを一つ自身のプロジェクトにコピーします。

利用するテーブルは、表のサイズが小さいbigquery-public-data:samples.shakespeareを利用していきます。

検証用のデータセットを作成します。

$ bq mk samples

自身のプロジェクトにテーブルをコピーします。

$ bq cp \
bigquery-public-data:samples.shakespeare \
samples.shakespeare

列の追加

はじめに列の追加方法としてbq updateコマンドで追加する方法とALTER TABLEで追加する方法を紹介していきます。

bq updateで追加

コピーした検証用テーブルよりJSON形式でスキーマファイル取得します。

$ bq show \
--schema \
--format=prettyjson \
samples.shakespeare > shakespeare.json

末尾に新しい列を追加します。

$ vim shakespeare.json

例えばcreated_dateという名前でDATE型を追加する場合、下記の差分箇所のような内容をスキーマファイルへ追加します。

注意点として既存のテーブルに列を追加する場合、modeでREQUIREDを指定することはできません。

@@ -22,5 +22,11 @@
     "mode": "REQUIRED",
     "name": "corpus_date",
     "type": "INTEGER"
+  },
+  {
+    "description": "test description",
+    "mode": "NULLABLE",
+    "name": "created_date",
+    "type": "DATE"
   }
 ]

スキーマファイルを更新後、bq updateを実行することで列が追加されます。

$ bq update samples.shakespeare shakespeare.json

ALTER TABLEで追加

また下記のようにALTER TABLEを実行することで、列の追加が可能です。

bq query \
--nouse_legacy_sql \
'ALTER TABLE
   samples.shakespeare ADD COLUMN
IF NOT EXISTS created_date DATE OPTIONS(description="test description")'

列の削除

SELECT * EXCEPTで削除

次は列の削除方法として、SELECT * EXCEPTを使用して削除する方法を紹介していきます。

下記のようにbq queryコマンドを利用し、削除したい列をEXCEPTで指定しクエリ実行結果をreplaceオプションで既存テーブルへ上書きすることで削除可能です。

但しbq queryで上書きする場合、modeの値が全てNULLABLEに変更されるので注意してください。

$ bq query \
  --destination_table samples.shakespeare \
  --replace \
  --use_legacy_sql=false \
'SELECT
  * EXCEPT(corpus_date)
FROM
  samples.shakespeare'

元のmodeを保持して列の削除が必要な場合は、別途作業用にテーブルを用意しbq cpコマンドで既存テーブルを上書きする流れとなります。

事前に対象の列削除済みのスキーマで作業用のテーブルを作成します。

$ bq mk --table \
--schema shakespeare.json \
samples.tmp_shakespeare

既存のテーブルに対して、SELECT * EXCEPTした実行結果を作業用のテーブルへINSERTします。

$ bq query \
--use_legacy_sql=false \
'INSERT INTO samples.tmp_shakespeare
SELECT
  * EXCEPT(corpus_date)
FROM
  samples.shakespeare'

作業用テーブルをbq cpコマンドで上書きコピーすることで、列のmodeを保持した状態で対象の列を削除可能です。

$ bq cp samples.tmp_shakespeare samples.shakespeare

列名の変更

次は列名の変更方法になります。削除の場合と同じくSELECT * EXCEPTを使用していきます。

変更したい列をEXCEPTで指定し、変更後の列名をAS句で指定した実行結果をreplaceオプションで既存テーブルへ上書きすることで列名は変更可能です。

注) bq queryで上書きする場合、modeの値が全てNULLABLEに変更されますが、列の削除と同じような流れで回避可能です。

$ bq query \
  --destination_table samples.shakespeare \
  --replace \
  --use_legacy_sql=false \
'SELECT
  * EXCEPT(corpus_date),
  corpus_date AS rename_corpus_date
FROM
  samples.shakespeare'

列のデータ型の変更

最後に列のデータ型の変更方法です。対象の列をCASTして変更する方法を紹介していきます。

データ型を変更したい列をCASTで指定しreplaceオプションで既存テーブルへ上書きすることでデータ型の変更可能です。

注) bq queryで上書きする場合、modeの値が全てNULLABLEに変更されますが、列の削除と同じような流れで回避可能です。

$ bq query \
  --destination_table samples.shakespeare \
  --replace \
  --use_legacy_sql=false \
'SELECT
  word,
  word_count,
  corpus,
  CAST(corpus_date AS STRING) corpus_date
FROM
  samples.shakespeare'

さいごに

BigQueryのテーブルスキーマ変更方法いかがでしたでしょうか。

スキーマの変更の流れについて、本記事が参考になれば幸いでございます。

最後までご覧いただきありがとうございます。