Materialize Logo

CREATE VIEW

CREATE VIEW creates a non-materialized view, which only provides an alias for the SELECT statement it includes.

Note that this is very different from Materialize’s main type of view, materialized views, which you can create with CREATE MATERIALIZED VIEW.

Conceptual framework

CREATE VIEW simply stores the verbatim SELECT query, and provides a shorthand for performing the query. For more information, see API Components: Sources.

Syntax

CREATE TEMP TEMPORARY VIEW IF NOT EXISTS OR REPLACE VIEW view_name 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 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

Querying non-materialized views

You can only directly SELECT from a non-materialized view if all of the sources it depends on (i.e. views and sources in its FROM clause) have access to materialized data (i.e. indexes). That is to say that all of a non-materialized view’s data must exist somewhere in memory for it to process SELECT statements. For those inclined toward mathematics, it’s possible to think of this as “transitive materialization.”

If non-materialized views can process SELECT statements, we call them “queryable.”

However, this limitation does not apply to creating materialized views. Materialized view definitions can SELECT from non-materialized view, irrespective of the non-materialized view’s dependencies. This is done by essentially inlining the definition of the non-materialized view into the materialized view’s definition.

The diagram below demonstrates this restriction using a number of views (a-h) with a complex set of interdependencies.

transitive materialization diagram

A few things to note from this example:

Memory

Non-materialized views do not store the results of the query. Instead, they simply store the verbatim of the included SELECT. This means they take up very little memory, but also provide very little benefit in terms of reducing the latency and computation needed to answer queries.

Converting to materialized view

You can convert a non-materialized view into a materialized view by adding an index.

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 views, but non-temporary views may not depend on a temporary view.

Materialize does not yet support creating indexes on temporary views.

Examples

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;

This view is useful only in as much as it is easier to type purchase_sum_by_region than the entire SELECT statement.

However, it’s important to note that you could only SELECT from this view: