メインコンテンツまでスキップ
メインコンテンツまでスキップ

重複排除戦略

重複排除とは、データセットの重複行を削除するプロセスを指します。OLTPデータベースでは、各行に一意の主キーがあるため、これを簡単に行うことができますが、挿入が遅くなるという代償があります。挿入されたすべての行は、まず検索され、もし見つかった場合には置き換えられる必要があります。

ClickHouseはデータ挿入の速度を考慮して構築されています。ストレージファイルは不変であり、ClickHouseは行を挿入する前に既存の主キーをチェックしないため、重複排除には少し余分な労力が必要です。これはまた、重複排除が即時に行われないことを意味します - 最終的に行われるものであり、いくつかの副作用があります：

  • いつでも、テーブルには重複（同じソートキーを持つ行）が存在する可能性があります
  • 重複行の実際の削除はパーツのマージ中に発生します
  • クエリは重複の可能性を考慮する必要があります
ClickHouseは、重複排除やその他多くのトピックに関する無料トレーニングを提供しています。 データの削除と更新のトレーニングモジュールは、始めるのに良い場所です。

重複排除のオプション​

重複排除は、以下のテーブルエンジンを使用してClickHouseで実装されています。

  1. ReplacingMergeTreeテーブルエンジン：このテーブルエンジンでは、同じソートキーを持つ重複行がマージ中に削除されます。ReplacingMergeTreeは、クエリが最後に挿入された行を返すようにしたい場合に、upsertの動作を模倣するのに良い選択です。

  2. 行の崩壊：CollapsingMergeTreeおよびVersionedCollapsingMergeTreeテーブルエンジンは、既存の行が「キャンセル」され、新しい行が挿入されるというロジックを使用します。これらはReplacingMergeTreeよりも実装が複雑ですが、データがまだマージされているかどうかを気にせずに、クエリと集約を簡単に記述できます。これらの2つのテーブルエンジンは、データを頻繁に更新する必要がある場合に便利です。

以下に、これらのテクニックの両方を説明します。詳細については、無料のオンデマンドデータの削除と更新のトレーニングモジュールをチェックしてください。

ReplacingMergeTreeを使用したUpserts​

テーブルがHacker Newsのコメントを含み、viewsカラムがコメントが閲覧された回数を示しているシンプルな例を見てみましょう。記事が公開されたときに新しい行を挿入し、もし値が増加した場合は、毎日合計閲覧数で新しい行をupsertするとします：

2行を挿入しましょう：

viewsカラムを更新するためには、同じ主キーで新しい行を挿入します（viewsカラムの新しい値に注意してください）：

現在、テーブルには4行あります：

出力の上部の別々のボックスは、背後での2つのパーツを示しています - このデータはまだマージされていないため、重複行はまだ削除されていません。クエリ結果の論理的なマージを行うために、SELECTクエリでFINALキーワードを使用しましょう：

結果には2行のみがあり、最後に挿入された行が返されます。

注記

FINALを使用することは少量のデータであれば良好ですが、大量のデータを処理する場合、FINALを使用することはお勧めできません。列の最新値を見つけるためのより良い選択肢を議論しましょう...

FINALの回避​

ユニークな行の両方のviewsカラムを更新しましょう：

現在、テーブルには6行あり、実際のマージはまだ行われておらず（FINALを使用した際のクエリ時間のマージのみ）、

FINALを使用する代わりに、ビジネスロジックを利用しましょう - viewsカラムは常に増加していると知っているので、希望するカラムでグループ化した後、max関数を使用して最大値を持つ行を選択します：

上記のクエリのようにグループ化することは、実際にはFINALキーワードを使用するよりも効率的（クエリ性能の観点から）です。

私たちのデータの削除と更新のトレーニングモジュールでは、この例を拡張し、ReplacingMergeTreeでversionカラムを使用する方法を含めます。

columnsを頻繁に更新するためのCollapsingMergeTreeの使用​

カラムを更新することは、既存の行を削除し、新しい値で置き換えることを含みます。すでに見たように、ClickHouseではこのタイプの変異は 最終的に 発生します - マージの際に。更新する行が多い場合、ALTER TABLE..UPDATEを避けて、既存のデータとともに新しいデータを挿入する方が実際には効率的であることがあります。データが古いか新しいかを示すカラムを追加することができ... 実際には、この動作を非常にうまく実装しているテーブルエンジンがあり、古いデータは自動的に削除されます。どのように機能するか見てみましょう。

