BigQuery のコストを削減するために。パーティショニングテーブルの使い方を調べてみた
こんにちは、みかみです。
BigQuery では実行した SQL クエリの処理データ量に対して課金が発生します。 料金はスキャンしたデータ量に比例するため、LIMIT 句を使用したとしても、SELECT * で全列をスキャンする場合、処理データ量が多くなり料金も高くなります。
コストを抑えるためには、SQL をチューニングしたり、事前に処理データ量を確認する方法もありますが、スキャンするデータの総量を減らせば処理データ量も減らすことができます。
他の多くの DB サービスでもテーブルのパーティショニング機能をサポートしていますが、BigQuery でもパーティショニングテーブルを利用できるとのことなので、試してみました。
やりたいこと
- BigQuery のテーブルはどんな単位でパーティショニングできるのか知りたい
- BigQuery でパーティショニングテーブルを定義するにはどうすればよいのか知りたい
- BigQuery のパーティショニングテーブルデータを参照する場合の挙動を確認したい
使用データ
動作確認で使用してしるのは、以下のサイトから頂いてきた、2020 年 13 週 酒類の POS データの売れ筋ランキングデータです。
取り込み時間で分割
バッチ処理にてファイルデータを差分更新で DB にロードする場合、自前でロード日時項目をデータに付与することも多くあります。
BigQuery では、テーブル作成時に指定しておけば、自動で _PARTITIONTIME という疑似列を追加し、ロード日単位でテーブルを分割してくれます。
取り込み時間分割のテーブルを定義するには、テーブル作成時に「パーティショニング」項目で「取込時間により分割」を選択するだけです。
テーブルスキーマには _PARTITIONTIME を定義していませんが
SQL クエリで _PARTITIONTIME カラムを利用することができるようになります。
__PARTITIONS_SUMMARY__ メタテーブルで分割情報を確認してみると、データ取り込み日ごとにパーティショニングされていることが確認できます。
__PARTITIONS_SUMMARY__ の参照は標準 SQL には対応していないため、「クエリの設定」からレガシー SQL に切り替える必要があります。
続いて、各パーティションに対して SQL を実行してみます。
WHERE 句で _PARTITIONTIME の日付を指定することにより、各パーティションデータに対してクエリを実行することができます。
なお、テーブル作成時にパーティションフィルタを指定しなければ、SQL 実行時にパーティションの指定は不要ですが、
フィルタを指定すると、WHERE 句でパーティションを指定していないクエリはエラーになります。
フィルタ指定している場合でも、明示的にパーティションの指定を行えば、パーティションをまたいだ SQL の実行も可能です。
うっかりフルスキャンのクエリを実行して失敗することがないように、パーティションフィルタは設定しておくと便利ですね。
日付 / タイムスタンプ項目で分割
ロードデータに日付やタイムスタンプ型の項目がある場合、該当カラムを分割単位に指定できます。
テーブル定義時に「パーティショニング」項目でカラムを指定するだけです。
管理画面の UI 操作でテーブルを作成する場合、テーブルスキーマを定義しないと、分割単位に指定可能な項目がプルダウンに表示されません。
メタデータを見てみると、ちゃんと指定したカラムが partition_id になっていることが確認できました。
整数範囲で分割
データに整数のデータ型が含まれる場合、該当カラムの値ごとにパーティショニングできます。
テーブル定義時に「パーティショニング」項目でカラム名を指定し、カラム値の範囲と分割のレンジを指定します。
テーブル作成後、メタテーブルを確認してみると、指定したカラムの値毎に分割されていることが確認できました。
例えば何かのユーザーデータをロードしていてデータに年齢のカラムがある場合に、年齢カラムを 10 ずつのレンジでパーティショニング指定しておけば、年代別にクラスタリングされるので使いやすそうです。
まとめ（所感）
自動でデータ取り込み日を付与してくれる分割単位もあり、テーブル作成時に簡単にパーティショニングを指定できました。
クエリ課金の面で処理データ量を減らせるという利点のほかに、ストレージ課金でも長期保存料金が各パーティションごとに適用されるメリットがあります。
大量データをデイリーでロードしていて、古いデータを参照する可能性はあるけれど、通常業務では直近半年分のデータしか参照する必要がない場合などには、パーティショニングを指定しておくと良いと思いました。
