インクリメンタルマテリアライズドビュー
背景
増分マテリアライズドビュー（Materialized Views）は、ユーザーがクエリ時の計算コストを挿入時に移行できるようにし、結果としてSELECTクエリをより迅速に実行できるようにします。
Postgresのようなトランザクショナルデータベースとは異なり、ClickHouseのマテリアライズドビューは、テーブルにデータが挿入される際にデータのブロックに対してクエリを実行するトリガーに過ぎません。このクエリの結果は、別の「ターゲット」テーブルに挿入されます。更に行が挿入されると、結果は再びターゲットテーブルに送信され、中間結果が更新され、マージされます。このマージされた結果は、元のデータ全体に対してクエリを実行するのと同等です。
マテリアライズドビューの主な動機は、ターゲットテーブルに挿入された結果が行の集計、フィルタリング、または変換の結果を示すことです。これらの結果は、元のデータの小さな表現（集計の場合の部分的なスケッチ）となることがよくあります。これにより、ターゲットテーブルから結果を読み取るためのクエリがシンプルになり、同じ計算が元のデータに対して行われるよりもクエリ時間が短縮され、計算（およびその結果、クエリの待機時間）がクエリ時から挿入時に移されます。
ClickHouseのマテリアライズドビューは、データが基づくテーブルに流れ込むにつれてリアルタイムで更新され、常に更新されるインデックスのように機能します。これは、マテリアライズドビューが通常クエリの静的なスナップショットであり、更新が必要な他のデータベースとは対照的です（ClickHouseのRefreshable Materialized Viewsに類似）。
例
例の目的のために、"スキーマ設計"で文書化されたStack Overflowデータセットを使用します。
例えば、投稿ごとの日に対するアップボートおよびダウンボートの数を取得したいとします。
これは、toStartOfDay関数のおかげでClickHouseではかなりシンプルなクエリです:
このクエリは既にClickHouseによって速く処理されますが、さらに良くできますか？
挿入時にマテリアライズドビューを使用してこれを計算したい場合、結果を受け取るためのテーブルが必要です。このテーブルは、1日に対して1行のみを保持する必要があります。既存の日に対する更新が受信された場合、他のカラムは既存の日の行にマージされる必要があります。このインクリメンタルな状態のマージが行われるためには、他のカラムの部分的な状態を保存する必要があります。
これには、ClickHouseの特別なエンジンタイプが必要です: SummingMergeTree。これにより、同じ順序キーを持つすべての行が1つの行に置き換えられ、その行には数値カラムの合計値が含まれます。次のテーブルは、同じ日付の行をマージし、数値カラムを合計します:
マテリアライズドビューを示すために、投票テーブルが空でデータをまだ受信していないと仮定します。マテリアライズドビューは、votesに挿入されたデータに対して上記のSELECTを実行し、結果をup_down_votes_per_dayに送信します:
ここでのTO句は重要であり、結果が送信される場所、すなわちup_down_votes_per_dayを示しています。
以前の挿入から投票テーブルを再ポピュレートできます:
完了したら、up_down_votes_per_dayのサイズを確認できます - 1日に対して1行保有しているはずです:
ここでは、238百万行（votes内）の数を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カラムの不整合は、クエリのパフォーマンスを低下させ、最適でないマージやデータの不一致を引き起こす可能性があります。
より複雑な例
上記の例は、日に対して2つの合計を計算および維持するためにマテリアライズドビューを使用しています。合計は、部分的な状態を維持するための最も単純な形式の集計を表します - 新しい値が到着すると、単に既存の値に追加できます。ただし、ClickHouseのマテリアライズドビューは、任意の集計タイプに使用できます。
例えば、各日に対する投稿に関する統計を計算したいとします: 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テーブルを使用して、Nullテーブルを示すためにposts_nullを使用しました。
最後のクエリは、関数にMerge接尾辞を使用する必要があります（列が部分的な集約状態を保存しているため）:
ここでは、FINALを使用する代わりにGROUP BYを使用しています。
その他のアプリケーション
上記は、主にマテリアライズドビューを使用してデータの部分的集約を増分的に更新することに焦点を当てています。従って、計算をクエリから挿入時に移動させています。この一般的な使用ケースを越えて、マテリアライズドビューには多くのその他のアプリケーションがあります。
フィルタリングと変換
特定の状況では、挿入時に行とカラムのサブセットのみを挿入することを望むかもしれません。この場合、posts_nullテーブルが挿入を受け入れ、SELECTクエリが行をフィルタリングしてpostsテーブルへの挿入を行うことができます。例えば、postsテーブル内のTagsカラムを変換したいとします。これは、パイプで区切られたタグ名のリストを含みます。これを配列に変換することで、個々のタグ値による集計がより簡単になります。
この変換は、
INSERT INTO SELECTを実行するときに行うことができます。マテリアライズドビューは、このロジックをClickHouseのDDLでカプセル化し、挿入を簡素化し、新しい行に対して変換を適用できます。
この変換のためのマテリアライズドビューは次のように示されます:
ルックアップテーブル
ユーザーは、ClickHouseの順序キーを選択する際にアクセスパターンを考慮すべきです。フィルターや集計句で頻繁に使用されるカラムを使用する必要があります。これは、ユーザーが単一のカラムのセットにカプセル化できない、より多様なアクセスパターンを持つシナリオに制限される可能性があります。例えば、次のcommentsテーブルを考えてみましょう:
ここでの順序キーは、PostIdでフィルタリングするクエリのためにテーブルを最適化します。
特定のUserIdでフィルタリングし、その平均Scoreを計算したいとします:
これは高速ですが（ClickHouseではデータが小さいため）、処理された行数からフルテーブルスキャンが必要であることがわかります - 90.38百万行。より大きなデータセットでは、マテリアライズドビューを使用してフィルタリングカラムUserIdのために順序キーPostIdをルックアップすることができます。これらの値を使用して効率的にルックアップします。
この例では、マテリアライズドビューは非常にシンプルで、挿入時にcommentsからPostIdとUserIdのみを選択します。これにより結果は、UserIdで順序付けられたcomments_posts_usersというテーブルに送信されます。以下に、コメントテーブルのNullバージョンを作成し、これを使用してビューをポピュレートし、comments_posts_usersテーブルを作成します:
これにより、以前のクエリを加速するためにサブクエリでこのビューを使用できます:
チェイン
マテリアライズドビューはチェインでき、複雑なワークフローを確立することができます。実用的な例については、このブログ記事を読むことをお勧めします。
マテリアライズドビューとJOIN
以下は増分マテリアライズドビューにのみ適用されます。リフレッシュ可能なマテリアライズドビューは、ターゲットデータセット全体に対して定期的にクエリを実行し、JOINを完全にサポートします。複雑なJOINにリザルトの新鮮さが許容できる場合は、それを検討してください。
ClickHouseの増分マテリアライズドビューは、JOIN操作を完全にサポートしますが、1つの重要な制約があります: マテリアライズドビューはソーステーブル（クエリの左側）への挿入時のみトリガーされます。 JOINの右側のテーブルは、たとえデータが変更されても更新をトリガーしません。この動作は、挿入時にデータが集約または変換される増分マテリアライズドビューを構築する際に特に重要です。
増分マテリアライズドビューがJOINを使用して定義されると、SELECTクエリの最も左側のテーブルがソースとして機能します。このテーブルに新しい行が挿入されると、ClickHouseはその新しく挿入された行でのみマテリアライズドビュークエリを実行します。JOIN内の右側のテーブルはこの実行中に完全に読み取られますが、それ自体がトリガーされることはありません。
この動作により、マテリアライズドビュー内のJOINは、静的な次元データに対するスナップショットJOINに似たものとなります。
これは、参照または次元テーブルを使用してデータを付加するのに適しています。ただし、右側のテーブル（例えば、ユーザーメタデータ）への更新は、マテリアライズドビューを遡及的に更新しません。更新されたデータを見るためには、ソーステーブルに新しい挿入が到着する必要があります。
例
具体的な例を通じて、Stack Overflowデータセットを使用してみましょう。ユーザーごとの日次バッジを計算するためにマテリアライズドビューを使用し、usersテーブルからのユーザーの表示名を含めます。
思い出してください、私たちのテーブルスキーマは次のとおりです:
usersテーブルは事前にポピュレートされていると仮定します:
マテリアライズドビューとその関連ターゲットテーブルは次のように定義されています:
マテリアライズドビューのGROUP BY句は、ターゲットテーブルのORDER BY句と一致する必要があります。これにより、行が正しく集約およびマージされます。これらのいずれかを省略すると、不正確な結果や効率的でないマージにつながる可能性があります。
バッジをポピュレートすると、ビューがトリガーされ、daily_badges_by_userテーブルがポピュレートされます。
特定のユーザーが獲得したバッジを見ることができるように次のクエリを書くことができます:
このユーザーが新しいバッジを受け取ると、行が挿入され、ビューが更新されます:
挿入の待機時間に注意してください。挿入されたユーザーロウは、全体のusersテーブルに対して結合されるため、挿入パフォーマンスに大きな影響を与えます。これに対応する方法を、"Using Source Table in Filters and Joins"で提案します。
一方、新しいユーザーのためにバッジを挿入し、その後ユーザーの行が挿入された場合、マテリアライズドビューはユーザーの指標を捉えることに失敗します。
この場合、ビューはバッジの挿入に対してのみ実行され、そのユーザー行が存在する前に実行されます。もしそのユーザーのために別のバッジを挿入すると、行が挿入されるのは予想通りです:
ただし、この結果は不正確です。
マテリアライズドビューのJOINに関するベストプラクティス
-
左側のテーブルをトリガーとして使用する。
SELECT文の左側のテーブルのみがマテリアライズドビューをトリガーします。右側のテーブルに対する変更は更新をトリガーしません。 -
結合データを事前に挿入する。 ソーステーブルに行を挿入する前に、結合テーブルのデータが存在することを確認します。JOINは挿入時に評価されるため、データが欠落すると、行が一致しないかnullになります。
-
結合から取り出す列を制限する。 必要な列のみを結合テーブルから選択し、メモリ使用量を最小限に抑え、挿入時の待機時間を減らします（以下を参照）。
-
挿入時のパフォーマンスを評価する。 JOINは挿入のコストを増加させ、特に右側の大きなテーブルで顕著です。代表的な本番データを使用して挿入レートをベンチマーク化します。
-
単純なルックアップには辞書を優先する。 キー-バリューのルックアップ（例：ユーザーIDから名前）には辞書を使用し、高額なJOIN操作を回避します。
-
マージの効率のために
GROUP BYとORDER BYを整合させる。SummingMergeTreeまたはAggregatingMergeTreeを使用する場合、GROUP BYがターゲットテーブルのORDER BY句と一致することを確認し、効率的な行のマージを可能にします。 -
明示的な列エイリアスを使用する。 テーブルに重複する列名がある場合、エイリアスを使用してあいまいさを防ぎ、ターゲットテーブルで正確な結果を確保します。
-
挿入量と頻度を考慮する。 JOINは中程度の挿入負荷でうまく機能します。高スループットのインジェクションには、ステージングテーブル、事前の結合、または辞書やリフレッシュ可能なマテリアライズドビューなどの他のアプローチを検討してください。
マテリアライズドビュー内のソーステーブルの使用
ClickHouseでマテリアライズドビューを使用する場合、マテリアライズドビューのクエリ実行中にソーステーブルがどのように扱われるかを理解することが重要です。具体的には、マテリアライズドビューのクエリ内のソーステーブルは挿入されたデータブロックに置き換えられます。この動作は、正しく理解されていない場合にいくつかの思ってもみない結果を導く可能性があります。
例のシナリオ
以下のセットアップを考えてみましょう:
説明
上記の例では、mvw1とmvw2という2つのマテリアライズドビューが似たような操作を実行していますが、ソーステーブルt0を参照する方法にわずかな違いがあります。
mvw1の中では、t0テーブルがJOINの右側にある(SELECT * FROM t0)サブクエリの中で直接参照されています。t0にデータが挿入されると、マテリアライズドビューのクエリは、挿入されたデータブロックがt0を置き換えた状態で実行されます。これは、JOIN操作が新しく挿入された行にのみ実行されることを意味します。
JOINにvt0を使用する場合、ビューはt0からのすべてのデータを読み取ります。これにより、JOIN操作がt0内のすべての行を考慮し、新しく挿入されたブロックに限定されることがなくなります。
ClickHouseがマテリアライズドビューのクエリ内のソーステーブルをどのように扱うかにおける重要な違いです。マテリアライズドビューが挿入によってトリガーされると、ソーステーブル（この場合はt0）は挿入されたデータブロックに置き換えられます。この動作は、クエリを最適化するために利用できますが、期待しない結果を避けるためには注意が必要です。
利用ケースと注意事項
実際には、この動作を使用して、ソーステーブルのデータのサブセットのみを処理する必要があるMaterialized Viewを最適化することができます。たとえば、他のテーブルと結合する前にサブクエリを使用してソーステーブルをフィルタリングできます。これにより、Materialized Viewによって処理されるデータ量を削減し、パフォーマンスを向上させることができます。
この例では、IN (SELECT id FROM t0)サブクエリから構築されたセットには、新しく挿入された行のみが含まれているため、t1をそれに対してフィルタリングするのに役立ちます。
Stack Overflowの例
ユーザーごとの日次バッジを計算するための以前のMaterialized Viewの例を考えてみましょう。この例では、usersテーブルからユーザーの表示名を含みます。
このビューは、badgesテーブルへの挿入遅延に大きな影響を与えました。例えば、
上記のアプローチを使用して、このビューを最適化できます。挿入されたバッジ行のユーザーIDを使用して、usersテーブルにフィルタを追加します：
これにより、初期バッジの挿入が高速化されるだけでなく：
将来のバッジ挿入も効率的になります：
上記の操作では、ユーザーID 2936484のユーザーテーブルから1行のみが取得されます。このルックアップもIdのテーブルオーダリングキーによって最適化されています。
Materialized ViewsとUNION
UNION ALLクエリは、複数のソーステーブルからデータを単一の結果セットに結合するために一般的に使用されます。
UNION ALLはIncremental Materialized Viewsでは直接サポートされていませんが、各SELECTブランチについて個別のMaterialized Viewを作成し、その結果を共有ターゲットテーブルに書き込むことで同じ結果を得ることができます。
例としてStack Overflowデータセットを使用します。以下のbadgesとcommentsテーブルは、ユーザーが獲得したバッジと投稿に対するコメントを表しています。
これらは次のINSERT INTOコマンドでポピュレートできます：
ユーザーの活動を統合するビューを作成したいとしましょう。これには、これら2つのテーブルを結合して各ユーザーの最終活動を示します：
このクエリの結果を受け取るためのターゲットテーブルを作成したと仮定します。結果が正しくマージされるように、AggregatingMergeTreeテーブルエンジンとAggregateFunctionを使用します。
このテーブルがbadgesまたはcommentsに新しい行が挿入されると更新されるようにしたい場合、この問題に対する素朴なアプローチは、前述のUNIONクエリでMaterialized Viewを作成しようとすることです：
これは文法的には有効ですが、意図しない結果を生じさせます - このビューはcommentsテーブルへの挿入のみをトリガーします。例えば：
badgesテーブルへの挿入はビューをトリガーし、user_activityは更新されません：
これを解決するために、各SELECT文のためにMaterialized Viewを作成するだけです：
どちらのテーブルに挿入しても、正しい結果が得られます。例えば、commentsテーブルに挿入する場合：
同様に、badgesテーブルへの挿入もuser_activityテーブルに反映されます：
並列処理と逐次処理
前の例で示したように、1つのテーブルが複数のMaterialized Viewsのソースとして機能することができます。これらが実行される順序は、設定parallel_view_processingによります。
デフォルトでは、この設定は0 (false)であり、これはMaterialized Viewsがuuid順に逐次実行されることを意味します。
例えば、次のsourceテーブルと3つのMaterialized Viewsを考えてみましょう。それぞれはtargetテーブルに行を送信します。
各ビューは、targetテーブルに行を挿入する前に1秒間一時停止することに注意してください。また、名前と挿入時間を含んでいます。
テーブルsourceに1行挿入するには約3秒かかり、各ビューが逐次実行されます：
各行の到着をSELECTで確認できます：
これは、ビューのuuidと一致します：
対照的に、parallel_view_processing=1を有効にして行を挿入した場合、ビューは並列に実行され、targetテーブルに到着する行の順序は保証されません。
各ビューからの行の到着順序は同じですが、これは保証されていません - 各行の挿入時間の類似性が示すように。また、挿入パフォーマンスが向上したことにも注意してください。
並列処理を使用する時
parallel_view_processing=1を有効にすることで挿入スループットが大幅に向上する可能性があります。特に、単一のテーブルに複数のMaterialized Viewsが接続されている場合には特に顕著です。しかし、トレードオフを理解することが重要です：
- 挿入圧の増加：すべてのMaterialized Viewsが同時に実行されるため、CPUとメモリの使用量が増加します。各ビューが重い計算やJOINを実行する場合、システムがオーバーロードされることがあります。
- 厳格な実行順序の必要性：ビューの実行順序が重要な稀なワークフロー（例：連鎖する依存関係）では、並列実行が不整合な状態やレース条件につながる可能性があります。このような状況に対応する設計は可能ですが、そのようなセットアップは脆弱であり、将来のバージョンで壊れる可能性があります。
逐次実行は長い間デフォルトであり、部分的にエラーハンドリングの複雑さが原因でした。歴史的に、1つのMaterialized Viewの失敗が他の実行を妨げる可能性がありました。新しいバージョンでは、ブロックごとに失敗を隔離することで改善されていますが、逐次実行は依然として明確な失敗セマンティクスを提供します。
一般的に、parallel_view_processing=1を有効にするのは以下のような場合です：
- 複数の独立したMaterialized Viewsがある場合
- 挿入パフォーマンスを最大化することを目指している場合
- 並行ビュー実行を処理するというシステムの能力を理解している場合
無効にするべき場合は：
- Materialized Viewsが相互に依存している場合
- 予測可能で秩序ある実行が要求される場合
- 挿入動作をデバッグまたは監査し、決定論的リプレイを求める場合
Materialized Viewsと共通テーブル式（CTEs）
再帰的でない共通テーブル式（CTE）はMaterialized Viewsでサポートされています。
ClickHouseはCTEをマテリアライズしません。代わりに、CTE定義をクエリに直接置き換え、同じ式の複数回の評価を引き起こすことがあります（CTEが複数回使用された場合）。
以下の例は、各投稿タイプのデイリーアクティビティを計算します。
CTEはこの場合厳密には必要ありませんが、例のために、ビューは期待通りに動作します：
ClickHouseでは、CTEがインライン化されます。これは、最適化中にCTEがクエリに効果的にコピー＆ペーストされ、マテリアライズされないことを意味します。これには以下の意味があります：
- CTEがソーステーブル（Materialized Viewに接続されているテーブル）とは異なるテーブルを参照し、
JOINやIN句で使用される場合、それはサブクエリや結合のように振る舞い、トリガーにはなりません。 - Materialized Viewはメインソーステーブルへの挿入時のみトリガーされますが、CTEは挿入ごとに再実行されるため、特に参照テーブルが大きい場合には不要なオーバーヘッドが発生する可能性があります。
例えば、
この場合、ユーザーCTEは各postsへの挿入ごとに再評価され、Materialized Viewは新しいユーザーが挿入されても更新されません - 挿入が発生した場合のみ更新されます。
一般的に、Materialized Viewに接続されているソーステーブル上で機能するロジックにCTEsを使用するか、参照されるテーブルが小さく、パフォーマンスのボトルネックを引き起こさないと確信している場合にCTEを使用してください。また、Materialized Viewsに関するJOINの最適化を考慮するのも良いでしょう。
