Create views

This article shows how to create views in Unity Catalog.

A view is a read-only object composed from one or more tables and views in a metastore. It resides in the third layer of Unity Catalog’s three-level namespace. A view can be created from tables and other views in multiple schemas and catalogs.

Dynamic views can be used to provide row- and column-level access control, in addition to data masking.

Example syntax for creating a view:

CREATE VIEW main.default.experienced_employee
  (id COMMENT 'Unique identification number', Name)
  COMMENT 'View for experienced employees'
AS SELECT id, name
   FROM all_employee
   WHERE working_years > 5;

Note

Views might have different execution semantics if they’re backed by data sources other than Delta tables. Databricks recommends that you always define views by referencing data sources using a table or view name. Defining views against datasets by specifying a path or URI can lead to confusing data governance requirements.

Requirements

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>;

spark.sql("CREATE VIEW <catalog-name>.<schema-name>.<view-name> AS "
  "SELECT <query>")

library(SparkR)

sql(paste("CREATE VIEW <catalog-name>.<schema-name>.<view-name> AS ",
  "SELECT <query>",
  sep = ""))

spark.sql("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;

spark.sql("CREATE VIEW sales_metastore.sales.sales_redacted AS "
  "SELECT "
  "  user_id, "
  "  email, "
  "  country, "
  "  product, "
  "  total "
  "FROM sales_metastore.sales.sales_raw")

library(SparkR)

sql(paste("CREATE VIEW sales_metastore.sales.sales_redacted AS ",
  "SELECT ",
  "  user_id, ",
  "  email, ",
  "  country, ",
  "  product, ",
  "  total ",
  "FROM sales_metastore.sales.sales_raw",
  sep = ""))

spark.sql("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.

Create a dynamic view

In Unity Catalog, you can use dynamic views to configure fine-grained access control, including:

  • Security at the level of columns or rows.

  • Data masking.

Note

Fine-grained access control using dynamic views is not available on clusters with Single User access mode.

Unity Catalog introduces the following functions, which allow you to dynamically limit which users can access a row, column, or record in a view:

  • current_user(): Returns the current user’s email address.

  • is_account_group_member(): Returns TRUE if the current user is a member of a specific account-level group. Recommended for use in dynamic views against Unity Catalog data.

  • is_member(): Returns TRUE if the current user is a member of a specific workspace-level group. This function is provided for compatibility with the existing Hive metastore. Avoid using it with views against Unity Catalog data, because it does not evaluate account-level group membership.

Databricks recommends that you do not grant users the ability to read the tables and views referenced in the view.

The following examples illustrate how to create dynamic views in Unity Catalog.

Column-level permissions

With a dynamic view, you can limit the columns a specific user or group can access. In the following example, only members of the auditors group can access email addresses from the sales_raw table. During query analysis, Apache Spark replaces the CASE statement with either the literal string REDACTED or the actual contents of the email address column. Other columns are returned as normal. This strategy has no negative impact on the query performance.

-- Alias the field 'email' to itself (as 'email') to prevent the
-- permission logic from showing up directly in the column name results.
CREATE VIEW sales_redacted AS
SELECT
  user_id,
  CASE WHEN
    is_account_group_member('auditors') THEN email
    ELSE 'REDACTED'
  END AS email,
  country,
  product,
  total
FROM sales_raw

# Alias the field 'email' to itself (as 'email') to prevent the
# permission logic from showing up directly in the column name results.
spark.sql("CREATE VIEW sales_redacted AS "
  "SELECT "
  "  user_id, "
  "  CASE WHEN "
  "    is_account_group_member('auditors') THEN email "
  "  ELSE 'REDACTED' "
  "  END AS email, "
  "  country, "
  "  product, "
  "  total "
  "FROM sales_raw")

library(SparkR)

# Alias the field 'email' to itself (as 'email') to prevent the
# permission logic from showing up directly in the column name results.
sql(paste("CREATE VIEW sales_redacted AS ",
  "SELECT ",
  "  user_id, ",
  "  CASE WHEN ",
  "    is_account_group_member('auditors') THEN email ",
  "  ELSE 'REDACTED' ",
  "  END AS email, ",
  "  country, ",
  "  product, ",
  "  total ",
  "FROM sales_raw",
  sep = ""))

// Alias the field 'email' to itself (as 'email') to prevent the
// permission logic from showing up directly in the column name results.
spark.sql("CREATE VIEW sales_redacted AS " +
  "SELECT " +
  "  user_id, " +
  "  CASE WHEN " +
  "    is_account_group_member('auditors') THEN email " +
  "  ELSE 'REDACTED' " +
  "  END AS email, " +
  "  country, " +
  "  product, " +
  "  total " +
  "FROM sales_raw")

Row-level permissions

With a dynamic view, you can specify permissions down to the row or field level. In the following example, only members of the managers group can view transaction amounts when they exceed $1,000,000. Matching results are filtered out for other users.

 CREATE VIEW sales_redacted AS
 SELECT
   user_id,
   country,
   product,
   total
 FROM sales_raw
 WHERE
   CASE
     WHEN is_account_group_member('managers') THEN TRUE
     ELSE total <= 1000000
   END;

 spark.sql("CREATE VIEW sales_redacted AS "
   "SELECT "
   "  user_id, "
   "  country, "
   "  product, "
   "  total "
   "FROM sales_raw "
   "WHERE "
   "CASE "
   "  WHEN is_account_group_member('managers') THEN TRUE "
   "  ELSE total <= 1000000 "
   "END")

 library(SparkR)

 sql(paste("CREATE VIEW sales_redacted AS ",
   "SELECT ",
   "  user_id, ",
   "  country, ",
   "  product, ",
   "  total ",
   "FROM sales_raw ",
   "WHERE ",
   "CASE ",
   "  WHEN is_account_group_member('managers') THEN TRUE ",
   "  ELSE total <= 1000000 ",
   "END",
   sep = ""))

 spark.sql("CREATE VIEW sales_redacted AS " +
   "SELECT " +
   "  user_id, " +
   "  country, " +
   "  product, " +
   "  total " +
   "FROM sales_raw " +
   "WHERE " +
   "CASE " +
   "  WHEN is_account_group_member('managers') THEN TRUE " +
   "  ELSE total <= 1000000 " +
   "END")

Data masking

Because views in Unity Catalog use Spark SQL, you can implement advanced data masking by using more complex SQL expressions and regular expressions. In the following example, all users can analyze email domains, but only members of the auditors group can view a user’s entire email address.

-- The regexp_extract function takes an email address such as
-- user.x.lastname@example.com and extracts 'example', allowing
-- analysts to query the domain name.

CREATE VIEW sales_redacted AS
SELECT
  user_id,
  region,
  CASE
    WHEN is_account_group_member('auditors') THEN email
    ELSE regexp_extract(email, '^.*@(.*)$', 1)
  END
  FROM sales_raw

# The regexp_extract function takes an email address such as
# user.x.lastname@example.com and extracts 'example', allowing
# analysts to query the domain name.

spark.sql("CREATE VIEW sales_redacted AS "
  "SELECT "
  "  user_id, "
  "  region, "
  "  CASE "
  "    WHEN is_account_group_member('auditors') THEN email "
  "    ELSE regexp_extract(email, '^.*@(.*)$', 1) "
  "  END "
  "  FROM sales_raw")

library(SparkR)

# The regexp_extract function takes an email address such as
# user.x.lastname@example.com and extracts 'example', allowing
# analysts to query the domain name.

sql(paste("CREATE VIEW sales_redacted AS ",
  "SELECT ",
  "  user_id, ",
  "  region, ",
  "  CASE ",
  "    WHEN is_account_group_member('auditors') THEN email ",
  "    ELSE regexp_extract(email, '^.*@(.*)$', 1) ",
  "  END ",
  "  FROM sales_raw",
  sep = ""))

// The regexp_extract function takes an email address such as
// user.x.lastname@example.com and extracts 'example', allowing
// analysts to query the domain name.

spark.sql("CREATE VIEW sales_redacted AS " +
  "SELECT " +
  "  user_id, " +
  "  region, " +
  "  CASE " +
  "    WHEN is_account_group_member('auditors') THEN email " +
  "    ELSE regexp_extract(email, '^.*@(.*)$', 1) " +
  "  END " +
  "  FROM sales_raw")

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;