Qiita Engineer Festa20242024年7月17日まで開催中！

株式会社primeNumber

Snowflakeクラスタリングキー付与のためのステップ講座

Last updated at 2024-07-01Posted at 2024-06-23

はじめに

お世話になっております。primeNumberの庵原です。
入梅の候、皆様いかがお過ごしでしょうか。

今回はSnowflakeを使い込んでいくとぶつかるパフォーマンスチューニングについてです！
その中でもクラスタリングキーの設定方法について、実際の業務を通じて学んだ内容と実際の設定する際のステップについてを共有できればと思います！

対象者

Snowflakeのクラスタリングキーの設定方法が掴みづらいと感じている方
Snowflakeのクラスタリングについて知りたい方
実際に運用の流れの一部を知りたい方

そもそもクラスタリングキーって何？

の前に、マイクロパーティションの説明

Snowflakeは、MySQLやPostgreSQLのようなOLTP系のDBとは異なり、マイクロパーティションと呼ばれる一定量のデータを 列形式 で保存しています。

公式ドキュメントにある画像を用いて説明すると、前述のMySQLやPostgreSQLのようなOLTP系のDBは各行（2, 'A', 'UK', '11/2'）、（4, 'C', SP', '11/2'）のように行単位でデータが格納されます。

しかしSnowflakeでは画像右側のように、type, name, country, dateはそれぞれ固まりで保持しており、それを一定量でパーティション単位に区切られています。

マイクロパーティションが行われるメリットとして、各マイクロパーティション内の列単位で個別に圧縮されるため、スキャンする際の範囲を自動的に減らすことができます。
また各マイクロパーティションごとに最大値、最小値（例えば図のMicro-patition1のdate列は最大値・最小値が共に11/2）を保持しているため、WHERE句などの絞り込み条件を行う際に、効率的に プルーニング（剪定） と呼ばれる、不要なパーティションを読みにいく必要がなくなり、I/O速度の向上やその他処理の効率化・高速化に役立ちます。

このマイクロパーティションは、データが入ってきた順番にデータ量をチェックして、透過的にマイクロパーティションの塊が積み上がっていくため、date列のような値は、画像の例のように比較的綺麗な並びになることが多くあります。
ただ他の列についてはどうでしょうか？

マイクロパーティションの課題点

さて、マイクロパーティションによるプルーニングが発生することで、処理に扱うデータ量を削減することができると分かりました。
しかし、マイクロパーティションは常に最高の効率でプルーニングを発生させてくれるわけではありません。

再度例に挙げますが、画像のようなデータ構造の場合、date列でWHERE句による絞り込みを行う際は、先述の通り、データの特性上並び順が綺麗になることが多いため、効率的にプルーニングが発生します。
ですが、type列でwhere type = 2のような、WHERE句による絞り込みを行う時はどうなるでしょうか？

結論を言うと、where type = 2のような場合は、すべてのパーティションをスキャンする必要があり、プルーニングが発生しないのです。
理由として、各パーティションのtype列のデータの格納状況を見ると、すべてのパーティションは2 ~ 4の値を保持しているため、プルーニングが発生しないのです。

ただし、実運用として、type列でWHERE句による絞り込みを行うようなことがない場合は大きな問題になり得ませんが、頻繁にWHERE句による絞り込みやJOINによる結合を行う際は、データが増えれば増えるほどパフォーマンスが劣化していきます。

これを改善するのがクラスタリングキーによるクラスタリングです！

クラスタリングキー！！

今回の場合、date列によるプルーニングはうまくいくけど、type列によるプルーニングが発生しない...けど、ちゃんとプルーニングするようにしたい！のような場合に活用できる機能がクラスタリンキーによるクラスタリングです。

またまた公式ドキュメントから拝借しての説明ですが、下図のように元の来た順番で積み上がっていたデータを、設定したカラムに基づいてマイクロパーティションの配置を変更することができます。

図のように次のSQLで設定することが可能です。

ALTER TABLE t1 CLUSTER BY (date, type);
-- ALTER TABLE <table_name> CLUSTER BY (<column1>, <column2>, ..., <column4>);

例では、(date, type)の２列に基づいてクラスタリングを発生させるように設定しています。
２列設定するイメージとしては、SELECT時のORDER BY句を複数カラム設定する時と同じ形です。
dateが１番目、typeが２番目のように優先順位を決定している形です。

クラスタリングが行われたことにより、先ほど例に出していたwhere type = 2を再度実行すると、Micro-patition1をスキャンするだけで良くなるため、beforeより扱うデータ量が単純に1/4に削減されるのです！

Snowflakeでは、Warehouseの稼働した時間によって料金が計算されるため、処理するデータ量の削減が直結的にコスト削減につながるのです。

クラスタリングについての技術的な説明

ここでは、最低限抑えておくべきことをまとめます。

Point1: クラスタリングキーの設定を行うことでお金がかかります
- お金に関わるパラメーター
  - テーブル数（マテアライズドビューやダイナミックテーブルも含む）
  - データ量
  - カーディナリティ（詳しくは後述します！）
Point2: クラスタリングによるデータの再配置（Reclusteringとか書かれることがある）はデータが増えたり、内容が変わったりなどする際に行われます
Point3: クラスタリングのはWarehouseを指定する必要はなく、透過的に行われます
Point4: クラスタリングキーの同時設定カラム数は最大4つまで

詳しい内容については、公式ドキュメントを参考にしてみてください。

クラスタリングキー付与のためのStep

さて、実際にどのようなStepを踏んでクラスタリングキーを設定するかの流れを見ていきましょう！

ここから先は、Snowflakeのサンプルデータである、TPCH-SF1000のデータを利用します。

準備SQL

use role sysadmin;
create database qiita;
use database qiita;
create schema kensyo;
use schema kensyo;

create warehouse qiita_kensyo
    WAREHOUSE_SIZE='X-SMALL'
    INITIALLY_SUSPENDED=TRUE;

use warehouse qiita_kensyo;

-- IMPORT SHAREのデータはCLONEできないため、AS SELECTを使用
create or replace table ORDERS 
as select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.ORDERS
ORDER BY O_TOTALPRICE; -- データシャッフル（わざとです）

Step1 そもそも必要なのかチェック

Snowflakeのクラスタリングキーは便利である反面、適当な設定な設定を行うと、逆にコストの浪費になってしまう可能性があります。
そのため、以下の項目に当てはまるかどうかをまずチェックしましょう。最低で4つ当てはまれば、クラスタリングキーの設定の検討を進めてよいと考えます。

チェック	項目	理由
✅	テーブルの容量が数TB以上 or 10億行以上	データの処理量に関連
✅	マイクロパーティションの数が1000を超えている	データの処理量に関連（チェック方法は後述）
✅	頻繁にSELECTされる	データの処理量に関連
✅	WHERE句による絞り込みが頻繁に行われる or JOINで等価評価(=など)が頻繁に行われる	プルーニングに関連
✅	テーブルの更新が高頻度ではない（1日に多くて3回更新まで）	運用上のコストに関連（Automatic Reclustering）

Step2 利用状況のチェック

次に、実際の運用でどのようなクエリが叩かれていて、どれくらい時間がかかっているか？を確認していきましょう。

確認の方法としては2つあると考えており、1つ目が頻繁に叩かれているSQLで、時間がかかっているものの確認、2つ目が頻繁にアクセスされているテーブルのデータの状況の確認です。

それぞれ確認の方法について説明します。

1. 頻繁に叩かれているクエリのチェック

簡単なチェック方法としては、Snowsightのクエリ履歴から確認する方法です。

モニタリング > クエリ履歴から確認することができます。

画面ではどのようなSQLが実際に叩かれていたかや、誰が叩いているか、どれくらい時間がかかったかなどを過去とリアルタイムを含めて確認することができます。

また下記の画像のようにフィルター条件を加えることで、より詳細に確認することができます。
今回は、例として10分以上のクエリを探す際のフィルター条件を設定しているところです。

より詳細に確認したい場合は、SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYビューにアクセスする方法などもありますが、ほとんどの場合はSnowsight上のクエリ履歴を用いて十分に調査が可能です。（Snowflakeありがとう！）

