View-related operations

Create or update a view

This operation creates a new view or updates an existing one by using a query statement.

• 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

  Parameter	Required	Description
  OR REPLACE	No	This field must be included to update an existing view.
  IF NOT EXISTS	No	If IF NOT EXISTS is not specified, attempting to create a view that already exists by using CREATE VIEW will result in an exception. In this case, CREATE OR REPLACE VIEW can be used to recreate the view, and the view's permissions remain unchanged after recreation.
  view_name	Yes	The name of the view to be created or updated.
  col_name	Yes	The names of the columns in the view to be created.
  col_comment	No	The comment for the columns in the view to be created.
  view_comment	No	The comment for the view to be created.
  select_statement	Yes	The SELECT query statement that serves as the data source for the view. You must have read permission for the tables referenced in the view. Only one valid SELECT statement is allowed in a view.

  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

  Parameter	Required	Description
  view_name	Yes	The name of the view to be renamed.
  new_view_name	Yes	The new name for the view. An error is returned if a view with the new 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

  Parameter	Required	Description
  view_name	Yes	The name of the view whose owner you want to modify.
  new_owner	Yes	The account of the new owner after modification.

• 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 the views in a project, use SHOW VIEWS;. Alternatively, SHOW TABLES; lists 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 the chart 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

View the definition information, creation time, project to which the view belongs, and other metadata information.

• Syntax

  DESC <view_name>; 

• Parameters

  view_name: Required. The name of the view you want to query.

• 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

This command removes an existing view from the database.

• Syntax

  DROP VIEW [IF EXISTS] <view_name>;

• Parameters

  Parameter	Required	Description

  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	The name of the view to be deleted.

• Example

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