Materialized Views

Materialized views names are defined by:

view_name::= re('[a-zA-Z_0-9]+')

CREATE MATERIALIZED VIEW

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

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

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:

• select statement that restrict the data included in the view.
• primary key definition for the view.
• options for the view. IF NOT EXISTS option is used. If it is used, the statement will be a no-op if the materialized view already exists.

  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:

• 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. TheWHERE` clause has the following restrictions:

• bind_marker

• base table primary key that are not restricted by an IS NOT NULL restriction
• no other restriction is allowed
• view primary key be null, they must always be at least restricted by a IS NOT NULL restriction (or any other restriction, but they must have one).
• ordering clause, a limit, or xref:cassandra:developing/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);

  not allowed:

  // Error: cannot include both v1 and v2 in the primary key as both are not in the base table primary keyCREATE 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 columnCREATE MATERIALIZED VIEW mv1 AS   SELECT * FROM t   WHERE c1 IS NOT NULL AND c2 IS NOT NULL   PRIMARY KEY (c1, c2);

  MV options

  same options than creating a table <create-table-options>.

  ALTER MATERIALIZED VIEW

  ALTER MATERIALIZED VIEW statement:

  alter_materialized_view_statement::= ALTER MATERIALIZED VIEW [ IF EXISTS ] view_name WITH table_options

  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

  DROP MATERIALIZED VIEW statement:

  drop_materialized_view_statement::= DROP MATERIALIZED VIEW [ IF EXISTS ] view_name;

  IF EXISTS is used in which case the operation is a no-op.

  MV Limitations