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.

Syntax 

ALTER MATERIALIZED VIEW view_name
  { schedule }

schedule
  {
    { ADD | ALTER } SCHEDULE [ REFRESH ]
      CRON cron_string [ AT TIME ZONE timezone_id ] |
    DROP SCHEDULE
  }

Parameters

  • SCHEDULE [ REFRESH ] CRON cron_string [ AT TIME ZONE timezone_id ]

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

    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.

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;