Materialized views names are defined by:
CREATE MATERIALIZED VIEW
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 AS select_statement PRIMARY KEY '(' primary_key')' WITH table_options
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';
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
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:
The select statement that restrict the data included in the view.
The primary key definition for the view.
The options for the view.
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.
By default, materialized views are built in a single thread. The initial
build can be parallelized by increasing the number of threads specified
by the property
MV select statement
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:
the selection is limited to those that only select columns of the base table. In other words, you can’t use any function (aggregate or not), casting, term, etc. Aliases are also not supported. You can however use * as a shortcut of selecting all columns. Further, static columns cannot be included in a materialized view. Thus, a
SELECT *command isn’t allowed if the base table has static columns. The
WHEREclause has the following restrictions:
cannot include any
cannot have columns that are not part of the base table primary key that are not restricted by an
IS NOT NULLrestriction
no other restriction is allowed
cannot have columns that are part of the view primary key be null, they must always be at least restricted by a
IS NOT NULLrestriction (or any other restriction, but they must have one).
cannot have an ordering clause, a limit, or xref:cql/dml.adoc#allow-filtering[ALLOW FILTERING
MV primary key
A view must have a primary key and that primary key must conform to the following restrictions:
it must contain all the primary key columns of the base table. This ensures that every row of the view correspond to exactly one row of the base table.
it can only contain a single column that is not a primary key column in the base table.
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);
ALTER MATERIALIZED VIEW
After creation, you can alter the options of a materialized view using
ALTER MATERIALIZED VIEW statement:
alter_materialized_view_statement::= ALTER MATERIALIZED VIEW [ IF EXISTS ] view_name WITH table_options
The options that can be updated are the same than at creation time and
same than for tables
<create-table-options>. If the view does not exist, the statement will return an error, unless
IF EXISTS is used in which case the operation is a no-op.
DROP MATERIALIZED VIEW
Dropping a materialized view using the
DROP MATERIALIZED VIEW
drop_materialized_view_statement::= DROP MATERIALIZED VIEW [ IF EXISTS ] view_name;
If the materialized view does not exists, the statement will return an
IF EXISTS is used in which case the operation is a
Removal of columns not selected in the Materialized View (via