Configure MaxCompute views-MaxCompute(MaxCompute)-阿里云帮助中心

A view is a virtual table defined by a query based on one or more tables. You can use a view to save query logic (SQL statements) without creating a physical table that consumes storage space. This topic describes the commands to manage views and provides usage examples.

The following table describes the commands used to manage views.

Operation	Description	Required permission	Platform
Create or update a view	Creates a view based on a query or updates an existing view.	The `CreateTable` permission on the project.	You can run these commands on the following platforms: MaxCompute client (odpscmd) SQL Analysis Use Cloud Shell (odpscmd) DataWorks Develop and submit an SQL script
Rename a view	Changes the name of an existing view.	The `Alter` permission on the view.
Change the owner of a view	Changes the owner of an existing view.	The project owner.
List standard views	Lists all standard views in a project.	The `List` permission on the project.
View the DDL statement for a standard view	Shows the DDL statement of a standard view.	The `Describe` permission on the view.
Describe a view	Shows metadata for an existing view.	The `Describe` permission on the view.
Drop a view	Deletes an existing view.	The `Drop` permission on the view.

Create or update a view

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

Limitations
- A view can reference other views, but it cannot reference itself or create circular references.
- You cannot write data to a view. For example, you cannot use INSERT INTO or INSERT OVERWRITE 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	Include this clause to update a view.
IF NOT EXISTS	No	If you do not specify IF NOT EXISTS, an error is returned when you use `CREATE VIEW` on an existing view. In this case, you can use `CREATE OR REPLACE VIEW` to recreate the view. The view permissions remain unchanged after recreation.
view_name	Yes	The name of the view that you want to create or update.
col_name	Yes	The name of a column in the view to be created.
col_comment	No	The comment for a column in the view.
view_comment	No	The comment for the view.
select_statement	Yes	The SELECT query that defines the view. You must have read permissions on the tables that are referenced by the view. A view can contain only one valid `SELECT` statement.

Note

A view may become inaccessible if a referenced table is changed, for example, if the table is deleted. You must maintain the mappings between views and their referenced tables.

Examples

Example 1: Create a view named sale_detail_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 view sale_detail_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;

Rename a view

Changes the name of an existing view.

Syntax

ALTER VIEW <view_name> RENAME TO <new_view_name>;

Parameters
Parameter
Required
Description
view_name
Yes
The name of the view that you want to rename.
new_view_name
Yes
The new name of the view. An error occurs if a view with the same name already exists.

Example

-- Rename the view 'sale_detail_view' to 'market'.
ALTER VIEW sale_detail_view RENAME TO market;

Change the owner of a view

Changes the owner of an existing view.

Syntax

ALTER VIEW <view_name> CHANGEOWNER TO <new_owner>;

Parameters
Parameter
Required
Description
view_name
Yes
The name of the view.
new_owner
Yes
The new owner's account.

Example

-- Change the owner of the view 'sale_detail_view' to 'ALIYUN$xxx@aliyun.com'.
ALTER VIEW sale_detail_view CHANGEOWNER TO 'ALIYUN$xxx@aliyun.com';

List standard views

Note

The SHOW VIEWS; command requires MaxCompute client (odpscmd) 0.43.0 or later.

Syntax
Run SHOW VIEWS; to list only the views in a project. You can also run SHOW TABLES; to list all tables and views in a project. For more information, see List tables and views in a project.
```
-- List all standard views in a project.
SHOW VIEWS;
-- List views in a project whose names match a specified pattern.
SHOW VIEWS LIKE '<chart>';
```

Example

SHOW VIEWS LIKE 'sale*';

The following result is returned:

ALIYUN$account_name:sale_detail_view
......
-- The ALIYUN prompt indicates that you are using an Alibaba Cloud account. If you are using a RAM user, the prompt is RAM.

View the DDL statement

Shows the DDL statement of a standard view.

Syntax
```
SHOW CREATE VIEW <view_name>;
```
Parameters
view_name: Required. The name of the standard view.

Example

-- View the creation statement for 'sale_detail_view'.
SHOW CREATE VIEW sale_detail_view;
-- The following result is returned:
CREATE VIEW IF NOT EXISTS project.sale_detail_view(store_name,customer_id,price,sale_date,region) COMMENT 'a view for table sale_detail' AS SELECT sale_detail.shop_name, sale_detail.customer_id, sale_detail.total_price, sale_detail.sale_date, sale_detail.region FROM project.sale_detail sale_detail;

Describe a view

Shows the metadata of a view, such as its definition (ViewText), creation time, and its project.

Syntax
```
DESC <view_name>; 
```
Parameters
view_name: Required. The name of the view.

Example

DESC sale_detail_view;

The following result is returned:

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$san****@aliyunid.com                  |
| Project:                  aning****                                               |
| Schema:                   default                                                  |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-03-19 13:22:48                                      |
| LastDDLTime:              2025-03-19 13:22:48                                      |
| LastModifiedTime:         2025-03-19 13:22:48                                      |
+------------------------------------------------------------------------------------+
| VirtualView  : YES                                                                 |
| ViewText: SELECT shop_name, customer_id, total_price FROM sale_detail              |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| shop_name       | string     |       |                                             |
| customer_id     | string     |       |                                             |
| total_price     | double     |       |                                             |
+------------------------------------------------------------------------------------+

Drop a view

Deletes an existing view.

Syntax
```
DROP VIEW [IF EXISTS] <view_name>;
```
Parameters
Parameter
Required
Description
IF EXISTS
No
If the view does not exist and you do not specify IF EXISTS, an error occurs.
view_name
Yes
The name of the view to delete.

Example

-- Delete the view 'sale_detail_view'.
DROP VIEW IF EXISTS sale_detail_view;