CREATE MATERIALIZED VIEW
Applies to: Databricks SQL
A materialized view is a view where precomputed results are available for query and can be updated to reflect changes in the input. Each time a materialized view is refreshed, query results are recalculated to reflect changes in upstream datasets. All materialized views are backed by a DLT pipeline. You can refresh materialized views manually or on a schedule.
To learn more about how to perform a manual refresh, see REFRESH (MATERIALIZED VIEW or STREAMING TABLE).
To learn more about how to schedule a refresh, see Examples or ALTER MATERIALIZED VIEW.
Syntax
{ CREATE OR REPLACE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] }
view_name
[ column_list ]
[ view_clauses ]
AS query
column_list
( { column_name column_type column_properties } [, ...]
[ , table_constraint ] [...])
column_properties
{ NOT NULL | COMMENT column_comment | column_constraint | MASK clause } [ ... ]
view_clauses
{ PARTITIONED BY (col [, ...]) |
COMMENT view_comment |
TBLPROPERTIES clause |
SCHEDULE [ REFRESH ] CRON cron_string [ AT TIME ZONE timezone_id ] |
WITH { ROW FILTER clause } } [...]
Parameters
REPLACE
If specified, replaces the view and its content if it already exists.
IF NOT EXISTS
Creates the view if it does not exist. If a view by this name already exists, the
CREATE MATERIALIZED VIEW
statement is ignored.You may specify at most one of
IF NOT EXISTS
orOR REPLACE
.-
The name of the newly created view. The fully qualified view name must be unique.
column_list
Optionally labels the columns in the query result of the view. If you provide a column list the number of column aliases must match the number of expressions in the query. If no column list is specified, aliases are derived from the body of the view.
-
The column names must be unique and map to the output columns of the query.
column_type
Specifies the column’s data type. Not all data types supported by Azure Databricks are supported by materialized views.
column_comment
An optional
STRING
literal describing the column name. This option must be specified along withcolumn_type
. If the column type is not specified, the column comment is skipped.column_constraint
Adds an informational primary key or informational foreign key constraint to the column in a materialized view. If the column type is not specified, the column constraint is skipped.
-
Important
This feature is in Public Preview.
Adds a column mask function to anonymize sensitive data. All future queries from that column will receive the result of evaluating that function over the column in place of the column’s original value. This can be useful for fine-grained access control purposes wherein the function can inspect the identity and/or group memberships of the invoking user in order to decide whether to redact the value. If the column type is not specified, the column mask is skipped.
-
table_constraint
Adds an informational primary key or informational foreign key constraint to the table in a materialized view. If the column type is not specified, the table constraint is skipped.
view_clauses
Optionally specify partitioning, comments, user defined properties, and a refresh schedule for the new materialized view. Each sub clause may only be specified once.
-
An optional list of columns of the table to partition the table by.
COMMENT view_comment
A
STRING
literal to describe the table.-
Optionally sets one or more user defined properties.
SCHEDULE [ REFRESH ] CRON cron_string [ AT TIME ZONE timezone_id ]
If provided, schedules the streaming table or the materialized view to refresh its data with the given quartz cron schedule. Only time_zone_values are accepted.
AT TIME ZONE LOCAL
is not supported. IfAT TIME ZONE
is absent, the session time zone is used. IfAT TIME ZONE
is absent and the session time zone is not set, an error is thrown.SCHEDULE
is semantically equivalent toSCHEDULE REFRESH
.WITH ROW FILTER clause
Important
This feature is in Public Preview.
Adds a row filter function to the table. All future queries from that table will receive subset of its rows for which the function evaluates to boolean TRUE. This can be useful for fine-grained access control purposes wherein the function can inspect the identity and/or group memberships of the invoking user in order to decide whether to filter certain rows.
-
AS query
A query that constructs the view from base tables or other views.
Required permissions
The user who creates a materialized view (MV) is the MV owner and needs to have the following permissions:
SELECT
privilege over the base tables referenced by the MV.USE CATALOG
privilege on the parent catalog and theUSE SCHEMA
privilege on the parent schema.CREATE
privilege on the schema for the MV.
For a user to be able to refresh the MV, they require:
USE CATALOG
privilege on the parent catalog and theUSE SCHEMA
privilege on the parent schema.- Ownership of the MV or
REFRESH
privilege on the MV. - The owner of the MV must have the
SELECT
privilege over the base tables referenced by the MV.
For a user to be able to query the MV, they require:
USE CATALOG
privilege on the parent catalog and theUSE SCHEMA
privilege on the parent schema.SELECT
privilege over the materialized view.
Row filters and column masks
Important
This feature is in Public Preview.
Row filters let you specify a function that applies as a filter whenever a table scan fetches rows. These filters ensure that subsequent queries only return rows for which the filter predicate evaluates to true.
Column masks let you mask a column’s values whenever a table scan fetches rows. All future queries involving that column will receive the result of evaluating the function over the column, replacing the column’s original value.
For more information on how to use row filters and column masks, see Filter sensitive table data using row filters and column masks.
Managing Row Filters and Column Masks
Row filters and column masks on materialized views should be added through the CREATE
statement.
Behavior
- Refresh as Definer: When the
REFRESH MATERIALIZED VIEW
statement refreshes a materialized view, row filter functions run with the definer’s rights (as the table owner). This means the table refresh uses the security context of the user who created the materialized view. - Query: While most filters run with the definer’s rights, functions that check user context (such as
CURRENT_USER
andIS_MEMBER
) are exceptions. These functions run as the invoker. This approach enforces user-specific data security and access controls based on the current user’s context. - When creating materialized views over source tables that contain row filters and column masks, the refresh of the materialized view is always a full refresh. A full refresh reprocesses all data available in the source with the latest definitions. This ensures that security policies on the source tables are evaluated and applied with the most up-to-date data and definitions.
Observability
Use DESCRIBE EXTENDED
, INFORMATION_SCHEMA
, or the Catalog Explorer to examine the existing row filters and column masks that apply to a given materialized view. This functionality allows users to audit and review data access and protection measures on materialized views.
Limitations
- When a materialized view with a
sum
aggregate over a NULL-able column has the last non-NULL value removed from that column - and thus onlyNULL
values remain in that column - the materialized view’s resultant aggregate value returns zero instead ofNULL
. - Column-reference does not require an alias. Non-column reference expressions require an alias, as in the following example:
- Allowed:
SELECT col1, SUM(col2) AS sum_col2 FROM t GROUP BY col1
- Not Allowed:
SELECT col1, SUM(col2) FROM t GROUP BY col1
- Allowed:
NOT NULL
must be manually specified along withPRIMARY KEY
in order to be a valid statement.- Materialized views do not support identity columns or surrogate keys.
- Materialized views do not support
OPTIMIZE
andVACUUM
commands. Maintenance happens automatically. - Materialized views do not support expectations to define data quality constraints.
Examples
-- Create a materialized view if it doesn't exist
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies
AS SELECT mo.member_id, mb.full_name, mo.movie_title
FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;
-- Create and schedule a materialized view to be refreshed daily at midnight.
-- Note: All columns in a GROUP BY need to be explicitly aliased
> CREATE MATERIALIZED VIEW daily_sales
COMMENT 'Daily sales numbers'
SCHEDULE CRON '0 0 0 * * ? *'
AS SELECT date AS date, sum(sales) AS sumOfSales
FROM table1
GROUP BY date;
-- Create a materialized view with a table constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
member_id int NOT NULL,
full_name string,
movie_title string,
CONSTRAINT movie_pk PRIMARY KEY(member_id)
)
AS SELECT mo.member_id, mb.full_name, mo.movie_title
FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;
-- Create or replace the materialized view to remove the table constraint and add a partition
> CREATE OR REPLACE MATERIALIZED VIEW subscribed_movies
PARTITIONED BY (member_id)
AS SELECT mo.member_id, mb.full_name, mo.movie_title
FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;
-- Create a materialized view with a row filter and a column mask
> CREATE MATERIALIZED VIEW masked_view (
id int,
name string,
region string,
ssn string MASK catalog.schema.ssn_mask_fn
)
WITH ROW FILTER catalog.schema.us_filter_fn ON (region)
AS SELECT id, name, region, ssn
FROM employees;
Related articles
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for