Create and manage views

This article shows how to create views in Unity Catalog. See What is a view?.

Required permissions

To create a view:

  • You must have the USE CATALOG permission on the parent catalog and the USE SCHEMA and CREATE TABLE permissions on the parent schema. A metastore admin or the catalog owner can grant you all of these privileges. A schema owner can grant you USE SCHEMA and CREATE TABLE privileges on the schema.

  • You must be able to read the tables and views referenced in the view (SELECT on the table or view, as well as USE CATALOG on the catalog and USE SCHEMA on the schema).

  • If a view references tables in the workspace-local Hive metastore, the view can be accessed only from the workspace that contains the workspace-local tables. For this reason, Databricks recommends creating views only from tables or views that are in the Unity Catalog metastore.

  • You cannot create a view that references a view that has been shared with you using Delta Sharing. See Share data and AI assets securely using Delta Sharing.

To read a view, the permissions required depend on the compute type and access mode:

  • For shared clusters and SQL warehouses, you need SELECT on the view itself, USE CATALOG on its parent catalog, and USE SCHEMA on its parent schema.

  • For single-user clusters, you must also have SELECT on all tables and views that the view references, in addition to USE CATALOG on their parent catalogs and USE SCHEMA on their parent schemas.

To create or read dynamic views:

  • Requirements for dynamic views are the same as those listed in the preceding sections, except that you must use a shared cluster or SQL warehouse to create or read a dynamic view. You cannot use single-user clusters.

Create a view

To create a view, run the following SQL command. Items in brackets are optional. Replace the placeholder values:

  • <catalog-name>: The name of the catalog.

  • <schema-name>: The name of the schema.

  • <view-name>: A name for the view.

  • <query>: The query, columns, and tables and views used to compose the view.

CREATE VIEW <catalog-name>.<schema-name>.<view-name> AS
SELECT <query>;

For example, to create a view named sales_redacted from columns in the sales_raw table:

CREATE VIEW sales_metastore.sales.sales_redacted AS
FROM sales_metastore.sales.sales_raw;

You can also create a view by using the Databricks Terraform provider and databricks_table. You can retrieve a list of view full names by using databricks_views.

Drop a view

You must be the view’s owner to drop a view. To drop a view, run the following SQL command:

DROP VIEW IF EXISTS catalog_name.schema_name.view_name;