documentation/query/sql/create-view.md
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.
CREATE [ OR REPLACE ] VIEW [ IF NOT EXISTS ] view_name AS ( query )
| Parameter | Description |
|---|---|
IF NOT EXISTS | Prevents error if view already exists |
OR REPLACE | Replaces existing view or creates new one |
view_name | Name of the view (case-insensitive, Unicode supported) |
query | SELECT statement defining the view |
CREATE VIEW my_view AS (
SELECT ts, symbol, price FROM trades
)
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
)
CREATE VIEW high_value_trades AS (
SELECT ts, symbol, price, quantity
FROM trades
WHERE price * quantity > 10000
)
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
)
CREATE VIEW all_markets AS (
SELECT ts, symbol, price FROM nyse_trades
UNION ALL
SELECT ts, symbol, price FROM nasdaq_trades
)
CREATE VIEW IF NOT EXISTS price_view AS (
SELECT symbol, last(price) as price FROM trades SAMPLE BY 1h
)
CREATE OR REPLACE VIEW price_view AS (
SELECT symbol, last(price) as price, ts FROM trades SAMPLE BY 1h
)
By default, DECLARE variables are non-overridable. Users querying the view
cannot change the parameter value:
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
Use the OVERRIDABLE keyword to allow users to override the parameter at query
time:
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
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
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
CREATE VIEW 日本語ビュー AS (SELECT * FROM trades)
CREATE VIEW Részvény_árak AS (SELECT * FROM prices)
When a view's result doesn't have an obvious designated timestamp, you can specify one:
CREATE VIEW with_timestamp AS (
(SELECT ts, value FROM my_view ORDER BY ts) timestamp(ts)
)
| Error | Cause |
|---|---|
view already exists | View exists and IF NOT EXISTS not specified |
table does not exist | Referenced table doesn't exist |
Invalid column | Column in query doesn't exist |
circular dependency detected | View would create circular reference |
variable is not overridable | Attempted to override a non-OVERRIDABLE DECLARE variable |
View names follow the same rules as table names:
CREATE VIEW 'select' AS (...) -- Quoted reserved word
CREATE VIEW 'My View' AS (...) -- Quoted name with spaces
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';