📖

BigQueryでネストされたフィールドの追加/削除を既存データを残したまま行いたい

2022/09/04に公開

SQL

BigQuery

tech

はじめに

BigQuery でネストされたフィールドにあるカラムの追加/削除を既存のデータを残したまま行いたかった。
カラムの追加/削除というと真っ先に ALTER TABLE が思い浮かぶ。
BigQuery にも ALTER TABLE でのカラムの追加/削除は存在するが、残念なことにこれらではネストされたフィールド内にあるカラムはサポートしていない。

ALTER TABLE ADD COLUMN

このステートメントで以下のものを作成することはできません。
既存の RECORD フィールド内にネストされた列。

ALTER TABLE DROP COLUMN

このステートメントで次の列のドロップはできません。
既存の RECORD フィールド内にネストされた列

だが、 ALTER TABLE を使わずとも、ネストされたフィールドにあるカラムの追加/削除が行うことができたので、紹介する。

カラムの削除

ドキュメントにもあるとおり、 BigQuery のカラムの削除はALTER TABLE DROP COLUMN もしくは SELECT * EXCEPT を用いることができる。

SELECT * EXCEPT 自体にはテーブルのカラム削除のような機能はない。
たとえば、以下のようなクエリを実行した場合、 mytable のデータを column_two のカラムはのぞいて取得することができる。

SELECT
  * EXCEPT(column_two)
FROM
  mydataset.mytable

これと bq query コマンドの destination_table オプションと replace オプションを併用することで、テーブルのカラム削除と同等のことが行えるようになる。

destination_table を指定すると、 bq query でのクエリ結果を別のテーブルに書き込むことができる。
たとえば、以下のようなコマンドを実行すると、mytable から column_two 列を省いたものが mytable2 にという新たなテーブルに書き込まれる。

bq query \
--destination_table mydataset.mytable2 \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_two)
FROM
  mydataset.mytable'

replace を指定するとクエリの宛先テーブルを上書きすることができるようになる。
たとえば、以下のようなコマンドを実行すると mytable から column_two 列を省いたものが、 mytable 　に上書きで保存されるようになる。

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_two)
FROM
  mydataset.mytable'

ここまでの例は普通のカラム削除であり、ネストされたカラムの削除の話ではなかったが、この「EXCEPT を利用して、削除したいカラムの情報を省く」というやり方はネストされたカラムの削除でも用いることになる。
たとえば、以下のようなスキーマの場合を考える。

[
  {
    "mode": "REQUIRED",
    "name": "column1",
    "type": "STRING"
  },
  {
    "mode": "REQUIRED",
    "name": "column2",
    "type": "FLOAT"
  },
  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "nested1",
        "type": "STRING"
      },
      {
        "mode": "NULLABLE",
        "name": "nested2",
        "type": "STRING"
      }
    ],
    "mode": "REPEATED",
    "name": "column3",
    "type": "RECORD"
  }
]

ネストされたカラムは

column3.nested1
column3.nested2

の二つ存在するが、 column3.nested2 を削除したい場合以下のように書ける。

bq query \
--destination_table data_set.tables \
--replace=true \
--use_legacy_sql=false \
'SELECT
  * REPLACE ((SELECT AS STRUCT column3.* EXCEPT (nested2)) AS column3)
FROM
  mydataset.mytable'

SELECT * REPLACE というステートメントが新しく出てきたが、これを利用すると、列の内容を書き換えることができる。

REPLACE ((SELECT AS STRUCT column3.* EXCEPT (nested2)) AS column3)
だと column3 の列の内容を SELECT AS STRUCT column3.* EXCEPT (nested2) で置き換える、という意味になる。
ちなみに REPLACE とある通り、 AS で指定する対象のカラムは既存のカラム名と一致している必要がある。（存在しない新しいカラム名を AS で指定することはできない）

SELECT AS STRUCT column3.* EXCEPT (nested2) のクエリ結果は STRUCT 型の column.3 から nested2 の列を省いたものになる。それを新たな column3 とするので、column3 のスキーマは以下のようになる

  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "nested1",
        "type": "STRING"
      },
    ],
    "mode": "REPEATED",
    "name": "column3",
    "type": "RECORD"
  }

そのため、以下のクエリ結果は、 column3.nested2 の列だけを省くことになる。

SELECT
  * REPLACE ((SELECT AS STRUCT column3.* EXCEPT (nested2)) AS column3)
FROM
  mydataset.mytable

このクエリ結果と、前述の destination_table、 replace のオプションを併用することで、結果、ネストされたカラムの削除を既存のレコードは残したまま行うことができる。

bq query \
--destination_table data_set.tables \
--replace=true \
--use_legacy_sql=false \
'SELECT
  * REPLACE ((SELECT AS STRUCT column3.* EXCEPT (nested2)) AS column3)
FROM
  mydataset.mytable'

このような方法で、ネストされたカラムの削除が行えることは BigQuery のドキュメント上からは見つけられなかったので、知っておくと便利。

また、上記の bq query　コマンドと同様の処理を、BigQueryコンソール上のクエリエディタからも行うことができ、以下のように書ける。

CREATE OR REPLACE TABLE mydataset.mytable
AS (SELECT
  * REPLACE ((SELECT AS STRUCT column3.* EXCEPT (nested2)) AS column3)
FROM
  mydataset.mytable);

CREATE OR REPLACE のステートメントを利用すると、基本は新しいテーブルを作成するが、既に同じ名前のテーブルが存在する場合は置き換えてくれる。
そして、　AS を指定することで、新しく作成、もしくは置換するテーブルに用いるデータを指定することができる。

なので、 CREATE OR REPLACE TABLE ... AS SELECT ... を用いることで、 bq query コマンドで destination_table、 replace　を指定した時と同じようなことを行うことができる。

ちなみに、 CREATE TABLE ... AS SELECT ... のステートメントは度々 CTAS と略されるので、知っておくと調べ物をする際にちょっと便利。

カラムの追加

ネストされた列の追加は、削除の時とは違いちゃんと BigQuery のドキュメント上に記載がある。

詳細は上記の公式ドキュメントの通りだが、ざっくりとした作業の流れのイメージは以下の通り。

bq show --schema で現在のテーブルのスキーマを json　で保存
1 で保存したスキーマ（json）から削除したいカラム情報を削除する。
bq update を 2 で更新したスキーマファイルを引数に実行し、スキーマを更新する。

また、コマンド実行ではなく、 tables.patch の　API を用いることでもカラムの追加はできるよう。

おまけ

BigQuery でのカラム名の変更は今まで　CTAS で行っていたが、最近、ALTER TABLE ... RENAME COLUMN... でできるようになったよう。

この勢いで、ALTER TABLE でネストされたカラムの追加/削除などもサポートされればありがたいと思いました。

はじめに

カラムの削除

カラムの追加

おまけ

Discussion