その上で調査の結果、例えば、例として以下のクエリが頻繁に叩かれているとしましょう。

SELECT
    *
FROM
    ORDERS
WHERE
    O_ORDERPRIORITY = '2-HIGH';
    AND O_ORDERDATE BETWEEN '1993-01-01'::DATE AND '1993![スクリーンショット 2024-06-23 15.30.18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/333022/32f6cda3-dded-b003-fdcf-0a56ad132d94.png)
-12-31'::DATE;

実際に実行された履歴を、クエリ履歴で確認できるクエリプロファイルから確認すると、以下の通りでした。

見てみると赤枠のように、スキャンしているマイクロパーティション数が総マイクロパーティション数と同じ値を示しています。これはWHERE句に指定している条件による絞り込みを行うために、すべてのマイクロパーティションをスキャンしてしまっているということが言えます。

今回はWHERE句による等価評価（=やinなど）による絞り込み条件のみを例に挙げましたが、JOIN時の条件（結合述語）でも同じことが言えます。

では、クラスタリングの状況が実際にどうなっているかも確認してみましょう。

2. テーブルのデータ状況のチェック

以下の関数を呼び出すことで、指定したテーブルとカラムがどのようなデータ配置になっているかをクラスタリングの観点から現在の状況を 推定値 として算出してくれます。

SELECT SYSTEM$CLUSTERING_INFORMATION('ORDERS', '(O_ORDERPRIORITY, O_ORDERDATE)');

-- SELECT SYSTEM$CLUSTERING_INFORMATION('<table_name>', '(column_names)');
-- カラム名はカッコで囲う必要があります.

結果は以下の通りでした。

{
  "cluster_by_keys" : "LINEAR(O_ORDERPRIORITY, O_ORDERDATE)",
  "total_partition_count" : 2293,
  "total_constant_partition_count" : 0,
  "average_overlaps" : 2292.0,
  "average_depth" : 2293.0,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 0,
    "00002" : 0,
    "00003" : 0,
    "00004" : 0,
    "00005" : 0,
    "00006" : 0,
    "00007" : 0,
    "00008" : 0,
    "00009" : 0,
    "00010" : 0,
    "00011" : 0,
    "00012" : 0,
    "00013" : 0,
    "00014" : 0,
    "00015" : 0,
    "00016" : 0,
    "04096" : 2293
  },
  "clustering_errors" : [ ]
}

ここでチェックすべき指標を説明します。

項目	説明
total_partition_count	テーブル内のデータを格納している全マイクロパーティション数
average_overlaps	テーブル内にある各マイクロパーティションの重複するマイクロパーティションの平均数
average_depth	特定の値を見つけ出すために読み取る必要があるマイクロパーティション数の平均数。
partition_depth_histogram	特定の値を見つけ出すために読み取る必要があるマイクロパーティション数の最悪の場合の最大数。

total_partition_count以外が少しイメージしづらいと思われるので、簡単な図を用いて説明します。

図は、マイクロパーティションがどのような状態で格納されているかの物理的な側面を図示したものになります。

例えば、図の左側の一番上では、1990-01-01~1994-12-31までがすべて重複している形で格納されています。
これはマイクロパーティション内に1990-01-01~1994-12-31の値がすべて含まれてしまっていることを意味します。

これに比べて、図の左側の一番下では、各マイクロパーティションでは、1年単位で綺麗に格納されています。これは、1990年のマイクロパーティション内に、1991年〜1994年のデータが一切含まれていないことを意味しています。

この状態を数値化しているのが、average_overlaps、average_depth、partition_depth_histogramになります。

図の左側の一番上では重複しているマイクロパーティション数は「5」で、深さも「5」となります。
同様に上から２番目はそれぞれ「3」「3」、３番目は「3」「2」、一番下は「0」「1」となります。
もちろん今回の例は簡略化されているため、一意の値になりましたが、とある区間では重複は「3」だけど、別の区間では「７」のようにデータ特性によって、重複するパーティション数やそれによる深さには1つのテーブル内でも差異が発生します。これを平均化した値が、average_overlaps、average_depthというわけです。

