ALTER VIEW

Article
08/12/2024

Applies to: check marked yes Databricks SQL Databricks Runtime

Alters metadata associated with the view. It can change the definition of the view, change the name of a view to a different name, set and unset the metadata of the view by setting TBLPROPERTIES.

If the view is cached, the command clears cached data of the view and all its dependents that refer to it. The view's cache will be lazily filled when the view is accessed the next time. The command leaves view's dependents as uncached.

Syntax

ALTER [ MATERIALIZED ] VIEW view_name
  { rename |
    SET TBLPROPERTIES clause |
    UNSET TBLPROPERTIES clause |
    alter_body |
    schema_binding |
    owner_to |
    schedule
    SET TAGS clause |
    UNSET TAGS clause }}}

rename
  RENAME TO to_view_name

alter_body
  AS query

schema_binding
  WITH SCHEMA { BINDING | [ TYPE ] EVOLUTION | COMPENSATION }

property_key
  { idenitifier [. ...] | string_literal }

owner_to
  [ SET ] OWNER TO principal

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

Parameters

view_name

Identifies the view to be altered. If the view cannot be found Azure Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
RENAME TO to_view_name

Renames the existing view within the schema. Materialized views cannot be renamed.

to_view_name specifies the new name of the view. If the to_view_name already exists, a TableAlreadyExistsException is thrown. If to_view_name is qualified it must match the schema name of view_name.
SET TBLPROPERTIES

Sets or resets one or more user defined properties.
UNSET TBLPROPERTIES

Removes one or more user defined properties.
AS query

A query that constructs the view from base tables or other views.

This clause is equivalent to a CREATE OR REPLACE VIEW statement on an existing view, except that privileges granted on the view are preserved.
schema_binding

Applies to: Databricks Runtime 15.3 and above

Specifies how subsequent querying of the view adapts to changes to the view's schema due to changes in the underlying object definitions. See CREATE VIEW… WITH SCHEMA for details on schema binding modes.
[ SET ] OWNER TO principal

Transfers ownership of the view to principal. Unless the view is defined in the hive_metastore you may only transfer ownership to a group you belong to.

Applies to: Databricks SQL Databricks Runtime 11.3 LTS and above

SET is allowed as an optional keyword.
SET TAGS ( { tag_name = tag_value } [, …] )

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

Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above
UNSET TAGS ( tag_name [, …] )

Remove tags from the table. You need to have APPLY TAG permission to remove tags from the view.

Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above
tag_name

A literal STRING. The tag_name must be unique within the view.
tag_value

A literal STRING.
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

-- Rename only changes the view name.
-- The source and target schemas of the view have to be the same.
-- Use qualified or unqualified name for the source and target view.
> ALTER VIEW tempsc1.v1 RENAME TO tempsc1.v2;

-- Verify that the new view is created.
> DESCRIBE TABLE EXTENDED tempsc1.v2;
                            c1       int   NULL
                            c2    string   NULL

  # Detailed Table Information
                      Database   tempsc1
                         Table        v2

-- Before ALTER VIEW SET TBLPROPERTIES
> DESCRIBE TABLE EXTENDED tempsc1.v2;
                            c1       int   null
                            c2    string   null

  # Detailed Table Information
                      Database   tempsc1
                         Table        v2
              Table Properties    [....]

-- Set properties in TBLPROPERTIES
> ALTER VIEW tempsc1.v2 SET TBLPROPERTIES ('created.by.user' = "John", 'created.date' = '01-01-2001' );

-- Use `DESCRIBE TABLE EXTENDED tempsc1.v2` to verify
> DESCRIBE TABLE EXTENDED tempsc1.v2;
                            c1                                                   int   NULL
                            c2                                                string   NULL

  # Detailed Table Information
                      Database                                               tempsc1
                         Table                                                    v2
              Table Properties [created.by.user=John, created.date=01-01-2001, ....]

-- Remove the key created.by.user and created.date from `TBLPROPERTIES`
> ALTER VIEW tempsc1.v2 UNSET TBLPROPERTIES (`created`.`by`.`user`, created.date);

-- Use `DESCRIBE TABLE EXTENDED tempsc1.v2` to verify the changes
> DESCRIBE TABLE EXTENDED tempsc1.v2;
                            c1       int   NULL
                            c2    string   NULL

  # Detailed Table Information
                      Database   tempsc1
                         Table        v2
              Table Properties    [....]

-- Change the view definition
> ALTER VIEW tempsc1.v2 AS SELECT * FROM tempsc1.v1;

-- Use `DESCRIBE TABLE EXTENDED` to verify
> DESCRIBE TABLE EXTENDED tempsc1.v2;
                            c1                        int   NULL
                            c2                     string   NULL

  # Detailed Table Information
                      Database                    tempsc1
                         Table                         v2
                          Type                       VIEW
                     View Text   select * from tempsc1.v1
            View Original Text   select * from tempsc1.v1

-- Transfer ownership of a view to another user
> ALTER VIEW v1 OWNER TO `alf@melmak.et`

-- Change the view schema binding to adopt type evolution
> ALTER VIEW v1 WITH SCHEMA TYPE EVOLUTION;

-- 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;

-- Applies three tags to the view named `test`.
> ALTER VIEW test SET TAGS ('tag1' = 'val1', 'tag2' = 'val2', 'tag3' = 'val3');

-- Removes three tags from the view named `test`.
> ALTER VIEW test UNSET TAGS ('tag1', 'tag2', 'tag3');

ALTER VIEW

Syntax

Parameters

Examples

Related articles

Additional resources