CREATE VIEW
CREATE VIEW
defines a non-materialized 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.
Syntax
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;