CREATE VIEW (DLT)

Constructs a virtual table with no physical data based on the result-set of a SQL query in your DLT pipelines.

Syntax

The following describes the syntax for declaring views with SQL:

CREATE VIEW view_name
  [ COMMENT view_comment ]
  [ TBLPROPERTIES ]
  AS query

Parameters

• view_name

  The name for the view. The name must be unique within the catalog and schema targeted by the pipeline.

• view_comment

  An optional description for the view.

• TBLPROPERTIES

  An optional list of table properties for the table.

• query

  A query that constructs the view from base tables or other views.

Required permissions

The run-as user for the pipeline must have the following permissions to be able to create a view:

• SELECT privilege on the base tables referenced by the view.
• USE CATALOG privilege on the parent catalog and the USE SCHEMA privilege on the parent schema.
• CREATE TABLE privilege on the schema for the view.

For a user to be able to update the view within the pipeline, they must have the following permissions:

• USE CATALOG privilege on the parent catalog and the USE SCHEMA privilege on the parent schema.
• MANAGE permission for the view.
• SELECT privileges on the base tables referenced by the view.

For a user to be able to query the resulting view, they must have the following permissions:

• USE CATALOG privilege on the parent catalog and the USE SCHEMA privilege on the parent schema.
• SELECT privilege on the view.

Limitations

• CREATE VIEW is only available in pipelines that support the default publishing mode. Pipelines that use the LIVE schema (legacy) are not supported. For more information, see LIVE schema (legacy).
• The pipeline must be a Unity Catalog pipeline.
• Expectations, in the form of CONSTRAINT clauses, are not supported.
• Views cannot have streaming queries, or be used as a streaming source for a DLT pipeline.

Examples

SQL

-- Create a view from an external data source
CREATE VIEW taxi_raw AS SELECT *
  FROM read_files("/databricks-datasets/nyctaxi/sample/json/");

-- Use a view to create a filtered view:
CREATE VIEW taxi_silver AS SELECT *
  FROM taxi_raw
  WHERE distance > 0.0;