Clickhouse : remove duplicate data

user_12169451 0
Viewed 1135

i have a problem with duplicate data in clickhouse. my case is i have records come in parts then i have to group all these parts by text_id. The arrival time of the parts may be at different times

for example :

id,text_id,total_parts,part_number,text
101,11,3,1,How
102,12,2,2,World
103,12,2,1,Hello
104,11,3,3,you
105,11,3,2,are

and the result should be like this :

text_id,text
11, How are you
12, Hello World

i create a view to group all parts and it's working fine. but when i read from this view i want to remove the rows that i already read. I tried to add a column to the table called flag then update this column to 1 then change the view to read flag = 0. but i read in clickhouse docs that update it decrease the performance. and my table has billions of records.

1- the view will be slow if i can't remove the processed records.

2- If there is no performance issue in view i don't want to read the processed data again.

any suggestion?

clickhouse
1 Answers

The closest result you can is arrays of text column:

SELECT groupArray(text) as msg
FROM 
  (SELECT * ROM merge_rows ORDER BY text_id, part_number) 
GRUP BY text_id

┌─msg─────────────────┐
│ ['Hello','World']   │
│ ['How','are','you'] │
└─────────────────────┘

Since you have billions of rows, integrating into materialized views will do it really fast.

user_2347750
Related