CREATE VIEW

Creates a new view in the database. A view is a virtual table defined by a SQL SELECT statement that does not store data itself.

For more information on views, see the Views documentation.

Syntax​

CREATE [ OR REPLACE ] VIEW [ IF NOT EXISTS ] view_name AS ( query )

Parameters​

ParameterDescription
IF NOT EXISTSPrevents error if view already exists
OR REPLACEReplaces existing view or creates new one
view_nameName of the view (case-insensitive, Unicode supported)
querySELECT statement defining the view

Examples​

Basic view​

Create a simple view
CREATE VIEW my_view AS (
  SELECT ts, symbol, price FROM trades
)

View with aggregation​

Create a view with SAMPLE BY
CREATE VIEW hourly_ohlc AS (
  SELECT
    ts,
    symbol,
    first(price) as open,
    max(price) as high,
    min(price) as low,
    last(price) as close,
    sum(quantity) as volume
  FROM trades
  SAMPLE BY 1h
)

View with filtering​

Create a view with WHERE clause
CREATE VIEW high_value_trades AS (
  SELECT ts, symbol, price, quantity
  FROM trades
  WHERE price * quantity > 10000
)

View with JOIN​

Create a view with JOIN
CREATE VIEW enriched_trades AS (
  SELECT t.ts, t.symbol, t.price, m.company_name
  FROM trades t
  JOIN metadata m ON t.symbol = m.symbol
)

View with UNION​

Create a view with UNION
CREATE VIEW all_markets AS (
  SELECT ts, symbol, price FROM nyse_trades
  UNION ALL
  SELECT ts, symbol, price FROM nasdaq_trades
)

IF NOT EXISTS​

Create view only if it doesn't exist
CREATE VIEW IF NOT EXISTS price_view AS (
  SELECT symbol, last(price) as price FROM trades SAMPLE BY 1h
)

OR REPLACE​

Create or replace existing view
CREATE OR REPLACE VIEW price_view AS (
  SELECT symbol, last(price) as price, ts FROM trades SAMPLE BY 1h
)

Parameterized view with DECLARE​

Create a parameterized view
CREATE VIEW filtered_trades AS (
  DECLARE @min_price := 100
  SELECT ts, symbol, price FROM trades WHERE price >= @min_price
)

Query with default parameter:

SELECT * FROM filtered_trades
-- Uses @min_price = 100

Override parameter at query time:

DECLARE @min_price := 500 SELECT * FROM filtered_trades

DECLARE with CONST​

Create view with non-overridable parameter
CREATE VIEW secure_view AS (
  DECLARE CONST @min_value := 0
  SELECT * FROM trades WHERE value >= @min_value
)

Attempting to override a CONST parameter will fail:

-- This fails with "cannot override CONST variable: @min_value"
DECLARE @min_value := -100 SELECT * FROM secure_view

Multiple parameters​

View with multiple parameters
CREATE VIEW price_range AS (
  DECLARE @lo := 100, @hi := 1000
  SELECT ts, symbol, price FROM trades
  WHERE price >= @lo AND price <= @hi
)

-- Override multiple parameters
DECLARE @lo := 50, @hi := 200 SELECT * FROM price_range

Mixed CONST and non-CONST parameters​

View with mixed parameter types
CREATE VIEW mixed_params AS (
  DECLARE CONST @fixed := 5, @adjustable := 10
  SELECT * FROM data WHERE a >= @fixed AND b <= @adjustable
)

-- @adjustable can be overridden, @fixed cannot
DECLARE @adjustable := 20 SELECT * FROM mixed_params  -- OK
DECLARE @fixed := 0 SELECT * FROM mixed_params        -- ERROR

Unicode view name​

Create view with Unicode name
CREATE VIEW 日本語ビュー AS (SELECT * FROM trades)
CREATE VIEW Részvény_árak AS (SELECT * FROM prices)

Specifying timestamp column​

When a view's result doesn't have an obvious designated timestamp, you can specify one:

Create view with explicit timestamp
CREATE VIEW with_timestamp AS (
  (SELECT ts, value FROM my_view ORDER BY ts) timestamp(ts)
)

Errors​

ErrorCause
view already existsView exists and IF NOT EXISTS not specified
table does not existReferenced table doesn't exist
Invalid columnColumn in query doesn't exist
cycle detectedView would create circular reference

View naming​

View names follow the same rules as table names:

  • Case-insensitive
  • Unicode characters supported
  • Cannot be the same as an existing table or materialized view name
  • Reserved SQL keywords require quoting
Quoting reserved words
CREATE VIEW 'select' AS (...)  -- Quoted reserved word
CREATE VIEW "My View" AS (...)  -- Quoted name with spaces

OWNED BY (Enterprise)​

When a user creates a new view, they are automatically assigned all view level permissions with the GRANT option for that view. This behavior can be overridden using OWNED BY.

CREATE GROUP analysts;
CREATE VIEW trades_summary AS (
  SELECT ts, symbol, sum(quantity) as volume
  FROM trades
  SAMPLE BY 1h
)
OWNED BY analysts;

See also​