CREATE MATERIALIZED VIEW

Optimizes read requests and eliminates the need for multiple write requests by duplicating data from a base table.

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.

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 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.
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, set the 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.
column_list

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.

column_name 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.
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.
Note: The base table properties are not copied.
Restriction: Change log, CDC, is not available for materialized views.

Example

Two tables are used as base tables for the following examples, cyclist_base and cyclist_base_ext:
CREATE TABLE IF NOT EXISTS cycling.cyclist_base (
     cid UUID PRIMARY KEY, 
     name text, 
     age int, 
     birthday date, 
     country text);
CREATE TABLE IF NOT EXISTS cycling.cyclist_base_ext (
     cid UUID, 
     name text, 
     age int, 
     birthday date, 
     country text,
     PRIMARY KEY (cid, age, birthday));
Creates the materialized view cyclist_by_age based on the source table cyclist_base. 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, cid, birthday, country, name FROM cycling.cyclist_base 
WHERE age IS NOT NULL AND cid IS NOT NULL
PRIMARY KEY (age, cid)
WITH CLUSTERING ORDER BY (cid ASC)
     AND caching = { 'keys' : 'ALL', 'rows_per_partition' : '100' }
     AND comment = 'Based on table cyclist';
The results of a SELECT query:
SELECT * FROM cycling.cyclist_by_age;
are:
 age | cid                                  | birthday   | country       | name
-----+--------------------------------------+------------+---------------+---------------------
  28 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | 1987-06-07 |   Netherlands |   Steven KRUIKSWIJK
  19 | 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 |     Australia |       Kanden GROVES
  19 | 410919ef-bd1b-4efa-8256-b0fd8ab67029 | 1999-01-04 |    Uzbekistan | Iskandarbek SHODIEV
  18 | 15a116fc-b833-4da6-ab9a-4a7775752836 | 1997-08-19 | United States |        Adrien COSTA
  18 | 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503 | 1997-03-29 |   Netherlands |         Bram WELTEN
  18 | ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156 | 1997-02-08 |   Netherlands |    Pascal EENKHOORN
  22 | e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 1993-06-18 |   New Zealand |          Alex FRAME
  27 | c9c9c484-5e4a-4542-8203-8d047a01b8a8 | 1987-09-04 |        Brazil |     Cristian EGIDIO
  27 | d1aad83b-be60-47a4-bd6e-069b8da0d97b | 1987-09-04 |       Germany |     Johannes HEIDER
  20 | 862cc51f-00a1-4d5a-976b-a359cab7300e | 1994-09-04 |       Denmark |       Joakim BUKDAL
  38 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb | 1977-07-08 |         Italy |     Paolo TIRALONGO
  29 | 96c4c40d-58c8-4710-b73f-681e9b1f70ae | 1989-04-20 |     Australia |     Benjamin DYBALL

(12 rows)
Create the materialized view cyclist_by_birthday_and_age based on the source table cyclist_base. The WHERE clause ensures that only rows whose age, birthday, and cid columns are non-NULL are added to the materialized view.
CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age
    AS SELECT age, cid, birthday, country, name FROM cycling.cyclist_base
    WHERE age IS NOT NULL AND birthday IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (cid, birthday, age);
The results of a SELECT query:
SELECT * FROM cycling.cyclist_by_birthday_and_age;
are:
 cid                                  | birthday   | age | country       | name
--------------------------------------+------------+-----+---------------+---------------------
 ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156 | 1997-02-08 |  18 |   Netherlands |    Pascal EENKHOORN
 15a116fc-b833-4da6-ab9a-4a7775752836 | 1997-08-19 |  18 | United States |        Adrien COSTA
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 1993-06-18 |  22 |   New Zealand |          Alex FRAME
 c9c9c484-5e4a-4542-8203-8d047a01b8a8 | 1987-09-04 |  27 |        Brazil |     Cristian EGIDIO
 410919ef-bd1b-4efa-8256-b0fd8ab67029 | 1999-01-04 |  19 |    Uzbekistan | Iskandarbek SHODIEV
 d1aad83b-be60-47a4-bd6e-069b8da0d97b | 1987-09-04 |  27 |       Germany |     Johannes HEIDER
 862cc51f-00a1-4d5a-976b-a359cab7300e | 1994-09-04 |  20 |       Denmark |       Joakim BUKDAL
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 |  19 |     Australia |       Kanden GROVES
 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503 | 1997-03-29 |  18 |   Netherlands |         Bram WELTEN
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb | 1977-07-08 |  38 |         Italy |     Paolo TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | 1987-06-07 |  28 |   Netherlands |   Steven KRUIKSWIJK
 96c4c40d-58c8-4710-b73f-681e9b1f70ae | 1989-04-20 |  29 |     Australia |     Benjamin DYBALL

