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

  1. 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)

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

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

    DESCRIBE MATERIALIZED VIEW cycling.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';

  1. 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;
 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 base table can organize information by cyclists' birthdays or countries of origin.

Create MV based on DATE column birthday

  1. 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)

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

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

    DESCRIBE MATERIALIZED VIEW cycling.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';

  1. 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';
 age | name            | birthday
-----+-----------------+------------
  27 | Cristian EGIDIO | 1987-09-04

(1 rows)

Create MV based on TEXT column country

  1. 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)

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

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

    DESCRIBE MATERIALIZED VIEW cycling.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';

  1. 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';
 age | name              | birthday
-----+-------------------+------------
  18 |       Bram WELTEN | 1997-03-29
  28 | Steven KRUIKSWIJK | 1987-06-07
  18 |  Pascal EENKHOORN | 1997-02-08

(3 rows)

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:

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

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

SELECT * FROM cycling.cyclist_by_age_birthday_cid
  WHERE age = 19;

are:

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

(0 rows)

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

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

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:

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

-

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.

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com