ALTER MATERIALIZED VIEW
Applies to: 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. IfAT TIME ZONE
is absent, the session time zone is used. IfAT TIME ZONE
is absent and the session time zone is not set, an error is thrown.SCHEDULE
is semantically equivalent toSCHEDULE 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;