Dedupe data using ReplacingMergeTree()

[gane5hvarma]

Hi, I wanted to dedupe data using replacingMergeTree. One issue i'm facing is that i have two columns event_received_at(datetime) and event_id(string). I want to dedupe data by event_id, but sorting should be by event_received_at.

According to the docs replacingMergeTree dedupes based on sorting key for me it is event_received_at, but there can be two different events which has the same event_received_at.
https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/replacingmergetree/

So is it possible to dedupe by event_id and sort by event_received_at using replacingMergeTree
or there is another mergeTree that could achieve this functionality?

tried below example:

CREATE TABLE IF NOT EXISTS test.app
(

    event_id     String,
    event_received_at   DateTime
) ENGINE = ReplacingMergeTree() order by (event_id, event_received_at) primary key event_id;


INSERT INTO test.app FORMAT CSV
abc, 2019-12-23 14:36:31
abc, 2020-12-23 14:36:31
def, 2019-12-23 14:36:31


select * from test.app final;
output:
abc, 2019-12-23 14:36:31
abc, 2020-12-23 14:36:31
def, 2019-12-23 14:36:31

expecting output to be like this

abc, 2020-12-23 14:36:31
def, 2019-12-23 14:36:31

if its not possible by replacingMergeTree is there another mergeTree which could achieve the above functionality

[den-crane]

All engines use order by table section to do sorting data on disk and collapsing rows during merges.

primary key section sets a set of columns which will be used in table primary index (it should be prefix of order by)

Basically it's impossible by any engine collapse rows by id and store them in order of event_received_at.

the best you can do:

drop table test.app

CREATE TABLE IF NOT EXISTS test.app
( event_id String,
event_received_at DateTime
) ENGINE = ReplacingMergeTree() order by (event_id)

INSERT INTO test.app values ('abc','2019-12-23 14:36:31'),('abc','2020-12-23 14:36:31'),('def','2019-12-23 14:36:31')

select * from test.app final order by event_received_at desc;

┌─event_id─┬───event_received_at─┐
│ abc      │ 2020-12-23 14:36:31 │
└──────────┴─────────────────────┘
┌─event_id─┬───event_received_at─┐
│ def      │ 2019-12-23 14:36:31 │
└──────────┴─────────────────────┘

[gane5hvarma]

@den-crane got it, but querying by event_received_at will decrease the query performance right if its not a primary key.

[den-crane]

@gane5hvarma you don't have a choice. No engine to fulfill your need.