インクリメンタルマテリアライズドビュー
背景
増分マテリアライズドビュー(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の最適化を考慮するのも良いでしょう。