めもめも

データエンジニアリング、機械学習について書いてます

BigQueryのパーティションテーブルとシャーディングテーブルどちらが良いか

概要

BigQueryで日付ごとのテーブルを作成するときの選択肢として、パーティションテーブルとシャーディングテーブルの2つがあり、どう使い分けるべきか疑問に思いました。

結論から言うと、パーティションテーブルを使うことが公式からも推奨されているようです。

この記事では、パーティションテーブルが優れる理由を解説していきます。

パーティションテーブルとは

パーティションテーブルとは、パーティションという単位に分割された特殊なテーブルです。

BigQueryは列指向データベースであるため、WHERE句でデータを絞り込んでもフルスキャンしてしまいます。しかし、パーティションテーブルの場合、WHERE句に、パーティションキーを使うことで、フルスキャンすることなく、特定の行のデータだけを抜き出すことができます。

それによって、クエリのコストとパフォーマンス向上につながります。

現在、パーティションは、取り込み時間・日付などの時間列・特定の整数列に基づいて設定可能です。

シャーディングテーブルとは

シャーディングテーブルは、{prefix}-YYYYMMDD などの名前の接頭辞を使用した複数のテーブルです。

BigQueryのコンソール画面からは、一つのテーブルのように見えますが、複数のテーブルから成ります。(下の赤枠部分)

スクリーンショット 2022-02-24 17.49.52.png

パーティションが優れる理由① クエリのオーバーヘッドが少ない

前提として、BigQueryのテーブルにクエリを実行した際の挙動として、クエリ実行ユーザーがテーブルに対してアクセス権があるかどうか確認する処理が発生します。

パーティションテーブルは、パーティションに分割されているが、実体は1つのテーブルなので、アクセス権の確認処理が1回で済みます。

対して、シャーディングテーブルは、実体は複数のテーブルなので、複数テーブルにまたがるクエリを実行した際は、アクセス権の確認処理がテーブル毎に発生します。

そのため、アクセス権確認のオーバヘッドが少ない分、パーティションテーブルの方がパフォーマンスが優れます。

パーティションが優れる理由② テーブルの削除が楽

2つ目は、運用で感じたちょっとしたメリットです。 現在、BigQueryに複数テーブルを同時に削除する機能はありません。

パーティションテーブルは、1つのテーブルであるため、削除は1回で済みます。

対して、シャーディングテーブルの実体は、複数テーブルであるため、コンソール画面から削除しようとした時、テーブルの分、削除ボタンをぽちぽちする必要があります。

bqコマンドを上手く使うことで、特定のprefixを含むテーブル全てを削除することは可能です。

まとめ

パーティションテーブルは、 ・クエリのオーバーヘッドが少ない ・テーブルの削除が楽 の2点でシャーディングテーブルより優れます。

あえてシャーディングテーブルを使う場面は、日によってテーブルのスキーマが変わる時くらいでしょうか。

参考

パーティションテーブルの概要 https://cloud.google.com/bigquery/docs/partitioned-tables?hl=ja

パーティショニングとシャーディング https://cloud.google.com/bigquery/docs/partitioned-tables?hl=ja#dt_partition_shard