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 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 keys in the materialized view’s primary key.
- Only one column can be added to the materialized view’s primary key.
- 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.

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