Materialised Views

Reference link

Original table

CREATE TABLE cyclist_mv (cid UUID PRIMARY KEY, name text, age int, birthday date, country text);

Creating a materialised view

age is used as the Primary Key

CREATE MATERIALIZED VIEW cyclist_by_age 
AS SELECT age, birthday, name, country 
FROM cyclist_mv 
WHERE age IS NOT NULL AND cid IS NOT NULL 
PRIMARY KEY (age, cid);

Inserting into table

insert into cyclist_mv (cid, name, age, birthday, country)values(c37d661d-7e61-49ea-96a5-68c34e83db3a,'Saket Haux',25,'1996-12-19','India');

Output

cqlsh:admatic> SELECT age, name, birthday FROM cyclist_by_age WHERE age = 25;

 age | name       | birthday
-----+------------+------------
  25 | Saket Haux | 1996-12-19

(1 rows)
cqlsh:admatic>

Other examples of materialised views

CREATE MATERIALIZED VIEW cyclist_by_birthday 
AS SELECT age, birthday, name, country 
FROM cyclist_mv 
WHERE birthday IS NOT NULL AND cid IS NOT NULL
PRIMARY KEY (birthday, cid);

CREATE MATERIALIZED VIEW cyclist_by_country 
AS SELECT age, birthday, name, country 
FROM cyclist_mv 
WHERE country IS NOT NULL AND cid IS NOT NULL
PRIMARY KEY (country, cid);

• any update of the original table will be reflected onto the materialised view