A view is a virtual table that is created based on existing tables. Its structure and content are derived from these tables. A view corresponds to one or more tables. You can use views if you want to retain query results without creating additional tables.

The following table describes view-related operations.

Operation Description Role Operation platform
Create or update a view Creates a view or updates an existing view based on a query statement. Users who have the CREATE TABLE permission on a project You can execute the statements that are described in this topic on the following platforms:
Rename a view Renames an existing view. Users who have the ALTER permission on tables
Query a view Views the information of an existing view. Users who have the DESCRIBE permission on the metadata of a table
Drop a view Drops an existing view. Users who have the DROP permission on tables
Change the owner of a view Changes the owner of an existing view. Users who have the ALTER permission on tables

Create or update a view

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
    • or replace: optional. This parameter is required when you want to update a view.
    • if not exists: optional. If the CREATE VIEW statement is executed without if not exists and the view that you want to create already exists, an error 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: required. The name of the view that you want to create or update.
    • col_name: required. The names of the columns in the view that you want to create.
    • col_comment: optional. The column comments of the view that you want to create.
    • view_comment: optional. The comment of the view that you want to create.
    • select_statement: required. The SELECT clause that provides the data source of the view. You must have read permissions on the table that the view references. When you create or update a view, you can use only one valid SELECT clause.
      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

Renames an existing view.

  • Syntax
    alter view <view_name> rename to <new_view_name>;
  • Parameters
    • view_name: required. The name of the view that you want to rename.
    • new_view_name: required. The new name of the view. If a view with the same name already exists, an error is returned.
  • Example
    -- Rename the sale_detail_view view as market. 
    alter view sale_detail_view rename to market;

Query a view

For more information about this command, see View the information about tables or views.

Drop a view

Drops an existing view.

  • Syntax
    drop view [if exists] <view_name>;
  • Parameters
    • if exists: optional. If you run the command without specifying the if exists option and the view does not exist, an error is returned.
    • view_name: required. The name of the view that you want to drop.
  • Example
    -- Drop the sale_detail_view view. 
    drop view if exists sale_detail_view;

Change the owner of a view

Changes the owner of an existing view.

  • Syntax
    alter view <view_name> changeowner to <new_owner>;
  • Parameters
    • view_name: required. The name of the view whose owner you want to change.
    • new_owner: required. The new owner of the view.
  • 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';