Create a materialized view (MV)

Create a materialized view from a table. The table used to create a materialized view will be called the base table in the discussion below. A materialized view is a table, just like the base table, but with a new primary key defined.

Materialized views are experimental, and not recommended for production use.

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 in materialized views.
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.
A materialized view cannot be created on a system table.

Prerequisites

To create a materialized view:

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

using the base table:

CREATE TABLE IF NOT EXISTS cycling.cyclist_base (
  cid UUID PRIMARY KEY,
  name text,
  age int,
  birthday date,
  country text
);

Create a materialized view (MV)

Prerequisites

Was this helpful?

Give Feedback