Deduplication strategies

Let's define a table to store the number of views:

```
CREATE TABLE hackernews_views (
    id UInt32,
    author String,
    views UInt64,
    sign Int8
)
ENGINE = CollapsingMergeTree(sign)
PRIMARY KEY (id, author)
```

Notice the `hackernews_views` table has an `Int8` column named sign which is referred to as the **sign** column. The name of the sign column is arbitrary, but the `Int8` data type is required, and notice the column name was passed in to the constructor of the `CollapsingMergeTree` table.

What is the sign column of a `CollapsingMergeTree` table? It represents the *state* of the row, and the sign column can only be 1 or -1. Here is how it works:

- If two rows have the same primary key (or sort order if that is different than the primary key), but different values of the sign column, then the last row inserted with a +1 becomes the state row and the other rows cancel each other
- Rows that cancel each other out are deleted during merges
- Rows that don't have a matching pair are kept

Let's add a row to the `hackernews_views` table. Since it is the only row for this primary key, we set its state to 1:

```
INSERT INTO hackernews_views VALUES
   (123, 'ricardo', 0, 1)
```

Now suppose we want to change the views column. You insert two rows: one that cancels the existing row, and one that contains the new state of the row:

```
INSERT INTO hackernews_views VALUES
   (123, 'ricardo', 0, -1),
   (123, 'ricardo', 150, 1)
```

The table now has 3 rows with the primary key `(123, 'ricardo')`:

```
SELECT *
FROM hackernews_views
```

```
┌──id─┬─author──┬─views─┬─sign─┐
│ 123 │ ricardo │     0 │   -1 │
│ 123 │ ricardo │   150 │    1 │
└─────┴─────────┴───────┴──────┘
┌──id─┬─author──┬─views─┬─sign─┐
│ 123 │ ricardo │     0 │    1 │
└─────┴─────────┴───────┴──────┘
```

Notice adding `FINAL` returns the current state row:

```
SELECT *
FROM hackernews_views
FINAL
```

```
┌──id─┬─author──┬─views─┬─sign─┐
│ 123 │ ricardo │   150 │    1 │
└─────┴─────────┴───────┴──────┘
```

But of course, using `FINAL` isn't recommended for large tables.

Note

The value passed in for the `views` column in our example isn't really needed, nor does it have to match the current value of `views` of the old row. In fact, you can cancel a row with just the primary key and a -1:

```
INSERT INTO hackernews_views(id, author, sign) VALUES
   (123, 'ricardo', -1)
```

## Real-time updates from multiple threads[​](#real-time-updates-from-multiple-threads "Direct link to Real-time updates from multiple threads")

With a `CollapsingMergeTree` table, rows cancel each other using a sign column, and the state of a row is determined by the last row inserted. But this can be problematic if you're inserting rows from different threads where rows can be inserted out of order. Using the "last" row doesn't work in this situation.

This is where `VersionedCollapsingMergeTree` comes in handy - it collapses rows just like `CollapsingMergeTree`, but instead of keeping the last row inserted, it keeps the row with the highest value of a version column that you specify.

Let's look at an example. Suppose we want to track the number of views of our Hacker News comments, and the data is updated frequently. We want reporting to use the latest values without forcing or waiting for merges. We start with a table similar to `CollapsedMergeTree`, except we add a column to store the version of the state of the row:

```
CREATE TABLE hackernews_views_vcmt (
    id UInt32,
    author String,
    views UInt64,
    sign Int8,
    version UInt32
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
PRIMARY KEY (id, author)
```

Notice the table uses `VersionsedCollapsingMergeTree` as the engine and passes in the **sign column** and a **version column**. Here is the table works:

- It deletes each pair of rows that have the same primary key and version and different sign
- The order that rows were inserted doesn't matter
- Note that if the version column isn't a part of the primary key, ClickHouse adds it to the primary key implicitly as the last field

You use the same type of logic when writing queries - group by the primary key and use clever logic to avoid rows that have been canceled but not deleted yet. Let's add some rows to the `hackernews_views_vcmt` table:

```
INSERT INTO hackernews_views_vcmt VALUES
   (1, 'ricardo', 0, 1, 1),
   (2, 'ch_fan', 0, 1, 1),
   (3, 'kenny', 0, 1, 1)
```

Now we update two of the rows and delete one of them. To cancel a row, be sure to include the prior version number (since it is a part of the primary key):

```
INSERT INTO hackernews_views_vcmt VALUES
   (1, 'ricardo', 0, -1, 1),
   (1, 'ricardo', 50, 1, 2),
   (2, 'ch_fan', 0, -1, 1),
   (3, 'kenny', 0, -1, 1),
   (3, 'kenny', 1000, 1, 2)
```

We will run the same query as before that cleverly adds and subtracts values based on the sign column:

```
SELECT
    id,
    author,
    sum(views * sign)
FROM hackernews_views_vcmt
GROUP BY (id, author)
HAVING sum(sign) > 0
ORDER BY id ASC
```

The result is two rows:

```
┌─id─┬─author──┬─sum(multiply(views, sign))─┐
│  1 │ ricardo │                         50 │
│  3 │ kenny   │                       1000 │
└────┴─────────┴────────────────────────────┘
```

Let's force a table merge:

```
OPTIMIZE TABLE hackernews_views_vcmt
```

There should only be two rows in the result:

```
SELECT *
FROM hackernews_views_vcmt
```

```
┌─id─┬─author──┬─views─┬─sign─┬─version─┐
│  1 │ ricardo │    50 │    1 │       2 │
│  3 │ kenny   │  1000 │    1 │       2 │
└────┴─────────┴───────┴──────┴─────────┘
```

A `VersionedCollapsingMergeTree` table is quite handy when you want to implement deduplication while inserting rows from multiple clients and/or threads.

## Why aren't my rows being deduplicated?[​](#why-arent-my-rows-being-deduplicated "Direct link to Why aren't my rows being deduplicated?")

One reason inserted rows may not be deduplicated is if you're using a non-idempotent function or expression in your `INSERT` statement. For example, if you're inserting rows with the column `createdAt DateTime64(3) DEFAULT now()`, your rows are guaranteed to be unique because each row will have a unique default value for the `createdAt` column. The MergeTree / ReplicatedMergeTree table engine won't know to deduplicate the rows as each inserted row will generate a unique checksum.

In this case, you can specify your own `insert_deduplication_token` for each batch of rows to ensure that multiple inserts of the same batch won't result in the same rows being re-inserted. Please see the [documentation on `insert_deduplication_token`](/docs/operations/settings/settings#insert_deduplication_token) for more details about how to use this setting.

- [Options for deduplication](#options-for-deduplication)- [Using ReplacingMergeTree for Upserts](#using-replacingmergetree-for-upserts)
    - [Avoiding FINAL](#avoiding-final)- [Using CollapsingMergeTree for updating columns frequently](#using-collapsingmergetree-for-updating-columns-frequently)- [Real-time updates from multiple threads](#real-time-updates-from-multiple-threads)- [Why aren't my rows being deduplicated?](#why-arent-my-rows-being-deduplicated)

Was this page helpful?