Use CREATE VIEW to create or update a view - MaxCompute - Alibaba Cloud - MaxCompute

Creates a view or updates an existing view based on a query statement.

Limits

A view can reference other views but cannot reference itself. Circular reference is not supported.
You are not allowed to write data to a view. For example, INSERT INTO or 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

Parameter	Required	Description
or replace	No	This parameter is required when you want to update a view.
if not exists	No	If you execute the `CREATE VIEW` statement to create an existing view without specifying IF NOT EXISTS, an exception is returned. In this case, you can execute the `CREATE OR REPLACE VIEW` statement to recreate the view. The permissions on the view remain unchanged after the view is recreated.
view_name	Yes	The name of the view that you want to create or update.
col_name	Yes	The names of the columns in the view that you want to create.
col_comment	No	The comments of the columns in the view that you want to create.
view_comment	No	The comment of the view that you want to create.
select_statement	Yes	The `SELECT` clause that provides the data source of the view. You must have read permissions on the table that the view references. A view can contain only one valid `SELECT` statement.

Note After a view is created or updated, the view may be inaccessible if its referenced table is modified. For example, the referenced table is deleted. You must maintain the mappings between referenced tables and views.

Examples

Example 1: Create the sale_detail_view view based on the sale_detail table.

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 the sale_detail_view view based on the sale_detail table.

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;

Related statements

ALTER VIEW: Changes the name or owner of an existing view.
DESC VIEW: Views the information about an existing view.
DROP VIEW: Drops an existing view.