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 ]
  [ 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 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.

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

  • 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 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;