BigQuery でビッグデータを扱うのはそんなに簡単じゃなかった話

この記事はデータ基盤 Advent Calendar 2020 の 14日目の記事となります。

BigQuery Advent Calendar 2020 というのもあるので、BigQuery に興味のある方はこちらもどうぞ。

はじめに

BigQuery は小規模なデータをとりあえずをつっこむ先としては使い勝手の良いツールです。しかし、扱うデータのサイズがある程度のサイズ (数十 TB ~) になってくるといくつかの問題にぶつかります。

私がぶつかった問題と、どういう風に対応したかを話そうと思います。

カラム名・型の変更ができない

BigQuery では、一度追加したカラムの名前や型を変更することが出来ません。テーブルのカラム追加はまあ発生する作業だと思いますが、カラム名・型を間違えて追加してしまった場合に後戻り出来ないのはヤバい仕様です。

https://cloud.google.com/bigquery/docs/manually-changing-schemas?hl=ja

その回避方法として、SQLでテーブルをフルスキャンした結果を別のテーブルに読み込む方法、テーブルのデータを Cloud Storage に一旦エクスポートして別のテーブルに読み込みなおす方法、の二つが紹介されています。

前者はテーブルのフルスキャンが発生するため、テーブルのサイズが大きい場合、数万円~の費用が発生してしまいます。多少手間がかかりますが、後者の Cloud Storage に一旦エクスポートする方法を選びました。

Cloud Storage へエクスポートできるデータ量に上限がある

とはいえ、Cloud Storage へエクスポートする方法にも落とし穴があります。

https://cloud.google.com/bigquery/quotas?hl=ja#export_jobs

BigQuery のテーブルから Cloud Storage へのエクスポートできるデータ量は一日あたり 50 TB の上限が適用されます。もしテーブルのサイズが 50 TB を超える場合、カラム名・型の変更作業が数日がかりになってしまいます。

https://cloud.google.com/bigquery/docs/partitioned-tables#dt_partition_shard

テーブルのサイズが 50 TB を超えないように（year, month, day 等で）適宜シャード化すると良いでしょう。

以前この上限が 10 TB だった頃に、ダメ元でサポートに依頼したら 100 TB まで上げてもらったことがあります。いざという時は一か八かで上限緩和申請をしてみるといいかもしれません。

テーブルを作り直すとストレージ料金が二倍になるかも？

https://cloud.google.com/bigquery/pricing/?hl=ja

過去 90 日間変更されていないテーブル・パーティションのデータには長期保存の割引料金が適用されます。リージョンによって違いはあるものの、長期保存料金は通常料金の大体半額くらいの金額設定になっています。

カラム名・型の変更のためにテーブルの再作成を行うと、長期保存の料金が適用されなくなります。

テーブルをシャード化しておけばこの被害を最小限にとどめることが出来ます。

ワイルドカードを使ったクエリの結果がキャッシュされない

dataset.hoge_20201214 のように year, month, day でシャード化したテーブルに、一括でアクセスするために次のようなビューを作成しました。

select
  substr(__TABLE_SUFFIX, 1, 4) as year,
  substr(__TABLE_SUFFIX, 5, 2) as month,
  substr(__TABLE_SUFFIX, 7, 2) as day,
  col1,
  col2,
  ...
from
  `dataset.hoge_*`
;

__TABLE_SUFFIX にはシャード化したテーブルのサフィックス（20201214 等）が入るため、それを substr() で year, month, day カラムに分解します。このようなビューを作ることで、利用者はシャード化してないテーブルと同じようにデータにアクセスできるようになります。

https://cloud.google.com/bigquery/docs/cached-results

惜しいことに、ワイルドカードを使ったクエリの結果はキャッシュされません。

データポータルからの参照等で定期的に同じ SQL が実行される場合は、直接このビューにアクセスするのではなく、別に集約したテーブルを作成してそれを参照するようにした方が良いでしょう。

金がすべてを解決する

ここに上げた問題はすべて利用料金に関するものなので、（懐事情次第ですが）あれこれ考えるより金で解決するのも一つの手段だと思います。

記憶力が無い

プログラミングと室内園芸と何か