Creating a materialized view
Create materialized views with the CREATE MATERIALIZED VIEW command.
Materialized views are suited for high-cardinality data. The data in a materialized view is arranged serially based on the view's primary key. Materialized views cause hotspots when low-cardinality data is inserted.
Secondary indexes are suited for low-cardinality data. Queries of high-cardinality columns on secondary indexes require the database to access all nodes in a cluster, which causes high read latency.
- Include all of the source table primary keys in the materialized view's primary key.
- Only one column can be added to the materialized view's primary key. Static columns are not allowed.
- Exclude rows with null values in the materialized view primary key column.
- A materialized view cannot be created in a different keyspace from the base table. You also cannot create a materialized view in the system tables.
You can create a materialized view with its own WHERE conditions and its own properties.
Materialized view examples
CREATE TABLE cyclist_mv (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
d1aad83b-be60-47a4-bd6e-069b8da0d97b | 27 | 1987-09-04 | Germany | Johannes HEIDER
862cc51f-00a1-4d5a-976b-a359cab7300e | 20 | 1994-09-04 | Denmark | Joakim BUKDAL
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
cyclist_mv
table can be the basis of a materialized view that uses age in the primary key.CREATE MATERIALIZED VIEW cyclist_by_age
AS SELECT age, birthday, name, country
FROM cyclist_mv
WHERE age IS NOT NULL AND cid IS NOT NULL
PRIMARY KEY (age, cid);
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 source 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. - As with any table, the materialized view must specify the primary key columns. Because the
source table
cyclist_mv
uses cid as its primary key, cid must be present in the materialized view's primary key.Note: In this materialized view, age is used as the primary key and cid is a clustering column.
SELECT age, name, birthday FROM 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
CREATE MATERIALIZED VIEW cyclist_by_birthday
AS SELECT age, birthday, name, country
FROM cyclist_mv
WHERE birthday IS NOT NULL AND cid IS NOT NULL
PRIMARY KEY (birthday, cid);
CREATE MATERIALIZED VIEW cyclist_by_country
AS SELECT age, birthday, name, country
FROM cyclist_mv
WHERE country IS NOT NULL AND cid IS NOT NULL
PRIMARY KEY (country, cid);
SELECT age, name, birthday FROM 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
SELECT age, name, birthday FROM cyclist_by_birthday WHERE birthday = '1987-09-04';
age | name | birthday
-----+-----------------+------------
27 | Cristian EGIDIO | 1987-09-04
27 | Johannes HEIDER | 1987-09-04
CREATE MATERIALIZED VIEW cyclist_by_birthday_Netherlands
AS SELECT age, birthday, name, country
FROM cyclist_mv
WHERE birthday IS NOT NULL AND cid IS NOT NULL
AND country='Netherlands'
PRIMARY KEY (birthday, cid);
WHERE
clause:AND country = 'Netherlands'
SELECT age, name, birthday FROM cyclist_by_birthday WHERE birthday = '1997-02-08';
age | name | birthday | country
-----+------------------+-------------------------
18 | Pascal EENKHOORN | 1997-02-08 | Netherlands
When another INSERT is executed on the cyclist_mv
table, the database updates the source table and both materialized views.
When data is deleted from this table, the database deletes the same data from any related materialized views.
DSE can only write data directly to source tables, not to materialized views. The database updates a materialized view asynchronously after inserting data into the source table, so the update of materialized view is delayed. A read-repair operation to a materialized view is performed only after updating the source table.