ちなみにpartition_depth_histogramについては、一番値の大きいものを確認するのが重要で、先ほどの関数の結果だと4096と表示されていました。
これは最悪の場合、4096回の読み取りが発生してしまう可能性のあるマイクロパーティションが2293個ある、と言えます。要は今回の場合は最悪ということです。

Step3 クラスタリングキーの付与

ようやっと調査が完了し、クラスタリングをする必要があるとわかったので、実際にクラスタリングキーの付与を行いましょう！

今回の例では、頻繁に叩かれているクエリの条件として、O_ORDERPRIORITYとO_ORDERDATEがあるため、これらの複合カラムに対して設定を行いたいと思います。

この際に「O_ORDERPRIORITYとO_ORDERDATEをどの順番で設定しよう？」という疑問が出てきます。以下のような形です。

ALTER TABLE ORDERS CLUSTER BY (O_ORDERPRIORITY, O_ORDERDATE)
-- or 
ALTER TABLE ORDERS CLUSTER BY (O_ORDERDATE, O_ORDERPRIORITY)

この戦略には明確な正解があるわけでなはいのですが、一般的に以下の順番で考えられます。

最もWHERE句絞り込みもしくはJOIN句の結合述語の条件が行われるカラムの順番
日付カラムがある際は日付カラム(DATE型の列やTIMESTAMP_TRUNCなどを行った列)
カーディナリティが低い順番

1については、先ほどの調査の通り、どのSQLが叩かれているかに相当します。
絞り込みが行われないカラムに対して設定するのは、単に無駄な行為になってしまうので、気をつけましょう。
１つ飛ばして、「カーディナリティが低い順番」とありますが、これの説明の前に「カーディナリティ」について説明します。

カーディナリティとは？

これは簡単にデータのUnique数だと考えていただければ良いと思います。
例えば、性別を考える際は、例えば「男」「女」「その他」「答えたくない」などの4つが考えられます。
反対に別の例としては、uuidのような重複を許さないidなどの場合は、idの数 = 行数と考えられます。

上記の例であれば、Unique数が少ない性別を「カーディナリティが低い」といえ、Unique数が多いidを「カーディナリティが高い」と言えます。

そして、クラスタリングキーを設定する際はカーディナリティが低い順番に設定することが推奨されます。

低い順にする理由

理由としては、パフォーマンスとクラスタリングの「維持」が関係します。
パフォーマンスについては、プルーニングを発生させたい際に、カーディナリティが高いが優先されている状況だと、有効性が下がってしまう可能性があるためです。

また維持については、クラスタリングによるマイクロパーティションの配置は、データが追加・更新・削除することによって、再配置が行われますが、カーディナリティが高いカラムにクラスタリングキーを設定すると、維持のために多くのパーティションに対するアクセスや、パーティション内の配置に時間がかかるため、プルーニングによるコスト削減よりも、クラスタリングの維持の方にコストがかかってしまい、むしろお金がかかってしまう、みたいな状態になる可能性が大いにあります。

今回の場合...

今回の場合は、実はちょっと複雑です。観点としては以下が挙げられるためです。

カーディナリティの順番は、O_ORDERPRIORITY < O_ORDERDATE
しかし、粒度がDAYまでのO_ORDERDATEカラムが存在する

実際にこれを考える際は、先述の一般論の優先度と利用状況、データ特性を加味した上で、検討してみると良いと思います。

今回の場合はほぼ同列なので、正直どちらでも問題ないと思われますが、ORDERSはいわゆるファクトテーブルであるという点で、日付による絞り込みが他でも使われる可能性が高いと考えられます。そのため以下のように設定してみます。

ALTER TABLE ORDERS CLUSTER BY (O_ORDERDATE, O_ORDERPRIORITY)；

Step4 クラスタリングの状況確認

設定後は、ちゃんとクラスタリングの設定が反映されているか確認しましょう。

設定チェック

SHOW TABLES；からcluster_byとautomatic_clusteringで確認ができます。

クラスタリングの配置作業については、先述の通り裏側でSnowflakeが勝手に（透過的に）行ってくれており、配置中でもSELECTをはじめ、テーブルにアクセスすることが可能です。

