はじめに
こんにちは。ちゅらデータアドベントカレンダー３日目担当のたくまんです。
みんな大好きSnowflakeの小ネタを書きます。
ORDER BYでマイクロパーティションを最適化する
この記事では、Snowflakeを触っていて最近驚いたテーブル生成時やデータ挿入時のORDER BYで、マイクロパーティションの作成に影響を与えられることについて書きます。
例えば、テーブルをCTASで作ったり、INSERTとかする際、
create table t1 as
select col1, col2 from t2 order by col1
って書くと、このテーブルt1はcol1列の値をマイクロパーティション構成時のヒントに使ってくれるということです。ということは、col1でwhereでデータを絞るクエリとかだと、クエリによってはめっちゃ早くなったりします。
このような、任意の列でのマイクロパーティションの最適化というと、クラスタリングキーを定義することによる自動クラスタリングが有名だと思いますが、ORDER BYを使う方法もあるということです。（違いは後述します）
検証
実際に検証してみます。
検証用テーブルの作成
まずはSnowflakeサンプルデータのTPCH_SF100の１億５千万行のデータを使って、以下の２つのテーブルを作成します。
-- 1. o_custkeyでORDER BYしたテーブル
create or replace table tpch_sf100_order_by_o_custkey as
select * from snowflake_sample_data.tpch_sf100.orders
order by o_custkey;
-- 2. o_orderdateでORDER BYしたテーブル
create or replace table tpch_sf100_order_by_o_orderdate as
select * from snowflake_sample_data.tpch_sf100.orders
order by o_orderdate;
テーブルの内容は２つとも同じでtpch_sf100.ordersをコピーしてるだけですが、以下のように「ORDER BYに指定するカラム」を変えています。
- tpch_sf100_order_by_o_custkey ：
o_custkeyをORDER BYで指定 - tpch_sf100_order_by_o_orderdate ：
o_orderdateをORDER BYで指定
検証１：o_custkeyで絞る
では、作成した２つのテーブルそれぞれに、以下のクエリを投げてみます。
select
o_orderstatus
, sum(o_totalprice)
, count(distinct o_custkey)
from table
where o_custkey in (2847824, 2847853)
group by all;
o_custkeyをwhere句で使った集計クエリです。
この２つのクエリを実行した結果、実行結果は同じですが、クエリプロファイルでは以下の違いがありました。
| 対象テーブル | 読込パーティション数 | 総パーティション数 | 実行時間 |
|---|---|---|---|
| tpch_sf100_order_by_o_custkey | 1 | 224 | 355ms |
| tpch_sf100_order_by_o_orderdate | 235 | 235 | 1.2s |
今回whereに利用されたo_custkey列をORDER BYで指定していたtpch_sf100_order_by_o_custkey では、224あるうちのパーティション数のうち1つしか読み込まれておらず、かなりプルーニングが効いています。
一方で、CTAS時にo_orderdateをORDER BYを指定していた方のテーブルでは、235あるパーティションも全て読み込んでいます。また、簡単なクエリなのでどちらも十分高速ですが、実行時間もこちらは３倍ほどかかっています。
検証２：o_orderdateで絞る
もう１パターンやってみます。今度は以下のクエリを各テーブルに投げてみます。
select
o_orderstatus
, sum(o_totalprice)
, count(distinct o_custkey)
from table
where o_orderdate in ('1997-09-27', '1998-02-17', '1992-02-14')
group by all;
検証用クエリ１とwhere句だけ違い、今度はo_orderdateを使って絞り込んでいます。クエリ結果は以下の通りでした。
| 対象テーブル | 読込パーティション数 | 総パーティション数 | 実行時間 |
|---|---|---|---|
| tpch_sf100_order_by_o_custkey | 224 | 224 | 2.9s |
| tpch_sf100_order_by_o_orderdate | 3 | 235 | 499ms |
先ほどとは真逆の結果となり、o_orderdateでORDER BYをしたテーブルの方が、圧倒的に効率よく処理が行われていました。
検証３：CLUSTERING_INFORMATIONも見てみる
もう結果は明らかではありますが、任意のカラムのクラスタリング情報を取得できるSYSTEM$CLUSTERING_INFORMATIONも見てみてます。
各テーブルにおいてo_orderdate、o_custkeyそれぞれのaverage_overlapsを見てみると、やはり各テーブルでorder byで指定されたカラムはオーバーラップが少なくマイクロパーティションが最適化されていることが分かります。
| 対象テーブル | 指定列 | average_overlaps |
|---|---|---|
| tpch_sf100_order_by_o_custkey | o_orderdate | 223.0 |
| tpch_sf100_order_by_o_custkey | o_custkey | 1.61 |
| tpch_sf100_order_by_o_orderdate | o_orderdate | 1.72 |
| tpch_sf100_order_by_o_custkey | o_custkey | 234.0 |
このような結果から、ORDER BYがマイクロパーティションの作成において、一定の影響力を持つことは間違いなさそうです。
自動クラスタリングとの違い
任意の列によるマイクロパーティション最適化の手法として知られる、自動クラスタリングとの違いに関して、SELECT Labs社の記事にいろいろ知見が詰まっていました。
この記事では、このORDER BYによるマイクロパーティションの最適化手法を「Manual Sorting」と呼んでいます。そして、それはテーブルを全洗い替えするシナリオにおいて、自動クラスタリングよりも「遥かに経済的な手法」として紹介されています。
In this scenario of a table that is always fully recreated, we recommend always using manual sorting over the automatic clustering service as the table will be well-clustered, and at a much lower cost than the automatic clustering service.
経済的になる理由としては、
- （テーブルが更新される度に実行される自動クラスタリングとは異なり）再クラスタリングする頻度や費用をコントロールしやすい点
The benefit of manual re-sorting over the automatic clustering service is complete control over how frequently the table is re-clustered, and the associated spend.
- 自動クラスタリングの実行コストは高価であるが、Manual Sortingはウェアハウスリソースを利用するため自動クラスタリングより安価に実行できる点
Snowflake takes a higher margin on automatic clustering than warehouse compute costs, which can make automatic clustering less cost-effective than manual re-sorting.
と紹介されていました。
ケースにもよるかもしれませんが、実際に私の身の回りのケースでこの２つの手法を試したところ、自動クラスタリングより経済的な試算結果となりました。
余談：他のテーブルタイプだとどうなる？
余談です。
ORDER BYによるマイクロパーティションの最適化は、他の実体化されるタイプのオブジェクト（Materialized ViewとDynamic Table）にも使えるかも？と思って試してみました。
すると、
- Materialized View : そもそもビューの定義にORDER BYを入れられない（エラーになる）
- Dynamic Table ： DDLにORDER BYを入れることはできたが、マイクロパーティションは最適化されずプルーニングの改善も見られない
という結果になりました。
Dynamic Tableで効果がない理由には謎が残りますが、現時点では普通のテーブルのみを対象とした方が良さそうです。
参考記事
参考にした記事です。
SnowflakeによるMediumブログです。ORDER BYの活用を含めたマイクロパーティションのコントロールについて色々書いてあります。
記事の途中でも紹介したSELECT Lab社による記事です。自動クラスタリングや、Natural Clustering（挿入順序ではなく、更新時間を元にしたクラスタリング）との違いが詳細に述べられています。
まとめ
以上、ORDER BYはSnowflakeのマイクロパーティションに影響を与えることについて、いろいろ調べてたことを書いてみました。全国のSnowflakeユーザーにとって、少しでも新たな気づきになれば嬉しいです！
Discussion