ClickHouse ReplacingMergeTree — How does it work ?

Replacing merge trees, have the ability to dedupe records in near real time, by using two keys/attributes. One key is the supposed to be unique key or set of keys, and the other key decides the latest version.

An important thing to note here is that ReplacingMergeTree engine does not merge across partitions, therefore when we set the PARTITION BY column, we need to ensure our duplicates are only within the same partition.

Engine = ReplacingMergeTree([version_column])
ORDER BY <list_of_columns>

The catch is that, CH doesn’t dedupe the records immediately. CH dedupes when it merges partitions in the background, and this happens when CH feels like it.

Therefore there is some effort required from our side when writing queries to ensure that the ReplacingMergeTree doesn’t have duplicates when we query

There are three ways to achieve this :

• Use a view on top of the ReplacingMergeTree which uses the FINAL keyword :
  - The issue with FINAL is it loads the entire table to the RAM to be deduped, unless you have a WHERE clause that uses columns from ORDER BY or partitioning, then only the data matching WHERE condition is loaded to RAM and de-duped.
  - This might be risky for really large tables
  - There are a lot of improvement on the CH version > 23.2

CREATE VIEW queryable_view AS SELECT * from db._raw_table FINAL

• Create view using the maxArg Clickhouse keyword :

SELECT id,
argMax(attr_1, received_at) as attr_1,
argMax(attr_2, received_at) as attr_2,
argMax(attr_3, received_at) as attr_3,
argMax(attr_4, received_at) as attr_4
argMax(attr_5, received_at) as attr_5,
max(received_at) FROM db._raw_table GROUP BY id;

This is a work around, where we us the argMax keyword. argMax checks all the records grouped by the certain id, and fetches columns from the record that has the max(recieved_at), where recieved_at decides the latest row, as a result constructing the last record again.

• Create a view with a window query on top unique key of the ReplicateMergeTree table and take the latest record from each window:

CREATE VIEW queryable_view AS SELECT * from (
SELECT * , row_number() over (PARTITION BY id ORDER BY recieved_at DESC) as row_rank from db._raw_table
) where row_rank = 1

Thank you for taking the time to read my article! If you enjoyed it, I would greatly appreciate your support by giving it a thumbs-up or a like.