CODEWITHSUNDEEP
cassandra
Viraj
Viraj

Reputation: 5381

Using MATERIALIZED VIEW in Cassandra gives error

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 ?

Upvotes: 0

Views: 143

Answers (2)

Ashraful Islam
Ashraful Islam

Reputation: 12830

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);

Upvotes: 1

sayboras
sayboras

Reputation: 5165

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
----

Upvotes: 2

Related Questions