はじめに
BigQueryのリリースノートによると、Integer列によるPartitioned Tablesが2/18にGA化したらしいです。
BigQueryのテーブルをパーティションで分けて集計を高速化していくという手段も、様々増えてきましたね。
そこで、今回はテーブルのパーティショニングおよびクラスタ化について比較をしていきたいと思います。
分析環境
Google BigQuery
対象とするデータ
パーティションを作って、比較するからにはサイズが大きい方が良いかと思い、500GB超の下記の様なテーブル(myproject.mydataset.tbl0)を用意しました。
| id | 顧客ランク | 店舗 | 購入日 | 購入商品 | 購入金額 | 購入個数 |
|---|---|---|---|---|---|---|
| 001 | 1 | 〇〇店 | 2020-01-10 | product1 | 2500 | 1 |
| 002 | 3 | ☓☓店 | 2020-01-10 | product1 | 750 | 3 |
| 002 | 3 | ☓☓店 | 2020-01-10 | product2 | 1000 | 2 |
パフォーマンスを比較する集計は、購入日と顧客ランク（１～１０）を集計軸にしたいと思います。
1.パーティショニング・クラスタ化
パーティショニング・クラスタ化は、次のパターンを採用します。
パーティショニングは１変数までなのと、クラスタ化はパーティションしていないとできないので、選べる選択肢は４種類
| テーブル名 | パーティショニング | クラスタ化 |
|---|---|---|
| tbl1 | 購入日 | なし |
| tbl2 | 顧客ランク | なし |
| tbl3 | 購入日 | 顧客ランク |
| tb14 | 顧客ランク | 購入日 |
今回GAになった、Integer列によるPartitioned Tablesはtbl2, tbl4が該当します。
項目名は、BigQueryは和名は使えないのですが、分かりやすいように和名で表記しています。
tbl1作成
CREATE OR REPLACE TABLE `myproject.mydataset.tbl1`
PARTITION BY 購入日
AS
SELECT * FROM `myproject.mydataset.tbl0`
tbl2作成
CREATE OR REPLACE TABLE `myproject.mydataset.tbl2`
PARTITION BY RANGE_BUCKET(顧客ランク, GENERATE_ARRAY(1, 10, 1))
AS
SELECT * FROM `myproject.mydataset.tbl0`
tbl3作成
CREATE OR REPLACE TABLE `myproject.mydataset.tbl3`
PARTITION BY 購入日
CLUSTER BY 顧客ランク
AS
SELECT * FROM `myproject.mydataset.tbl0`
tbl4作成
CREATE OR REPLACE TABLE `myproject.mydataset.tbl4`
PARTITION BY RANGE_BUCKET(顧客ランク, GENERATE_ARRAY(1, 10, 1))
CLUSTER BY 購入日
AS
SELECT * FROM `myproject.mydataset.tbl0`
2.パフォーマンス検証①
顧客ランク・購入日別の合計金額を算出する
SELECT
顧客ランク, 購入日, SUM(購入金額) AS 合計金額
FROM `myproject.mydataset.tblX` #Xに0~4が入る
GROUP BY 顧客ランク, 購入日
| テーブル名 | パーティショニング | クラスタ化 | 処理タイム |
|---|---|---|---|
| tbl0 | なし | なし | 4.8秒 |
| tbl1 | 購入日 | なし | 5.6秒 |
| tbl2 | 顧客ランク | なし | 5.8秒 |
| tbl3 | 購入日 | 顧客ランク | 4.9秒 |
| tb14 | 顧客ランク | 購入日 | 5.4秒 |
処理したデータ量167GB
一番速かったのが、パーティショニングもクラスタ化もしていないtbl0？
とはいえ、他のtblもほぼ誤差みたいなものです。slotの状況とかもあるでしょうか。
とはいえ、167GBも処理して、約5秒とは。BigQueryのパフォーマンスは恐ろしいですね。
3.パフォーマンス検証②
と、#2で差が出なかったので、より重い処理で追加検証。
顧客ランク・購入日別の合計人数を算出する
SELECT
顧客ランク, 購入日, COUNT(DISTINCT id）AS 合計人数
FROM `myproject.mydataset.tblX` #Xに0~4が入る
GROUP BY 顧客ランク, 購入日
| テーブル名 | パーティショニング | クラスタ化 | 処理タイム |
|---|---|---|---|
| tbl0 | なし | なし | 1分2秒 |
| tbl1 | 購入日 | なし | 37.9秒 |
| tbl2 | 顧客ランク | なし | 1分3秒 |
| tbl3 | 購入日 | 顧客ランク | 25.5秒 |
| tb14 | 顧客ランク | 購入日 | 26.9秒 |
処理したデータ量260GB
こちらは結構差が出ましたね。何もしていないtbl0と、顧客ランクのパーティショニングのみのtbl2が1分オーバー。
それに対して、パーティショニング・クラスタ化を組み合わせたtbl3, tbl4は30秒弱と半分以下です。
おわりに
検証①では、パーティショニング・クラスタ化してもほぼ変わらないという、衝撃的な結果が出てしまいましたが、検証②で有効性が証明されて安心しました。
とはいえ、パーティショニング・クラスタ化はテーブルを作成するためにも結構時間がかかるので、無闇矢鱈に行うべきではないですね。
また、今回の検証とは直接関係ありませんが、集計に関係ない項目でクラスタ化すると、次の通りの結果となり、クラスタ化をしていないtbl1よりも時間が余計にかかってしまいました。
| テーブル名 | パーティショニング | クラスタ化 | 処理タイム |
|---|---|---|---|
| tbl5 | 購入日 | 店舗 | 44.8秒 |
そのテーブルで何度も集計する（マートのような使い方）＆何で集計するかが明確である。
この条件を満たす場合に、パーティショニング・クラスタ化は有効な手段と言えそうです。