--- layout: docpage title: "Documentation" is_homepage: false is_sphinx_doc: true doc-parent: "The Cassandra Query Language (CQL)" doc-title: "Materialized Views" doc-header-links: ' ' doc-search-path: "../search.html" extra-footer: ' ' ---
Materialized views names are defined by:
view_name ::= re('[a-zA-Z_0-9]+')
You can create a materialized view on a table using a CREATE MATERIALIZED VIEW
statement:
create_materialized_view_statement ::= CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]view_name
ASselect_statement
PRIMARY KEY '('primary_key
')' WITHtable_options
For instance:
CREATE MATERIALIZED VIEW monkeySpecies_by_population AS
SELECT * FROM monkeySpecies
WHERE population IS NOT NULL AND species IS NOT NULL
PRIMARY KEY (population, species)
WITH comment='Allow query by population instead of species';
The CREATE MATERIALIZED VIEW
statement creates a new materialized view. Each such view is a set of rows which
corresponds to rows which are present in the underlying, or base, table specified in the SELECT
statement. A
materialized view cannot be directly updated, but updates to the base table will cause corresponding updates in the
view.
Creating a materialized view has 3 main parts:
Attempting to create an already existing materialized view will return an error unless the IF NOT EXISTS
option is
used. If it is used, the statement will be a no-op if the materialized view already exists.
The select statement of a materialized view creation defines which of the base table is included in the view. That statement is limited in a number of ways:
SELECT *
isn’t allowed if the base table has static columns).WHERE
clause have the following restrictions:bind_marker
.IS NOT NULL
restriction. No other restriction is allowed.IS NOT NULL
restriction (or any other restriction, but they must have one).A view must have a primary key and that primary key must conform to the following restrictions:
So for instance, give the following base table definition:
CREATE TABLE t (
k int,
c1 int,
c2 int,
v1 int,
v2 int,
PRIMARY KEY (k, c1, c2)
)
then the following view definitions are allowed:
CREATE MATERIALIZED VIEW mv1 AS
SELECT * FROM t WHERE k IS NOT NULL AND c1 IS NOT NULL AND c2 IS NOT NULL
PRIMARY KEY (c1, k, c2)
CREATE MATERIALIZED VIEW mv1 AS
SELECT * FROM t WHERE k IS NOT NULL AND c1 IS NOT NULL AND c2 IS NOT NULL
PRIMARY KEY (v1, k, c1, c2)
but the following ones are not allowed:
// Error: cannot include both v1 and v2 in the primary key as both are not in the base table primary key
CREATE MATERIALIZED VIEW mv1 AS
SELECT * FROM t WHERE k IS NOT NULL AND c1 IS NOT NULL AND c2 IS NOT NULL AND v1 IS NOT NULL
PRIMARY KEY (v1, v2, k, c1, c2)
// Error: must include k in the primary as it's a base table primary key column
CREATE MATERIALIZED VIEW mv1 AS
SELECT * FROM t WHERE c1 IS NOT NULL AND c2 IS NOT NULL
PRIMARY KEY (c1, c2)
A materialized view is internally implemented by a table and as such, creating a MV allows the same options than creating a table.
After creation, you can alter the options of a materialized view using the ALTER MATERIALIZED VIEW
statement:
alter_materialized_view_statement ::= ALTER MATERIALIZED VIEWview_name
WITHtable_options
The options that can be updated are the same than at creation time and thus the same than for tables.
Dropping a materialized view users the DROP MATERIALIZED VIEW
statement:
drop_materialized_view_statement ::= DROP MATERIALIZED VIEW [ IF EXISTS ] view_name
;
If the materialized view does not exists, the statement will return an error, unless IF EXISTS
is used in which case
the operation is a no-op.