Materialized views (MVs) examples

Materialized views (MVs) are a feature of Cassandra that allows you to create a new table that is a copy of an existing table with a different primary key. Find the type of MV you want to create and follow the examples to create and query the MV.

Each example starts with a table creation, followed by MV creation, so that you can see the table schema and the MV schema. Then sample queries for using the index are presented in a tabbed codeblock, to make it easier to examine queries side-by-side.

A keyspace must be created before tables and MVs can be created. A table must be created before MVs can be created.

The following keyspace is used for all examples on this page:

CREATE KEYSPACE IF NOT EXISTS cycling
WITH REPLICATION = {
  'class' : 'SimpleStrategy',
  'replication_factor' : 1
};

Create MV based on INT column age

Create a table called cycling.cyclist_base with a single primary key on the cid column.

CREATE TABLE IF NOT EXISTS cycling.cyclist_base (
  cid UUID PRIMARY KEY,
  name text,
  age int,
  birthday date,
  country text
);

This table holds values for the name, age, birthday, and country affiliation of several cyclists.

 cid                                  | age | birthday   | country       | name
--------------------------------------+-----+------------+---------------+-------------------
 ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156 |  18 | 1997-02-08 |   Netherlands |  Pascal EENKHOORN
 15a116fc-b833-4da6-ab9a-4a7775752836 |  18 | 1997-08-19 | United States |      Adrien COSTA
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 |  22 | 1993-06-18 |   New Zealand |        Alex FRAME
 c9c9c484-5e4a-4542-8203-8d047a01b8a8 |  27 | 1987-09-04 |        Brazil |   Cristian EGIDIO
 862cc51f-00a1-4d5a-976b-a359cab7300e |  20 | 1994-09-04 |       Denmark |     Joakim BUKDAL
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 |  19 | 1998-12-23 |     Australia |     Kanden GROVES
 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503 |  18 | 1997-03-29 |   Netherlands |       Bram WELTEN
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  38 | 1977-07-08 |         Italy |   Paolo TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |  28 | 1987-06-07 |   Netherlands | Steven KRUIKSWIJK
 95fed1a4-db7e-4c8d-8b2d-f7f9340f2c0b |  20 | 1993-04-09 | United States |        Zam WEATON

(10 rows)

Create an MV using the age column, a non-primary key INT column as the partition key in the primary key. As with any table, the materialized view must specify the primary key columns. Because the base table cyclist_base uses cid as its primary key, cid must be present in the materialized view’s primary key. But cid is now a clustering column, not a partition key. The MV is called cycling.cyclist_by_age.
```
CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_age AS
  SELECT age, cid, birthday, country, name
  FROM cycling.cyclist_base 
  WHERE age IS NOT NULL AND cid IS NOT NULL
  PRIMARY KEY (age, cid)
  WITH CLUSTERING ORDER BY (cid ASC)
  AND caching = {
    'keys' : 'ALL',
    'rows_per_partition' : '100'
  }
  AND comment = 'Based on table cyclist_base';
```
This CREATE MATERIALIZED VIEW statement has several features:
- The AS SELECT phrase identifies the columns copied from the base table to the materialized view.
- The FROM phrase identifies the base table from which data is copied.
- The WHERE clause must include all primary key columns with the IS NOT NULL phrase so that only rows with data for all the primary key columns are copied to the materialized view.
Verify the materialized view by checking the MV schema with the DESCRIBE MATERIALIZED VIEW CQL command.
```
DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_age;
```

Because the new MV is partitioned by age, it supports queries based on the cyclists' ages.
```
SELECT age, name, birthday FROM cycling.cyclist_by_age WHERE age = 18;
```

Other materialized views based on the same base table can organize information by cyclists' birthdays or countries of origin.

Create MV based on DATE column birthday

Create a table called cycling.cyclist_base with a single primary key on the cid column if it does not already exist.

CREATE TABLE IF NOT EXISTS cycling.cyclist_base (
  cid UUID PRIMARY KEY,
  name text,
  age int,
  birthday date,
  country text
);

This table holds values for the name, age, birthday, and country affiliation of several cyclists.

 cid                                  | age | birthday   | country       | name
