CREATE MATERIALIZED VIEW

Materialized views are experimental. They aren’t recommended for production use.

Creates a materialized view (MV) from a table. A MV is a dependent copy of an existing table with a different primary key. This enables queries that aren’t possible on the base table without using ALLOW FILTERING or writing to multiple separate tables. The source table is referred to as the base table.

Limitations

  • You must include all of the base table’s primary key columns in the materialized view’s primary key definition. This is because the definition of row uniqueness must be the same in the base table and all views.

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

  • Only one column can be added to the materialized view’s primary key, and the selected column cannot contain null values.

  • Filtering can be used to create the materialized view from a subset of the base table’s data. Data excluded by the filter isn’t accessible from the view. To change the filter criteria, you must drop and re-create the view.

  • Static columns aren’t allowed in materialized views.

  • Don’t use rows with null values in the materialized view’s primary key column.

  • You must create the materialized view in the same keyspace as the base table.

  • You cannot create a materialized view based on a system table.

Data synchronization between base tables and materialized views

When data is written to the base table, the database automatically writes the data to all associated MVs.

When data is deleted from the base table, the database deletes the same data from any associated materialized views.

You cannot send a write request directly to a MV; you must write to the base table, which then triggers an update to the materialized view.

The database updates materialized views asynchronously after inserting data into the base table. Therefore, updates to materialized views are inherently delayed. Read-repair operations on materialized views are performed only after updating the base table.

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) ] ] ;
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.

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

Options

Options Description Default

keyspace_name

Optional. Name of the keyspace that contains the base table.

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

If not 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.

IS NOT NULL

Optional. 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 (
  ( pkcolumn1 [, pkcolumn2... ] ),
  clustercolumn1 [, clustercolumn2 ... ] )

<table_properties>

Optional. Specify table properties if different than the default table properties.

The base table’s properties aren’t copied.

Set table properties in a WITH clause using the following syntax:

  • String values wrapped in single quotes: <option_name> = '<value>'

  • Non-string, non-quoted values: <option_name> = <value>

  • Options with multiple subproperties in simple JSON format: <option_name> = { <option_map> }

  • Use AND to separate properties: <property = value> AND <property = value>.

Some properties aren’t applicable to views, for example:

  • Repair properties are invalid because materialized views don’t perform repair in the same way as base tables.

  • The CDC property is invalid because change data capture logs are available for base tables only.

  • Other properties not relevant to views, like default_time_to_live.

Default table properties (not the base table’s properties)

Examples

To follow along with these examples, create the following keyspace and table, and then load the sample data. The table has a single primary key column (cid), and it holds values for the name, age, birthday, and country affiliation of several cyclists.

  • Keyspace:

    CREATE KEYSPACE IF NOT EXISTS cycling
WITH REPLICATION = {
  'class' : 'SimpleStrategy',
  'replication_factor' : 1
};

  • Table:

    CREATE TABLE IF NOT EXISTS cycling.cyclist_base (
  cid UUID PRIMARY KEY,
  name text,
  age int,
  birthday date,
  country text
);

  • Rows:

     cid                                  | age | birthday   | country       | name
--------------------------------------+-----+------------+---------------+-------------------
 ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156 |  18 | 1997-02-08 |   Netherlands |  Pascal EENKHOORN
 15a116fc-b833-4da6-ab9a-4a7775752836 |  18 | 1997-08-19 | United States |      Adrien COSTA
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 |  22 | 1993-06-18 |   New Zealand |        Alex FRAME
 c9c9c484-5e4a-4542-8203-8d047a01b8a8 |  27 | 1987-09-04 |        Brazil |   Cristian EGIDIO
 862cc51f-00a1-4d5a-976b-a359cab7300e |  20 | 1994-09-04 |       Denmark |     Joakim BUKDAL
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 |  19 | 1998-12-23 |     Australia |     Kanden GROVES
 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503 |  18 | 1997-03-29 |   Netherlands |       Bram WELTEN
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  38 | 1977-07-08 |         Italy |   Paolo TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |  28 | 1987-06-07 |   Netherlands | Steven KRUIKSWIJK
 95fed1a4-db7e-4c8d-8b2d-f7f9340f2c0b |  20 | 1993-04-09 | United States |        Zam WEATON

