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.

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

注記

Altering a pipeline-created dataset in ways that contradict the defining SQL can cause some changes to be reverted. See Using ALTER commands with Lakeflow Spark Declarative Pipelines.

Syntax

ALTER MATERIALIZED VIEW view_name
  {
    { ADD | ALTER } schedule |
    DROP SCHEDULE |
    ALTER COLUMN 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 ] }

  column_clause
    column_identifier
    { COMMENT clause |
      SET MASK clause |
      DROP MASK |
      SET TAGS clause |
      UNSET TAGS clause }

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.

  注記

  You can't modify the schedule of a materialized view created with Lakeflow Spark Declarative Pipelines with this command. Use the pipeline editor.

  • 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

      注記

      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 when using TRIGGER ON UPDATE.
    • Maximum of 1000 streaming tables or materialized views can be specified with TRIGGER ON UPDATE.
    • The AT MOST EVERY clause defaults to 1 minute, and cannot be less than 1 minute.

• DROP SCHEDULE

  Removes a schedule from the materialized view.

• ALTER COLUMN clause

  See ALTER COLUMN clause.

• 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.

  Row filters added after creation only propagate to downstream tables after the next update. For continuous pipelines, this requires a pipeline restart.

• 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.

ALTER COLUMN clause

Preview

This feature is in Public Preview.

Changes a property or the location of a column.

Syntax

  ALTER COLUMN
    column_identifier
    { COMMENT comment |
      SET MASK mask_clause |
      DROP MASK |
      SET TAGS clause |
      UNSET TAGS clause }

Parameters

• column_identifier

  The name of the column to be altered.

• COMMENT comment

  Changes the description of the column_name column. comment must be a STRING literal.

• SET MASK clause

  Adds a column mask function to anonymize sensitive data. All subsequent queries from that column will receive the result of evaluating that function over the column in place of the column's original value. 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 redact the value.

  Column masks added after creation only propagate to downstream tables after the next update. For continuous pipelines, this requires a pipeline restart.

• DROP MASK

  Removes the column mask for this column, if any. Future queries from this column will receive the column's original values.

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

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

  • tag_name

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

  • tag_value

    A literal STRING.

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

  Remove the given tags from the column. You need to have APPLY TAG permission to remove tags from the column.

  • tag_name

    A literal STRING. The tag_name must be unique within the table 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;

-- Adds a column comment
> ALTER MATERIALIZED VIEW my_mv
    ALTER COLUMN my_column_name COMMENT 'test'

Related articles

• DESCRIBE TABLE
• COMMENT ON
• CREATE MATERIALIZED VIEW
• DROP VIEW