CREATE VIEW

Create or replace a view.

CREATE VIEW will parse and validate the SQL but does not fully plan the query or re-validate access control for any nested views. Full query planning and access control are only performed when the view is queried. If the view requires schema learning, the schema might be outdated at creation time but it will automatically update the first time the view is queried through the Dremio console.

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