CODEWITHSUNDEEP
databaseclickhouse
Jonathan
Jonathan

Reputation: 2233

Updating data in Clickhouse

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?

Upvotes: 19

Views: 40366

Answers (6)

Amir nazary
Amir nazary

Reputation: 705

To update values you can use the ReplacingMergeTree engine.

Having this engine on a table, if you try to append a row with the sort keys that already exists in the table, it will replace previous record that have the same sort keys (like upsert).

Upvotes: 0

uYSIZfoz
uYSIZfoz

Reputation: 911

UPDATE: This answer is no longer true, look at https://stackoverflow.com/a/55298764/3583139

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.

Upvotes: 19

supernova
supernova

Reputation: 2070

It's an old question, but updates are now supported in Clickhouse. Note it's not recommended to do many small changes for performance reasons. But it is possible.

Syntax:

ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr

Clickhouse UPDATE documentation

Upvotes: 4

Sunil Sunny
Sunil Sunny

Reputation: 571

Through Alter query in clickhouse we can able to delete/update the rows in a table.

For delete: Query should be constructed as 

ALTER TABLE testing.Employee DELETE  WHERE  Emp_Name='user4';

For Update: Query should be constructed as 

ALTER TABLE testing.employee UPDATE AssignedUser='sunil' where AssignedUser='sunny';

Upvotes: 23

Ivan Blinkov
Ivan Blinkov

Reputation: 2554

Functionality to UPDATE or DELETE data has been added in recent ClickHouse releases, but its expensive batch operation which can't be performed too frequently.

See https://clickhouse.yandex/docs/en/query_language/alter/#mutations for more details.

Upvotes: 6

samdoj
samdoj

Reputation: 144

You can drop and create new tables, but depending on their size this might be very time consuming. You could do something like this:

For deletion, something like this could work.

  INSERT INTO tableTemp SELECT * from table1 WHERE  rowID != @targetRowID;
    DROP table1;
    INSERT INTO table1 SELECT * from tableTemp;

Similarly, to update a row, you could first delete it in this manner, and then add it.

Upvotes: 5

Related Questions