ClickHouse プロジェクション機能
ClickHouse プロジェクション機能が、バージョン 22.2 で production ready となりました。
この機能を使うと、SELECTクエリを書き換えることなく、集計等のクエリを高速化することができます。
今までは、集計を行うマテリアライズド・ビューを作成し、そちらをSELECTするようにクエリを書き換える必要がありました。
プロジェクションを使うと、実テーブルに集計結果をあわせて格納することができ、SELECTクエリも自動的に集計済みのデータを使うようになり、高速化されます。
ダミーデータ準備
ページを閲覧したログを記録しておき、ページ別の閲覧数を集計する想定でサンプルデータを作ります。
簡単なテーブルなので1000万レコードほど用意します。
CREATE TABLE site_view (
event_time DateTime,
path String
)
ENGINE=MergeTree
ORDER BY event_time;
INSERT INTO site_view
SELECT
toDateTime('2022-01-01 00:00:00') + INTERVAL rand() % (number+1) SECOND AS event_time,
concat('/page/', toString(rand() % 100), '.html') path
FROM system.numbers
LIMIT 10000000;
集計
ログデータをそのまま集計するとこのようになります。
SELECT path, count()
FROM site_view
GROUP BY path
ORDER BY count() DESC;
SELECT path, count()
FROM site_view
WHERE toDate(event_time) = '2022-01-01'
GROUP BY path
ORDER BY count() DESC;
なんどか実行すると、全件では 0.130 sec. 前後、1日分では 0.013 sec. くらいでした。
実行計画は以下のようになります。
EXPLAIN
SELECT path, count()
FROM site_view
GROUP BY path
ORDER BY count() DESC;
┌─explain───────────────────────────────────────────────────────────────────────────┐
│ Expression (Projection) │
│ Sorting (Sorting for ORDER BY) │
│ Expression (Before ORDER BY) │
│ Aggregating │
│ Expression (Before GROUP BY) │
│ SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│ ReadFromMergeTree │
└───────────────────────────────────────────────────────────────────────────────────┘
プロジェクションを作成する
このテーブルに対して、プロジェクションで集計を高速化します。
toDate(event_time)、path 単位で集計を行います。
ALTER TABLE site_view
ADD PROJECTION site_view_by_path (
SELECT
toDate(event_time) event_date,
path,
count() view
GROUP BY
event_date,
path
);
PROJECTIONを作ると今後追加されるデータについて集計データが用意されますが、既存のデータについては用意されません。
既存データ分は次のようにして作成します。
ALTER TABLE site_view
MATERIALIZE PROJECTION site_view_by_path
SETTINGS mutations_sync = 1;
手元の環境では 0.649 sec. ほどで処理が完了しました。
プロジェクションによる高速化
最初と同じクエリを再度実行してみます。
SELECT path, count()
FROM site_view
GROUP BY path
ORDER BY count() DESC;
こちらのクエリは 0.005 sec. くらいになりました。
以前が 0.130 sec. ですので、かなり高速化できました。
SELECT path, count()
FROM site_view
WHERE toDate(event_time) = '2022-01-01'
GROUP BY path
ORDER BY count() DESC;
こちらは 0.006 sec. くらいになりました。
以前が 0.013 sec. なので、対象となるデータが少なければ効果は低めになるようです。
EXPLAINで実行計画を確認すると、projection が使用されていることを確認できます。
EXPLAIN
SELECT path, count()
FROM site_view
GROUP BY path
ORDER BY count() DESC;
┌─explain──────────────────────────────────────────────────────────────────────────┐
│ Expression (Projection) │
│ Sorting (Sorting for ORDER BY) │
│ Expression (Before ORDER BY) │
│ SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│ ReadFromStorage (MergeTree(with Aggregate projection site_view_by_path)) │
└──────────────────────────────────────────────────────────────────────────────────┘
クエリの書き換え
プロジェクションはクエリの自動書き換えによって高速化するため、クエリの指定の仕方は少し注意が必要です。
例えば 2022-01-01 のみのクエリを WHERE toDate(event_time) = '2022-01-01' ではなく、以下のように指定するとプロジェクションは使われません。
EXPLAIN
SELECT path, count()
FROM site_view
WHERE event_time >= '2022-01-01 00:00:00' AND event_time < '2022-01-02 00:00:00'
GROUP BY path
ORDER BY count() DESC;
┌─explain─────────────────────────────────────────────────────────────────────────────┐
│ Expression (Projection) │
│ Sorting (Sorting for ORDER BY) │
│ Expression (Before ORDER BY) │
│ Aggregating │
│ Expression (Before GROUP BY) │
│ Filter (WHERE) │
│ SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│ ReadFromMergeTree │
└─────────────────────────────────────────────────────────────────────────────────────┘
マテリアライズド・ビューとの差
プロジェクションは、元のテーブルのパート単位で、集計結果を保持します。
このため、パーティションが細かく別れていたり、パート数が多い場合は、マテリアライズド・ビューに比べて若干性能が落ちるかもしれません。