Use CREATE VIEW to create or update a view - MaxCompute

Limits

A view can reference other views but cannot reference itself. Circular reference is not supported.
Writing data to a view is not allowed. INSERT INTO and INSERT OVERWRITE cannot be executed on a view.

Syntax

create [or replace] view [if not exists] <view_name>
    [(<col_name> [comment <col_comment>], ...)]
    [comment <view_comment>]
    as <select_statement>;

Parameters

Required

Parameter	Description
`view_name`	The name of the view to create or update.
`col_name`	The column names in the view.
`select_statement`	The `SELECT` clause that defines the view's data source. You must have read permissions on the referenced table. Only one valid `SELECT` clause is allowed.

Optional

Parameter	Description
`or replace`	Updates an existing view. Permissions on the view remain unchanged after recreation.
`if not exists`	Skips view creation if a view with the same name already exists. Without this clause, `CREATE VIEW` returns an error if the view already exists. You can use `CREATE OR REPLACE VIEW` to recreate the view instead.
`col_comment`	The comment for each column.
`view_comment`	The comment for the view.

Usage notes

A view is not automatically updated when its underlying tables change. If a referenced table is modified — for example, the table is deleted — the view may become inaccessible. Maintain the mappings between views and their referenced tables.

Examples

Example 1: Create a view

Create sale_detail_view based on sale_detail, exposing all columns.

create view if not exists sale_detail_view
(store_name, customer_id, price, sale_date, region)
comment 'a view for table sale_detail'
as select * from sale_detail;

Example 2: Update a view

Replace sale_detail_view to expose only three columns.

create or replace view if not exists sale_detail_view
(store_name, customer_id, price)
comment 'a view for table sale_detail'
as select shop_name, customer_id, total_price from sale_detail;

What's next

ALTER VIEW: Renames an existing view or changes the owner of an existing view.
DESC VIEW: Views the information of an existing view.
DROP VIEW: Drops an existing view.