(12 rows)
Create the materialized view cyclist_by_country_and_birthday based on the source table cyclist_base. The WHERE clause ensures that only rows whose birthday and cid columns are non-NULL and country equals Australia are added to the materialized view.
CREATE MATERIALIZED VIEW cycling.cyclist_by_country_and_birthday
    AS SELECT age, cid, birthday, country, name FROM cycling.cyclist_base
    WHERE birthday IS NOT NULL AND cid IS NOT NULL AND country='Australia'
    PRIMARY KEY (cid, country, birthday);
The results of a SELECT query:
SELECT * FROM cycling.cyclist_by_country_and_birthday;
are:
 cid                                  | country   | birthday   | age | name
--------------------------------------+-----------+------------+-----+-----------------
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | Australia | 1998-12-23 |  19 |   Kanden GROVES
 96c4c40d-58c8-4710-b73f-681e9b1f70ae | Australia | 1989-04-20 |  29 | Benjamin DYBALL

(2 rows)
Create the materialized view cyclist_by_birthday_and_age19 based on the source table cyclist_base. The WHERE clause ensures that only rows whose birthday and cid columns are non-NULL and age equals the value 19 are added to the materialized view.
CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age19
    AS SELECT age, cid, birthday, country, name FROM cycling.cyclist_base
    WHERE birthday IS NOT NULL AND cid IS NOT NULL AND age=19
    PRIMARY KEY (cid, birthday, age);
The results of a SELECT query:
SELECT * FROM cycling.cyclist_by_birthday_and_age19;
are:
 cid                                  | birthday   | age | country    | name
--------------------------------------+------------+-----+------------+---------------------
 410919ef-bd1b-4efa-8256-b0fd8ab67029 | 1999-01-04 |  19 | Uzbekistan | Iskandarbek SHODIEV
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 |  19 |  Australia |       Kanden GROVE

(2 rows)
Create the materialized view cyclist_by_age_birthday_cid based on the source table cyclist_base_ext. The WHERE clause ensures that only rows whose age, birthday, and cid columns are non-NULL are added to the materialized view.
CREATE MATERIALIZED VIEW cycling.cyclist_by_age_birthday_cid
    AS SELECT age, cid, birthday, country, name FROM cycling.cyclist_base_ext
    WHERE age IS NOT NULL AND birthday IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (age, birthday, cid);
The results of a SELECT query using a WHERE clause:
SELECT * FROM cycling.cyclist_by_age_birthday_cid WHERE age=19;
are:
 cid                                  | birthday   | age | country    | name
--------------------------------------+------------+-----+------------+---------------------
 410919ef-bd1b-4efa-8256-b0fd8ab67029 | 1999-01-04 |  19 | Uzbekistan | Iskandarbek SHODIEV
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 |  19 |  Australia |       Kanden GROVE

(2 rows)
The results of a SELECT query using a WHERE clause for two values:
SELECT * FROM cycling.cyclist_by_age_birthday_cid WHERE age=19 AND birthday='1998-12-23';
are:
 age | birthday   | cid                                  | country   | name
-----+------------+--------------------------------------+-----------+---------------
  19 | 1998-12-23 | 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | Australia | Kanden GROVES

(1 rows)
Also notice that clustering columns must still be included in order, the results of a SELECT query that violates this rule:
SELECT * FROM cycling.cyclist_by_age_birthday_cid WHERE birthday='1998-12-23';
are:
cyclist_by_age-mv.cql:195:InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot 
execute this query as it might involve data filtering and thus may have unpredictable performance. If you 
want to execute this query despite the performance unpredictability, use ALLOW FILTERING.