Home  /  Reference

CREATE VIEW

CREATE VIEW defines a view, which simply provides an alias for the embedded SELECT statement.

The results of a view can be incrementally maintained in memory within a cluster by creating an index. This allows you to serve queries without the overhead of materializing the view.

Usage patterns

In Materialize, both indexes on views and materialized views incrementally update the view results when Materialize ingests new data. Whereas materialized views persist the view results in durable storage and can be accessed across clusters, indexes on views compute and store view results in memory within a single 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, while maintaining an index has memory costs.

Some general guidelines for usage patterns include:

Usage Pattern General Guideline
View results are accessed from a single cluster only View with an index
View results are accessed across clusters Materialized view
Final consumer of the view is a sink or a SUBSCRIBE operation Materialized view
View maintenance and query serving are scaled independently Materialized view

Syntax

CREATE TEMP TEMPORARY VIEW IF NOT EXISTS OR REPLACE VIEW view_name ( col_ident , ) AS select_stmt
Field Use
TEMP / TEMPORARY Mark the 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 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.
( col_ident… ) Rename the SELECT statement’s columns to the list of identifiers, both of which must be the same length. Note that this is required for statements that return multiple columns with the same identifier.
select_stmt The SELECT statement to embed in the view.

Details

Temporary views

The TEMP/TEMPORARY keyword creates a temporary view. Temporary 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 mz_temp schema.

Temporary views may depend upon other temporary database objects, but non-temporary views may not depend on temporary objects.

Examples

Creating a view

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

Privileges

The privileges required to execute this statement are:

  • Ownership of existing view_name if OR REPLACE is specified.
  • CREATE privileges on the containing schema.
  • USAGE privileges on all types used in the view definition.
  • USAGE privileges on the schemas that all types in the statement are contained in.

Additional information

  • Views can be monotonic; that is, views can be recognized as append-only.
Back to top ↑