CREATE MATERIALIZED VIEW

  • Article

Applies to: check marked yes 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 or OR REPLACE.

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

      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 with column_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.

    • MASK clause

      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.

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

    • 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 the USE 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 the USE 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 the USE 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 and IS_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 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
  • NOT NULL must be manually specified along with PRIMARY KEY in order to be a valid statement.
  • Materialized views do not support identity columns or surrogate keys.
  • Materialized views do not support OPTIMIZE and VACUUM 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;