For AI agents: the complete documentation index is at llms.txt. Every page is also available as markdown by appending .md to its URL, or by sending an Accept: text/markdown request header.

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 )
    [ WITH AUDIT ] [ OWNED BY owner_name ]

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
WITH AUDITEnterprise only. Records every read of the view. See WITH AUDIT
OWNED BYEnterprise only. Assigns the view's owner. See OWNED BY

WITH AUDIT and OWNED BY may appear in either order.

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​

By default, DECLARE variables are non-overridable. Users querying the view cannot change the parameter value:

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

Query uses the default parameter value:

SELECT * FROM filtered_trades
-- Uses @min_price = 100

Attempting to override a non-overridable parameter will fail:

-- This fails with "variable is not overridable: @min_price"
DECLARE @min_price := 500 SELECT * FROM filtered_trades

DECLARE with OVERRIDABLE​

Use the OVERRIDABLE keyword to allow users to override the parameter at query time:

Create view with overridable parameter
CREATE VIEW flexible_view AS (
  DECLARE OVERRIDABLE @min_value := 0
  SELECT * FROM trades WHERE value >= @min_value
)

Users can now override the parameter:

-- Override the default value
DECLARE @min_value := 100 SELECT * FROM flexible_view

Multiple overridable parameters​

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

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

Mixed overridable and non-overridable parameters​

View with mixed parameter types
CREATE VIEW mixed_params AS (
  DECLARE @fixed := 5, OVERRIDABLE @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: variable is not overridable: @fixed

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
circular dependency detectedView would create circular reference
variable is not overridableAttempted to override a non-OVERRIDABLE DECLARE variable

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';

WITH AUDIT (Enterprise)​

WITH AUDIT makes the view an audited view: every read of it records a row in sys.view_audit, with the principal, the time, and the resolved values of the variables the view declares AUDITED.

Create an audited view
CREATE VIEW trades_by_symbol AS (
  DECLARE OVERRIDABLE AUDITED @symbols := ('BTC-USDT', 'ETH-USDT')
  SELECT timestamp, symbol, price, amount
  FROM trades
  WHERE symbol IN @symbols
) WITH AUDIT;

Creating a view WITH AUDIT requires the AUDIT VIEW permission in addition to CREATE VIEW. The view stays audited through ALTER VIEW and CREATE OR REPLACE VIEW, which do not accept WITH AUDIT for an existing view. To audit an existing view, drop it and create it again.

See also​