基本的には同一のテーブルに対して設定できるクラスタリングキーは2個から3個。
ただし2個以上設定すると、1個の場合と比較して原理的に効果が落ちる。
パフォーマンスを落とさずにクラスタリングキーを追加できないか。
ベーステーブルへのクエリ実行において透過的にMaterialized Viewが使われる
Snowflakeの特徴を利用して、任意の個数のクラスタリングパターンを追加する。
そんな内容が書かれた記事を見つけたので読書感想文を書いてみる。
Defining multiple cluster keys in Snowflake with materialized views
[arst_toc tag=\"h4\"]
オレオレクラスタリングの必要性と難しさ
色々な理由でナチュラルクラスタリングに頼らず自力でクラスタリングキーを設定する場合がある。
その際、クエリの傾向をみて、より適切なプルーニングが行われるカラムを選ばないといけない。
(\"適切な\"といのは、想定する使われ方と異なる使われ方がされた場合にプルーニング対象が減り
クエリパフォーマンスが落ちる頻度を減らすということ。)
ナチュラルクラスタリングは良く出来ていて、オレオレキーがナチュラルキーを超えるのは難しい。
結構なケースで「あっちが立てばこっちが立たず」という状況になる(、と思う)。
腕力に任せてオレオレキーの数だけテーブルを作る
ストレージの料金は安いのだから、クラスタリングキーの数だけテーブルを作ってしまえ。
という、マッチョなやり方を取ることもできる。
同一テーブルに対して一度に設定できるクラスタリングキーは限られているが、
CREATE TABLE文に CLUSTER BY を付けることできるので、
クラスタリングキーごとにテーブルを複数作ってしまえば、この問題はある程度解決する。
注文管理テーブル的なテーブル\"注文\"があったとして、\"注文日付\"、\"顧客キー\"、\"店員キー\"の
いずれかを条件とするクエリが均等に来るだろうという前提。
以下のようにCREATE TABLE文に CLUSTER BY をつける。
--注文日付をクラスタリングキーとしてテーブルを再作成
create table \"注文_clustered_by_注文日付\" cluster by (\"注文日付\") as (
select
\"注文日付\",
\"注文キー\",
\"顧客キー\",
\"店員キー\"
from \"テストDB\".\"テストスキーマ\".\"注文\"
)
--顧客キーをクラスタリングキーとしてテーブルを再作成
create table \"注文_clustered_by_顧客キー\" cluster by (\"顧客キー\") as (
select
\"注文日付\",
\"注文キー\",
\"顧客キー\",
\"店員キー\"
from \"テストDB\".\"テストスキーマ\".\"注文\"
)
--店員キーをクラスタリングキーとしてテーブルを再作成
create table \"注文_clustered_by_店員キー\" cluster by (\"店員キー\") as (
select
\"注文日付\",
\"注文キー\",
\"顧客キー\",
\"店員キー\"
from \"テストDB\".\"テストスキーマ\".\"注文\"
)
もちろん通常のTableであれば利用者側が用途に応じて利用するテーブルを選択しないといけない。
利用者側にそれぞれのテーブルを提供し、利用者は用途に応じてテーブルを使用する必要がある。
もう一歩、使い勝手が良くない。利用者が1つのテーブルを触る際に、意識せずに(透過的に)
最適なクラスタリングが行われたデータにアクセスさせることはできないか。
Materialized Viewを使うとこれが可能になる。
Materialized Viewの復習
Snowflakeに限らず、普通のRDBMSにも Materialized View は存在する。
Materialized Viewの各列に設定したサブクエリを事前計算して、クエリ時に再利用する仕組み。
以下のようにすると、cntは事前に処理され、クエリ時に再利用される。
create materialized view \"注文_aggregated_by_日付\" as (
select
\"日付\",
count(*) as cnt
from \"注文\"
group by 1
)
Snowflakeの場合、ベーステーブルに対してクエリを行うと、自動的にMaterialized Viewが評価される。
例えば、以下のクエリは\"注文\"ではなく\"注文_aggregated_by_日付\"が評価される。
select
\"日付\",
count(*) as cnt
from \"注文\"
group by 1
meta-dataにMaterialized Viewの定義が記録され、特定のルールでベーステーブルを触ると
meta-dataからルックアップされたMaterialized Viewが評価される、的な動きだろうと思っている。
ベーステーブルを変更(INSERT,UPDATE)すると、自動的にMaterialized Viewに反映される。
その際、ベーステーブルにおいて自動クラスタリングが行われる。(瞬時ではなく後で非同期に行われる)
Materialized Viewの効果的な使い方
ベーステーブルをあらかじめ\"日付\"でソートしておく。
ナチュラルキーとして\"日付\"が選ばれて自動クラスタリングが行われ、\"日付\"のブロックごとに
パーティションが並ぶことになる。
create table \"注文\" as (
select
\"日付\",
\"注文キー\",
\"顧客キー\",
\"店員キー\"
from \"テストDB\".\"テストスキーマ\".\"注文\"
order by \"日付\"
)
\"注文\"テーブルは\"日付\"の順で並んでいるのだから、\"日付\"を条件としたクエリを叩くと、
超絶有効にプルーニングが効くことになる。
select
\"日付\",
count(*) as cnt
from \"注文\"
where
\"日付\" between date\'1993-03-01\' and date\'1993-03-31\'
group by 1
\"注文\"テーブルに対して\"顧客キー\"を指定してクエリを行なった場合、
もし Materialized View を作っていなかったとしたら、Full Scan的な検索になるはず。
select *
from \"注文\"
where
\"顧客キー\"=52671775
そこで、以下のように\"顧客キー\"をクラスタリングキーとしてMaterialized Viewを作っておく。
create materialized view \"注文_clustered_by_顧客キー\" cluster by(\"顧客キー\") as (
select
\"日付\",
\"注文キー\",
\"顧客キー\",
\"店員キー\"
from \"注文\"
)
;
すると、以下のクエリは\"注文\"ではなく \"注文_clusterd_by_顧客キー\"に対して行われる。
select *
from \"注文\"
where
\"顧客キー\"=52671775
Materialized Viewなので、ベーステーブルである\"注文\"に対して追加・更新を行うと、
ベーステーブルから派生させてMaterialized Viewも自動更新される。
ユーザには\"注文\"を提供するだけで良い。 (Materialized Viewへのアクセス権が必要)
コストが許す限り望むだけのMaterialized Viewを作っておくことで、
ベーステーブルに対するクエリが最適なクラスタリングが行われたMaterialized Viewに
通される。
