CREATE VIEW¶

Clause in a schema template definition to create a non-materialized view. A view is a virtual table whose contents are defined by a SQL query. Views do not store data themselves; each query against a view executes the underlying query against the base tables. Views are read-only and cannot be the target of INSERT, UPDATE, or DELETE statements. CREATE VIEW is a clause within a schema template and cannot be a standalone statement. View names must not collide with table, type, or function names in the same schema template.

Syntax¶

Parameters¶

viewName: The name of the view. Must be unique within the schema template — cannot collide with table, type, or other view names.
query: The SQL SELECT statement that defines the view. The query can reference tables, other views, and functions defined in the same schema template.

Examples¶

Setup¶

CREATE VIEW must appear inside a schema template definition and cannot be a standalone statement. For the examples on this page, assume the following schema template:

CREATE SCHEMA TEMPLATE my_template
    CREATE TABLE employees (
        id       BIGINT,
        name     STRING,
        dept     STRING,
        salary   BIGINT,
        PRIMARY KEY(id))

Basic View¶

Create a view that filters rows from a base table:

CREATE VIEW engineering AS
    SELECT id, name, salary
    FROM employees
    WHERE dept = 'Engineering'

Query the view like a table:

SELECT * FROM engineering

`id`	`name`	`salary`
`1`	`"Alice"`	`100000`
`2`	`"Bob"`	`110000`

Nested Views¶

Views can reference other views. The following creates a second view on top of the first:

CREATE VIEW engineering AS
    SELECT id, name, salary
    FROM employees
    WHERE dept = 'Engineering'

CREATE VIEW high_earners AS
    SELECT id, name
    FROM engineering
    WHERE salary > 100000

SELECT * FROM high_earners

`id`	`name`
`2`	`"Bob"`

View with JOIN¶

Views support joins, including self-joins (see inner_join for join syntax):

CREATE VIEW peer_pairs AS
    SELECT A.name AS emp1, B.name AS emp2
    FROM employees A, employees B
    WHERE A.dept = B.dept AND A.id < B.id

View with CTE¶

Views can use Common Table Expressions (see WITH for CTE syntax):

CREATE VIEW senior_engineering AS
    WITH filtered AS (
        SELECT id, name, salary
        FROM employees
        WHERE dept = 'Engineering' AND salary > 100000
    )
    SELECT * FROM filtered

Indexes on Views¶

Indexes can be defined on views using the standard CREATE INDEX syntax. For a self-contained example including array unnesting, see INDEX ON Syntax in CREATE INDEX.