外部システムを使用してHacker Newsのコメントの閲覧数を追跡し、数時間ごとにデータをClickHouseにプッシュするとしましょう。古い行を削除し、新しい行が各Hacker Newsのコメントの新しい状態を表すようにしたいと考えています。この動作を実装するためにCollapsingMergeTreeを使用できます。

閲覧数を保存するためのテーブルを定義しましょう：

hackernews_viewsテーブルにはInt8型のsignというカラムがあります。これはsignカラムと呼ばれます。signカラムの名前は任意ですが、Int8データ型が必要であり、signカラムの名前はCollapsingMergeTreeテーブルのコンストラクタに渡されました。

CollapsingMergeTreeテーブルのsignカラムとは何でしょうか？それは行の_状態_ を表し、signカラムは1または-1のみ可能です。動作は次のとおりです：

  • 二つの行が同じ主キー（または、主キーが異なる場合はソート順）を持ち、signカラムの値が異なる場合、+1で挿入された最後の行が状態行となり、他の行が互いにキャンセルされます。
  • 互いにキャンセルされる行はマージの際に削除されます。
  • 対になる行を持たない行は保持されます。

では、hackernews_viewsテーブルに行を追加しましょう。それがこの主キーの唯一の行なので、状態を1に設定します：

次に、viewsカラムを変更したいとします。既存の行をキャンセルする行と、その行の新しい状態を含む行の2行を挿入します：

テーブルには、主キー(123, 'ricardo')で3行があります：

FINALを加えると、現在の状態行が返されます：

しかし、大きなテーブルに対してFINALを使用することは推奨されません。

注記

私たちの例で挿入したviewsカラムの値は実際には必要ありませんし、古い行のviewsの現在の値と一致する必要もありません。実際には、主キーと-1だけで行をキャンセルできます：

複数スレッドからのリアルタイム更新​

CollapsingMergeTreeテーブルでは、行がsignカラムを使って互いにキャンセルされ、行の状態は最後に挿入された行によって決まります。しかし、異なるスレッドから行を挿入している場合、行が順序を無視して挿入される可能性があるため、これは問題になることがあります。「最後の」行を使用することは、この状況では機能しません。

ここでVersionedCollapsingMergeTreeが便利です - これはCollapsingMergeTreeのように行を崩しますが、最後に挿入された行ではなく、指定したバージョンカラムの最大値を持つ行を保持します。

例を見てみましょう。Hacker Newsのコメントの閲覧数を追跡したいとし、データが頻繁に更新されるとます。レポートには、強制的にマージを待つことなく最新の値を使用することを望みます。CollapsedMergeTreeに類似したテーブルから始め、行の状態のバージョンを保存するためのカラムを追加しましょう：

テーブルはVersionedCollapsingMergeTreeをエンジンとして使用し、signカラムとversionカラムを渡しています。テーブルの動作は次の通りです：

  • 同じ主キーとバージョンを持ち、異なるsignを持つ行のペアが削除されます。
  • 行が挿入された順序は重要ではありません。
  • バージョンカラムが主キーの一部でない場合、ClickHouseはそれを暗黙的に主キーの最後のフィールドとして追加します。

クエリを書くときも同様のロジックを使用します - 主キーでグループ化し、キャンセルされているがまだ削除されていない行を避けるための巧妙なロジックを使用します。hackernews_views_vcmtテーブルに行を追加しましょう：

次に、2行を更新し、そのうちの1行を削除します。行をキャンセルするためには、以前のバージョン番号を含めることを確認してください（それも主キーの一部であるため）：

以前のように、signカラムに基づいて値を増加させたり減少させたりするクエリを実行します：

結果は2行です：

テーブルのマージを強制します：

結果には2行だけが表示されるはずです：

VersionedCollapsingMergeTreeテーブルは、複数のクライアントおよび/またはスレッドから行を挿入しながら重複排除を実装したい場合に非常に便利です。

なぜ行が重複排除されないのか？​

挿入された行が重複排除されない理由の一つは、INSERT文に非冪等関数または式を使用している場合です。例えば、createdAt DateTime64(3) DEFAULT now()というカラムを持つ行を挿入している場合、各行にはcreatedAtカラムの一意のデフォルト値があるため、行は確実に一意です。MergeTree / ReplicatedMergeTreeテーブルエンジンは、挿入された各行が一意のチェックサムを生成するため、行を重複排除することはできません。

この場合、同じバッチの行が複数回挿入されても同じ行が再挿入されないように、各バッチごとに独自のinsert_deduplication_tokenを指定できます。この設定の使用方法についての詳細は、insert_deduplication_tokenに関するドキュメントを参照してください。