Create a materialized view (MV)

Create a materialized view from a table. The table used to create a materialized view will be called the base table in the discussion below. A materialized view is a table, just like the base table, but with a new primary key defined.

Materialized views are experimental, and not recommended for production use.

Restrictions for materialized views:

  • Include all of the source table primary keys in the materialized view’s primary key.

  • Only one column can be added to the materialized view’s primary key. Static columns are not allowed in materialized views.

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

  • A materialized view cannot be created on a system table.

Prerequisites

To create a materialized view:

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

using the base table:

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

See also:

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

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