Create a materialized view (MV)

Use CREATE MATERIALIZED VIEW to create a materialized view (MV) from a table. A materialized view is a table, just like the table is based on, but the MV table has a modified primary key definition.

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

  1. Use an existing table or create a table to use for your MV:

    This example creates a table named cyclist_base in the cycling keyspace:

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

    You cannot create a materialized view from a system table.

  2. Determine how you will modify the primary key in your MV. Note the following limitations:

    • You must include all of the original table’s primary keys in the materialized view’s primary key.

    • Only one column can be added to the materialized view’s primary key.

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

  3. Create a materialized view from your original table:

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

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2025 DataStax, an IBM Company | 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: +1 (650) 389-6000, info@datastax.com