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 thecid
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 tablecyclist_base
usescid
as its primary key,cid
must be present in the materialized view’s primary key. Butcid
is now a clustering column, not a partition key. The MV is calledcycling.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 theIS 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 thecid
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 tablecyclist_base
usescid
as its primary key,cid
must be present in the materialized view’s primary key. Butcid
is now a clustering column, not a partition key. The MV is calledcycling.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 thecid
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 tablecyclist_base
usescid
as its primary key,cid
must be present in the materialized view’s primary key. Butcid
is now a clustering column, not a partition key. The MV is calledcycling.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 thecid
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 tablecyclist_base
usescid
as its primary key,cid
must be present in the materialized view’s primary key. Butcid
is now a clustering column, not a partition key. AWHERE
clause restricts the MV to cyclists from the Netherlands. The MV is calledcycling.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 thecid
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 theage
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 tablecyclist_base
usescid
as its primary key,cid
must be present in the materialized view’s primary key. In the MV,cid
is still the partition key, butbirthday
andage
are now clustering columns. The MV is calledcycling.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 bycid
.
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 thecid
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 theage
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 tablecyclist_base
usescid
as its primary key,cid
must be present in the materialized view’s primary key. In the MV,cid
is still the partition key, butbirthday
andage
are now clustering columns. The additionalWHERE
clause ensures that only rows whosebirthday
andcid
columns are non-NULL andage
equals the value19
are added to the materialized view. The MV is calledcycling.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.