❄️

Snowflake：ORDER BYでマイクロパーティションを最適化する

たくまん

2023/12/03に公開

はじめに

こんにちは。ちゅらデータアドベントカレンダー３日目担当のたくまんです。
みんな大好き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による手法の方が自動クラスタリングよりも経済的になる結果となりました。

余談：他のテーブルタイプだとどうなる？

余談です。
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ユーザーにとって、少しでも新たな気づきになれば嬉しいです！

ちゅらデータ株式会社Publication

沖縄に「最高に面白い仕事」を作る IT ベンチャーです！エンジニア積極採用中！！