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
orINSERT 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 theCREATE 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 validSELECT
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;
- Example 1: Create the sale_detail_view view based on the sale_detail table.
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';