CREATE MATERIALIZED VIEW
Applies to: Databricks SQL
Preview
This feature is in Public Preview. To sign up for access, fill out this form.
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, on a schedule, or by scheduling the DLT pipeline in which they’re contained.
Syntax
CREATE MATERIALIZED VIEW [IF NOT EXISTS]
view_name
[ column_list ]
[ view_clauses ]
AS query
column_list
( { column_alias [ COMMENT column_comment ] } [, ...] )
view_clauses
{ PARTITIONED BY (col [, ...]) |
COMMENT view_comment |
TBLPROPERTIES clause |
SCHEDULE [ REFRESH ] CRON cron_string [ AT TIME ZONE timezone_id ] } [...]
Parameters
IF NOT EXISTS
Creates the view if it does not exist. If a view by this name already exists, the
CREATE VIEW
statement is ignored.You may specify at most one of
IF NOT EXISTS
orOR REFRESH
.-
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 aliases must be unique.
column_comment
An optional
STRING
literal describing the column alias.
-
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
.You cannot use the
SCHEDULE
syntax in a Delta Live Tables pipeline definition.
-
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.USAGE
privilege on the catalog and the schema that will contain it.CREATE
privilege on the schema for the MV.
Only the owner can REFRESH
the MV.
For a user to be able to query the MV, they require:
USAGE
privilege on the catalog and the schema that contains the MV.SELECT
privilege over the materialized view.
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
Materialized views do not support identity columns or surrogate keys.
Materialized views do not support ad-hoc
OPTIMIZE
andVACUUM
commands. Maintenance happens automatically.
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;