(10 rows)

Create MV based on a non-primary key INT column

  1. Using the example table, create a MV using the age column (a non-primary key INT column) as the partition key in the primary key for the MV.

    As with any table, the materialized view must specify the primary key columns.

    Because the base table cyclist_base uses cid as its primary key, cid must be present in the materialized view’s primary key. In this example, cid is used as a clustering column, not a partition key.

    The MV is called cycling.cyclist_by_age.

    CREATE MATERIALIZED VIEW IF NOT EXISTS 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_base';

    Note the following characteristics of this CREATE MATERIALIZED VIEW statement:

    • The AS SELECT phrase identifies the columns copied from the base table to the materialized view.

    • The FROM phrase identifies the base table from which data is copied.

    • The WHERE clause must include all primary key columns with the IS NOT NULL phrase so that only rows with data for all the primary key columns are copied to the materialized view.

  2. To verify that the materialized view was created, use DESCRIBE MATERIALIZED VIEW to get the MV schema:

    DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_age;

    The resulting materialized view definition includes the same information from your CREATE MATERIALIZED VIEW statement and any table options that weren’t set in your CREATE MATERIALIZED VIEW statement. In the following example, the matrialized view definition is truncated to show the schema defined in the previous CREATE MATERIALIZED VIEW statement:

    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_base'
    ... ;

  3. Query using the MV.

    Because this MV is partitioned by age, it supports queries on the age column:

    SELECT age, name, birthday FROM cycling.cyclist_by_age WHERE age = 18;
     age | name             | birthday
-----+------------------+------------
  18 |     Adrien COSTA | 1997-08-19
  18 |      Bram WELTEN | 1997-03-29
  18 | Pascal EENKHOORN | 1997-02-08

(3 rows)

You can create other materialized views based on the same table that use different primary key columns to support other queries, as shown in the following examples.

Create MV based on a non-primary key DATE column

  1. Using the example table, create a MV using the birthday column (a non-primary key DATE column) as the partition key in the primary key.

    As with any table, the materialized view must specify the primary key columns.

    Because the base table cyclist_base uses cid as its primary key, cid must be present in the materialized view’s primary key. In this example, cid is used as a clustering column, not a partition key.

    The MV is called cycling.cyclist_by_birthday.

    CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday
  AS SELECT age, cid, birthday, name, country
  FROM cycling.cyclist_base
  WHERE birthday IS NOT NULL AND cid IS NOT NULL
  PRIMARY KEY (birthday, cid);

  2. Verify that the materialized view was created:

    DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_birthday;

    The resulting materialized view definition includes the schema defined in the CREATE MATERIALIZED VIEW statement, along with the table options (WITH clause). In this example, cid is a clustering column; therefore, the materialized view definition includes WITH CLUSTERING ORDER BY (cid ASC). The following example materialized view definition is truncated to focus on the column and key definitions. A full response would include all table options.

    CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday AS
    SELECT birthday, cid, age, country, name
    FROM cycling.cyclist_base
    WHERE birthday IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (birthday, cid)
    WITH CLUSTERING ORDER BY (cid ASC)
    AND ... ;

  3. Query using the MV.

    Because this MV is partitioned by birthday, it supports queries based on the birthday column:

    SELECT age, name, birthday FROM cycling.cyclist_by_birthday
  WHERE birthday = '1987-09-04'
     age | name            | birthday
-----+-----------------+------------
  27 | Cristian EGIDIO | 1987-09-04

(1 rows)

