Snowflake: クラスタリングの 5 バイト制限を正しく理解する
Intro
ドキュメントに下記のような記述があります。
VARCHAR 列ごとに、クラスタリングの現在の実装は最初の5バイトのみを使用します。
最初のN文字がすべての行で同じである場合、または十分なカーディナリティを提供しない場合は、同一の文字の後に始まり、最適なカーディナリティを持つサブストリングでクラスタリングすることを検討します。（最適なカーディナリティの詳細については、 クラスタリングキーを選択するための戦略 をご参照ください。）例:
create or replace table t3 (vc varchar) cluster by (SUBSTRING(vc, 5, 5));
VARCHAR 型のカラムをクラスタリングキーで使用した場合、文字列全体ではなく、最初の 5 バイトを使用してクラスタリングする、という話ですが、
- 実際にクラスタリングされたテーブルを参照したときのプルーニングとの関係はどうなってるのか？
- なぜ
SUBSTRで中間抽出された部分文字列でのクラスタリングに効果があるのか？
など、わかりにくい部分があるので、解説したいと思います。
結論
結論としては、
- 5 バイト制限はクラスタリングのみに適用され、メタデータには適用されない
- メタデータは、5 バイトよりも長い文字列を最小値/最大値として保持している
- メタデータは、クラスタリングキーにかかわらず、各カラムの元の値の最大値/最小値を保持している
- プルーニングは、クラスタリングキーにかかわらず、対象カラムのメタデータを参照する
の 4 点に集約されます。
ここで言う「クラスタリング」とは、「テーブルをクラスタリングキー順でソートして分割する」という処理を指します。
以下に例を上げて解説します。
解説
例えば、
create or replace table t1 (code varchar, name varchar)
cluster by (code);
のようなテーブルについて、code の値が、
'code-0''code-1''code-2'- ...
'code-99'
のように 'code-' という共通のプレフィックスを持っていた場合を考えます。
このとき、クラスタリングは「code の最初の 5 バイトの順にソートし分割する」という動作になるので、ソートキーが全行で同値 ('code-') となってしまい、順序が不定となります。
create or replace table t1 (code varchar, name varchar)
cluster by (code) as
select 'code-' || seq4()%100, randstr(10, random())
from table(generator(rowcount => 10000000))
;
select *
from t1
order by left(code, 5)
limit 1;
/*
CODE NAME
code-85 LeGENHPoal
*/
そのため、各 code 値が各パーティションに分散してしまい、プルーニング効率が著しく下がってしまう形になります。
select *
from t1
where code = 'code-0'
;
/*
Pruning
Partitions scanned 8
Partitions total 8
*/
ここで注意しなければいけないのが、各マイクロパーティションは code について5 バイト以上の情報をメタデータに持っているものの、クラスタリングキー長の制限により本来の code 順にソートされていないために、プルーニング効率が下がっている、という点です。
すなわち、メタデータが (min, max) = ('code-', 'code-') のようになっているということではなく、単純に本来の code 順にソート・分割されていないがために、すべてのマイクロパーティションで (min, max) = ('code-0', 'code-99') のような状態になってしまっている状況です。
ここで、クラスタリングキーを SUBSTR(code, 6) (code の 6 文字目以降) に変更します。
create or replace table t1 (code varchar, name varchar)
cluster by (substr(code, 6));
すると、クラスタリングは「code の 6 文字目以降の部分文字列順にソートし分割する」という動作になるので、'0' から '99' までの値でソートされることになり、意図した順序でパーティショニングされる形となります。
create or replace table t1 (code varchar, name varchar)
cluster by (substr(code, 6)) as
select 'code-' || seq4()%100, randstr(10, random())
from table(generator(rowcount => 10000000))
;
select *
from t1
order by substr(code, 6)
limit 1;
/*
CODE NAME
code-0 RnhWRMxeRi
*/
このとき、メタデータはクラスタリングキーの式にかかわらず、最大値/最小値に 'code-0' や 'code-99' などのプレフィックスを含んだ文字列を保持しています。
つまり、substr(code, 6) でクラスタリングしていたとしても、WHERE 句には普通に WHERE code = 'code-0' のように記述すれば、
- マイクロパーティションのソート・分割は
SUBSTRのおかげで (プレフィックスを無視した)codeの番号順に行われている - プルーニングは単純に
codeの最大値/最小値を参照する
という形となり、プルーニングの恩恵を受けることができるようになります。
select *
from t1
where code = 'code-0'
;
/*
/*
Pruning
Partitions scanned 1
Partitions total 8
*/
また上記の 5 バイトの制限があるのは VARCHAR と BINARY のみですが、「クラスタリングキーの式とメタデータの内容は関係ない」という点は、他の型でも同じです。
例えば TIMESTAMP 型や NUMBER 型のカラムをクラスタリングキーに設定したいときに、自動クラスタリングの頻度を下げるために、TRUNC, TO_DATE, DATE_TRUNC などでカーディナリティを下げる、というテクニックがあります。
一般に、列（または式）のカーディナリティが高い場合、その列でのクラスタリングの維持はより高価になります。
一意のキーでのクラスタリングのコストは、特にそのテーブルの主な使用例ではないポイントルックアップの場合、そのキーでのクラスタリングの利点を上回る場合があります。
カーディナリティが非常に高い列をクラスタリングキーとして使用する場合は、個別の値の数を減らすために、キーを列ではなく列の式として定義することをSnowflakeはお勧めします。式は、各パーティションの最小値と最大値でプルーニングが有効になるように、列の元の順序を保持する必要があります。
例えば、ファクトテーブルに、多くの離散値（テーブル内のマイクロパーティションの数よりも多く）を含む TIMESTAMP 列 c_timestamp がある場合、タイムスタンプではなく日付に値をキャストすることで、列にクラスタリングキーを定義できます（例： to_date(c_timestamp)）。これにより、カーディナリティが合計日数に削減され、より優れたプルーニング結果が通常生成されます。
別の例として、 TRUNC 関数とスケールの負の値（例: TRUNC(123456789, -5)）を使用して、数値をより少ない有効桁数に切り捨てることができます。
このケースでも、「クラスタリングキーの式とメタデータの内容は関係ない」ので、普通に c_timestamp = '2024-01-01T12:34:56Z' や c_number = 123456789 のように検索してプルーニングの恩恵を受けることができます。
Discussion