Skip to main content

ALTER MATERIALIZED VIEW

Applies to: check marked yes Databricks SQL

Alters metadata associated with the view.

Allows you to perform any of the following actions:

  • Add a schedule for refreshing an existing materialized view.
  • Alter an existing refresh schedule for a materialized view.
  • Drop the refresh schedule for a materialized view. If the schedule is dropped, the object needs to be refreshed manually to reflect the latest data.

To add or alter a comment on a view, use COMMENT ON.

Syntax​

ALTER MATERIALIZED VIEW view_name
  {
    { ADD | ALTER } schedule |
    DROP SCHEDULE |
    ALTER COLUMN clause |
    SET ROW FILTER clause |
    DROP ROW FILTER |
    SET TAGS clause |
    UNSET TAGS clause }

  schedule
    { SCHEDULE [ REFRESH ] schedule_clause |
      TRIGGER ON UPDATE [ AT MOST EVERY trigger_interval ] }

  schedule_clause
    { EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS } |
      CRON cron_string [ AT TIME ZONE timezone_id ] }

Parameters​

  • view_name

    The name of the materialized view to alter the definition of. The name must not include a temporal specification.

  • schedule

    Add or alter a SCHEDULE or TRIGGER statement on the materialized view.

    • SCHEDULE [ REFRESH ] schedule_clause

      • EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS }

        To schedule a refresh that occurs periodically, use EVERY syntax. If EVERY syntax is specified, the streaming table or materialized view is refreshed periodically at the specified interval based on the provided value, such as HOUR, HOURS, DAY, DAYS, WEEK, or WEEKS. The following table lists accepted integer values for number.

        Time unit

        Integer value

        HOUR or HOURS

        1 <= H <= 72

        DAY or DAYS

        1 <= D <= 31

        WEEK or WEEKS

        1 <= W <= 8

        note

        The singular and plural forms of the included time unit are semantically equivalent.

      • CRON cron_string [ AT TIME ZONE timezone_id ]

        To schedule a refresh using a quartz cron value. Valid 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.

    • TRIGGER ON UPDATE [ AT MOST EVERY trigger_interval ]

      Beta

      The TRIGGER ON UPDATE feature is in Beta.

      Sets the materialized view to refresh when an upstream data source is updated, at most once every minute. Set a value for AT MOST EVERY to require at least a minimum time between refreshes.

      The upstream data sources must be either external or managed Delta tables (including materialized views or streaming tables), or managed views whose dependencies are limited to supported table types.

      Enabling file events can make triggers more performant, and increases some of the limits on trigger updates.

      The trigger_interval is an INTERVAL statement that is at least 1 minute.

      TRIGGER ON UPDATE has the following limitations

      • No more than 10 upstream data sources per materialized view using table triggers.
      • Maximum of 50 streaming tables or materialized views using table triggers (enabling file events on upstream data sources removes this limit).
      • For source data in an external Delta table, there is a limit of 10,000 rows per change set (enabling file events on upstream data sources removes this limit).
      • The AT MOST EVERY clause defaults to 1 minute, and cannot be less than 1 minute.

  • ALTER COLUMN

    Preview

    This feature is in Public Preview.

    Changes a property of a column.

  • SET ROW FILTER clause

    Preview

    This feature is in Public Preview.

    Adds a row filter function to the materialized view. All subsequent queries to the materialized view receive a subset of the rows where the function evaluates to boolean TRUE. This can be useful for fine-grained access control purposes where the function can inspect the identity or group memberships of the invoking user to determine whether to filter certain rows.

  • DROP ROW FILTER

    Preview

    This feature is in Public Preview.

    Drops the row filter from the materialized view, if any. Future queries will return all rows from the table without any automatic filtering.

  • SET TAGS ( { tag_name = tag_value } [, ...] )

    Preview

    This feature is in Public Preview.

    Apply tags to the materialized view. You need to have APPLY TAG permission to add tags to the materialized view.

    • tag_name

      A literal STRING. The tag_name must be unique within the materialized view or column.

    • tag_value

      A literal STRING.

  • UNSET TAGS ( tag_name [, ...] )

    Preview

    This feature is in Public Preview.

    Remove tags from the materialized view. You need to have APPLY TAG permission to remove tags from the materialized view.

    • tag_name

      A literal STRING. The tag_name must be unique within the materialized view or column.

Examples​

SQL
  -- Adds a schedule to refresh the materialized view whenever its upstream data gets updated.
  > ALTER MATERIALIZED VIEW my_mv
      ADD TRIGGER ON UPDATE;

  -- Alters the schedule to refresh the materialized view when its upstream data
  -- gets updated, and make sure the update frequency is no more than 1 update
  -- per hour.
  > ALTER MATERIALIZED VIEW my_mv
      ALTER TRIGGER ON UPDATE AT MOST EVERY INTERVAL 1 hour;

  -- Alters the schedule to run every two hours for a materialized view
  > ALTER MATERIALIZED VIEW my_mv
      ALTER SCHEDULE EVERY 2 HOURS;

  -- Alters the schedule to refresh a materialized view once a day
  -- at midnight in Los Angeles
  > ALTER MATERIALIZED VIEW my_mv
      ALTER SCHEDULE CRON '0 0 0 * * ? *' AT TIME ZONE 'America/Los_Angeles';

  -- Drops the schedule for a materialized view
  > ALTER MATERIALIZED VIEW my_mv
      DROP SCHEDULE;
Last updated on