Create MV based on a non-primary key TEXT column

  1. Using the example table, create a MV using the country column (a non-primary key TEXT column) as the partition key in the primary key.

    As with any table, the materialized view must specify the primary key columns.

    Because the base table cyclist_base uses cid as its primary key, cid must be present in the materialized view’s primary key. In this example, cid is used as a clustering column, not a partition key.

    The MV is called cycling.cyclist_by_country.

    CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_country
  AS SELECT age, cid, birthday, name, country
  FROM cycling.cyclist_base
  WHERE country IS NOT NULL AND cid IS NOT NULL
  PRIMARY KEY (country, cid);

    The resulting materialized view definition includes the schema defined in the CREATE MATERIALIZED VIEW statement, along with the table options (WITH clause). In this example, cid is a clustering column; therefore, the materialized view definition includes WITH CLUSTERING ORDER BY (cid ASC). The following example materialized view definition is truncated to focus on the column and key definitions. A full response would include all table options.

    CREATE MATERIALIZED VIEW cycling.cyclist_by_country AS
    SELECT country, cid, age, birthday, name
    FROM cycling.cyclist_base
    WHERE country IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (country, cid)
    WITH CLUSTERING ORDER BY (cid ASC)
    AND ... ;

  2. Query using the MV.

    Because this MV is partitioned by country, it supports queries on the country column:

    SELECT age, name, birthday FROM cycling.cyclist_by_country
  WHERE country = 'Netherlands';
     age | name              | birthday
-----+-------------------+------------
  18 |       Bram WELTEN | 1997-03-29
  28 | Steven KRUIKSWIJK | 1987-06-07
  18 |  Pascal EENKHOORN | 1997-02-08

(3 rows)

