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

Results

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 original 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 original table to the materialized view.
- The FROM phrase identifies the original 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;

Results

CREATE MATERIALIZED VIEW 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 additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': '100'}
    AND comment = 'Based on table cyclist_base'
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

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;

Results

 age | name             | birthday
-----+------------------+------------
  18 |     Adrien COSTA | 1997-08-19
  18 |      Bram WELTEN | 1997-03-29
  18 | Pascal EENKHOORN | 1997-02-08

(3 rows)

Other materialized views based on the same original 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
);

Results

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

Results

CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday AS
    SELECT birthday, cid, age, country, name
    FROM cycling.cyclist_base
    WHERE birthday IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (birthday, cid)
    WITH CLUSTERING ORDER BY (cid ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

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

Results

 age | name            | birthday
-----+-----------------+------------
  27 | Cristian EGIDIO | 1987-09-04

(1 rows)

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

Results

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

Results

CREATE MATERIALIZED VIEW cycling.cyclist_by_country AS
    SELECT country, cid, age, birthday, name
    FROM cycling.cyclist_base
    WHERE country IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (country, cid)
    WITH CLUSTERING ORDER BY (cid ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

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

Results

 age | name              | birthday
-----+-------------------+------------
  18 |       Bram WELTEN | 1997-03-29
  28 | Steven KRUIKSWIJK | 1987-06-07
  18 |  Pascal EENKHOORN | 1997-02-08

(3 rows)

Create MV restricted to cyclists from the Netherlands

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

Results

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 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 original 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. A WHERE clause restricts the MV to cyclists from the Netherlands. The MV is called cycling.cyclist_by_birthday_netherlands.
```
  CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday_netherlands AS 
    SELECT age, cid, birthday, name, country 
    FROM cycling.cyclist_base
    WHERE birthday IS NOT NULL AND cid IS NOT NULL 
      AND country = 'Netherlands'
    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_netherlands;

Results

CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_netherlands AS
    SELECT birthday, cid, age, country, name
    FROM cycling.cyclist_base
    WHERE birthday IS NOT NULL AND cid IS NOT NULL AND country = 'Netherlands'
    PRIMARY KEY (birthday, cid)
    WITH CLUSTERING ORDER BY (cid ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

Because the new MV is partitioned by birthday, it supports queries based on the cyclists' birthday designations.

  SELECT age, name, birthday FROM cycling.cyclist_by_birthday_netherlands
    WHERE birthday = '1997-02-08';

Results

 age | name             | birthday
-----+------------------+------------
  18 | Pascal EENKHOORN | 1997-02-08

(1 rows)

Create MV based on DATE column birthday and INT column age

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

Results

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 TEXT column, and the age column, a non-primary key INT column as the clustering columns in the primary key. As with any table, the materialized view must specify the primary key columns. Because the original table cyclist_base uses cid as its primary key, cid must be present in the materialized view’s primary key. In the MV, cid is still the partition key, but birthday and age are now clustering columns. The MV is called cycling.cyclist_by_birthday_and_age.
```
  CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday_and_age AS
    SELECT age, cid, birthday, country, name 
    FROM cycling.cyclist_base
    WHERE age IS NOT NULL AND birthday IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (cid, birthday, age);
```

Verify the materialized view by checking the MV schema with the DESCRIBE MATERIALIZED VIEW CQL command.

  DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age;

Results

CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age AS
    SELECT cid, birthday, age, country, name
    FROM cycling.cyclist_base
    WHERE age IS NOT NULL AND birthday IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (cid, birthday, age)
    WITH CLUSTERING ORDER BY (birthday ASC, age ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

Queries that use the new MV can be based on the cyclists' birthdays and ages.

  SELECT age, name, birthday FROM cycling.cyclist_by_birthday_and_age
    WHERE birthday = '1997-02-08' ALLOW FILTERING;

Results

 age | name             | birthday
-----+------------------+------------
  18 | Pascal EENKHOORN | 1997-02-08

(1 rows)

This query fails because the MV is not partitioned by age, but by cid.

Create MV based on DATE column birthday and INT column age WHERE age = 19

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

Results

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 TEXT column, and the age column, a non-primary key INT column as the clustering columns in the primary key. As with any table, the materialized view must specify the primary key columns. Because the original table cyclist_base uses cid as its primary key, cid must be present in the materialized view’s primary key. In the MV, cid is still the partition key, but birthday and age are now clustering columns. The additional WHERE clause ensures that only rows whose birthday and cid columns are non-NULL and age equals the value 19 are added to the materialized view. The MV is called cycling.cyclist_by_birthday_and_age_19.

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

Query the table:

  DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age_19;

The results are as follows:

CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age_19 AS
    SELECT cid, birthday, age, country, name
    FROM cycling.cyclist_base
    WHERE birthday IS NOT NULL AND cid IS NOT NULL AND age = 19
    PRIMARY KEY (cid, birthday, age)
    WITH CLUSTERING ORDER BY (birthday ASC, age ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

Queries that use the new MV can be based on the cyclists' birthday.

  SELECT * FROM cycling.cyclist_by_birthday_and_age_19
    WHERE birthday = '1999-01-04' ALLOW FILTERING;

Results

 cid | birthday | age | country | name
-----+----------+-----+---------+------

(0 rows)

The following materialized view cyclist_by_age_birthday_cid uses the original 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);

Query the table:

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_age_birthday_cid;

The results are as follows:

CREATE MATERIALIZED VIEW cycling.cyclist_by_age_birthday_cid AS
    SELECT age, birthday, cid, 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)
    WITH CLUSTERING ORDER BY (birthday ASC, cid ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

Query using a WHERE clause for two values:

SELECT * FROM cycling.cyclist_by_age_birthday_cid
  WHERE age = 19;

The results are as follows:

 age | birthday | cid | country | name
-----+----------+-----+---------+------

(0 rows)

Notice that clustering columns must still be included in order. This query violates the ordering 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[]

CREATE MATERIALIZED VIEW 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 additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': '100'}
    AND comment = 'Based on table cyclist_base'
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

// end::materialized-view-describe-cyclist-by-age[]
// tag::select-all-by-age[]

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

(10 rows)
// end::select-all-by-age[]
// tag::select-age-18[]

 age | name             | birthday
-----+------------------+------------
  18 |     Adrien COSTA | 1997-08-19
  18 |      Bram WELTEN | 1997-03-29
  18 | Pascal EENKHOORN | 1997-02-08

(3 rows)
// end::select-age-18[]
// tag::materialized-view-create-birthday[]
// end::materialized-view-create-birthday[]
// tag::materialized-view-describe-cyclist-by-birthday[]

CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday AS
    SELECT birthday, cid, age, country, name
    FROM cycling.cyclist_base
    WHERE birthday IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (birthday, cid)
    WITH CLUSTERING ORDER BY (cid ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

// end::materialized-view-describe-cyclist-by-birthday[]
// tag::select-all-by-birthday[]

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

(10 rows)
// end::select-all-by-birthday[]
// tag::select-birthday-1987-09-04[]

 age | name            | birthday
-----+-----------------+------------
  27 | Cristian EGIDIO | 1987-09-04

(1 rows)
// end::select-birthday-1987-09-04[]
// tag::materialized-view-create-country[]
// end::materialized-view-create-country[]
// tag::materialized-view-describe-cyclist-by-country[]

CREATE MATERIALIZED VIEW cycling.cyclist_by_country AS
    SELECT country, cid, age, birthday, name
    FROM cycling.cyclist_base
    WHERE country IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (country, cid)
    WITH CLUSTERING ORDER BY (cid ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

// end::materialized-view-describe-cyclist-by-country[]
// tag::select-all-by-country[]

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

(10 rows)
// end::select-all-by-country[]
// tag::select-country-netherlands[]

 age | name              | birthday
-----+-------------------+------------
  18 |       Bram WELTEN | 1997-03-29
  28 | Steven KRUIKSWIJK | 1987-06-07
  18 |  Pascal EENKHOORN | 1997-02-08

(3 rows)
// end::select-country-netherlands[]
// tag::materialized-view-create-birthday-country-is-netherlands[]
// end::materialized-view-create-birthday-country-is-netherlands[]
// tag::materialized-view-describe-cyclist-by-birthday-country-is-netherlands[]

CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_netherlands AS
    SELECT birthday, cid, age, country, name
    FROM cycling.cyclist_base
    WHERE birthday IS NOT NULL AND cid IS NOT NULL AND country = 'Netherlands'
    PRIMARY KEY (birthday, cid)
    WITH CLUSTERING ORDER BY (cid ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

// end::materialized-view-describe-cyclist-by-birthday-country-is-netherlands[]
// tag::select-all-by-birthday-netherlands[]

 birthday   | cid                                  | age | country     | name
------------+--------------------------------------+-----+-------------+-------------------
 1997-02-08 | ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156 |  18 | Netherlands |  Pascal EENKHOORN
 1997-03-29 | 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503 |  18 | Netherlands |       Bram WELTEN
 1987-06-07 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |  28 | Netherlands | Steven KRUIKSWIJK

(3 rows)
// end::select-all-by-birthday-netherlands[]
// tag::select-by-birthday-netherlands-1997-02-08[]

 age | name             | birthday
-----+------------------+------------
  18 | Pascal EENKHOORN | 1997-02-08

(1 rows)
// end::select-by-birthday-netherlands-1997-02-08[]
// tag::materialized-view-create-birthday-and-age[]
// end::materialized-view-create-birthday-and-age[]
// tag::materialized-view-describe-cyclist-by-birthday-and-age[]

CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age AS
    SELECT cid, birthday, age, country, name
    FROM cycling.cyclist_base
    WHERE age IS NOT NULL AND birthday IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (cid, birthday, age)
    WITH CLUSTERING ORDER BY (birthday ASC, age ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

// end::materialized-view-describe-cyclist-by-birthday-and-age[]
// tag::select-all-by-birthday-and-age[]

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

(10 rows)
// end::select-all-by-birthday-and-age[]
// tag::select-by-birthday-and-age-1997-02-08[]

 age | name             | birthday
-----+------------------+------------
  18 | Pascal EENKHOORN | 1997-02-08

(1 rows)
// end::select-by-birthday-and-age-1997-02-08[]
// tag::materialized-view-create-birthday-and-age-is-19[]
// end::materialized-view-create-birthday-and-age-is-19[]
// tag::materialized-view-describe-cyclist-by-birthday-and-age-19[]

CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age_19 AS
    SELECT cid, birthday, age, country, name
    FROM cycling.cyclist_base
    WHERE birthday IS NOT NULL AND cid IS NOT NULL AND age = 19
    PRIMARY KEY (cid, birthday, age)
    WITH CLUSTERING ORDER BY (birthday ASC, age ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

// end::materialized-view-describe-cyclist-by-birthday-and-age-19[]
// tag::select-all-by-birthday-and-age-is-19[]

 cid                                  | birthday   | age | country   | name
--------------------------------------+------------+-----+-----------+---------------
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 |  19 | Australia | Kanden GROVES

(1 rows)
// end::select-all-by-birthday-and-age-is-19[]
// tag::select-all-by-birthday-1999-01-04-and-age-is-19[]

 cid | birthday | age | country | name
-----+----------+-----+---------+------

(0 rows)
// end::select-all-by-birthday-1999-01-04-and-age-is-19[]
// tag::materialized-view-create-age-birthday-cid[]
// end::materialized-view-create-age-birthday-cid[]
// tag::materialized-view-describe-cyclist-by-age-birthday-cid[]

CREATE MATERIALIZED VIEW cycling.cyclist_by_age_birthday_cid AS
    SELECT age, birthday, cid, 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)
    WITH CLUSTERING ORDER BY (birthday ASC, cid ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

// end::materialized-view-describe-cyclist-by-age-birthday-cid[]
// tag::select-all-by-age-19-birthday-cid[]

 age | birthday | cid | country | name
-----+----------+-----+---------+------

(0 rows)
// end::select-all-by-age-19-birthday-cid[]
// tag::select-all-by-birthday-1998-12-23-age-19-cid[]

 age | birthday | cid | country | name
-----+----------+-----+---------+------

(0 rows)
// end::select-all-by-birthday-1998-12-23-age-19-cid[]
// tag::select-all-by-birthday-1998-12-23-cid[]

 age | birthday | cid | country | name
-----+----------+-----+---------+------

(0 rows)
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[]

CREATE MATERIALIZED VIEW 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 additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.02
    AND caching = {'keys': 'NONE', 'rows_per_partition': '15'}
    AND comment = 'A most excellent and useful view'
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '64', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.DeflateCompressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

// end::describe-materialized-view-after-alters[]
// tag::data-insert-more[]
// end::data-insert-more[]

Use a materialized view to perform queries that aren’t possible on the original table

The following scenario shows how to use a materialized view to perform queries that aren’t possible on the original table unless ALLOW FILTERING is used. ALLOW FILTERING is not recommended because of the performance degradation.

This example 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:

 emp_id | dept_id | age | birthdate  | name
--------+---------+-----+------------+------------
      5 |       3 |  25 | 1996-10-04 | Lisa SMITH
      1 |       1 |  21 | 1992-06-18 | Fred GREEN
      2 |       1 |  22 | 1993-01-15 | John SMITH
      4 |       2 |  24 | 1995-08-19 | Jack JONES
      3 |       2 |  23 | 1994-02-07 |   Jane DOE

(5 rows)

 emp_id | dept_id | age | birthdate  | name
--------+---------+-----+------------+------------
      5 |       3 |  25 | 1996-10-04 | Lisa SMITH
      1 |       1 |  21 | 1992-06-18 | Fred GREEN
      2 |       1 |  22 | 1993-01-15 | John SMITH
      4 |       2 |  24 | 1995-08-19 | Jack JONES
      3 |       2 |  23 | 1994-02-07 |   Jane DOE

(5 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 original table without ALLOW FILTERING. The output from the previous query is as follows:

 age | emp_id | dept_id | birthdate  | name
-----+--------+---------+------------+------------
  21 |      1 |       1 | 1992-06-18 | Fred GREEN

(1 rows)

 age | emp_id | dept_id | birthdate  | name
-----+--------+---------+------------+------------
  21 |      1 |       1 | 1992-06-18 | Fred GREEN

(1 rows)

When another INSERT is executed on the cyclist_base table, the database updates the original 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 original tables, not to materialized views. The database updates a materialized view asynchronously after inserting data into the original table, so the update of materialized view is delayed. A read-repair operation to a materialized view is performed only after updating the original 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

Create MV restricted to cyclists from the Netherlands

Create MV based on DATE column birthday and INT column age

Create MV based on DATE column birthday and INT column age WHERE age = 19

Use a materialized view to perform queries that aren’t possible on the original table

Was this helpful?

Give Feedback