Training
Module
Create tables, views, and temporary objects - Training
This content is a part of Create tables, views, and temporary objects.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
This article shows how to create views in Unity Catalog. See What is a view?.
To create a view:
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.SELECT
on the table or view, as well as USE CATALOG
on the catalog and USE SCHEMA
on the schema).To read a view, the permissions required depend on the compute type, Databricks Runtime version, and access mode:
SELECT
on the view itself, USE CATALOG
on its parent catalog, and USE SCHEMA
on its parent schema. This applies to all compute types that support Unity Catalog, including SQL warehouses, clusters in shared access mode, and clusters in single user access mode on Databricks Runtime 15.4 and above.SELECT
on all tables and views that are referenced by the view, in addition to USE CATALOG
on their parent catalogs and USE SCHEMA
on their parent schemas.Note
If you’re using a single-user cluster on Databricks Runtime 15.4 LTS and above and you want to avoid the requirement to have SELECT
on the underlying tables and views, verify that your workspace is enabled for serverless compute.
Serverless compute handles data filtering, which allows access to a view without requiring permissions on its underlying tables and views. Be aware that you might incur serverless compute charges when you use single user compute to query views. For more information, see Fine-grained access control on single user compute.
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.
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;
Training
Module
Create tables, views, and temporary objects - Training
This content is a part of Create tables, views, and temporary objects.