Snowflake:ORDER BYでマイクロパーティションを最適化する
はじめに
こんにちは。ちゅらデータアドベントカレンダー3日目担当のたくまんです。
みんな大好き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億5千万行のデータを使って、以下の2つのテーブルを作成します。
-- 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;
テーブルの内容は2つとも同じで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で指定
検証1:o_custkeyで絞る
では、作成した2つのテーブルそれぞれに、以下のクエリを投げてみます。
SELECT
o_orderstatus,
SUM(o_totalprice),
COUNT(DISTINCT o_custkey)
FROM table
WHERE o_custkey IN (2847824, 2847853)
GROUP BY ALL;
o_custkey
をwhere句で使った集計クエリです。
この2つのクエリを実行した結果、実行結果は同じですが、クエリプロファイルでは以下の違いがありました。
対象テーブル | 読込パーティション数 | 総パーティション数 | 実行時間 |
---|---|---|---|
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あるパーティションも全て読み込んでいます。また、簡単なクエリなのでどちらも十分高速ですが、実行時間もこちらは3倍ほどかかっています。
検証2:o_orderdateで絞る
もう1パターンやってみます。今度は以下のクエリを各テーブルに投げてみます。
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;
検証用クエリ1と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をしたテーブルの方が、圧倒的に効率よく処理が行われていました。
検証3: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.
と紹介されていました。
ケースにもよるかもしれませんが、実際に私のテーブルの全洗い替えが発生するケースでこの2つの手法を比較したところ、確かにORDER BYによる手法の方が自動クラスタリングよりも経済的になる結果となりました。
余談:他のテーブルタイプだとどうなる?
余談です。
ORDER BYによるマイクロパーティションの最適化は、他の実体化されるタイプのオブジェクト(Materialized ViewとDynamic Table)にも使えるかも?と思って試してみました。
すると、以下のような結果になりました。
- Materialized View: そもそもビューの定義にORDER BYを含めることが出来ない(エラーになる)
- Dynamic Table: 以前はORDER BYによるプルーニング改善は見られませんでしたが、24年4月のDynamic Tableの自動クラスタリングサポートに伴い(?)、ORDER BYによるプルーニング改善が見られるようになりました
Materialized Viewでマイクロパーティションの最適化を狙う場合は、ORDER BYではなく、自動クラスタリングを活用する必要がありそうです。
参考記事
参考にした記事です。
SnowflakeによるMediumブログです。ORDER BYの活用を含めたマイクロパーティションのコントロールについて色々書いてあります。
記事の途中でも紹介したSELECT Lab社による記事です。自動クラスタリングや、Natural Clustering(挿入順序ではなく、更新時間を元にしたクラスタリング)との違いが詳細に述べられています。
まとめ
以上、ORDER BYはSnowflakeのマイクロパーティションに影響を与えることについて、いろいろ調べてたことを書いてみました。全国のSnowflakeユーザーにとって、少しでも新たな気づきになれば嬉しいです!
Discussion