増分マテリアライズドビュー
増分マテリアライズドビュー（Materialized Views）を使用することで、ユーザーはクエリ実行時の計算コストを挿入時に移行し、結果として SELECT クエリを高速化できます。
Postgresなどのトランザクショナルデータベースとは異なり、ClickHouseのマテリアライズドビューは、テーブルにデータが挿入される際にデータブロックに対してクエリを実行するトリガーに過ぎません。このクエリの結果は、二つ目の「ターゲット」テーブルに挿入されます。さらに行が挿入されると、結果は再びターゲットテーブルに送信され、中間結果が更新されてマージされます。このマージされた結果は、元のデータ全体にクエリを実行するのと同等です。
マテリアライズドビューの主な動機は、ターゲットテーブルに挿入される結果が、行に対する集約、フィルタリング、または変換の結果を表すことです。これらの結果は、元のデータの小さい表現（集約の場合の部分的なスケッチ）であることがよくあります。これにより、ターゲットテーブルから結果を読み取るためのクエリがシンプルになるため、同じ計算を元のデータで行うよりもクエリ時間が短くなり、計算（したがってクエリの待機時間）がクエリ時間から挿入時間にシフトされます。
ClickHouseのマテリアライズドビューは、基盤となるテーブルにデータが流入するにつれてリアルタイムで更新され、常に更新されるインデックスのように機能します。これは、他のデータベースでマテリアライズドビューが通常静的なスナップショットであり、更新が必要であるのと対照的です（ClickHouseの refreshable materialized views に類似）。
例
ある投稿の1日ごとの上票と下票の数を取得したいとします。
このクエリは、ClickHouseのおかげで合理的にシンプルで、toStartOfDay 関数を利用できます：
このクエリはすでにClickHouseによって高速ですが、さらに良くすることはできますか？
挿入時にマテリアライズドビューを使用してこれを計算したい場合、結果を受け取るテーブルが必要です。このテーブルは、1日ごとに1行のみを保持する必要があります。既存の日に対する更新が受信された場合、他のカラムは既存の日の行にマージされる必要があります。この増分状態のマージを行うには、他のカラムの部分的な状態を保存しておく必要があります。
これにはClickHouseの特別なエンジンタイプが必要です：SummingMergeTree。これにより、同じ順序キーを持つすべての行が1行に置き換えられ、数値カラムの合計値が含まれます。次のテーブルは、同じ日付の行をマージし、数値カラムを合計します：
マテリアライズドビューを示すために、votesテーブルが空であり、まだデータを受信していないと仮定しましょう。私たちのマテリアライズドビューは、votesに挿入されるデータに対して上記の SELECT を実行し、その結果を up_down_votes_per_day に送信します：
ここでの TO クレーズは重要で、結果が送信される場所を示します、すなわち up_down_votes_per_day です。
先ほどの挿入からvotesテーブルに再挿入できます：
完了すると、up_down_votes_per_day のサイズを確認できます - 1日ごとに1行があるはずです：
ここでは、votes から238百万行を5000行に効果的に減少させています。しかし、重要なのは、新しい票が votes テーブルに挿入されると、それぞれの日に対する新しい値が up_down_votes_per_day に送信され、非同期的にバックグラウンドで自動的にマージされ、1日ごとに1行だけが保持されることです。そのため、up_down_votes_per_day は常に小さく、最新の状態であると言えます。
行のマージは非同期で行われるため、ユーザーがクエリを実行する際には、1日に複数の投票があるかもしれません。保留中の行がクエリ時にマージされることを保証するには、以下の2つのオプションがあります：
- テーブル名に
FINAL修飾子を使用する。上記のカウントクエリでこれを行いました。 - 最終テーブルで使用される順序キー（即ち
CreationDate）で集約し、メトリクスを合計する。通常、こちらがより効率的で柔軟ですが（テーブルは他の目的にも使用可能）、前者は一部のクエリには簡単かもしれません。以下に両方を示します：
これにより、クエリの実行時間が0.133秒から0.004秒に短縮されました - 25倍以上の改善です！
ORDER BY = GROUP BY通常、マテリアライズドビューの変換で使用される GROUP BY 句のカラムは、SummingMergeTree または AggregatingMergeTree テーブルエンジンを使用する場合、ターゲットテーブルの ORDER BY 句で使用されるカラムと一致している必要があります。これらのエンジンは、バックグラウンドで行をマージする際に同じ値を持つ行をマージするために ORDER BY カラムに依存します。 GROUP BY と ORDER BY カラム間の不一致は、クエリパフォーマンスの非効率さ、最適でないマージ、またはデータの不一致を引き起こす可能性があります。
より複雑な例
上記の例では、マテリアライズドビューを使用して1日ごとに2つの合計を計算および維持しました。合計は、部分的な状態を維持する最も単純な集約の形を表します - 新しい値を到着したときに既存の値に加えるだけです。しかし、ClickHouseのマテリアライズドビューは、あらゆる集約タイプに使用することができます。
投稿ごとに1日ごとの統計を計算したいとします： Score の99.9パーセンタイルと CommentCount の平均。これを計算するクエリは次のようになります：
以前と同様に、新しい投稿が posts テーブルに挿入されるときに上記のクエリを実行するマテリアライズドビューを作成できます。
例の目的上、投稿データをS3からロードするのを避けるために、posts と同じスキーマを持つ重複テーブル posts_null を作成します。しかし、このテーブルはデータを格納せず、行が挿入されたときにマテリアライズドビューによって使用されるだけです。データの格納を防ぐために、Null テーブルエンジンタイプ を使用できます。
Nullテーブルエンジンは強力な最適化です - /dev/null のように考えてください。私たちのマテリアライズドビューは、posts_null テーブルが挿入時に行を受け取るときにサマリー統計を計算し保存します - それは単なるトリガーです。しかし、一次データは保存されません。私たちの場合、元の投稿を保存したいかもしれませんが、このアプローチは生データの保存オーバーヘッドを回避しつつ集約を計算するのに使用できます。
マテリアライズドビューは次のようになります：
集約関数の最後に State サフィックスを追加していることに注意してください。これにより、関数の集約状態が最終結果の代わりに返され、他の状態とマージできる追加情報が含まれます。たとえば、平均の場合、これにはカウントとカラムの合計が含まれます。
部分集約状態は、正しい結果を計算するために必要です。たとえば、平均を計算するために、サブ範囲の平均を単に平均化することは不正確な結果を生じます。
次に、このビュー post_stats_per_day のためのターゲットテーブルを作成し、これらの部分集約状態を保存します：
以前に SummingMergeTree がカウントを保存するのに十分でしたが、他の関数にはより高度なエンジンタイプが必要です： AggregatingMergeTree。
集約状態が保存されることをClickHouseに知らせるために、Score_quantiles と AvgCommentCount のタイプを AggregateFunction と定義し、部分状態のソース関数とそのソースカラムのタイプを指定します。SummingMergeTree のように、ORDER BY キーの値が同じ行はマージされます（上記の例の Day）。
マテリアライズドビューを通じて post_stats_per_day にポピュレートするために、posts からすべての行を posts_null に単純に挿入できます：
本番環境では、通常マテリアライズドビューを
postsテーブルに接続します。ここでは、Nullテーブルを使用してデモを行いました。
私たちの最終クエリは、関数に対して Merge のサフィックスを利用する必要があります（カラムが部分集約状態を保存しているため）：
ここでは FINAL の代わりに GROUP BY を使用しています。
マテリアライズドビュー内でのソーステーブルのフィルタやジョインの使用
ClickHouseのマテリアライズドビューで作業する際には、マテリアライズドビューのクエリの実行中にソーステーブルがどのように扱われるかを理解することが重要です。特に、マテリアライズドビューのクエリ内のソーステーブルは、挿入されたデータブロックによって置き換えられます。この動作を適切に理解しないと、予期しない結果を引き起こす可能性があります。
例のシナリオ
以下のセットアップを考えてみましょう：
説明
上記の例では、mvw1 と mvw2 という二つのマテリアライズドビューがあり、互いに似た操作を実行していますが、ソーステーブル t0 への参照の方法にわずかな違いがあります。
mvw1 では、テーブル t0 がJOINの右側にある (SELECT * FROM t0) サブクエリ内で直接参照されています。データが t0 に挿入されると、マテリアライズドビューのクエリは挿入されたデータブロックで t0 を置き換えて実行されます。これにより、JOIN操作が新たに挿入された行だけに実行され、テーブル全体ではなく、新しく挿入された行のみが考慮されます。
vt0をJOINする2番目のケースでは、ビューが t0 からすべてのデータを読み取ります。これにより、JOIN操作は新しく挿入されたブロックだけでなく、t0 のすべての行を考慮することができます。
なぜこのような動作をするのか
ClickHouseがマテリアライズドビューのクエリ内のソーステーブルをどのように扱うかに重要な違いがあります。マテリアライズドビューが挿入によってトリガーされると、ソーステーブル（この場合 t0 ）は挿入されたデータブロックによって置き換えられます。この動作は、クエリを最適化するために利用できますが、予期しない結果を避けるためには注意が必要です。
使用例と警告
実際には、この動作を利用して、ソーステーブルのデータのサブセットのみを処理する必要があるマテリアライズドビューを最適化することができます。たとえば、ソーステーブルをフィルタリングしてから他のテーブルにJOINするためにサブクエリを使用できます。これにより、マテリアライズドビューが処理するデータ量を減らし、パフォーマンスを向上させることができます。
この例では、IN (SELECT id FROM t0) サブクエリから構築されたセットが新しく挿入された行のみを持ち、これを使って t1 に対するフィルタリングを行います。
その他のアプリケーション
上記では、主にマテリアライズドビューを使用してデータの部分集約をインクリメンタルに更新することに焦点を当て、計算をクエリから挿入時に移行しました。この一般的な使用例を超えて、マテリアライズドビューには他の多くのアプリケーションがあります。
フィルタリングと変換
特定の状況では、挿入時に行とカラムのサブセットのみを挿入したい場合があります。この場合、posts_null テーブルが挿入を受け取り、posts テーブルへの挿入前に行をフィルタリングする SELECT クエリが考えられます。たとえば、posts テーブル内の Tags カラムを変換したいとします。これは、パイプ区切りのタグ名リストを含んでいます。これを配列に変換することで、個々のタグ値で集約する際にもっと簡単に処理できます。
この変換を
INSERT INTO SELECTの実行時に行うことができます。マテリアライズドビューを使用すると、このロジックをClickHouse DDLにカプセル化し、挿入がシンプルになり、変換が新しい行に適用されます。
この変換のためのマテリアライズドビューは次のようになります：
ルックアップテーブル
ユーザーは、フィルタや集約句で頻繁に使用されるカラムとともにClickHouseの順序キーを選択する際にアクセスパターンを考慮すべきです。これは、ユーザーが単一のカラムセットにカプセル化できない多様なアクセスパターンを持つシナリオには制約がある可能性があります。たとえば、次の comments テーブルを考えてみましょう：
ここでの順序キーは PostId でフィルタリングされるクエリの最適化に役立ちます。
ユーザーが特定の UserId でフィルタリングし、彼らの平均 Score を計算したいとします：
ClickHouseにはデータが小さいため高速ですが、処理された行数から、これが90.38百万行のフルテーブルスキャンを必要とすることがわかります。大規模なデータセットでは、マテリアライズドビューを使用して、フィルタリングカラム UserId の PostId の順序キー値をルックアップすることができます。これらの値は、その後効率的なルックアップを実施するために使用されます。
この例では、マテリアライズドビューは非常にシンプルで、comments から PostId と UserId だけを選択します。これらの結果は comments_posts_users テーブルに送信され、UserId で並び替えられます。以下に Comments テーブルのNULLバージョンを作成し、これを使用してビューと comments_posts_users テーブルをポピュレートします：
このビューをサブクエリで使用すると、前のクエリを加速できます：
チェーン化
マテリアライズドビューはチェーン化でき、複雑なワークフローを確立できます。実用的な例については、この ブログ記事 をお勧めします。