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 theUSE SCHEMA
andCREATE 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 youUSE SCHEMA
andCREATE 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 asUSE CATALOG
on the catalog andUSE 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 What is Delta Sharing?.
To read a view, the permissions required depend on the compute type, Databricks Runtime version, and access mode:
For single-user compute resources on Databricks Runtime 15.4 and above, shared compute resources, and SQL warehouses, you need
SELECT
on the view itself,USE CATALOG
on its parent catalog, andUSE SCHEMA
on its parent schema.Note
If you are using a single-user compute resource on Databricks Runtime 15.4 LTS and above, you must also verify that your workspace is enabled for serverless compute, which runs the data filtering functionality that supports view access without requiring access to the view's underlying tables and views. You might therefore be charged for serverless compute resources when you use single-user compute to query views. See Fine-grained access control on single-user compute. Data filtering using Databricks Runtime 15.4 and serverless compute is in Public Preview.
For single-user compute resources on Databricks Runtime 15.3 and below, you must also have
SELECT
on all tables and views that the view references, in addition toUSE CATALOG
on their parent catalogs andUSE SCHEMA
on their parent schemas.
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
SELECT
user_id,
email,
country,
product,
total
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;