How Do I Set Up a Materialized View?

Concept

A materialized view is a standard CQL table that automatically maintains the consistency between the data that meets certain conditions and the data in the base tables.

Constraints

• The primary key of a materialized view must contain all primary keys of the base table. Static columns cannot be included in a materialized view.
• All columns that are part of the view primary key are restricted by the "IS NOT NULL" restriction, meaning that they cannot be null.
• In a materialized view, a CQL row must be mapped from the base table to another row of the view, meaning that the rows of the view and base table correspond to each other.
• The WHERE condition of the SELECT statement does not constrain non-primary key columns in a view, except the IS NOT NULL condition.
  Figure 1 Example value
  
• Static columns, counter, superColumn, and duration types are not supported.

Setting Up a Materialized View

1. Insert a record into the base table and query the result.

   Example:

   CREATE TABLE person (
      id int,
      name text,
      addr text,
      age int,
      email text,
      PRIMARY KEY (id, name));

   Insert a record.

   insert into person(id, name, age, addr, email) values (0, 'ruby', 26, 'beijing', 'ruby@email.com');

   Query the result.

   Figure 2 Querying the result
   
2. Create a materialized view.

   CREATE MATERIALIZED VIEW person_addr AS

   SELECT * from person WHERE id IS NOT NULL AND addr IS NOT NULL AND name IS NOT NULL

   primary key (addr, id, name);

   The system_schema.views table records the association between views and base tables.

   Figure 3 Mapping between views and base tables
   

   The query results that do not meet the condition are not displayed, for example, IS NOT NULL.

3. Insert a record in which the addr value is null.

   insert into person(id, name, age, addr, email) values (1, 'mike', 30, null, 'mike@email.com');

   Query the data in the base table and materialized view.

   Figure 4 Querying the result
   
4. Delete the materialized view.
   DROP MATERIALIZED VIEW person_adder;

   Figure 5 Deleting a view