Share via

Configure materialized views in Databricks SQL

This article describes how to configure materialized views in Databricks SQL, including access control on the results. Most configuration can be done when you create the materialized view with the CREATE OR REPLACE MATERIALIZED VIEW statement, or after creation, with the ALTER TABLE statement.

Describe a materialized view

To retrieve the columns and data types for a materialized view, use the DESCRIBE statement. To retrieve the columns, data types, and metadata such as owner, location, creation time, and refresh status for a materialized view, use DESCRIBE EXTENDED. To submit a DESCRIBE statement, use the SQL editor in the Azure Databricks UI, the Databricks SQL CLI, or the Databricks SQL API.

Get detailed information about materialized views with Catalog Explorer

You can alternately use the Catalog Explorer to view details about a materialized view.

  1. Click Data icon. Catalog in the sidebar.
  2. In the Catalog Explorer tree at the left, open the catalog and select the schema where your materialized view is located.
  3. Open the Tables item under the schema you selected, and click the materialized view.

From here, you can use the tabs under the materialized view name to view and edit information about the materialized view, including:

  • Refresh status and schedule
  • See the pipeline details to see the history of refreshes, as well as detailed logs for each refresh. Click See refresh details from the Overview tab to see the pipeline details.
  • The table schema
  • Sample data (requires an active compute)
  • Permissions
  • Lineage, including tables and pipelines that this materialized view depends on
  • Insights into usage
  • Monitors that you have created for this materialized view

There are a few properties of the table that are not available in Catalog Explorer. For those properties, or to get the information programmatically, you can use the DESCRIBE EXTENDED command.

Update the definition of a materialized view

The query that specifies the materialized view is its definition. To change the definition of the materialized view, edit the query, or create a new CREATE OR REPLACE MATERIALIZED VIEW query with the same view name, and then run it. A full refresh is performed to update the materialized view, and further refreshes use the new definition.

Control access to materialized views

Materialized views support rich access controls to support data-sharing while avoiding exposing potentially private data. A materialized view owner or a user with the MANAGE privilege can grant SELECT privileges to other users. Users with SELECT access to the materialized view do not need SELECT access to the tables referenced by the materialized view. This access control enables data sharing while controlling access to the underlying data.

You can also modify the owner of a materialized view.

Grant privileges to a materialized view

To grant access to a materialized view, use the GRANT statement:

GRANT <privilege_type> ON <mv_name> TO <principal>;

The privilege_type can be:

  • SELECT - the user can SELECT the materialized view.
  • REFRESH - the user can REFRESH the materialized view. Refreshes are run using the owner's permissions.

The following example creates a materialized view and grants select and refresh privileges to users:

CREATE MATERIALIZED VIEW mv_name AS SELECT * FROM source_table;

-- Grant read-only access:
GRANT SELECT ON mv_name TO read_only_user;

-- Grand read and refresh access:
GRANT SELECT ON mv_name TO refresh_user;
GRANT REFRESH ON mv_name TO refresh_user;

Revoke privileges from a materialized view

To revoke access from a materialized view, use the REVOKE statement:

REVOKE privilege_type ON <mv_name> FROM principal;

When SELECT privileges on a source table are revoked from the materialized view owner or any other user who has been granted MANAGE or SELECT privileges on the materialized view, or the source table is dropped, the materialized view owner or user granted access is still able to query the materialized view. However, the following behavior occurs:

  • The materialized view owner or others who have lost access to a materialized view can no longer REFRESH that materialized view, and the materialized view will become stale.
  • If automated with a schedule, the next scheduled REFRESH fails or is not run.

The following example revokes the SELECT privilege from read_only_user:

REVOKE SELECT ON mv_name FROM read_only_user;

Change the owner of a materialized view

A user with MANAGE permissions on a materialized view defined in Databricks SQL can set a new owner through the Catalog Explorer. The new owner can be themselves or a service principal on which they have the Service Principal User role.

  1. From your Azure Databricks workspace, click Data icon. Catalog to open the Catalog Explorer.

  2. Select the materialized view that you want to update.

  3. In the right sidebar, under About this materialized view, find the Owner, and click Pencil icon. edit.

    Note

    If you get a message that tells you to update the owner by changing the Run as user in pipeline settings, then the materialized view is defined in Lakeflow Spark Declarative Pipelines, not Databricks SQL. The message includes a link to the pipeline settings, where you can change the Run as user.

  4. Select a new owner for the materialized view.

    Owners automatically have MANAGE and SELECT privileges on materialized views that they own. If you are setting a service principal as the owner for a materialized view that you own, and you do not explicitly have SELECT or MANAGE privileges on the materialized view, then this change would cause you to lose all access to the materialized view. In this case, you are prompted to explicitly provide those privileges.

    Select both Grant MANAGE and Grant SELECT privileges to provide those on Save.

  5. Click Save to change the owner.

The owner of the materialized view is updated. All future refreshes are run using the new owner's identity.

When the owner loses privileges to source tables

If you change the owner, and the new owner does not have access to the source tables (or SELECT privileges are revoked on the underlying source tables), users can still query the materialized view. However:

  • They cannot REFRESH the materialized view.
  • The next scheduled refresh of the materialized view will fail.

Losing access to the source data prevents updates, but doesn't immediately invalidate the existing materialized view from being read.

Set the runtime channel

Materialized views created using SQL warehouses are automatically refreshed using a pipeline. Pipelines use the runtime in the current channel by default. See Lakeflow Spark Declarative Pipelines release notes and the release upgrade process to learn about the release process.

Databricks recommends using the current channel for production workloads. New features are first released to the preview channel. You can set a pipeline to the preview channel to test new features by specifying preview as a table property. You can specify this property when you create the table or after the table is created using an ALTER statement.

The following code example shows how to set the channel to preview in a CREATE statement:

CREATE OR REPLACE MATERIALIZED VIEW sales
TBLPROPERTIES ('pipelines.channel' = 'preview')
  AS ...

To change the channel after creation, use the ALTER TABLE statement:

ALTER TABLE <table-name> SET TBLPROPERTIES ('pipelines.channel' = 'preview');
  • Last updated on