Drop column(s) on a table that has a materialized view

Question

It seems that it's not possible to drop a column from a table when there is a defined materialized view on this table. For example, let's assume that we have this table:

> CREATE TABLE healthera.users (
>   user_id timeuuid PRIMARY KEY,
>   address text,
>   birthday int,
>   forename text,
>   user_password text,
>   username text
> );

and we define the materialized view below:

> CREATE MATERIALIZED VIEW users_by_username AS
>   SELECT * FROM users
>   WHERE user_id IS NOT NULL AND username IS NOT NULL
>   PRIMARY KEY (username, user_id);

Then we alter the users table and we add a column:

> ALTER TABLE users ADD last_name text;

When we try to drop this column or any other column(s) from the users table then we get back this error:

> ALTER TABLE users DROP last_name ;
> InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot drop column last_name, depended on by materialized views (healthera.{users_by_username})"

or

> ALTER TABLE users DROP forename ;
> InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot drop column forename, depended on by materialized views (healthera.{users_by_username})"

Is this something expected? How should we handle this? Do we need to drop the materialized view, drop the column(s) and then recreate the materialized view again? If yes, how expensive is this for cassandra?

Answer 1

You can add a column to the base table of a MV, but you cannot drop a column even if it is not part of the PK.

http://www.doanduyhai.com/blog/?p=1930

When creating a MV for an existing table w/data, it will kick off a building process to populate the MV in the background.

You can check the status -

SELECT * FROM system.views_builds_in_progress;
SELECT * FROM system.built_views;