CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW defines a view that is persisted in durable storage and
incrementally updated as new data arrives.
A materialized view specifies a cluster that is tasked with keeping its results up-to-date, but can be referenced in any cluster. This allows you to effectively decouple the computational resources used for view maintenance from the resources used for query serving.
|OR REPLACE||If a materialized 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 materialized 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 materialized view.|
|( col_ident… )||Rename the
|cluster_name||The cluster to maintain this materialized view. If not specified, defaults to the active cluster.|
Maintaining a materialized view in durable storage has resource and latency costs that should be carefully considered depending on the main usage of the view. It’s a good idea to create a materialized view if:
- The results need to be available across clusters;
- View maintenance and query serving would benefit from being scaled independently;
- The final consumer of the view is a sink or a
On the other hand, if you only need to access a view from a single cluster, you should consider creating a non-materialized view and building an index on it instead. The index will incrementally maintain the results of the view updated in memory within that cluster, allowing you to avoid the costs and latency overhead of materialization.
Although you can query a materialized view directly, these queries will be issued against Materialize’s storage layer. This is expected to be fast, but still slower than reading from memory. To improve the speed of queries on materialized views, we recommend creating indexes based on common query patterns.
It’s important to keep in mind that indexes are local to a cluster, and
maintained in memory. As an example, if you create a materialized view and
build an index on it in the
default cluster, querying the view from a
different cluster will not use the index; you should create the appropriate
indexes in each cluster you are referencing the materialized view in.
Creating a materialized view
CREATE MATERIALIZED VIEW winning_bids AS SELECT auction_id, bid_id, item, amount FROM highest_bid_per_auction WHERE end_time < mz_now();