Materialize Logo

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW creates a materialized view, which lets you retrieve incrementally updated results of a SELECT query very quickly. Despite the simplicity of creating a materialize view, it’s Materialize’s most powerful feature.

Conceptual framework

CREATE MATERIALIZED VIEW computes and maintains the results of a SELECT query in memory. For more information, see API Components: Materialized views.

Syntax

CREATE MATERIALIZED VIEW IF NOT EXISTS OR REPLACE MATERIALIZED VIEW view_name AS select_stmt
Field Use
OR REPLACE If a view exists with the same name, replace it with the view defined in this statement. You cannot replace views that other views or sinks depend on, nor can you replace a non-view object with a view.
IF NOT EXISTS If specified, do not generate an error if a view of the same name already exists.

If not specified, throw an error if a view of the same name already exists. (Default)
view_name A name for the view.
select_stmt The SELECT statement whose output you want to materialize and maintain.

Details

Memory

Views are maintained in memory. Because of this, one must be sure that all intermediate stages of the query, as well as its result set can fit in the memory of a single machine, while also understanding the rate at which the query’s result set will grow.

For more detail about how different clauses impact memory usage, check out our SELECT documentation.

Indexes

A brief mention on indexes: Materialize automatically creates an in-memory index which stores all columns in the SELECT query’s result set; this is the crucial structure that the view maintains to provide low-latency access to your query’s results.

Some things you might want to do with indexes…

Examples

CREATE MATERIALIZED VIEW purchase_sum_by_region
AS
    SELECT sum(purchase.amount) AS region_sum, region.id AS region_id
    FROM mysql_simple_region AS region
    INNER JOIN mysql_simple_user AS user ON region.id = user.region_id
    INNER JOIN mysql_simple_purchase AS purchase ON purchase.user_id = user.id
    GROUP BY region.id;

In this example, as new users or purchases come in, the results of the view are incrementally updated. For example, if a new purchase comes in for a specific user, the underlying dataflow will determine which region that user belongs to, and then increment the region_sum field with those results.