Updating data in Clickhouse

database clickhouse
Jonathan picture Jonathan · Jun 18, 2016 · Viewed 14.1k times · Source

I went over the documentation for Clickhouse and I did not see the option to UPDATE nor DELETE. It seems to me its an append only system. Is there a possibility to update existing records or is there some workaround like truncating a partition that has records in it that have changed and then re-insering the entire data for that partition?

Answer

uYSIZfoz picture uYSIZfoz · Jun 20, 2016

ClickHouse doesn't support real UPDATE/DELETE. But there are few possible workarounds:

  1. Trying to organize data in a way, that is need not to be updated. You could write log of update events to a table, and then calculate reports from that log. So, instead of updating existing records, you append new records to a table.

  2. Using table engine that do data transformation in background during merges. For example, (rather specific) CollapsingMergeTree table engine: https://clickhouse.yandex/reference_en.html#CollapsingMergeTree Also there are ReplacingMergeTree table engine (not documented yet, you could find example in tests: https://github.com/yandex/ClickHouse/blob/master/dbms/tests/queries/0_stateless/00325_replacing_merge_tree.sql) Drawback is that you don't know, when background merge will be done, and will it ever be done.

Also look at samdoj's answer.

Related questions

Clickhouse Data Import

I created a table in Clickhouse: CREATE TABLE stock ( plant Int32, code Int32, service_level Float32, qty Int32 ) ENGINE = Log there is a data file :~$ head -n 10 /var/rs_mail/IN/qv_stock_20160620035119.csv 2010,646,1.00,13 2010,2486,1.00,19 2010,8178,1.00,10 2010,15707,1.00,4 2010,15708,1.00,10 2010,15718,1.00,4 2010,16951,1.00,8 2010,17615,1.00,13 2010,17616,1.00,4 2010,17617,1.00,8 I am trying to load data: :~$ …

Read More
How to create primary keys in ClickHouse

I did found few examples in the documentation where primary keys are created by passing parameters to ENGINE section. But I did not found any description about any argument to ENGINE, what it means and how do I create a …

Read More
Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (…

Read More