| = 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. |
| ==== |