Skip to main content

CREATE VIEW

Create or replace a view.

Syntax
CREATE [ OR REPLACE ] VIEW <view_name> AS
<select_statement> FROM <table_name>

Parameters

<view_name> String

The path of the view that you want to create. The name of the view should be unique and cannot include the following special characters: /, :, [, or ].


<select_statement> String

The query used to populate the view.

note

If the query refers to a table in a remote catalog, then that table must be fully qualified with an AT specification. For example:

CREATE VIEW StudentCatalog.first quarter
AS SELECT * FROM StudentCatalog.studentName
JOIN EnrollmentCatalog.sourceName AT BRANCH firstQuarter
ON TRUE

[ OR REPLACE ] String   Optional

If specified, any table/view with the same name will be replaced. This is primarily used to create new tables/views with security policies applied for restricted access. You cannot specify this parameter with the IF NOT EXISTS qualifier.


<table_name> String

The name of the object. Object names within a project must be unique, cannot conflict with system-defined objects, and are case-insensitive.


<column_name> String

The unique name of the column. Multiple columns may be specified, provided they include their data type and column-name/data-type pairs are separated by commas.

Examples

Create a view
CREATE VIEW demo.example_view AS
SELECT *
FROM "oracle_tpch".DREMIO.JOBS
Create a view from a specified tag
CREATE VIEW demo.example_view AS
SELECT *
FROM "oracle_tpch".DREMIO.JOBS AT TAG Jan2020
Replace view if it already exists or create a new one
CREATE OR REPLACE VIEW demo.example_view AS
SELECT *
FROM "oracle_tpch".DREMIO.INVENTORY
Create a view from a specified commit
CREATE VIEW demo.example_view AS
SELECT *
FROM "oracle_tpch".DREMIO.JOBS AT COMMIT "ff2fe50fef5a030c4fc8e61b252bdc33c72e2b6f929d813833d998b8368302e2"
Creating a view as SELECT * from another table
CREATE VIEW myAmazonS3Source.myFolder.myTable
AS SELECT * FROM myAmazonS3Source.anotherFolder.anotherTable