--------------------------------------+-----+------------+---------------+-------------------
 ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156 |  18 | 1997-02-08 |   Netherlands |  Pascal EENKHOORN
 15a116fc-b833-4da6-ab9a-4a7775752836 |  18 | 1997-08-19 | United States |      Adrien COSTA
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 |  22 | 1993-06-18 |   New Zealand |        Alex FRAME
 c9c9c484-5e4a-4542-8203-8d047a01b8a8 |  27 | 1987-09-04 |        Brazil |   Cristian EGIDIO
 862cc51f-00a1-4d5a-976b-a359cab7300e |  20 | 1994-09-04 |       Denmark |     Joakim BUKDAL
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 |  19 | 1998-12-23 |     Australia |     Kanden GROVES
 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503 |  18 | 1997-03-29 |   Netherlands |       Bram WELTEN
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  38 | 1977-07-08 |         Italy |   Paolo TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |  28 | 1987-06-07 |   Netherlands | Steven KRUIKSWIJK
 95fed1a4-db7e-4c8d-8b2d-f7f9340f2c0b |  20 | 1993-04-09 | United States |        Zam WEATON

(10 rows)

Create an MV using the birthday column, a non-primary key DATE column as the partition key in the primary key. As with any table, the materialized view must specify the primary key columns. Because the base table cyclist_base uses cid as its primary key, cid must be present in the materialized view’s primary key. But cid is now a clustering column, not a partition key. The MV is called cycling.cyclist_by_birthday.
```
CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday 
  AS SELECT age, cid, birthday, name, country 
  FROM cycling.cyclist_base
  WHERE birthday IS NOT NULL AND cid IS NOT NULL
  PRIMARY KEY (birthday, cid);
```
Verify the materialized view by checking the MV schema with the DESCRIBE MATERIALIZED VIEW CQL command.
```
DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_birthday;
```

Because the new MV is partitioned by birthday, it supports queries based on the cyclists' birthdays.
```
SELECT age, name, birthday FROM cycling.cyclist_by_birthday
  WHERE birthday = '1987-09-04';
```

Create MV based on TEXT column country

Create a table called cycling.cyclist_base with a single primary key on the cid column if it does not already exist.

CREATE TABLE IF NOT EXISTS cycling.cyclist_base (
  cid UUID PRIMARY KEY,
  name text,
  age int,
  birthday date,
  country text
);

This table holds values for the name, age, birthday, and country affiliation of several cyclists.

 cid                                  | age | birthday   | country       | name
--------------------------------------+-----+------------+---------------+-------------------
 ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156 |  18 | 1997-02-08 |   Netherlands |  Pascal EENKHOORN
 15a116fc-b833-4da6-ab9a-4a7775752836 |  18 | 1997-08-19 | United States |      Adrien COSTA
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 |  22 | 1993-06-18 |   New Zealand |        Alex FRAME
 c9c9c484-5e4a-4542-8203-8d047a01b8a8 |  27 | 1987-09-04 |        Brazil |   Cristian EGIDIO
 862cc51f-00a1-4d5a-976b-a359cab7300e |  20 | 1994-09-04 |       Denmark |     Joakim BUKDAL
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 |  19 | 1998-12-23 |     Australia |     Kanden GROVES
 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503 |  18 | 1997-03-29 |   Netherlands |       Bram WELTEN
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  38 | 1977-07-08 |         Italy |   Paolo TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |  28 | 1987-06-07 |   Netherlands | Steven KRUIKSWIJK
 95fed1a4-db7e-4c8d-8b2d-f7f9340f2c0b |  20 | 1993-04-09 | United States |        Zam WEATON

(10 rows)

Create an MV using the country column, a non-primary key TEXT column as the partition key in the primary key. As with any table, the materialized view must specify the primary key columns. Because the base table cyclist_base uses cid as its primary key, cid must be present in the materialized view’s primary key. But cid is now a clustering column, not a partition key. The MV is called cycling.cyclist_by_country.
```
CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_country 
  AS SELECT age, cid, birthday, name, country 
  FROM cycling.cyclist_base
  WHERE country IS NOT NULL AND cid IS NOT NULL
  PRIMARY KEY (country, cid);
```
Verify the materialized view by checking the MV schema with the DESCRIBE MATERIALIZED VIEW CQL command.
```
DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_country;
```

Because the new MV is partitioned by country, it supports queries based on the cyclists' country designations.
```
SELECT age, name, birthday FROM cycling.cyclist_by_country
  WHERE country = 'Netherlands';
```

The following materialized view cyclist_by_age_birthday_cid uses the base table cyclist_base_ext. The WHERE clause ensures that only rows whose age, birthday, and cid columns are non-NULL are added to the materialized view.

CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_age_birthday_cid AS
  SELECT age, cid, birthday, country, name FROM cycling.cyclist_base_ext
  WHERE age IS NOT NULL
    AND birthday IS NOT NULL
    AND cid IS NOT NULL
  PRIMARY KEY (age, birthday, cid);

