サマリ

BigQueryのExternal Tableをスキーマごとにバージョン分けし、それを包含するviewを作成することで、データのスキーマ変更にも対応しやすくなります。

背景と課題

弊社のデータシステムでは、データをGCSといった安価なデータストアに置き、それをBigQueryのExternal Tableから参照する構成を採用することがあります。

その際、データストアに置いたデータのスキーマが変わってしまうことで、External Tableでスキーマエラーが発生する問題がよく発生します。

方法検討

そこで、スキーマ変更に対応しつつテーブルに対してクエリを実行できるようにする方法を検討しました。

アプローチ1

まず考えられるのは、データストアにあるデータそのものに手を入れる方法です。

スキーマに何らかの変更が入った際に、過去の全データをさかのぼって修正を入れていきますが、この方法では以下のような問題があります。

オリジナルのデータを残しておけない、もしくはバックアップをとる必要がありストレージの余分なコストがかかる
一つ一つのファイルを開いてスキーマや値を修正する作業はデータが増えるとさかのぼる量も増えるためスケールしない

アプローチ2

一方で、データストアにあるデータそのものに手を入れない方法を検討しました。

異なるスキーマごとにデータストアのパスを分離し、それぞれに個別のExternal Tableを作成します。そして、それらの差分を吸収してまとめてクエリを実行できるようにするViewを作成します。

より具体的には、下記のようなものになります。

データストアの準備: スキーマにバージョンをつけて、そのバージョンごとにデータストアのパスを分けてファイルを配置する
テーブルの準備: そのバージョンごとにExternal Tableを作成する
viewの準備: そのExternal Tableごとのスキーマの差分を吸収するViewを作る
スキーマの変更への対応: 新しいデータストアのパスにファイルを配置し、そのパスを参照するExternal Tableを作成し、　Viewを修正する

この方法は、アプローチ1に対して下記のようなメリットがあります。

オリジナルのデータをデータストアに残しておけるため、オリジナルのデータを参照したいユースケースが後で出てきてもすぐに対応できる
スキーマが変わった際にもその新しいスキーマ用のExternal Tableの用意とViewの変更だけでよいため、スケールする

そのため、弊社ではアプローチ2を採用することにしました。

実践例

ここからは、上記アプローチ2のごく簡単な実践例を示します。

前提

この例で使うバージョンとそのスキーマを下記の通りとします。

version 1.0.0のデータ
- id STRING
- updated_at TIMESTAMP
version 1.1.0のデータ
- id STRING
- updated_at TIMESTAMP
- label STRING

データストアの準備

GCSのバケットを用意し、パスをバージョンごとに区切り、それぞれのパス配下に対応するバージョンのデータを置きます。ここでは、下記のようにファイルをアップロードしました。

gs://sample/1.0.0/file1.0.0.csv としてアップロード

id,updated_at
1,2024-10-24 00:00:00

gs://sample/1.1.0/file1.1.0.csv としてアップロード

id,updated_at,label
2,2024-10-25 00:00:00,foo

テーブルの準備

次に、上記GCSのパスを参照するExternal Tableをそれぞれ作成します。

cloud.google.com

gs://sample/1.0.0/*.csvを参照するExternal Table sample.version_1_0_0 を作成します。

CREATE EXTERNAL TABLE sample.version_1_0_0 (
  id STRING,
  updated_at TIMESTAMP
) OPTIONS (
    format = "CSV",
    uris = ["gs://sample/1.0.0/*.csv"],
    skip_leading_rows = 1
)

gs://sample/1.1.0/*.csv を参照する External Table sample.version_1_1_0 を作成します。

CREATE EXTERNAL TABLE sample.version_1_1_0 (
  id STRING,
  updated_at TIMESTAMP,
  label STRING
) OPTIONS (
    format = "CSV",
    uris = ["gs://sample/1.1.0/*.csv"],
    skip_leading_rows = 1
)

viewの準備

最後に、上記External Tableを集約するviewを次の通り作成します。

cloud.google.com

CREATE VIEW sample.all AS (
  SELECT
    *,
    NULL AS label
  FROM
    sample.version_1_0_0
  UNION ALL
  SELECT
    *
  FROM
    sample.version_1_1_0
)

スキーマの変更への対応

ここでは、カラム名がlabelからvalueに変更になり、以下のような新しいスキーマのデータが入ってくることになったとします。これをversion 1.2.0とします。

version 1.2.0のデータ
- id STRING
- updated_at TIMESTAMP
- value STRING

gs://sample/1.2.0/file1.2.0.csv として下記ファイルをアップロードします。

id,updated_at,value
3,2024-10-26 00:00:00,bar

次に、gs://sample/1.2.0/*.csv を参照するExternal Table sample.version_1_2_0 を作成します。

CREATE EXTERNAL TABLE sample.version_1_2_0 (
  id STRING,
  updated_at TIMESTAMP,
  value STRING
) OPTIONS (
    format = "CSV",
    uris = ["gs://sample/1.2.0/*.csv"],
    skip_leading_rows = 1
)

最後に、viewを変更します。

CREATE OR REPLACE VIEW sample.all AS (
  SELECT
    *,
    NULL AS value
  FROM
    sample.version_1_0_0
  UNION ALL
  SELECT
    * EXCEPT(label),
    label AS value
  FROM
    sample.version_1_1_0
  UNION ALL
  SELECT
    *
  FROM
    sample.version_1_2_0
)