This topic describes how to use DDL statements to create, delete, and rename a view.

Create a view

Syntax
CREATE [OR REPLACE] VIEW [IF NOT EXISTS] view_name
    [(col_name [COMMENT col_comment], ...)]
    [COMMENT view_comment]
    [AS select_statement]

Description

This syntax is used to create a view.
Notice
  • To create a view, you must have read permissions on the table referenced by the view.
  • A view can reference other views but cannot reference itself. Circular reference is not supported.
  • Writing data into a view is not allowed. For example, you cannot manage a view by using the INSERT INTO or INSERT OVERWRITE statement.
  • After a view is created, it may be inaccessible if the referenced table is altered. For example, a referenced table is deleted. You must maintain the mappings between referenced tables and views.
Parameters
  • view_name: the name of the created view.
  • IF NOT EXISTS: If IF NOT EXISTS is not specified and the view already exists, the execution of the CREATE VIEW statement causes exceptions. In this case, you can execute CREATE OR REPLACE VIEW to recreate the view. The permissions on the view remain unchanged after the view is recreated.
  • col_name: the name of the column contained in the view.
  • view_comment: the comment of the view.
  • select_statement: the query statement, which is the data source of the view. A view can contain only one valid SELECT statement.
Example
-- Create a view named sale_detail_view.
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;

Rename a view

Syntax
ALTER VIEW view_name RENAME TO new_view_name;
Parameters
  • view_name: the name of the view you want to rename.
  • new_view_name: the new name of the view. If a view with the same name already exists, an error is returned.
Example
-- Rename sale_detail_view to market.
alter view sale_detail_view rename to market;

Delete a view

Syntax
DROP VIEW [IF EXISTS] view_name;
Parameters
  • view_name: the name of the view you want to delete.
  • IF NOT EXISTS: If the view does not exist and IF NOT EXISTS is not specified, an error is returned.
Example
-- Delete sale_detail_view.
DROP VIEW IF EXISTS sale_detail_view;