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.

Restrictions for materialized views:
  • 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

The following table is the original, or source, table for the materialized view examples.
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
 410919ef-bd1b-4efa-8256-b0fd8ab67029 |  19 | 1999-01-04 |    Uzbekistan | Iskandarbek SHODIEV
 d1aad83b-be60-47a4-bd6e-069b8da0d97b |  27 | 1987-09-04 |       Germany |     Johannes HEIDER
 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
 96c4c40d-58c8-4710-b73f-681e9b1f70ae |  29 | 1989-04-20 |     Australia |     Benjamin DYBALL

(12 rows)
The cyclist_mv table can be the basis of a materialized view that uses age in the primary key.
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';
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 the IS 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.
Because the new materialized view 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 source table can organize information by cyclists' birthdays or countries of origin.
CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday 
  AS SELECT age, birthday, name, country 
  FROM cycling.cyclist_base
  WHERE birthday IS NOT NULL
    AND cid IS NOT NULL
  PRIMARY KEY (birthday, cid);
CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_country 
  AS SELECT age, birthday, name, country 
  FROM cycling.cyclist_base
  WHERE country IS NOT NULL
    AND cid IS NOT NULL
  PRIMARY KEY (country, cid);

The following queries use the new materialized views.

SELECT age, name, birthday
FROM cycling.cyclist_by_birthday
WHERE birthday = '1987-09-04';
 age | name            | birthday
-----+-----------------+------------
  27 | Cristian EGIDIO | 1987-09-04
  27 | Johannes HEIDER | 1987-09-04

(2 rows)
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)
A materialized view can be created using a filtering statement that includes a restriction on a non-primary key column.
CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday_Netherlands 
  AS SELECT age, birthday, name, country 
  FROM cycling.cyclist_base
  WHERE birthday IS NOT NULL
    AND cid IS NOT NULL
    AND country = 'Netherlands'
  PRIMARY KEY (birthday, cid);
This materialized view only stores information for cyclists from the Netherlands because of the following addition to the WHERE clause:
AND country = 'Netherlands'
Now a query can be submitted to find those cyclists from the Netherlands with a particular birthday:
SELECT age, name, birthday
FROM cycling.cyclist_by_birthday_Netherlands
WHERE birthday = '1997-02-08';
 age | name             | birthday
-----+------------------+------------
  18 | Pascal EENKHOORN | 1997-02-08

(1 rows)

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.

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