CREATE VIEW

Creates a view, which is useful to filter, focus, and simplify a data set for querying. Views provide a level of abstraction that can make subqueries easier to write, especially for commonly referenced subsets of data. View results are not stored for future usage. Each time a query references a view, the view runs its SELECT query. For this reason, views do not provide a performance advantage. Consider using a materialized common table expression (CTE) as an alternative. For more information, see Materialized common table expressions. The optional IF NOT EXISTS and OR REPLACE clauses are mutually exclusive. They specify behavior if a view with the same name already exists. If neither clause is specified, an error occurs if a view with the same <view_name> already exists.

Syntax

CREATE VIEW [IF NOT EXISTS] <view_name> [(<column_list>)]
AS SELECT <select_statement>

—OR—

CREATE [OR REPLACE] VIEW <view_name> [(<column_list>)]
AS SELECT <select_statement>

Parameters

Parameter	Description
`IF NOT EXISTS`	Specifies that an existing view with `<view_name>` will not be replaced with this view definition. This is the default behavior, but this clause suppresses the error message that would otherwise occur, which is useful for scripted and programmatic implementations.
`OR REPLACE`	Specifies that an existing view with `<view_name>` will be replaced with this view definition.
`<view_name>`	An identifier that specifies the name of the view. This name must be unique within the database.
`<column_list>`	An optional list of column names to be used for columns of the view. If not specified, the column names are deduced from the query.
`<select_statement>`	The select statement for creating the view.

Examples

Filtering a table

CREATE VIEW fob_shipments
AS SELECT   l_shipmode,
            l_shipdate,
            l_linestatus,
            l_orderkey,
FROM    lineitem
WHERE   l_shipdate > 1990-01-01
AND     l_shipmode = 'FOB';

Wrapping an Iceberg table

You can wrap a READ_ICEBERG TVF call in a view for easier querying of Iceberg tables:

CREATE VIEW lineitem AS SELECT * FROM READ_ICEBERG(
    URL => 's3://firebolt-core-us-east-1/test_data/tpch/iceberg/tpch.db/lineitem'
  );

SELECT l_shipmode, l_extendedprice
FROM lineitem
WHERE l_shipdate > '1995-01-01'
LIMIT 10;

For a complete guide, see Simplifying queries with views.

CREATE TABLE CLONE

DROP ACCOUNT

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Examples

Filtering a table

Wrapping an Iceberg table

​Syntax

​Parameters

​Examples

​Filtering a table

​Wrapping an Iceberg table

Syntax

Parameters

Examples

Filtering a table

Wrapping an Iceberg table