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.
- Click
Catalog in the sidebar.
- In the Catalog Explorer tree at the left, open the catalog and select the schema where your materialized view is located.
- 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:
GRANT <privilege_type> ON <mv_name> TO <principal>;
The privilege_type can be:
SELECT
- the user canSELECT
the materialized view.REFRESH
- the user canREFRESH
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;
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:
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');