PLAID Advent Calendar 2018 7日目の記事です。
BigQueryのClustered Table(クラスタ化テーブル)という機能について調べてみました。
Clustered Tableとは
- 同じ属性のデータを物理的に同じ場所に配置することによってクエリ実行時の効率を上げる(スキャンするデータ量を減らせる)
- テーブルを作成する際に、クラスタリング列を最大４列まで指定できる
- 現在はPartitioned Tableでのみ使用可能
Partitioned Tableに関しては以下を参照して下さい。
分割テーブルの概要
BigQuery の Partitioned Table 調査記録
Clustered Tableの作成方法
以下のようなDDLで作成することができます。
CREATE TABLE `mydataset.mytable`
(
date DATE,
category STRING
)
PARTITION BY
date
CLUSTER BY
category
Clustered Tableのパフォーマンス検証
試行１: Clustered Tableと通常のテーブルの比較
サンプルデータである publicdata.samples.natality をベースにしてテスト用のClustered Tableを作成します。
サンプルデータの一覧については以下のクエリで取得できます。
SELECT * FROM `publicdata.samples.__TABLES__`
publicdata.samples.natalityには約1億4000万レコード入っています。
Clustered Tableを作成
以下のようなDDLでテーブルを作成します。
CREATE TABLE
`mydataset.clustered_natality`
(
date DATE,
state STRING,
mother_age INT64
)
PARTITION BY
date
CLUSTER BY
state,
mother_age
データの挿入
作成されたテーブルにpublicdata.samples.natalityのデータを入れます。
宛先テーブルをmydataset.clustered_natalityに設定し、以下のクエリを実行します。
パフォーマンスを試したかったため大量のデータを入れていますが、重いクエリになるので注意が必要です。
SELECT
DATE(year, month, 1) date,
*
FROM `publicdata.samples.natality`
dayの部分を1に固定しているのは、partitionの上限2000を超えないようにするためです。
パフォーマンス測定
Clustered Tableに対するクエリ
SELECT *
FROM `mydataset.clustered_natality`
WHERE state = 'WY'
経過時間: 3.955 秒
処理されたバイト数: 22.01 GB
消費したスロット時間: 16.769 秒
元のテーブルに対するクエリ
SELECT *
FROM `publicdata.samples.natality`
WHERE state = 'WY'
経過時間: 5.790 秒
処理されたバイト数: 21.94 GB
消費したスロット時間: 30.238 秒
考察
- 「処理されたバイト数」に差が出るかと思ったが、ほとんど出ない。
- partition fieldの方で優先的に分割される？ その結果stateではほとんど分割されないのではないか。
試行2: Clustered Table(partitionしない)と通常のテーブルの比較
データの挿入部分を以下のようなクエリに変えてみました。
partitionに使用しているdateを全て同じにするという暴挙です。
SELECT
DATE(2000, 1, 1) date,
*
FROM `publicdata.samples.natality`
パフォーマンス測定
Clustered Tableに対するクエリ
SELECT *
FROM `mydataset.clustered_natality`
WHERE state = 'WY'
経過時間: 4.405 秒
処理されたバイト数: 2.41 GB
消費したスロット時間: 4.734 秒
元のテーブルに対するクエリ(再掲)
SELECT *
FROM `publicdata.samples.natality`
WHERE state = 'WY'
経過時間: 5.790 秒
処理されたバイト数: 21.94 GB
消費したスロット時間: 30.238 秒
思った通りの結果が！
考察
- partitioned fieldで分割されず、stateで分割されるため、処理されたバイト数と消費したスロット時間には大きな差が出た
- 分散するので実行時間についてはそこまで大きな差はない
試行3: Clustered TableとPartitioned Tableの比較
テーブルの作成
以下のDDLでPartitioned Tableを作成します。
CREATE TABLE
`mydataset.partitioned_natality`
(
date DATE
)
PARTITION BY
date
データの挿入
以下のクエリでclustered_natality、partitioned_natalityのそれぞれにデータを挿入します。
SELECT
DATE(year, month, 1) date,
*
FROM `publicdata.samples.natality`
パフォーマンス測定
Clustered Tableに対するクエリ
SELECT *
FROM `mydataset.clustered_natality`
WHERE
date = DATE(2000, 1, 1)
AND state = 'WY'
経過時間: 1.405 秒
処理されたバイト数: 60.99 MB
消費したスロット時間: 0.210 秒
Partitioned Tableに対するクエリ
SELECT *
FROM `mydataset.partitioned_natality`
WHERE
date = DATE(2000, 1, 1)
AND state = 'WY'
経過時間: 1.189 秒
処理されたバイト数: 60.99 MB
消費したスロット時間: 0.208 秒
差は無し...
考察
- partitionで分割されきっていてstateでは分割されていないのではないか
まとめ
Clustered Tableを使用すれば、使い方によってはスロットの消費をかなり節約できることが分かりました。
データサイズがもっと大きければより効果が発揮されると思います。
ただ現状ではpartition fieldでほとんど決まってしまいそうです。
Partitioned Table以外のテーブルでのクラスタリングのサポートも開発中のようなので、今後に期待です。