Skip to main content

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 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 Catalog 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.

Change the owner of a materialized view​

You can change the owner of a materialized view if you are a both a metastore admin and a workspace admin. Materialized views automatically create and use DLT pipelines to process changes. Use the following steps to change a materialized views owner:

  • Open the materialized view in Catalog Explorer, then on the Overview tab, click See refresh details. This opens the pipeline details for the pipeline that manages the materialized view.
  • Click Share. The Permissions Settings dialog appears. You must be a metastore and workspace admin to edit these settings.
  • Click x to the right of the current owner's name to remove the current owner.
  • Start typing to filter the list of available users. Click the user who should be the new pipeline owner.
  • Click Save to save your changes and close the dialog.

All pipeline assets, including materialized views defined in the pipeline, are owned by the new pipeline owner. All future updates 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.

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.

Grant privileges to a materialized view​

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

SQL
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:

SQL
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:

SQL
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:

SQL
REVOKE SELECT ON mv_name FROM read_only_user;

Set the runtime channel​

Materialized views created using SQL warehouses are automatically refreshed using a DLT pipeline. DLT pipelines use the runtime in the current channel by default. See DLT 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 DLT 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:

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

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

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