CREATE MATERIALIZED VIEW

Optimizes read requests by allowing different partitioning and clustering columns than the base table and eliminates the need for individual write requests to multiple tables. When data is written to the base table, it is also automatically written to all associated materialized views.

Restriction:

Use all base table primary keys in the materialized view.
Multiple non-primary key columns from the base table are supported when the partition key is the same as in the base table, otherwise only a single non-primary key from the base table is allowed in the materialized view’s PRIMARY KEY.
Static columns are not supported.
Exclude rows with null values in the materialized view primary key column.
A materialized view cannot be created in a different keyspace from the base table. You also cannot create a materialized view in the system tables.

Synopsis

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] [<keyspace_name>.]<view_name>
  AS SELECT [ (<column_list>) ]
  FROM [<keyspace_name>.]<table_name>
  [ WHERE <column_name> IS NOT NULL
  [ AND <column_name> IS NOT NULL ... ] ]
  [ AND <relation> [ AND ... ] ]
  PRIMARY KEY ( <column_list> )
  [ WITH [ table_properties ]
  [ [ AND ] CLUSTERING ORDER BY (<cluster_column_name> DESC | ASC) ] ] ;

Syntax legend

Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`< >`	Variable value. Replace with a user-defined value.
`[]`	Optional. Square brackets (`[]`) surround optional command arguments. Do not type the square brackets.
`( )`	Group. Parentheses ( `( )` ) identify a group to choose from. Do not type the parentheses.
`\|`	Or. A vertical bar (`\|`) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
`...`	Repeatable. An ellipsis ( `...` ) indicates that you can repeat the syntax element as often as required.
`'<Literal string>'`	Single quotation (`'`) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
`{ <key> : <value> }`	Map collection. Braces (`{ }`) enclose map collections or key value pairs. A colon separates the key and the value.
`<datatype2`	Set, list, map, or tuple. Angle brackets ( `< >` ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
`<cql_statement>;`	End CQL statement. A semicolon (`;`) terminates all CQL statements.
`[--]`	Separate the command line options from the command arguments with two hyphens ( `--` ). This syntax is useful when arguments might be mistaken for command line options.
`' <<schema\> ... </schema\>> '`	Search CQL only: Single quotation marks (`'`) surround an entire XML schema declaration.
`@<xml_entity>='<xml_entity_type>'`	Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.

Parameters

Parameter Description Default

keyspace_name

Optional. Name of the keyspace that contains the table to index.

Base tables and materialized views are always in the same keyspace.

If no name is specified, the current keyspace is used.

view_name

Name of the materialized view. Materialized view names can only contain alpha-numeric characters and underscores. The view name must begin with a number or letter and can be up to 49 characters long.

column_list

Comma-separated list of columns from the base table to include in the materialized view. Comma-separated list of columns from the base table to include in the materialized view. Static columns, even when specified, are not supported and not included in the materialized view.

table_name

Name of the base table.

column_name

Name of the column to include in the MV primary key. Test all such column names for null values.

cluster_column_name

Name of the column to use as a clustering column.

Options

IS NOT NULL: Test all columns for null values in the WHERE clause. Separate each condition with AND. Rows with null values in any column are not inserted into the materialized view table.
IF NOT EXISTS: Optional. Suppresses the error message when attempting to create a materialized view that already exists. Use to continue executing commands, such as a SOURCE command. The option only validates that a materialized view with the same name exists; columns, primary keys, properties, and other settings can differ.
AND relation: Other relations that target the specific data needed.
PRIMARY KEY ( column_list ): Comma-separated list of columns used to partition and cluster the data. You can add non-primary key columns from the base table. Reorder the primary keys as needed to query the table more efficiently, including changing the partitioning and clustering keys.

List the partition key first, followed by the clustering keys. Create a compound partition key by enclosing column names in parenthesis, for example:
```
PRIMARY KEY (
  ( <PK_column1> [, <PK_column2>... ] ),
  <clustering_column1> [, <clustering_column2> ... ] )
```
<table_properties>: Optional. Specify table properties if different than default. Separate table property definitions with an AND. See table properties.

The base table properties are not copied.
Restriction: Change log, CDC, is not available for materialized views. Not all table properties are available when creating a materialized view; for example, default_time_to_live is not available.

Usage notes

Examples

Detailed examples can be found for each type of indexing:

MV type Example links

MV type	Example links
Non-primary INT column used for MV primary key	Create MV based on INT column age
Non-primary DATE column used for MV primary key	Create MV based on DATE column birthday
Non-primary TEXT column used for MV primary key	Create MV based on TEXT column country
Non-primary DATE column used for MV primary key, WHERE clause for another non-primary TEXT column	Create MV restricted to cyclists from the Netherlands
Non-primary DATE column and non-primary key INT column used for MV primary key	Create MV based on DATE column birthday and INT column age
Non-primary DATE column and non-primary key TEXT column set with `WHERE`	Create MV based on DATE column birthday and TEXT column country restricted with `WHERE` clause

Non-primary INT column used for MV primary key

Create MV based on INT column age

Non-primary DATE column used for MV primary key

Create MV based on DATE column birthday

Non-primary TEXT column used for MV primary key

Create MV based on TEXT column country

Non-primary DATE column used for MV primary key, WHERE clause for another non-primary TEXT column

Create MV restricted to cyclists from the Netherlands

Non-primary DATE column and non-primary key INT column used for MV primary key

Create MV based on DATE column birthday and INT column age

Non-primary DATE column and non-primary key TEXT column set with WHERE

Create MV based on DATE column birthday and TEXT column country restricted with WHERE clause