ALTER VIEW Statement

The ALTER VIEW statement changes the characteristics of a view.

Because a view is a logical construct, an alias for a query, with no physical data behind it, ALTER VIEW only involves changes to metadata in the metastore database, not any data files in HDFS.

To see the definition of the updated view, issue a DESCRIBE FORMATTED statement.

Syntax:

ALTER VIEW [database_name.]view_name
   [(column_name [COMMENT 'column_comment'][, ...])]
   AS select_statement;

ALTER VIEW [database_name.]view_name
   RENAME TO [database_name.]view_name;

ALTER VIEW [database_name.]view_name SET OWNER USER user_name;

ALTER VIEW [database_name.]view_name
    SET TBLPROPERTIES ('name' = 'value'[, 'name' = 'value' ...]);

ALTER VIEW [database_name.]view_name
    UNSET TBLPROPERTIES ('name'[, ...]);

• The AS clause associates the view with a different query.

  An optional list of column names can be specified with or without the column-level comments.

  For example:

  
  ALTER VIEW v1 AS SELECT x, UPPER(s) s FROM t2;
  ALTER VIEW v1 (c1, c2) AS SELECT x, UPPER(s) s FROM t2;
  ALTER VIEW v7 (c1 COMMENT 'Comment for c1', c2) AS SELECT t1.c1, t1.c2 FROM t1;
  

• The RENAME TO clause changes the name of the view, moves the view to a different database, or both.
  For example:

  ALTER VIEW db1.v1 RENAME TO db2.v2; -- Move the view to a different database with a new name.
  ALTER VIEW db1.v1 RENAME TO db1.v2;  -- Rename the view in the same database.
  ALTER VIEW db1.v1 RENAME TO db2.v1; -- Move the view to a difference database with the same view name.

• The SET OWNER clause transfers the ownership of the view from the current owner to another user.

  The view owner is originally set to the user who creates the view. The term OWNER is used to differentiate between the ALL privilege that is explicitly granted via the GRANT statement and a privilege that is implicitly granted by the CREATE VIEW statement.

• The SET TBLPROPERTIES clause is primarily a way to associate arbitrary user-specified data items with a particular view.

  You can associate arbitrary items of metadata with a table by specifying the TBLPROPERTIES clause. This clause takes a comma-separated list of key-value pairs and stores those items in the metastore database. You can also unset the view properties later with an UNSET TBLPROPERTIES clause.

  For example:

  ALTER VIEW v1 SET TBLPROPERTIES ('tblp1' = '1', 'tblp2' = '2');
  ALTER VIEW v1 UNSET TBLPROPERTIES ('tblp1', 'tblp2');
  

Statement type: DDL

If you connect to different Impala nodes within an impala-shell session for load-balancing purposes, you can enable the SYNC_DDL query option to make each DDL statement wait before returning, until the new or changed metadata has been received by all the Impala nodes. See SYNC_DDL Query Option for details.

Security considerations:

If these statements in your environment contain sensitive literal values such as credit card numbers or tax identifiers, Impala can redact this sensitive information when displaying the statements in log files and other administrative contexts. See the documentation for your Apache Hadoop distribution for details.

Cancellation: Cannot be cancelled.

HDFS permissions: This statement does not touch any HDFS files or directories, therefore no HDFS permissions are required.

Related information:

Overview of Impala Views, CREATE VIEW Statement, DROP VIEW Statement