Using MATERIALIZED VIEW in Cassandra gives error

0
votes

I have following table.

CREATE TABLE test_x (id text PRIMARY KEY, type frozen<mycustomtype>);

mycustomtype is defined as follows,

CREATE TABLE mycustomtype (
   id uuid PRIMARY KEY,
   name text
 )

And i have created following materialized view for queries based on mycustometype filed.

CREATE MATERIALIZED VIEW test_x_by_mycustomtype_name AS
   SELECT id, type
   FROM test_x
   WHERE type IS NOT NULL
   PRIMARY KEY (id, type)
   WITH CLUSTERING ORDER BY (type ASC)

With above view i hope to execute following query.

select id from test_x_by_mycustomtype_name where type =
{id: a3e64f8f-bd44-4f28-b8d9-6938726e34d4,  name: 'Sample'};

But the query fails saying i need to use 'ALLOW FILTERING'. I created the view not to use ALLOW FILTERING. Why this error is happening here since i have used the part of primary key of the view ?

2
cassandra

2 Answers

2
votes

In you view, the type column is still clustering key. Hence, ALLOW FILTER should be used. You can change the view as per below and retry

 CREATE MATERIALIZED VIEW test_x_by_mycustomtype_name_2 AS
   SELECT id, type
   FROM test_x
   WHERE type IS NOT NULL
   PRIMARY KEY (type, id)
   WITH CLUSTERING ORDER BY (id ASC);

cqlsh:test> select id from test_x_by_mycustomtype_name_2 where type = {id: a3e64f8f-bd44-4f28-b8d9-6938726e34d4,  name: 'Sample'};

id
----
1
votes

Change the order of the primary key of materialized view

CREATE MATERIALIZED VIEW test_x_by_mycustomtype_name AS
   SELECT id, type
   FROM test_x
   WHERE type IS NOT NULL
   PRIMARY KEY (type, id)
   WITH CLUSTERING ORDER BY (type ASC);