ALTER VIEW
Applies to: 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 VIEW view_name
{ rename |
SET TBLPROPERTIES clause |
UNSET TBLPROPERTIES clause |
alter_body |
schema_binding |
owner_to
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
Parameters
-
Identifies the view to be altered. If the view cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
RENAME TO to_view_name
Renames the existing view within the schema.
to_view_name specifies the new name of the view. If the
to_view_name
already exists, aTableAlreadyExistsException
is thrown. Ifto_view_name
is qualified it must match the schema name ofview_name
.-
Sets or resets one or more user defined properties.
-
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.
-
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 thehive_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
. Thetag_name
must be unique within the view.tag_value
A literal
STRING
.
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`