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 ]
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. IfEVERY
syntax is specified, the streaming table or materialized view is refreshed periodically at the specified interval based on the provided value, such asHOUR
,HOURS
,DAY
,DAYS
,WEEK
, orWEEKS
. The following table lists accepted integer values fornumber
.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. 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;