BigQueryのスキーマを変更する

背景

BigQueryでカラムを追加してほしいという依頼がきました。重要なカラムのようで、今までのデータは不要というレアなケースです。ですので、一度データセットを消しますが、スキーマ変更では一般的ではないと思いますので、後ほどデータを消さないスキーマ変更の方法についても調べてみようと思います。

スキーマの変更

既存スキーマの取得

まず既存のスキーマを取得します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ bq show --schema --format=prettyjson project_name:dataset_name.table_name
[
{
"mode": "REQUIRED",
"name": "id",
"type": "STRING"
},
{
"mode": "REQUIRED",
"name": "hostname",
"type": "STRING"
},
...
]

ファイル出力する場合はリダイレクトします。

1
$ bq show --schema --format=prettyjson project_name:dataset_name.table_name > schema.json

テーブルの削除

既存のスキーマを保存したのでテーブルを削除します

1
2
3
4
5
6
7
8
$ bq rm project_name:dataset_name.table_name


Updates are available for some Cloud SDK components. To install them,
please run:
$ gcloud components update

rm: remove table 'project_name:dataset_name.table_name' (y/N)

yを入力してエンターキーを押すと削除できます

スキーマの変更

先ほど保存したスキーマのファイルを変更します。追加したいカラムを追記します。

1
2
3
4
5
6
7
8
$ git diff schema.json
},
+ {
+ "mode": "NULLABLE",
+ "name": "additional_id",
+ "type": "STRING"
+ },
{

追記したらこのスキーマファイルを使ってテーブルを作成します。

作成していたテーブルは以前の記事のBigQueryで時間単位の列でのパーティション分割テーブルを作成するで作成したパーティション分割テーブルなので --time_partitioning_fieldオプションもつけます。

1
2
$ bq mk --table --schema schema.json --time_partitioning_field created_date project_name:dataset_name.table_name
Table 'project_name:dataset_name.table_name' successfully created.

テーブルが作成できました。

データを消さないスキーマ変更

データを消さない(通常の)スキーマ変更の方法はドキュメントに記載があります。

https://cloud.google.com/bigquery/docs/managing-table-schemas

bq updateというコマンドがあるんですね。

1
$ bq update project_id:dataset.table schema.json

schema.jsonを書き換えるだけでOKでした。

おまけ

先ほどテーブル削除時に

1
2
3
Updates are available for some Cloud SDK components.  To install them,
please run:
$ gcloud components update

とメッセージが出ていたので実行しました

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
$ gcloud components update


Your current Cloud SDK version is: 319.0.0
You will be upgraded to version: 343.0.0

┌─────────────────────────────────────────────────────────────────────────────┐
│ These components will be updated. │
├─────────────────────────────────────────────────────┬────────────┬──────────┤
│ Name │ Version │ Size │
├─────────────────────────────────────────────────────┼────────────┼──────────┤
│ BigQuery Command Line Tool │ 2.0.69 │ < 1 MiB │
│ BigQuery Command Line Tool (Platform Specific) │ 2.0.65 │ < 1 MiB │
│ Cloud SDK Core Libraries │ 2021.05.27 │ 18.8 MiB │
│ Cloud SDK Core Libraries (Platform Specific) │ 2021.03.12 │ < 1 MiB │
│ Cloud Storage Command Line Tool │ 4.62 │ 3.9 MiB │
│ Cloud Storage Command Line Tool (Platform Specific) │ 4.59 │ < 1 MiB │
│ anthoscli │ 0.2.16 │ 47.5 MiB │
│ gcloud cli dependencies │ 2021.05.27 │ 11.0 MiB │
│ gcloud cli dependencies │ 2021.04.16 │ < 1 MiB │
└─────────────────────────────────────────────────────┴────────────┴──────────┘

A lot has changed since your last upgrade. For the latest full release notes,
please visit:
https://cloud.google.com/sdk/release_notes

Do you want to continue (Y/n)? Y

╔════════════════════════════════════════════════════════════╗
╠═ Creating update staging area ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Uninstalling: BigQuery Command Line Tool ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Uninstalling: BigQuery Command Line Tool (Platform Sp... ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Uninstalling: Cloud SDK Core Libraries ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Uninstalling: Cloud SDK Core Libraries (Platform Spec... ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Uninstalling: Cloud Storage Command Line Tool ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Uninstalling: Cloud Storage Command Line Tool (Platfo... ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Uninstalling: anthoscli ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Uninstalling: gcloud cli dependencies ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Uninstalling: gcloud cli dependencies ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: BigQuery Command Line Tool ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: BigQuery Command Line Tool (Platform Spec... ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Cloud SDK Core Libraries ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Cloud SDK Core Libraries (Platform Specific) ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Cloud Storage Command Line Tool ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Cloud Storage Command Line Tool (Platform... ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: anthoscli ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: gcloud cli dependencies ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: gcloud cli dependencies ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Creating backup and activating new installation ═╣
╚════════════════════════════════════════════════════════════╝

Performing post processing steps...done.

Update done!

To revert your SDK to the previously installed version, you may run:
$ gcloud components update --version 319.0.0



To take a quick anonymous survey, run:
$ gcloud survey

しばらく実行していなかったからか結構バージョン変わってますね。

アップデート後の最後の方に戻し方などが書かれています。便利ですね。