MaxCompute:View-related operations

Create or update a view

Creates a view from a SELECT statement or updates an existing view.

• Limits

  • A view can reference other views but you must not create a circular reference or reference itself.

  • Data cannot be written to a view. Operations such as INSERT INTO or INSERT OVERWRITE are not permitted.

• 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 to update an existing view.
  IF NOT EXISTS	No	If omitted and the view already exists, CREATE VIEW fails. Use CREATE OR REPLACE VIEW to recreate; permissions are preserved.
  view_name	Yes	Name of the view to create or update.
  col_name	Yes	Column names for the view.
  col_comment	No	Comment for a column.
  view_comment	No	Comment for the view.
  select_statement	Yes	The SELECT that defines the view. You must have read permission on the referenced tables. A view must contain exactly one valid SELECT.

  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;

Rename a view

• Syntax

  ALTER VIEW <view_name> RENAME TO <new_view_name>;

• Parameters

  Parameter	Required	Description
  view_name	Yes	Current name of the view.
  new_view_name	Yes	New name. An error is returned if a view with this 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

• Syntax

  ALTER VIEW <view_name> CHANGEOWNER TO <new_owner>;

• Parameters

  Parameter	Required	Description
  view_name	Yes	Name of the view.
  new_owner	Yes	Account of the new owner.

• Example

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

List all standard views in a project

• Syntax

  To list only views in a project: SHOW VIEWS;. You can also use 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 pattern.
  SHOW VIEWS LIKE '<chart>';

• Example

  SHOW VIEWS LIKE 'sale*';  

  Returned result:

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

Query a view

Shows the view definition (ViewText), creation time, project, and other metadata.

• Syntax

  DESC <view_name>; 

• Parameters

  view_name: Required. Name of the view to describe.

• Example

  DESC sale_detail_view;

  Returned result:

  +------------------------------------------------------------------------------------+
  | 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

Removes an existing view.

• Syntax

  DROP VIEW [IF EXISTS] <view_name>;

• Parameters

  Parameter	Required	Description
  IF EXISTS	No	If the view does not exist and IF EXISTS is not specified, an error is returned.
  view_name	Yes	Name of the view to drop.

• Example

  --Drop the sale_detail_view view.
  DROP VIEW IF EXISTS sale_detail_view;