CREATE VIEW

Syntax

CREATE [ OR REPLACE ] VIEW view_name [(column_name [COMMENT 'column_comment'][, ...])] [COMMENT 'view_comment'] [TBLPROPERTIES (property_name = property_value)] AS query

Remarks

Only Catalog of the Hive data source supports the column description of the view.

For the views created in HetuEngine, the definition of the views is stored in the data source in encoding mode. You can query the view in the data source but cannot perform operations on the view.

Views are read-only. You cannot perform the LOAD or INSERT operation on views.

A view can contain the ORDER BY and LIMIT clauses. If a query statement associated with the view also contains these clauses, the ORDER BY and LIMIT clauses in the query statement are calculated based on the result of the view.

Description

It is used to create a view using the SELECT query result. A view is a logical table that can be referenced by future queries. A view has no data. The query corresponding to the view is executed each time the view is referenced by another query.

If the view already exists, the optional ORREPLACE clause causes the view to be replaced without reporting an error.

Example

• To create a view named test in the orders table:

  CREATE VIEW test (oderkey comment 'orderId',orderstatus comment 'status',half comment 'half') AS
  SELECT orderkey, orderstatus, totalprice / 2 AS half FROM orders;

• Create the orders_by_date view based on the summary result of the orders table.

  CREATE VIEW orders_by_date AS
  SELECT orderdate, sum(totalprice) AS price
  FROM orders
  GROUP BY orderdate;

• Create a view to replace the existing view:

  CREATE OR REPLACE VIEW test AS
  SELECT orderkey, orderstatus, totalprice / 4 AS quarter
  FROM orders

• Create a view and set table attributes:

  create or replace view  view1 comment 'the first view' TBLPROPERTIES('format'='orc') as select * from fruit;

Precautions

When alter is used to modify the table on which the created view depends, you need to create the view again. Otherwise, an error will be reported when you query the view again.