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
CREATE MATERIALIZED VIEW computes and maintains the results of a
query in memory. For more information, see API Components: Materialized views.
|TEMP / TEMPORARY||Mark the materialized view as temporary.|
|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.|
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
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
Some things you might want to do with indexes…
- View the details of a view’s indexes through
- If you find that your queries would benefit from other indexes, e.g. you want to join two relations on some foreign key, you can create indexes.
Temporary materialized views
TEMPORARY keyword creates a temporary materialized view. Temporary
materialized views are automatically dropped at the end of the SQL session and
are not visible to other connections. They are always created in the special
Temporary materialized views may depend upon other temporary database objects, but non-temporary materialized views may not depend on temporary objects.
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.