doc/modules/cassandra/pages/cql/mvs.adoc

= Materialized Views

Materialized views names are defined by:

[source,bnf]
----
include::example$BNF/view_name.bnf[]
----

[[create-materialized-view-statement]]
== CREATE MATERIALIZED VIEW

You can create a materialized view on a table using a
`CREATE MATERIALIZED VIEW` statement:

[source,bnf]
----
include::example$BNF/create_mv_statement.bnf[]
----

For instance:

[source,cql]
----
include::example$CQL/create_mv_statement.cql[]
----

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:

* The xref:cql/mvs.adoc#mv-select[select statement] that restrict the data included in
the view.
* The xref:cql/mvs.adoc#mv-primary-key[primary key] definition for the view.
* The xref:cql/mvs.adoc#mv-options[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.

[NOTE]
.Note
====
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 `concurrent_materialized_view_builders` in
`cassandra.yaml`. This property can also be manipulated at runtime
through both JMX and the `setconcurrentviewbuilders` and
`getconcurrentviewbuilders` nodetool commands.
====

[[mv-select]]
=== 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 xref:cql/mvs.adoc#selection-clause[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, xref:cql/types.adoc#static-columns[static columns] cannot be included in a materialized view.
Thus, a `SELECT *` command isn't allowed if the base table has static columns.
The `WHERE` clause has the following restrictions:

** cannot include any `bind_marker`
** cannot have columns that are not part of the _base table_ primary key that are not restricted by an `IS NOT NULL` restriction 
** 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 NULL`
restriction (or any other restriction, but they must have one).
* cannot have an xref:cql/dml.adoc#ordering-clause[ordering clause], a xref:cql/dml.adoc#limit-clause[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:

[source,cql]
----
include::example$CQL/mv_table_def.cql[]
----

then the following view definitions are allowed:

[source,cql]
----
include::example$CQL/mv_table_from_base.cql[]
----

but the following ones are *not* allowed:

[source,cql]
----
include::example$CQL/mv_table_error.cql[]
----

=== MV options

A materialized view is internally implemented by a table and as such,
creating a MV allows the `same options than
creating a table <create-table-options>`.

[[alter-materialized-view-statement]]
== ALTER MATERIALIZED VIEW

After creation, you can alter the options of a materialized view using
the `ALTER MATERIALIZED VIEW` statement:

[source,bnf]
----
include::example$BNF/alter_mv_statement.bnf[]
----

The options that can be updated are the same than at creation time and
thus the `same than for tables
<create-table-options>`.

[[drop-materialized-view-statement]]
== DROP MATERIALIZED VIEW

Dropping a materialized view using the `DROP MATERIALIZED VIEW`
statement:

[source, bnf]
----
include::example$BNF/drop_mv_statement.bnf[]
----

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.

=== MV Limitations

[NOTE]
.Note
====
Removal of columns not selected in the Materialized View (via
`UPDATE base SET unselected_column = null` or
`DELETE unselected_column FROM base`) may shadow missed updates to other
columns received by hints or repair. For this reason, we advise against
doing deletions on base columns not selected in views until this is
fixed on CASSANDRA-13826.
====