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 [ 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
-
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, 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
.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. IfAT TIME ZONE
is absent, the session time zone is used. IfAT TIME ZONE
is absent and the session time zone is not set, an error is thrown.SCHEDULE
is semantically equivalent toSCHEDULE 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');
Related articles
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for