Clickhouse: [Question]How about UPDATE&DELETE now?

[zlzforever]

I saw the roadmap: Q1 2018 implement UPDATE&DELETE. Any updates?

[lwolters]

Yeah, that is also something we would like to know.... Keep on going the great work!

[rbrondgeest]

+1

[blinkov]

ALTER DELETEs are almost ready, there has been a working demo on last ClickHouse meetup in Moscow few weeks ago.
ALTER UPDATEs are coming straight after, using the same approach.

The roadmap is kind of outdated at the moment, we'll deal with that soon.

[salisbury-espinosa]

zlzforever
https://github.com/yandex/ClickHouse/blob/master/dbms/src/Parsers/ASTAlterQuery.cpp#L170

else if (p.type == ASTAlterQuery::DELETE)
        {
            settings.ostr << (settings.hilite ? hilite_keyword : "") << indent_str << "DELETE WHERE " << (settings.hilite ? hilite_none : "");
            p.predicate->formatImpl(settings, state, frame);
        }

https://github.com/yandex/ClickHouse/blob/c888903488fa995245dcbe8db63c261343ac1ffb/dbms/src/Parsers/ASTAlterQuery.h#L66

so alter deletes are ready and was released in https://github.com/yandex/ClickHouse/releases/tag/v1.1.54386-testing (4 days ago)

at the time of your question this has not happened yet ;)

@blinkov by the way, why the code responsible for removing of rows is in AlterQuery (ALTER DELETE vs DELETE)

[yjygit]

+1

[tonylauCN]

it's great work，thks！

i saw DELETE was released in

https://github.com/yandex/ClickHouse/releases/tag/v1.1.54386-testing

if you give me some tips how to deal with that?

 -------------------------------------
 ALTER TABLE ontime_all
 DELETE WHERE Year = 1987

 Received exception from server (version 1.1.54386):
 Code: 48. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Mutations are not 
 supported by storage Distributed. 
 -------------------------------------

[vitalvas]

Is it possible to add this functionality to the "MergeTree" engine?

ALTER TABLE log
    DELETE WHERE ClientIPv4 = 0

Received exception from server (version 1.1.54388):
Code: 48. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: 
    Mutations are not supported by storage MergeTree.

[blinkov]

by the way, why the code responsible for removing of rows is in AlterQuery (ALTER DELETE vs DELETE)

To make it clear that those are not cheap operations that can be done with high rate (unlike normal DELETEs in RDBMS).

if you give me some tips how to deal with that?

Point ALTER towards local tables, not the distributed one.

Is it possible to add this functionality to the "MergeTree" engine?

See #2634

[blinkov]

I'm grouping up issues about updates in #167