Skip to main content

CREATE VIEW

Description​

Creates a view.

A view, or a logical view, is a virtual table whose data is derived from a query against other existing physical tables. Therefore, a view uses no physical storage, and all queries against the view are equivalent to sub-queries of the query statement used to build the view.

For information about materialized views supported by StarRocks, see Synchronous materialized views and Asynchronous materialized views.

tip

Only users with the CREATE VIEW privilege on a specific database can perform this operation.

Syntax​

CREATE [OR REPLACE] VIEW [IF NOT EXISTS]
[<database>.]<view_name>
(
    <column_name>[ COMMENT 'column comment']
    [, <column_name>[ COMMENT 'column comment'], ...]
)
[COMMENT 'view comment']
AS <query_statement>

Parameters​

ParameterDescription
OR REPLACEReplace an existing view.
databaseThe name of the database where the view resides.
view_nameThe name of the view. For the naming conventions, see System limits.
column_nameThe name of the column(s) in the view. Note that the columns in the view and the columns queried in the query_statement must agree in number.
COMMENTThe comment on the column in the view or the view itself.
query_statementThe query statement used to create the view. It can be any query statement supported by StarRocks.

Usage notes​

  • Querying a view requires the SELECT privilege on the view and on its corresponding base tables.
  • If the query statement used to build a view cannot be executed due to the Schema Change on the base tables, StarRocks returns an error when you query the view.

Examples​

Example 1: Create a view named example_view in example_db with an aggregate query against example_table.

CREATE VIEW example_db.example_view (k1, k2, k3, v1)
AS
SELECT c1 as k1, k2, k3, SUM(v1) FROM example_table
WHERE k1 = 20160112 GROUP BY k1,k2,k3;

Example 2: Create a view named example_view in the database example_db with an aggregate query against the table example_table, and specify comments for the view and each column in it.

CREATE VIEW example_db.example_view
(
    k1 COMMENT 'first key',
    k2 COMMENT 'second key',
    k3 COMMENT 'third key',
    v1 COMMENT 'first value'
)
COMMENT 'my first view'
AS
SELECT c1 as k1, k2, k3, SUM(v1) FROM example_table
WHERE k1 = 20160112 GROUP BY k1,k2,k3;

Relevant SQLs​