TL;DR
原則としてパーティションテーブルを使うが、上位互換ではないため１次保存層にはシャーディングテーブルを使う
マサカリください
概要
データ分析を行う際には分析基盤の整備が欠かせません
分析基盤としては様々なプロダクトがありますが、そのなかでBigQueryには下記の利点があります
- よく使われるため分析者にとって馴染み深い
- 処理が分散実行されるためとても高速
- GCPのフルマネージドであるため運用が楽
ここではデータ加工層(データマート、データウェアハウス)だけでなく、１次保存層(データレイク)としてもBigQueryを用いる場合について考えます
※データの３層構造に関しては下記記事の思想に従います
http://yuzutas0.hatenablog.com/entry/2018/12/02/180000
あなたが格納しようとしているデータが日毎に増えていくような大規模データであるとしましょう
１つの大きなテーブルにすべてを入れてしまうと毎回フルスキャンをしてしまい課金額が恐ろしいことになります。そのためデータを分割する必要があるのですが、いくつかある方法のなかからどれを使うべきかを考察します
３種類の分割方法
BigQueryのテーブル分割方法は新しい順に下記３つの方法があります
- パーティションテーブル(分割テーブル)のうち、分割カラムを自分で指定するもの
- パーティションテーブル(分割テーブル)のうち、取り込み時間で分割するもの
- シャーディングテーブル（日付別テーブル)
ドキュメントは下記です
https://cloud.google.com/bigquery/docs/partitioned-tables
それぞれの説明については下記記事にまとまっています
https://qiita.com/mokrai/items/3eaa57e700ac084d33d8
このうち、２番目の取り込み時間で分割するパーティションテーブルは今から選択するメリットが無さそうです
元データをどこかから連携してBigQueryに入れるわけですが、連携処理には遅延が発生しえます。取り込み時間でパーティションを分けてしまうと本来入るべきだったパーティションとは別のパーティションに入る可能性があります。１番目の方法で適切な時間カラムをパーティションに用いたほうがよいでしょう
１番目の方法と３番目の方法は一長一短であるため利点と欠点について考えます
それぞれの利点と欠点
パーティションテーブル(分割テーブル)
パーティションテーブルはデータを日付ごとに分割してパーティションで区切り、１つのテーブルに入れる方法です
シャーディングテーブルの問題点を解決すべく後から作られました。そのため原則的にはこちらを推奨するとドキュメントに書いてありますが、制約もあります
https://cloud.google.com/bigquery/docs/partitioned-tables#partitioning_versus_sharding
pros
- 処理が最適化されるためにクエリの実行速度が速い
- 日付ごとに異なるスキーマを適用できない。そのため利用者側はカラムが日付ごとに変更される可能性を考慮しなくて良い
cons
- １つのテーブル内のパーティション数には上限がある(現在は4000) ※実は4080くらいまで入るのを確認しました。本当の上限は4092とかかもしれません
- 日付ごとに異なるスキーマを適用できない。そのためデータソースでカラムが増減した場合、無視するか何らかの工夫が必要
パーティション制限に関しては昔2000だったのが4000に増量された実績があります。そのため今後googleが良きタイミングで増量してくれる可能性がありますが、あなたのデータの限界には間に合わないかもしれません
パーティション制限を回避しようとすると例えば10年ごとにテーブルを分けることが考えられますが、下記理由により多少煩雑になります
- CUIだけでなくGUIからも複数のテーブルに分かれて見える
- 例えば2000年代、2010年代、2020年代とテーブルを分けると、ワークフローエンジンが2020/1/1の処理で新規にテーブルを作るが、何かあった場合正月休みが爆発するかも
シャーディングテーブル(日付別テーブル)
シャーディングテーブルとはデータを日付ごとに別のテーブルとして作成する方法です。BigQueryのGUIからはひとつのテーブルとしてまとめられて見えます
pros
- 日付ごとに異なるスキーマを適用できる。そのためデータソースでカラムが増減した場合でもそのまま取り込める
cons
- 日付ごとに異なるスキーマを適用できる。利用者としてはカラムの不一致により日をまたいだクエリが失敗する可能性がある ※どのみちデータの特性は利用者でも把握する必要があるとも言えます
- BigQueryのUI上ではシャーディングされたテーブルはまとめられるものの、CUIや他ツール(Tableauなど)連携の場合にまとめられておらず、日付テーブルごとに全部出てしまうので見づらい
- 日付をまたいだクエリを書きたい場合、ワイルドカードテーブルという書き方を使いますが下記制約・注意事項があります
- ビューのなかでワイルドカードテーブルを使うことができない
- ワイルドカードテーブルを用いたクエリはキャッシュされない
- GUIで合計クエリ容量が出てくるまで時間がかかる場合がある ※１
- ワイルドカードは単純な末尾マッチなので誤爆がありえる ※2
ワイルドカードテーブルクエリの制約のドキュメントは下記になります
https://cloud.google.com/bigquery/docs/querying-wildcard-tables#limitations
ワイルドカードテーブルでスキャン範囲を絞るためには_TABLE_SUFFIXを用います
https://cloud.google.com/bigquery/docs/querying-wildcard-tables#filtering_selected_tables_using_table_suffix
※1
GUIの合計クエリ容量というのは下記の様にクエリの課金対象のデータ量が右下に表示されるのですが、この値が出るまでに時間がかかる場合があります
※2
例えばdataset.product_*と書くとdataset.product_20190425だけでなくdataset.product_special_20190425にもヒットします
この問題はテーブル名と日時の区切り文字を特別なものにし、dataset.product__20190425のようにするプラクティスで回避可能です
どちらを使うべきか
焦点としては
- パーティション数の上限の存在
- 異なるスキーマを適用できるかの仕様の違い
- パフォーマンスと運用上の利点
になるのではないかと思います
データウェアハウス・データマート層に関しては、パフォーマンスと運用上の利点からパーティションテーブルを用いたほうが使い勝手が良いと思います
一方でデータレイク層に関しては、データを貯蔵するという特質からデータを保存する確実性を重視し、シャーディングテーブルを用いたほうが良いのではないかと考えています
最後に
上記の考えは現時点での個人的な考えです。何かあればマサカリを投げていただけるとありがたいです