The results of this query:

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_age_birthday_cid;

are:

The results of this query using a WHERE clause for two values:

SELECT * FROM cycling.cyclist_by_age_birthday_cid
  WHERE age = 19;

are:

Notice that clustering columns must still be included in order. This query violates the rule:

SELECT * FROM cycling.cyclist_by_age_birthday_cid
  WHERE age = 19 AND birthday = '1998-12-23' ALLOW FILTERING;

Results

// tag::materialized-view-drop[]
// end::materialized-view-drop[]
// tag::materialized-view-drop-other[]
// end::materialized-view-drop-other[]
// tag::materialized-view-create-age[]
// end::materialized-view-create-age[]
// tag::materialized-view-describe-cyclist-by-age[]
// end::materialized-view-describe-cyclist-by-age[]
// tag::select-all-by-age[]
// end::select-all-by-age[]
// tag::select-age-18[]
// end::select-age-18[]
// tag::materialized-view-create-birthday[]
// end::materialized-view-create-birthday[]
// tag::materialized-view-describe-cyclist-by-birthday[]
// end::materialized-view-describe-cyclist-by-birthday[]
// tag::select-all-by-birthday[]
// end::select-all-by-birthday[]
// tag::select-birthday-1987-09-04[]
// end::select-birthday-1987-09-04[]
// tag::materialized-view-create-country[]
// end::materialized-view-create-country[]
// tag::materialized-view-describe-cyclist-by-country[]
// end::materialized-view-describe-cyclist-by-country[]
// tag::select-all-by-country[]
// end::select-all-by-country[]
// tag::select-country-netherlands[]
// end::select-country-netherlands[]
// tag::materialized-view-create-age-birthday-cid[]
// end::materialized-view-create-age-birthday-cid[]
// tag::materialized-view-describe-cyclist-by-age-birthday-cid[]
// end::materialized-view-describe-cyclist-by-age-birthday-cid[]
// tag::select-all-by-age-19-birthday-cid[]
// end::select-all-by-age-19-birthday-cid[]
// tag::select-all-by-birthday-1998-12-23-age-19-cid[]
// end::select-all-by-birthday-1998-12-23-age-19-cid[]
// tag::select-all-by-birthday-1998-12-23-cid[]
INTENTIONAL ERROR
// end::select-all-by-birthday-1998-12-23-cid[]
// tag::table-alter-comment[]
// end::table-alter-comment[]
// tag::table-alter-compression[]
// end::table-alter-compression[]
// tag::table-alter-cache[]
// end::table-alter-cache[]
// tag::describe-materialized-view-after-alters[]
// end::describe-materialized-view-after-alters[]
// tag::data-insert-more[]
// end::data-insert-more[]

Using a materialized view to perform queries that are not possible on a base table

The following scenario shows how to use a materialized view to perform queries that are not possible on a base table unless ALLOW FILTERING is used. ALLOW FILTERING is not recommended because of the performance degradation. This table stores the cycling team mechanic information:

CREATE TABLE IF NOT EXISTS cycling.mechanic (
  emp_id int,
  dept_id int,
  name text,
  age int,
  birthdate date,
  PRIMARY KEY (emp_id, dept_id)
);

The table contains these rows:

This materialized view selects the columns from the previous table and contains a different primary key from the table:

CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.mechanic_view AS
  SELECT emp_id, dept_id, name, age, birthdate FROM cycling.mechanic
  WHERE emp_id IS NOT NULL
    AND dept_id IS NOT NULL
    AND name IS NOT NULL
    AND age IS NOT NULL
    AND birthdate IS NOT NULL
  PRIMARY KEY (age, emp_id, dept_id);

This query retrieves the rows where the age is 21:

SELECT * FROM cycling.mechanic_view WHERE age = 21;

The previous query cannot be run on the base table without ALLOW FILTERING. The output from the previous query is as follows:

When another INSERT is executed on the cyclist_base table, the database updates the base table and any associated materialized views. When data is deleted from this table, the database deletes the same data from any associated materialized views.

CQL can only write data directly to base tables, not to materialized views. The database updates a materialized view asynchronously after inserting data into the base table, so the update of materialized view is delayed. A read-repair operation to a materialized view is performed only after updating the base table.

Materialized views (MVs) examples

Create MV based on INT column age

Create MV based on DATE column birthday

Create MV based on TEXT column country

Using a materialized view to perform queries that are not possible on a base table

Was this helpful?

Give Feedback