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
);
See also: