CREATE MATERIALIZED VIEW

Create a materialized view.

Create a materialized view creates a query only table from a base table; when changes are made to the base table the materialized view is automatically updated. Use materialized views to more efficiently query the same data in different ways, see Creating a materialized view.

Restriction:

Use all base table primary keys in the materialized view as primary keys.
Optionally, add one non-PRIMARY KEY column from the base table to the materialized view's PRIMARY KEY.
Static columns are not supported as a PRIMARY KEY.

Synopsis

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [keyspace_name.] view_name
AS SELECT column_list
FROM [keyspace_name.] base_table_name
WHERE column_name IS NOT NULL [AND column_name IS NOT NULL ...] 
      [AND relation...] 
PRIMARY KEY ( column_list )
[WITH [table_properties]
      [AND CLUSTERING ORDER BY (cluster_column_name order_option )]]

Table 1. Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`Italics`	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.
`<datatype1,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.

Create MATERIALIZED VIEW clause
IF NOT EXISTS: Optional. Suppresses the error message when attempting to create a materialized view that already exists. Use to continuing executing commands, such as in 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.
keyspace_name.: Optional. When no keyspace is selected or to create the view in another keyspace, enter keyspace name before the materialized view name.
Note: Base tables and materialized views are always in the same keyspace.
view_name: 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.

AS SELECT column_list

column_list

Comma separated list of non-PRIMARY KEY columns from the base table to include in the materialized view. All primary key columns are automatically included.

Static columns, even when specified, are not included in the materialized view.

FROM [keyspace_name].]base_table_name
keyspace_name.: Keyspace where the base table is located. Only required when creating a materialized view in a different keyspace than the current keyspace.
base_table_name: Name of the table that the materialized view is based on.

WHERE PRIMARY_KEY_column_name IS NOT NULL [AND PK_column_name IS NOT NULL …]
PK_column_name IS NOT NULL: Test all primary key columns for null values in the where clause. Separate each condition with AND. Rows with null values in the primary key are not inserted into the materialized view table.
AND relation: Other relations that target the specific data needed. See the relation section of the CQL SELECT documentation.

PRIMARY KEY ( column_list)

column_list

Comma separated list of columns used to partition and cluster the data. You can add a single non-primary key column 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...])

Note: Static columns are not supported in materialized views.

WITH table_properties
table_properties: Optional. Specify table properties if different than default. Separate table property definitions with an AND.
Note: The base table properties are not copied.

Example

Creates the materialized view cyclist_by_age based on the source table cyclist_mv. The WHERE clause ensures that only rows whose age and cid columns are non-NULL are added to the materialized view.

CREATE MATERIALIZED VIEW cycling.cyclist_by_age 
AS SELECT age, name, country 
FROM cycling.cyclist_mv 
WHERE age IS NOT NULL AND cid IS NOT NULL 
PRIMARY KEY (age, cid)
WITH caching = { 'keys' : 'ALL', 'rows_per_partition' : '100' }
   AND comment = 'Based on table cyclist' ;