ALTER VIEW (Databricks SQL)

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.

Syntax

ALTER VIEW view_name { rename | set_properties | unset_properties | alter_body }

rename
  RENAME TO to_view_name

set_properties
  SET TBLPROPERTIES ( property_key = property_val [ , ... ] )

unset_properties
  UNSET TBLPROPERTIES [ IF EXISTS ] ( property_key [ , ... ] )

alter_body
  AS query

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

Parameters

  • view_name

    Identifies the view to be altered.

  • RENAME TO to_view_name

    Renames the existing view within the database.

    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 database name of view_name.

  • set_properties

    Sets one or more user defined properties of an existing view. Properties are key value pairs. If the properties’ keys exist, the values are replaced with the new values. If the properties’ keys do not exist, the key-value pairs are added to the properties.

    The ALTER VIEW statement does not support SET SERDE or SET SERDEPROPERTIES properties.

    • property_key

      The property key. The key can consist of one or more identifiers separated by a dot, or a string literal.

      Property keys must be unique.

    • property_val

      The new value for the property. The value must be a BOOLEAN, STRING, INTEGER, or DECIMAL literal.

  • unset_properties

    Removes one or more user defined properties of view_name.

    • IF EXISTS

      Unless IF EXISTS is specified an error is raided if the property has not been set.

    • property_key

      The key can consist of one or more identifiers separated by a dot, or a string literal.

      Property keys must be unique.

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

Examples

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

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

  # Detailed Table Information
                      Database   tempdb1
                         Table        v2

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

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

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

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

  # Detailed Table Information
                      Database                                               tempdb1
                         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 tempdb1.v2 UNSET TBLPROPERTIES (`created`.`by`.`user`, created.date);

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

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

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

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

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