Execution of ALTER queries on materialized views

[bgaifullin]

Currently execution of ALTER queries on materialized views has not been fully developed.
(according to documentation)

It will be very convenient if materialized view supports:

• ALTER commands which are supported by underlaying table (with the same limitations).
• ALTER to modify the query, which is used to transform data.

[arctica]

This feature would be very useful. I learned (via google groups search result) that the best way right now is:

1. DETACH view
2. ALTER the .inner. table of the view
3. ATTACH the view with the updated table definition and select query

These steps are not atomic and if 2. or 3. fail, then you might have a problem. It's also not trivial to block all running clients from accessing the relevant tables while this is happening.

A full fletched ALTER on materialized views would solve these issues.

[nvartolomei]

@alexey-milovidov do you have a preference for alter materialized view query syntax? I might give it a try.

[gelin]

ATTACH MATERIALIZED VIEW is not documented. Both here and here. I cannot guess the correct syntax :( And it looks like for Ordinary and Atomic database engines this syntax also differs.
These instructions doesn't work with ClickHouse 21.12.

The same issue is described here.

[gelin]

The instructions work well on Ordinary database. But with Atomic database the syntax of ATTACH MATERIALIZED VIEW differs and is unknown.

[SaltTan]

Always create materialized views using the TO syntax (requires an existing storage table).
It is much easier to manage, especially in Atomic databases.
You can convert an implicit table to an explicit one using these steps (tested on Ordinary databases):

detach table <materialized_view_name>;
rename table ".inner.<materialized_view_name>" to <storage_table>;
attach table <materialized_view_name>;
drop table <materialized_view_name>;
CREATE MATERIALIZED VIEW <materialized_view_name> to <storage_table>...