Operations with views

CREATE VIEW — Create a new view
DROP VIEW — Delete a view
DESCRIBE VIEW — Display information about the view

Create a new view

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [<view_schema>.]<view_name> AS
    <select_expr>

Creates a new view with the specified name based on the result of the specified SELECT query.

Parameters

<view_name> — name of the view to be created

<view_schema> — schema of the view to be created

<select_expr> — SELECT expression, based on the result of execution of which the view will be created

If the OR REPLACE modifier is specified, the final action is equivalent to deleting the existing view and creating a new one with the same name.

The optional IF NOT EXISTS modifier restricts the query to only those cases in which the specified object does not already exist.

The modifiers are mutually exclusive. Specifying them both will result in an error.

Delete a view

DROP VIEW [IF EXISTS] <view_name>;

Removes the view from the system.

The optional IF EXISTS modifier restricts the query to only those cases in which the specified object exists.

Display information about the view

DESC[RIBE] VIEW <view_name>;

Displays information about the view.

Output format:

+-------------+-------------+------+---------+-----------+-------+
| column_name | column_type | null | default | partition | order |
+-------------+-------------+------+---------+-----------+-------+
| ...         | ...         | ...  | ...     | ...       | ...   |
+-------------+-------------+------+---------+-----------+-------+

column_name — column name
column_type — data type of the column
null — whether NULL values are allowed in the column
default — default value of the column
partition — partition expression for the given column or NULL
order — yes if the column is in the view’s sort order, otherwise — no