Create a materialized view (MV)

Use CREATE MATERIALIZED VIEW to create a materialized view (MV) from a table. A materialized view is a table, just like the table it is based on, but the MV table has a modified primary key definition.

Materialized views are experimental. They aren’t recommended for production use.

Use an existing table or create a table to use for your MV.

This example creates a table named cyclist_base in the cycling keyspace:
```
CREATE TABLE IF NOT EXISTS cycling.cyclist_base (
  cid UUID PRIMARY KEY,
  name text,
  age int,
  birthday date,
  country text
);
```
You cannot create a materialized view from a system table.
Determine how you will modify the primary key in your MV. Note the following limitations:
- You must include all of the original table’s primary key columns in the materialized view’s primary key definition. This is because the definition of row uniqueness must be the same in the base table and all views.
- Only one column can be added to the materialized view’s primary key, and the select column cannot contain null values.
- Static columns aren’t allowed in materialized views.
- Don’t use rows with null values in the materialized view’s primary key column.
- You must create the materialized view in the same keyspace as the original table.
- Filtering can be used to create the materialized view from a subset of the base table’s data. Data excluded by the filter isn’t accessible from the view, and the view must be dropped and recreated if the filter criteria need to be changed.

Create a materialized view from your original table:

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

Create a materialized view (MV)

Was this helpful?

Give Feedback