はじめに
BigQueryで既存テーブルにフラグカラムを追加しようとしました。
よくあるやつです。論理削除フラグとか、有効フラグとか。
書いたSQLはこれです。
ALTER TABLE `project_id.dataset_id.table_name`
ADD COLUMN IS_FLG INTEGER NOT NULL DEFAULT 0;
見た目は完全に正しい。
RDBを触ってきた人なら、何の違和感もないはずです。
でも、エラーになります。
しかも、DEFAULT 0 も書いているのにダメ。
「え、なんで？」となりました。
さらに混乱するのは、CREATE TABLEなら同じ定義が通ることです。
今回はこの挙動について整理します。
結論
BigQueryでは以下の挙動になります。
✅ CREATE TABLE時は NOT NULL + DEFAULT を指定できる
❌ ALTER TABLEで既存テーブルにNOT NULLカラムは追加できない
❌ NULLABLE → NOT NULL に変更もできない
これは仕様です。
CREATE TABLEでは問題なく定義できる
例えば以下のSQLは正常に実行できます。
CREATE TABLE `project_id.dataset_id.table_name` (
id INT64,
is_flg INT64 NOT NULL DEFAULT 0
);
この場合、テーブルは新規作成です。
まだ1行もデータが存在しません。
そのためBigQueryは安全に
- NOT NULL制約
- DEFAULT値
を定義できます。
この挙動は、一般的なRDBと同じです。
ではなぜALTER TABLEでは失敗するのか？
問題は「既存データの扱い」です。
すでにデータが入っているテーブルに対して、REQUIRED（NOT NULL）カラムを追加することをBigQueryは許可していません。
たとえこう書いてもです。
ALTER TABLE `project_id.dataset_id.table_name`
ADD COLUMN IS_FLG INT64 NOT NULL DEFAULT 0;
一見、DEFAULT 0 があるので既存行にも0が入って安全に見えます。
しかしBigQueryは内部仕様として、
既存テーブルにREQUIREDフィールドは追加不可
としています。
DEFAULTがあるかどうかは関係ありません。
ここがRDBとの大きな違い
PostgreSQLやMySQLなどのOLTP向けRDBでは、
DEFAULT付きでNOT NULLカラムを追加できる
既存行には自動でDEFAULT値が入る
という動きが一般的です。
しかしBigQueryは分析基盤（DWH）です。
設計思想が違います。
BigQueryは、
トランザクション整合性よりもスキャン性能を優先
スキーマ変更は最小限に抑える
大量データ前提の列指向ストレージ
という思想で作られています。
そのため、制約系のALTERはかなり制限されています。
実務での正しい対応方法
既存テーブルにカラムを追加したい場合は、次の手順になります。
① NULLABLEで追加する
ALTER TABLE `project_id.dataset_id.table_name`
ADD COLUMN IS_FLG INT64 DEFAULT 0;
この時点ではカラムはNULLABLEです。
② 必要なら既存データをUPDATEする
UPDATE `project_id.dataset_id.table_name`
SET IS_FLG = 0
WHERE IS_FLG IS NULL;
これで既存データには値が入ります。
③ それでもNOT NULLにしたい場合
残念ながら、
NULLABLE → NOT NULL へのALTER変更は不可
REQUIREDカラムの後付けは不可
です。
つまりテーブル再作成が必要になります。
例えば：
CREATE OR REPLACE TABLE `project_id.dataset_id.new_table` AS
SELECT
id,
IFNULL(IS_FLG, 0) AS IS_FLG
FROM `project_id.dataset_id.old_table`;
その上で、スキーマを明示的にNOT NULLで定義し直します。
BigQueryでは「制約よりクエリ制御」が基本
BigQueryでは、物理的な制約に頼るよりも、
クエリ側でIFNULLを使う
データ投入時に整形する
アプリケーション側で保証する
という設計が一般的です。
DWHはOLTPとは思想が違います。
ここを理解していないと、
「RDB感覚で触ってハマる」
という状況になります。
今回のケースはまさにそれでした。
CREATEでは通るのにALTERでは通らない。
地味ですが、かなりハマりやすいポイントです。
BigQueryを触り始めた人が同じところで時間を溶かさないように、備忘録として残します。