Clickhouse: ALTER TABLE ... UPDATE

[allyraza]

Hi, is it possible to update records in a table?

[hatarist]

Kind of. There's no UPDATE statements. There are several choices though

1) CollapsingMergeTree or ReplacingMergeTree (this one isn't documented, see an example in the sources).
There are some drawbacks (like you have to use GROUP BY / FINAL keywords; worse performance)

2) temporary tables / table renaming. Create a new table, insert the updated data to it (insert the unchanged one too), drop the old table, rename the new table to the name of the old one.
You should make sure that no data is inserted in between (basically, create a lock on your side of the application).
It's alright and is pretty doable if there isn't much data.

Perhaps, there are other choices, but I can recall just these two, for now. :)

There are also some discussions at Google Groups.

[allyraza]

I see but we already have several billion records we can't simply move data in out of tables affects our production system, all this time our operations were append only now we need to update to cater for new feature, do you guys have any plans to implement update and delete operation/queries.

[alexey-milovidov]

We have plans for delete. (Currently without schedule, I expect in Q2 2017)

[goranc]

Just idea for possible update/delete option with MergeTree family tables.
Delete can be covered using ReplacingMergeTree with functionality of version field of int type, just to allow using of -1 for deletion on merge. When merge is triggered then just skip record from main part when there is new part with -1 version.

[kszucs]

@alexey-milovidov is Q2 2017 still actual?

[alexey-milovidov]

This estimate is outdated :( Now we expect it not sooner than Q4 2017.

[shahbour]

@alexey-milovidov is it still on road map the delete

[quba]

@alexey-milovidov any update on this? GDPR is coming.

[meshpaul]

Is there any update on this feature... is this 2018 or will it make it in 2017 Q4.. We are looking for limited capability example would be a look up table but to update just the record without drop, rename whole table data.

[alexey-milovidov]

In first step, we will implement "batch updates", probably under a special syntax like "ALTER ... UPDATE/DELETE", that will perform updates rather suboptimal but without any sacrifice of SELECT performance.

In second step, we will apply some techniques to support short-running updates with little (if any) impact on SELECT performance.

This is the first task in a queue for one of developers.

[meshpaul]

@alexey-milovidov класс спасибо

[menvil]

@alexey-milovidov Отличная новость. Давайте очень нужно.

[sartor]

Is there any news for updates/deletes?

[blackush]

@alexey-milovidov awesome product!
there is any chance for update / delete in the near future ?

[AlekSi]

See https://github.com/yandex/ClickHouse/pull/2366 for initial DELETE implementation.

[blinkov]

FYI: this feature is currently on code review at #3035

[simPod]

I have a question. Shouldn't the UPDATE and DELETE propagate to materialized views as well?

[ztlpn]

I have a question. Shouldn't the UPDATE and DELETE propagate to materialized views as well?

Well in theory that would be nice, but I don't see how this could be realized in practice. Materialized views often store aggregated data and deleting/modifying data inside aggregate function states is in general impossible.

Materialized views are INSERT triggers that have been misnamed.

[ztlpn]

ALTER UPDATE feature has been released in v18.12.14.

[simPod]

I see, you're right. Thanks!

On Mon, Oct 8, 2018, 21:25 Alex Zatelepin [e-mail] wrote:

Closed #167 https://github.com/yandex/ClickHouse/issues/167.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/yandex/ClickHouse/issues/167#event-1891115785, or mute
the thread
https://github.com/notifications/unsubscribe-auth/AAUAJd9WkFOUaI-Nqa2r9bI-oEgnRzQ4ks5ui6ajgaJpZM4KtdK1
.

[charmingcode]

ALTER UPDATE feature has been released in v18.12.14.

why do not master contain the code of ApplyingMutationsBlockInputStream?

[ztlpn]

why do not master contain the code of ApplyingMutationsBlockInputStream?

ApplyingMutationsBlockInputStream was removed. Look into dbms/src/Interpreters/MutationsInterpreter.h/.cpp for the relevant logic.