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

Parameter Required Description
or replace No This parameter is required when you want to update a view.
if not exists No 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 Yes The name of the view that you want to create or update.
col_name Yes The names of the columns in the view that you want to create.
col_comment No The column comments of the view that you want to create.
view_comment No The comment of the view that you want to create.
select_statement Yes 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;

Related commands

  • ALTER VIEW: Renames an existing view or changes the owner of an existing view.
  • DESC VIEW: Views the information of an existing view.
  • DROP VIEW: Drops an existing view.