CREATE MATERIALIZED VIEW

Applies to: check marked yes 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 ]
  [ table_constraints ]
  [ view_clauses ]
  AS query

column_list
   ( { column_alias [ COMMENT column_comment | column_constraint ] [...] } [, ...] [ , table_constraint ] [...])

table_constraints
   ( table_constraint [, ...] )

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 or OR REFRESH.
view_name

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.
- column_alias
  
  The column aliases must be unique.
- column_comment
  
  An optional STRING literal describing the column alias.
- column_constraint
  
  Preview
  
  This feature is in Public Preview.
  
  Adds an informational primary key or informational foreign key constraint to the column in a materialized view. Constraints are not supported for materialized views in the hive_metastore catalog.
table_constraint

Preview

This feature is in Public Preview.

Adds an informational primary key or informational foreign key constraints to a materialized view. Constraints are not supported for materialized views in the hive-metastore catalog.
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.
- PARTITIONED BY
  
  An optional list of columns of the table to partition the table by.
- COMMENT view_comment
  
  A STRING literal to describe the table.
- TBLPROPERTIES
  
  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. If AT TIME ZONE is absent, the session time zone is used. If AT TIME ZONE is absent and the session time zone is not set, an error is thrown. SCHEDULE is semantically equivalent to SCHEDULE 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.
USE CATALOG privilege on the parent catalog and the USE SCHEMA privilege on the parent schema.
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:

USE CATALOG privilege on the parent catalog and the USE SCHEMA privilege on the parent schema.
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 only NULL values remain in that column - the materialized view’s resultant aggregate value returns zero instead of NULL.
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 and VACUUM 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;

-- Create a materialized view with a column constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    member_id NOT NULL PRIMARY KEY,
    full_name,
    movie_title
  )
  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 table constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    member_id NOT NULL,
    full_name,
    movie_title,
    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 a materialized view without column list
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    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;