ALTER VIEW Statement

Changes the characteristics of a view. The syntax has two forms:

  • The AS clause associates the view with a different query.
  • The RENAME TO clause changes the name of the view, moves the view to a different database, or both.

Because a view is purely 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.

Syntax:

ALTER VIEW [database_name.]view_name AS select_statement
ALTER VIEW [database_name.]view_name RENAME TO [database_name.]view_name

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.

Examples:

create table t1 (x int, y int, s string);
create table t2 like t1;
create view v1 as select * from t1;
alter view v1 as select * from t2;
alter view v1 as select x, upper(s) s from t2;
To see the definition of a view, issue a DESCRIBE FORMATTED statement, which shows the query from the original CREATE VIEW statement:
[localhost:21000] > create view v1 as select * from t1;
[localhost:21000] > describe formatted v1;
Query finished, fetching results ...
+------------------------------+------------------------------+------------+
| name                         | type                         | comment    |
+------------------------------+------------------------------+------------+
| # col_name                   | data_type                    | comment    |
|                              | NULL                         | NULL       |
| x                            | int                          | None       |
| y                            | int                          | None       |
| s                            | string                       | None       |
|                              | NULL                         | NULL       |
| # Detailed Table Information | NULL                         | NULL       |
| Database:                    | views                        | NULL       |
| Owner:                       | doc_demo                     | NULL       |
| CreateTime:                  | Mon Jul 08 15:56:27 EDT 2013 | NULL       |
| LastAccessTime:              | UNKNOWN                      | NULL       |
| Protect Mode:                | None                         | NULL       |
| Retention:                   | 0                            | NULL       |
| Table Type:                  | VIRTUAL_VIEW                 | NULL       |
| Table Parameters:            | NULL                         | NULL       |
|                              | transient_lastDdlTime        | 1373313387 |
|                              | NULL                         | NULL       |
| # Storage Information        | NULL                         | NULL       |
| SerDe Library:               | null                         | NULL       |
| InputFormat:                 | null                         | NULL       |
| OutputFormat:                | null                         | NULL       |
| Compressed:                  | No                           | NULL       |
| Num Buckets:                 | 0                            | NULL       |
| Bucket Columns:              | []                           | NULL       |
| Sort Columns:                | []                           | NULL       |
|                              | NULL                         | NULL       |
| # View Information           | NULL                         | NULL       |
| View Original Text:          | SELECT * FROM t1             | NULL       |
| View Expanded Text:          | SELECT * FROM t1             | NULL       |
+------------------------------+------------------------------+------------+

Related information:

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