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