ハッピバースデートゥミ〜〜〜♪ハッピバースデートゥミ〜〜〜♪、ハッピバースデーディアわたし〜♪
ハッピバースデートゥミ〜〜〜♪
がく＠ちゅらデータ（49）です。
アドベンドカレンダーがものすごく活発ですよね！！！
クラスタリング絡みで、2023年のSnowflake Advend Calendarで2件出てましたね
なにげに、パフォーマンスに直結するすごい重要なことで、めちゃいい記事書いてはるなぁ〜
弊社たくまんさんの記事
Snowflake社hiroshixさんの記事
クラスタリングに絡みSelect.devさんですごく良質な記事があったので読んでみました
Snowflakeにおける効果的なクラスタリング
SnowflakeのClusteringとは
Clusteringは、特定のテーブルについて、Snowflakeのストレージ単位であるマイクロパーティションにどうデータを分散配置させるか？
テーブルが適切にクラスタリングされていると、Snowflakeは、非常に効率的にパフォーマンスを向上させることができる
- メタデータの活用（最大値、最小値、Null数など）
- → クエリがスキャンするファイル数（マイクロパーティション）を最小限に抑えられる
クラスタ化されたテーブル
created_atをベースにクラスタリングされているテーブルの場合、SQLの where句に created_atの条件が入っていると、走査するマイクロパーティションの数を激減させることができる
select *
from orders
where created_at > '2022/08/14'
↑マイクロパーティションには、メタデータとして created_atの最大値、最小値を持っているので、とある3つのマイクロパーティションしか走査しなくてよくなるので、パフォーマンスが激良くなります
マイクロパーティション
Snowflakeは、各マイクロパーティションの各カラムの最小値と最大値のメタデータを保持します。このテーブルでは、各マイクロパーティションに狭い範囲のcreated_at値のレコードが含まれているため、テーブルは列でうまくクラスタ化されています。Snowflakeはwhere句と最小値と最大値のマイクロパーティションメタデータに基づいて残りを無視できることを知っているため、次のクエリはハイライトされた最初の3つのマイクロパーティションのみをスキャンします。この動作はクエリー・プルーニングと呼ばれている
クラスタリングはいつ使うべき？
ほとんどのSnowflakeユーザはクラスタリングを考慮する必要はなくて、ナチュラルなクラスタリング（Snowflakeが予期に計らってくれる）だけで、十分にクエリは早くて、コストも比較的最適化してくれる。
プルーニングとは、いわゆる枝刈り。走査するデータ量を削減するテクノロジー、無駄なデータ走査を行わない、最適化手法。
また、Snowflakeにおけるクラスタリングは複数テラバイトから有効・・・とあるが、数百メガバイトのテーブルからパフォーマンス上のメリットが有る（BIツールでの表示を8秒→4秒とか、そういうパフォーマンス上の要求があった際に使える1つのテクニック）
Snowflakeの公式ドキュメントでは、「クラスタリングは数TBのデータを含むテーブルに対してのみ有効である。との記載があるが、数百メガから始まるデータに対してもパフォーマンス上のメリットが有る」
※弊社では、TableauなどのBIツールからアクセスするMart層などを使ったりするけど、そこのパフォーマンス改善（とあるBIツールでの表示を8秒→4秒）に非常に有用だったと聞き及んでいます
Snowflakeでクラスタリングを有効にするには？
クラスタリングには3つの方法がある
自然なクラスタリング
挿入順の自然クラスタリングを活用
長所：追加費用や労力は不要
短所：データが挿入される順番に関連するカラムでフィルタリングするクエリでのみ機能する
自動クラスタリングサービス（Automatic clustering service）
- テーブルを特定のキーでソートする
- ソートの操作にはコストがかかる
- テーブルはどの式／列でも再クラスタ化できる
- 再クラスタ化は結構お金がかかるので、どでかいテーブルで気軽な気分でやるとクレジットがめちゃくちゃ消費されるというやらかしは枚挙の暇がない（あるある事例）
クラスタリングキーが設定される場合にのみ実行される
-- カンマで区切られた列でクラスタリングすることができます。
alter table my_table cluster by (column_to_cluster_by)；
-- あるいは、式でクラスタリングすることもできます。
alter table my_table cluster by (substring(column_to_cluster_by, 5, 15))；
長所：
- ナチュラル・キーとは異なるキーでクラスターを形成する、最も労力の少ない方法。
- DML操作をブロックしたり妨害したりしない。
短所：
- 予測不可能なコスト。※めちゃくちゃクレジットを消費する場合が稀によくある
- Snowflakeは倉庫の計算コストよりも自動クラスタリングに高いマージンを取っているため、自動クラスタリングは手動の再ソートよりもコスト効率が悪くなる可能性があります。
手作業によるソート
CTASで新規に作る
いわゆるCTASで作成する際にORDER BYをつかってクラスタリングキーを指定することができます。
create or replace my_table as (
with transformations as (
...
)
select *
from transformations
order by my_cluster_key
)
※CTASのSelectにorder by入れるなんてなんの意味もないやろ！！！って思った私がいます orz
新規に作る場合、テーブルの作成（CTAS）クエリにorder by文を追加することで、任意のキーでテーブルを完全にクラスタ化することができる
既存のテーブルの場合
既存のテーブルをCTASで作り直す
create or replace table sales as ()
select * from sales order by store_id
)
※自分自身を読み込んで、ソート結果を自分自身に洗替してますね
手動再ソートの利点は、自動クラスタリングサービスとの違いは、
メリット：
「再クラスタリングされる頻度と費用を完全にコントロールすることができる」
デメリット：
DML操作に影響が出る可能性がある（再ソート実行中に適用したDML操作が全て元に戻る）
長所：
- クラスタリングプロセスを完全に制御します。
- どのキーでも完璧なクラスタリングを実現する最も低コストな方法。
短所：
- 自動クラスタリングサービスよりも手間がかかる。ソートクエリを手動で実行するか、ソートクエリの自動オーケストレーションを実装する必要がある。
- 既存のテーブルをそれ自身のソートされたバージョンで置き換えると、再ソート中に実行されるDML操作はすべてもとに戻ってしまう
まとめ
プルーニングをいかに効率化させるかがパフォーマンスに影響するので
今後複数の列でうまくクラスタリングする必要がある場合のオプションを検討していく予定とのこと
Super-charge Snowflake Query Performance with Micro-partitions
SYSTEM$CLUSTERING_INFORMATIONからの出力を使用して、クラスタリング情報に基づいてテーブルを再構築するジョブを構築することができます。このようなステートメントを毎日実行するタスクに組み込むことができます。どのテーブルをチェックするかを制御するルックアップテーブルを追加することで、よりスマートにすることができます。
execute immediate
$$
declare
average_depth float;
begin
select parse_json(system$clustering_information('TABLE_1', '(CLUSTERING_KEY_1)')):total_partition_count::int as total_partition_count INTO :average_depth;
if (average_depth > 10) then
//insert overwrite into TABLE1 select * from TABLE_1 order by CLUSTERING_KEY_1;
return 'Partition depth is ' || :average_depth || '. Ran insert overwrite.';
else
return 'Partition depth is ' || :average_depth || '. Did not run insert overwrite.';
end if;
end;
$$;
（私の）まとめ
Select.devは、SELECT.CLOUDの会社が書かれている技術ブログで、凄まじく良質な記事が多いです。
先日、peiさんがSELECT.CLOUDについて下記のようなブログを公開なさっていました。
このあたりが、SELECT.CLOUDの技術力の源泉となっているんだろうな〜と思いました
※ガワはMediumなので、Mediumの課金版とか使ってるんだろな〜（笑）
Discussion