ALTER VIEW

Applies to: Databricks SQL Databricks Runtime

Alters metadata associated with a view. It can change the definition of a view, rename a view, and set or unset metadata by setting TBLPROPERTIES. For a metric view, it can also add and drop relationships (Beta).

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

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 is lazily filled when the view is next accessed. The command leaves the view's dependents as uncached.

Required privileges

If you use Unity Catalog, the following operations do not require ownership of the view:

• SET TAGS: Requires the APPLY TAG privilege.
• UNSET TAGS: Requires the APPLY TAG privilege.

The following operations require ownership of the view:

• SET TBLPROPERTIES
• UNSET TBLPROPERTIES
• AS query (change view definition)
• WITH SCHEMA (change schema binding)

RENAME TO requires the MANAGE privilege on the view.

ADD CONSTRAINT and DROP CONSTRAINT require the MANAGE privilege on the metric view or ownership of it, plus USE CATALOG and USE SCHEMA on its catalog and schema. ADD CONSTRAINT for a relationship also requires the SELECT privilege on the target metric view, plus USE CATALOG and USE SCHEMA on the target's catalog and schema.

For SET OWNER TO, the required privileges depend on the new owner:

• If the principal is yourself, you must have the MANAGE privilege on the view.
• If the principal is a group, you must be a member of that group.
• In all other cases, you must be a metastore admin.

Syntax

ALTER VIEW view_name
  { rename |
    SET TBLPROPERTIES clause |
    UNSET TBLPROPERTIES clause |
    alter_body |
    schema_binding |
    owner_to |
    SET TAGS clause |
    UNSET TAGS clause |
    add_constraint |
    drop_constraint }

rename
  RENAME TO to_view_name

alter_body
  AS { query | yaml_definition }

yaml_definition
  $$
    yaml_string
  $$

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

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

owner_to
  [ SET ] OWNER TO principal

add_constraint
  ADD [ CONSTRAINT name ] relationship_constraint

relationship_constraint
  cardinality RELATIONSHIP TO target_view [ [ AS ] target_alias ]
    { ON condition | USING ( column [, ...] ) }

cardinality
  { MANY | ONE } TO { MANY | ONE }

drop_constraint
  DROP CONSTRAINT [ IF EXISTS ] name

Parameters

• view_name

  Identifies the view to be altered. If the view can't be found, Azure Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.

• RENAME TO to_view_name

  Renames the existing view to to_view_name.

  For Unity Catalog views, the to_view_name must be within the same catalog as view_name. For other views, the to_view_name must be within the same schema as view_name.

  If to_view_name is unqualified, it is implicitly qualified with the current schema.

  Materialized views can't be renamed.

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

  AS query is not supported for metric 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.

• AS yaml_definition

  Applies to: Databricks SQL Databricks Runtime 16.4 and above Unity Catalog only

  A yaml_definition for a metric view.

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

  Re-specifying the YAML definition adds, drops, or changes the metric view's parameters without any syntax change. For the parameter syntax, see CREATE VIEW. Parameters require Databricks Runtime 18.2 and above.

• schema_binding

  Applies to: Databricks SQL 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.

  This clause is not supported for metric views.

• [ SET ] OWNER TO principal

  Transfers ownership of the view to principal. Unless the view is defined in the hive_metastore, you can 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 } [, …] )

  Applies tags to the view. You need the APPLY TAG privilege to add tags to the view.

  Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above

• UNSET TAGS ( tag_name [, …] )

  Removes tags from the view. You need the APPLY TAG privilege 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.

• add_constraint

  Applies to: Databricks SQL Databricks Runtime 19 and above Unity Catalog only

  Adds an informational relationship from the metric view to a target metric view. This clause is in Beta. Both view_name and target_view must be Unity Catalog metric views. Temporary metric views aren't supported. Azure Databricks doesn't enforce relationships. See Relationships between metric views.

  • CONSTRAINT name

    Optionally names the relationship. The name must be unique among all constraints in the schema. If you omit it, Azure Databricks names the relationship <source>_to_<target>, using the unqualified names of the two metric views.

  • cardinality

    One of MANY TO ONE, ONE TO MANY, ONE TO ONE, or MANY TO MANY. Azure Databricks doesn't check the data against the cardinality.

  • TO target_view

    Identifies the target metric view. The target can be in any catalog or schema of the same metastore.

  • AS target_alias

    Optionally names the target in condition. A relationship from a metric view to itself requires an alias that differs from the name of view_name.

  • ON condition

    A deterministic BOOLEAN expression over the columns of view_name and target_view, with the same rules as the ON clause of a join. The expression can't reference a measure. It can't call an aggregate function, including MEASURE and AGG, a window function, or a generator. It can be up to 1,024 characters.

  • USING ( column [, ...] )

    One to 20 columns that exist with the same name in both metric views.

• drop_constraint

  Applies to: Databricks SQL Databricks Runtime 19 and above Unity Catalog only

  Drops the relationship name from the metric view. This clause is in Beta. If you specify IF EXISTS, the statement succeeds when no relationship with that name exists. Otherwise, it fails with NO_SUCH_CONSTRAINT_EXCEPTION.

  Warning

  Don't specify RESTRICT or CASCADE when you drop a relationship. Unlike ALTER TABLE DROP CONSTRAINT, these keywords have no defined behavior for relationships, and their behavior might change in a future release.

Examples

-- Rename only changes the view name.
-- The source and target schemas of the view must 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;

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

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

-- Alter the metric view `region_sales_metrics` defined in CREATE VIEW
-- to drop the `total_revenue_for_open_orders` measure.
> ALTER VIEW region_sales_metrics
  AS $$
   version: 0.1
   source: samples.tpch.orders
   filter: o_orderdate > '1990-01-01'
   dimensions:
   - name: month
     expr: date_trunc('MONTH', o_orderdate)
   - name: status
     expr: case
       when o_orderstatus = 'O' then 'Open'
       when o_orderstatus = 'P' then 'Processing'
       when o_orderstatus = 'F' then 'Fulfilled'
       end
   - name: order_priority
     expr: split(o_orderpriority, '-')[1]
   measures:
   - name: count_orders
     expr: count(1)
   - name: total_revenue
     expr: SUM(o_totalprice)
   - name: total_revenue_per_customer
     expr: SUM(o_totalprice) / count(distinct o_custkey)
  $$;

> DESCRIBE EXTENDED region_sales_metrics;
 col_name                    data_type
 month                       timestamp
 status                      string
 order_priority              string
 count_orders                bigint measure
 total_revenue               decimal(28,2) measure
 total_revenue_per_customer  decimal(38,12) measure

 # Detailed Table Information
 Catalog                     main
 Database                    default
 Table                       region_sales_metrics
 Owner                       alf@melmak.et
 Created Time                Sun May 18 23:45:25 UTC 2025
 Last Access                 UNKNOWN
 Created By                  Spark
 Type                        METRIC_VIEW
 Comment                     A metric view for regional sales metrics.
 View Text                   "
    version: 0.1
    source: samples.tpch.orders
    filter: o_orderdate > '1990-01-01'
    dimensions:
    - name: month
      expr: date_trunc('MONTH', o_orderdate)
    - name: status
      expr: case
        when o_orderstatus = 'O' then 'Open'
        when o_orderstatus = 'P' then 'Processing'
        when o_orderstatus = 'F' then 'Fulfilled'
        end
    - name: order_priority
      expr: split(o_orderpriority, '-')[1]
    measures:
    - name: count_orders
      expr: count(1)
    - name: total_revenue
      expr: SUM(o_totalprice)
    - name: total_revenue_per_customer
      expr: SUM(o_totalprice) / count(distinct o_custkey)
   "
 Language                    YAML
 Table Properties            [metric_view.from.name=samples.tpch.orders, metric_view.from.type=ASSET, metric_view.where=o_orderdate > '1990-01-01']

-- Declare that each order in the `orders_mv` metric view matches one customer
-- in the `customers_mv` metric view, joined on the shared `customer_id` column.
> ALTER VIEW main.sales.orders_mv ADD CONSTRAINT orders_customer
    MANY TO ONE RELATIONSHIP TO main.sales.customers_mv
    USING (customer_id);

-- Declare a relationship that refers to the target by an alias in the ON condition.
> ALTER VIEW main.travel.flights_mv ADD CONSTRAINT departure_city
    MANY TO ONE RELATIONSHIP TO main.travel.cities_mv AS c
    ON flights_mv.departure_city_id = c.city_id;

-- Drop the relationship.
> ALTER VIEW main.travel.flights_mv DROP CONSTRAINT IF EXISTS departure_city;

Related articles

• DESCRIBE TABLE
• COMMENT ON
• CREATE VIEW
• CREATE MATERIALIZED VIEW
• DROP VIEW
• SHOW VIEWS
• Relationships between metric views