How to create a materialized view in Cassandra to filter based on part of a user defined type

Question

I have a table with columns (id as primary key, myudt) where myudt is a user defined type. Now I want to do a query based on part of myudt. Based on following discussion it seems one way is to use materialized view but how? Can someone give an example?

how to filter cassandra query by a field in user defined type

When I try something like below it fails:

CREATE MATERIALIZED VIEW my_view AS SELECT myud.fname FROM source_table WHERE id IS NOT NULL AND myudt IS NOT NULL AND myudt.fname IS NOT NULL PRIMARY KEY (myudt.fname, id);

The error I get in cqlsh is:

ErrorMessage code=2000 [Syntax error in CQL query] message="line 7:28 mismatched input '.' expecting ')' (...NOT NULL PRIMARY KEY (myudt[.]fname...)"

Answer 1

The materialized view feature is being retroactively classified as experimental, and not recommended for new production uses, https://www.mail-archive.com/[email protected]/msg54073.html. So its better to stay away from them.

Searching on a portion of the UDT, defeats the purpose of having them combined in the first place. Design data models based on the queries it has to serve and not vice-versa. Its better to duplicate data by creating another table to serve the queries based on the columns of UDT that you care about.