設定後の自動クラスタリング作業については、下記の公式ドキュメントに詳しい情報の記載があります。

さて、反映が確認できた後は、現在のデータ配置の状況を確認するために、先ほど紹介したSYSTEM$CLUSTERING_INFORMATIONを用いて中身を確認してみましょう。

クラスタリング状況の再チェック

下記を実行してみましょう。

SELECT SYSTEM$CLUSTERING_INFORMATION('ORDERS', '(O_ORDERDATE, O_ORDERPRIORITY)');

クラスタリングは設定したテーブルのサイズやカラムのカーディナリティによって処理が収束するまで時間がかかることがあります。
設定後にすぐに確認すると、「あれ...？まだあんまり進んでない...?」となることがありますが、処理中の可能性があるため、気長に待ちましょう。

処理の途中だったかもしれませんが、10分後には下記の状況になっていました。

{
  "cluster_by_keys" : "LINEAR(O_ORDERDATE, O_ORDERPRIORITY)",
  "total_partition_count" : 2515,
  "total_constant_partition_count" : 0,
  "average_overlaps" : 9.4123,
  "average_depth" : 5.9125,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 0,
    "00002" : 9,
    "00003" : 107,
    "00004" : 183,
    "00005" : 690,
    "00006" : 694,
    "00007" : 529,
    "00008" : 245,
    "00009" : 58,
    "00010" : 0,
    "00011" : 0,
    "00012" : 0,
    "00013" : 0,
    "00014" : 0,
    "00015" : 0,
    "00016" : 0
  },
  "clustering_errors" : [ ]
}

最初に比べたらこの時点でも十分に改善されていますね！

比較は下記の通りです。

項目	BEFORE	AFTER	説明
average_overlaps	2292.0	9.4123	マイクロパーティションの平均重複数がBEFOREから0.41%に減少
average_depth	2293.0	5.9125	特定の値を見つけ出すのに読み取る必要があるマイクロパーティション数が、BEFOREから0.25%に減少
partition_depth_histogram(MAX)	"04096" : 2293	"00009" : 58	最悪のマイクロパーティションの読み取り回数が最大で4096回→9回に減少

実際にクエリを叩いてチェック

これはマストではないです。
理由としては、対象のテーブルが巨大すぎる場合は、検証のために１発クエリするだけでも料金がかかるためです。
ですが、どれくらい改善したかは実際に同じクエリを実行してみるのが一番手っ取り早くわかるので、おすすめはしたいところです。

再度クエリ実行した際にクエリプロファイルは下記の通りでした。

こちらも簡単に比較してみましょう。

項目	BEFORE	AFTER
実行時間	1分18秒	33秒（-45秒）
スキャンしたパーティション数	2293	380(-1913)
スキャンしたデータ量	38.65GB	4.85GB(-33.8GB)

実行されたクエリからもパフォーマンスが改善されたことが確認できました！

Step5 コストの確認

特に、Snowflakeが裏側で行ってくれている自動クラスタリング（再配置）のコスト監視は、重要です！なんなら毎日みてください！！！！！（圧）

というのは嘘ですが、この自動クラスタリングのコストが嵩みすぎるは本末転倒のため、監視することを強く推奨します。

確認に際しては、ACCOUNTADMINロールが利用可能であることが前提になりますが、

管理者 > コスト管理 > 消費で表示されるヒストグラムから確認できます。
（お見せしたかったのですが、いい感じのスクショが撮れる環境にありませんでした...無念）

注意点 & 最後に

いかがでしたでしょうか？
とても便利ではある反面、どのようなステップで設定すればいいのか、どのように付き合えばいいのかがわかりづらいクラスタリングについて、実際の運用で行った方法をご共有しました。

注意点として、私自身はこれが完全な正解とは思っておりません。
確認観点や設定のための考慮点が不足している可能性があるなど、まだまだ道半ばのステップだと考えています。
今後当たらな気づきがあったり、Snowflake自体の機能の追加、皆様のお声を反映して、さらにより良いものにできればと思っております！ぜひ気になった点についてはコメントいただけますと幸いです！

今後もデータ関連の記事を発信していければと思いますので、他の記事もぜひご覧ください！

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up