Help Center/ GeminiDB/ GeminiDB Cassandra API/ FAQs/ Database Usage/ How Do I Set Up a Materialized View?
Updated on 2023-02-23 GMT+08:00

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