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
- Insert a record into the base table and query the result.
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
- 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.
- 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
- Delete the materialized view.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.