この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
ウィスキー、シガー、パイプをこよなく愛する大栗です。
BigQuery でカラムのデフォルト値を設定することが可能になりました!DWH はデータをインポートするときに一部のカラムに値が入らない場合などがあり、インポート後に NULL のカラムに対して一定の値を設定するとことが有ると思いますが、今回のアップデートでインポート後の値設定のいち部が不要になると思われます。
BigQuery のカラムに対するデフォルト値設定は2022年8月18日現在において、プレビューのステータスです。このプロダクトまたは機能は、Google Cloud Platform の利用規約の一般提供前のサービス規約の対象となります。一般提供前のプロダクトと機能では、サポートが制限されることがあります。また、一般提供前のプロダクトや機能に変更が加えられると、他の一般提供前バージョンと互換性がない場合があります。詳細については、リリースステージの説明をご覧ください。
カラムのデフォルト値
カラムのデフォルト値は、テーブルに新規にレコードを追加した時に値を設定していない場合に自動的に値を設定する機能です。一般的な RDBMS と同様に更新時に値は設定/更新されません。
カラムのデフォルト値は、リテラルか以下の関数のいずれかで有る必要があります。
- CURRENT_DATE
- CURRENT_DATETIME
- CURRENT_TIME
- CURRENT_TIMESTAMP
- GENERATE_UUID
- RAND
- SESSION_USER
- ST_GEOGPOINT
デフォルト値の設定はCREATE TABLE
文とALTER TABLE
文で設定できます。また、ALTER TABLE
文でデフォルト値の設定を削除することも可能です。
やってみた
ここではbq
コマンドを使用して実行しますが、Cloud Console の BigQuery UI 上で同様のクエリを実行しても同じ結果となります。
テーブル作成時のデフォルト値設定
$ bq query --use_legacy_sql=false 'CREATE SCHEMA default_value_dataset;'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (0s) Current status: DONE
num1
とstr1
にデフォルト値を設定したテーブルを作成します。
$ bq query --use_legacy_sql=false 'CREATE TABLE default_value_dataset.foo
(
num1 int default 9999,
str1 string default "Hello",
d datetime
);'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (0s) Current status: DONE
Created myproject.default_value_dataset.foo
レコードを挿入すると値を設定していないstr1
にデフォルト値のHello
が入っています。
$ bq query --use_legacy_sql=false 'INSERT INTO `default_value_dataset.foo` (num1)
VALUES (1);'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (1s) Current status: DONE
Number of affected rows: 1
$ bq query --use_legacy_sql=false 'SELECT * FROM `default_value_dataset.foo`;'
+------+-------+------+
| num1 | str1 | d |
+------+-------+------+
| 1 | Hello | NULL |
+------+-------+------+
str1
にNULL
を設定するとデフォルトは設定されずNULL
が設定されます。
$ bq query --use_legacy_sql=false 'INSERT INTO `default_value_dataset.foo` (num1, str1)
VALUES (2, NULL);'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (1s) Current status: DONE
Number of affected rows: 1
$ bq query --use_legacy_sql=false 'SELECT * FROM `default_value_dataset.foo`;'
+------+-------+------+
| num1 | str1 | d |
+------+-------+------+
| 2 | NULL | NULL |
| 1 | Hello | NULL |
+------+-------+------+
既存カラムのデフォルト値設定
次に既存カラムのd
にデフォルト値を設定してみます。以下のクエリでd
カラムにデフォルトで現在時刻を設定します。
$ bq query --use_legacy_sql=false 'ALTER TABLE `default_value_dataset.foo`
ALTER COLUMN d SET DEFAULT CURRENT_DATETIME();'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (0s) Current status: DONE
Altered myproject.default_value_dataset.foo
もう一度レコードを挿入してみます。今度はd
カラムに現在時刻が設定されています。
$ bq query --use_legacy_sql=false 'INSERT INTO `default_value_dataset.foo` (num1)
VALUES (3);'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (1s) Current status: DONE
Number of affected rows: 1
$ bq query --use_legacy_sql=false 'SELECT * FROM `default_value_dataset.foo`;'
+------+-------+----------------------------+
| num1 | str1 | d |
+------+-------+----------------------------+
| 1 | Hello | NULL |
| 2 | NULL | NULL |
| 3 | Hello | 2022-08-18T08:31:23.863404 |
+------+-------+----------------------------+
デフォルト値の評価タイミングの確認
d
カラムには非決定的なCURRENT_DATETIME()
をデフォルト値で設定しています。そのため大量のレコードを挿入した時に最初のレコードと最後のレコードの値がそうなるのか挙動を確認したいと思います。
ここで 6,000 万レコード近くあるテーブルであるbigquery-public-data.new_york_citibike.citibike_trips
を使用します。
$ bq query --use_legacy_sql=false 'SELECT COUNT(*)
FROM `bigquery-public-data.new_york_citibike.citibike_trips`;'
+----------+
| f0_ |
+----------+
| 58937715 |
+----------+
大量のレコードを挿入してみます。
$ bq query --use_legacy_sql=false 'INSERT INTO `default_value_dataset.foo` (num1)
SELECT start_station_id
FROM `bigquery-public-data.new_york_citibike.citibike_trips`;'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (5s) Current status: DONE
Number of affected rows: 58937715
d
カラムの値を確認してみます。すると先程 5 秒程度かかって挿入したデータですがd
カラムには同一の値が設定されています。つまりクエリ実行時に1回だけデフォルト値を評価していると思われます。
$ bq query --use_legacy_sql=false 'SELECT d, COUNT(*)
FROM `default_value_dataset.foo`
GROUP BY d;'
+----------------------------+----------+
| d | f0_ |
+----------------------------+----------+
| NULL | 2 |
| 2022-08-18T08:31:23.863404 | 1 |
| 2022-08-18T08:41:47.760857 | 58937715 |
+----------------------------+----------+
デフォルト値の設定削除
デフォルト値の設定を削除してみます。
$ bq query --use_legacy_sql=false 'ALTER TABLE `default_value_dataset.foo2` ALTER COLUMN str1 DROP DEFAULT;'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (0s) Current status: DONE
Altered myproject.default_value_dataset.foo
レコードを挿入して確かめてみます。str1
カラムがNULL
になっておりデフォルト値の設定が削除されていることが確認できました。
$ bq query --use_legacy_sql=false 'INSERT INTO `default_value_dataset.foo` (num1, d)
VALUES (0, "2000-01-01");'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (0s) Current status: DONE
Number of affected rows: 1
$ bq query --use_legacy_sql=false 'SELECT *
FROM `default_value_dataset.foo`
WHERE d = "2000-01-01";'
+------+------+---------------------+
| num1 | str1 | d |
+------+------+---------------------+
| 0 | NULL | 2000-01-01T00:00:00 |
+------+------+---------------------+
さいごに
デフォルト値はデータベースとしては結構基本的な機能だと思っていました。そのため当然有るものだと考えていたため、今回のアップデートを確認して今まで無かったと知り、不思議に思いました。デフォルト値は無ければ無いでどうにかできるものですが、デフォルト値が設定できるので余計な手間が省けるようになったかなと思います。