Create MV using a WHERE clause to exclude rows

  1. Using the example table, create a MV using the birthday column (a non-primary key TEXT column) as the partition key in the primary key, and use a WHERE clause to include only rows with a specific value in the country column.

    As explained in the previous examples, the materialized view must specify the primary key columns, and it must include, at minimum, the primary key columns from the base table. In this example, cid (from the base table’s primary key) is used as a clustering column, not a partition key.

    The MV is called cycling.cyclist_by_birthday_netherlands.

    CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday_netherlands AS
  SELECT age, cid, birthday, name, country
  FROM cycling.cyclist_base
  WHERE birthday IS NOT NULL AND cid IS NOT NULL
    AND country = 'Netherlands'
  PRIMARY KEY (birthday, cid);

  2. Verify that the materialized view was created:

    DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_birthday_netherlands;
    CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_netherlands AS
    SELECT birthday, cid, age, country, name
    FROM cycling.cyclist_base
    WHERE birthday IS NOT NULL AND cid IS NOT NULL AND country = 'Netherlands'
    PRIMARY KEY (birthday, cid)
    WITH CLUSTERING ORDER BY (cid ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

  3. Query using the MV.

    Because this MV is partitioned by birthday, it supports queries on the birthday column:

    SELECT age, name, birthday FROM cycling.cyclist_by_birthday_netherlands
  WHERE birthday = '1997-02-08';
     age | name             | birthday
-----+------------------+------------
  18 | Pascal EENKHOORN | 1997-02-08

(1 rows)

Create MV based on non-primary key DATE and INT columns

  1. Using the example table, create a MV using the birthday column (a non-primary key DATE column) and the age column (a non-primary key INT column) as the clustering columns in the primary key for the MV.

    As explained in the previous examples, the materialized view must specify the primary key columns, and it must include, at minimum, the primary key columns from the base table. In this example, cid (from the base table’s primary key) is still the partition key, and birthday and age are used as clustering columns.

    The MV is called cycling.cyclist_by_birthday_and_age.

    CREATE MATERIALIZED VIEW IF NOT EXISTS 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);

  2. Verify that the materialized view was created:

    DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age;
    CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age AS
    SELECT cid, birthday, age, 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)
    WITH CLUSTERING ORDER BY (birthday ASC, age ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

  3. Query using the MV.

    Clustering columns must be queried in the order they are defined in the primary key.

    Based on the primary key for this MV, you can query on the birthday and age columns:

    SELECT age, name, birthday FROM cycling.cyclist_by_birthday_and_age
  WHERE birthday = '1997-02-08'

Create MV based on non-primary key columns with a WHERE clause

  1. Using the example table, create a MV using the birthday and age columns as the clustering columns in the primary key, as well as a WHERE clause.

    As explained in the previous examples, the materialized view must specify the primary key columns, and it must include, at minimum, the primary key columns from the original table. In this example, cid (from the original table’s primary key) is still the partition key, and birthday and age are used as clustering columns.

    Additionally, this example uses a WHERE clause to ensure that the MV includes only rows that meet all of the following conditions:

    • birthday is not NULL

    • cid is not NULL

    • age is equal to 19

    The MV is called cycling.cyclist_by_birthday_and_age_19.

    CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.cyclist_by_birthday_and_age_19 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);

  2. Verify that the MV was created:

    DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age_19;
    CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age_19 AS
    SELECT cid, birthday, age, country, name
    FROM cycling.cyclist_base
    WHERE birthday IS NOT NULL AND cid IS NOT NULL AND age = 19
    PRIMARY KEY (cid, birthday, age)
    WITH CLUSTERING ORDER BY (birthday ASC, age ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

  3. Query using the MV:

    SELECT * FROM cycling.cyclist_by_birthday_and_age_19
  WHERE birthday = '1998-12-23';
     age | name          | birthday
-----+---------------+-----------
  19 | Kanden GROVES | 1998-12-23

(1 rows)

    When querying on multiple clustering columns, these columns must be queried in the order they are defined in the primary key.

Use a materialized view to perform queries that aren’t possible on the original table

The following example demonstrates how to use a materialized view to perform queries that aren’t possible on the original table unless you used ALLOW FILTERING. ALLOW FILTERING is not recommended because of performance degradation.

This example uses the following table:

CREATE TABLE IF NOT EXISTS cycling.mechanic (
  emp_id int,
  dept_id int,
  name text,
  age int,
  birthdate date,
  PRIMARY KEY (emp_id, dept_id)
);

The table contains these rows:

 emp_id | dept_id | age | birthdate  | name
--------+---------+-----+------------+------------
      5 |       3 |  25 | 1996-10-04 | Lisa SMITH
      1 |       1 |  21 | 1992-06-18 | Fred GREEN
      2 |       1 |  22 | 1993-01-15 | John SMITH
      4 |       2 |  24 | 1995-08-19 | Jack JONES
      3 |       2 |  23 | 1994-02-07 |   Jane DOE

(5 rows)

Based on the cycling.mechanic table, the following MV includes only rows where all columns are not NULL, and it reconfigures the primary key to support different queries. In the primary key for the MV, the age column is the partition key, and the original table’s primary key columns, emp_id and dept_id, are used as clustering columns.

CREATE MATERIALIZED VIEW IF NOT EXISTS cycling.mechanic_view AS
  SELECT emp_id, dept_id, name, age, birthdate FROM cycling.mechanic
  WHERE emp_id IS NOT NULL
    AND dept_id IS NOT NULL
    AND name IS NOT NULL
    AND age IS NOT NULL
    AND birthdate IS NOT NULL
  PRIMARY KEY (age, emp_id, dept_id);

Queries using the MV only need to specify the partition key for the MV. For example, the following query succeeds when run on the MV, but it would fail if run on the original table without ALLOW FILTERING:

SELECT * FROM cycling.mechanic_view WHERE age = 21;
 age | emp_id | dept_id | birthdate  | name
-----+--------+---------+------------+------------
  21 |      1 |       1 | 1992-06-18 | Fred GREEN

(1 rows)

Was this helpful?

Give Feedback

How can we improve the documentation?

© Copyright IBM Corporation 2026 | Privacy policy | Terms of use |  Manage Privacy Choices

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: Contact IBM