重複除去戦略 (CDCを使用)

Updates and deletes replicated from Postgres to ClickHouse result in duplicated rows in ClickHouse due to its data storage structure and the replication process. This page covers why this happens and the strategies to use in ClickHouse to handle duplicates.

データはどのようにレプリケートされますか？

PostgreSQLの論理デコーディング

ClickPipesは、Postgres Logical Decodingを使用して、Postgres内で発生する変更を消費します。Postgresの論理デコーディングプロセスにより、ClickPipesのようなクライアントは、INSERT、UPDATE、およびDELETEの一連の操作として、人間が読みやすい形式で変更を受け取ることができます。

ReplacingMergeTree

ClickPipesは、ReplacingMergeTreeエンジンを使用してPostgresテーブルをClickHouseにマップします。ClickHouseは、追加専用のワークロードで最も良いパフォーマンスを発揮し、頻繁なUPDATEを推奨していません。ここで、ReplacingMergeTreeが特に強力になります。

ReplacingMergeTreeでは、更新は、新しいバージョン(_peerdb_version)を持つ行の挿入としてモデル化され、削除は新しいバージョンを持ち、_peerdb_is_deletedがtrueとしてマークされた挿入としてモデル化されます。ReplacingMergeTreeエンジンは、バックグラウンドでデデュプリケート/マージを行い、特定の主キー(id)の最新バージョンの行を保持し、バージョン付きのINSERTとしてUPDATEとDELETEを効率的に処理します。

以下は、ClickPipesによってClickHouseでテーブルを作成するために実行されたCREATE TABLEステートメントの例です。

例示的な例

以下のイラストは、PostgreSQLとClickHouse間のテーブルusersの同期の基本的な例を示しています。

ステップ1では、PostgreSQL内の2行の初期スナップショットとClickPipesがそれらの2行をClickHouseに初期ロードしている様子が示されています。観察できるように、両方の行はそのままClickHouseにコピーされます。

ステップ2では、ユーザーテーブルに対する3つの操作が示されています：新しい行の挿入、既存の行の更新、別の行の削除。

ステップ3では、ClickPipesがINSERT、UPDATE、DELETE操作をClickHouseにバージョン付きの挿入としてレプリケートする様子が示されています。UPDATEはID2の行の新しいバージョンとして現れ、一方でDELETEはID1の新しいバージョンとして現れ、_is_deletedを使用してtrueとしてマークされます。このため、ClickHouseにはPostgreSQLに比べて3つの追加行があります。

その結果、SELECT count(*) FROM users;のようなシンプルなクエリを実行すると、ClickHouseとPostgreSQLで異なる結果が得られることがあります。ClickHouseマージドキュメントによると、古くなった行のバージョンは最終的にマージプロセス中に破棄されます。しかし、このマージのタイミングは予測できず、ClickHouseのクエリはそれが行われるまで一貫性のない結果を返す可能性があります。

ClickHouseとPostgreSQLの両方で同じクエリ結果を保証するにはどうすればよいでしょうか？

FINALキーワードを使用してデデュプリケートする

ClickHouseクエリでデデュプリケートデータを処理する推奨方法は、FINAL修飾子を使用することです。これにより、デデュプリケートされた行のみが返されます。

これを3つの異なるクエリに適用する方法を見てみましょう。

次のクエリのWHERE句に注意してください。これは削除された行を除外するために使用されます。

• 単純なカウントクエリ：投稿の数をカウントします。

これは、同期が正常かどうかを確認するために実行できる最も簡単なクエリです。両方のクエリは同じカウントを返すべきです。

• JOINを使用した単純な集計：最も多くのビューを獲得した上位10ユーザー。

単一のテーブルに対する集計の例です。ここに重複があると、SUM関数の結果に大きな影響を与えるでしょう。

FINAL設定

クエリ内の各テーブル名にFINAL修飾子を追加する代わりに、FINAL設定を使用して、クエリ内のすべてのテーブルに自動的に適用することができます。

この設定は、クエリごとまたはセッション全体に適用できます。

ROWポリシー

冗長な_peerdb_is_deleted = 0フィルターを隠す簡単な方法は、ROWポリシーを使用することです。以下は、テーブルvotesのすべてのクエリから削除された行を除外するための行ポリシーを作成する例です。

行ポリシーは、ユーザーとロールのリストに適用されます。この例では、すべてのユーザーとロールに適用されています。これは特定のユーザーやロールのみに調整できます。

PostgreSQLのようにクエリする

PostgreSQLからClickHouseに分析データセットを移行するには、データ処理とクエリ実行の違いを考慮してアプリケーションクエリを変更する必要があります。

このセクションでは、オリジナルのクエリを変更せずにデータをデデュプリケートする技術を探ります。

ビュー

ビューは、クエリからFINALキーワードを隠すのに最適な方法です。なぜなら、ビューはデータを格納せず、各アクセス時に別のテーブルから単に読み込みを行うからです。

以下に、削除された行のフィルターとFINALキーワードを使用して、ClickHouseのデータベース内の各テーブルのビューを作成する例を示します。

その後、PostgreSQLで使用するのと同じクエリを使ってビューをクエリできます。

更新可能なマテリアライズドビュー

別のアプローチとして、更新可能なマテリアライズドビューを使用することができます。これにより、行のデデュプリケーションのためのクエリの実行をスケジュールし、その結果を宛先テーブルに保存できます。各スケジュールされた更新時に、宛先テーブルは最新のクエリ結果に置き換えられます。

この方法の主な利点は、FINALキーワードを使用するクエリが更新時に1回だけ実行され、その後の宛先テーブルに対するクエリでFINALを使用する必要がなくなることです。

ただし、欠点は、宛先テーブルのデータは最も最近の更新時点のものに過ぎないということです。それでも、多くのユースケースでは、数分から数時間の更新間隔が十分であるかもしれません。

その後、deduplicated_postsテーブルを通常通りクエリできます。