CREATE VIEW creates a non-materialized view, which only provides an alias
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.
CREATE VIEW simply stores the verbatim
SELECT query, and provides a
shorthand for performing the query. For more information, see API Components: Sources.
|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.|
Querying non-materialized views
You can only directly
SELECT from a non-materialized view if all of the
objects it depends on (i.e. views and sources in its
FROM clause) have access
to materialized data (i.e. indexes or constants). 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 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
h) with a complex set of interdependencies.
A few things to note from this example:
- c can be materialized despite the dependency on a non-materialized view.
- If g were materialized, all views would be queryable.
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 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
Temporary views may depend upon other temporary database objects, but non-temporary views may not depend on temporary objects.
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
However, it’s important to note that you could only
SELECT from this view:
- In the definition of
CREATE MATERIALIZED VIEWor
purchasehad access to materialized data (i.e. indexes) directly or transitively.