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 or its columns, use COMMENT ON.

Syntax 

ALTER MATERIALIZED VIEW view_name
  { schedule }

schedule
  {
    { ADD | ALTER } SCHEDULE [ REFRESH ]
      schedule_clause |
    DROP SCHEDULE
  }

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

Parameters

  • SCHEDULE [ REFRESH ] schedule_clause

    Allows you to add a schedule to or alter the schedule of a materialized view.

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

      Preview

      This feature is in Public Preview.

      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.

Examples 

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

  -- Alters the schedule to run every 15 minutes for a materialized view
  > ALTER MATERIALIZED VIEW my_mv
      ALTER SCHEDULE CRON '0 0/15 * * * ? *';

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