Updated:
テクニカルブログ

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

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

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