View-related operations

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';