Cloudera Impala Create View Syntax and Examples

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:BigData
  • Reading time:3 mins read

A View creates a pseudo-table or virtual table. It appears exactly as a regular table, you can use it in SELECT statements, JOINs etc. The Impala CREATE VIEW statement allows you to create a shorthand abbreviation for a more complicated query. The base query can have tables, joins, column alias etc. In this article, we will check Cloudera Impala create view syntax and some examples.

Impala Create View

Just like views or table in other database, an Impala view contains rows and columns. The fields in a view are fields from one or more real tables in the referencing database. Impala views are read-only. The system does not allow you an insert, update, or delete on a view.

Cloudera Impala Create View Syntax

Below is the syntax for creating a view in Cloudera Impala:

CREATE VIEW [IF NOT EXISTS] view_name [(column_list)]
AS select_statement;

Where, IF NOT EXISTS creates the view if it is not present already. Statement will be ignored if present.

Cloudera Impala Create View Examples

Create a view that consists of all action films, enter:

https://gist.github.com/713a5f84a1323653e07892e31c62eb42

Cloudera Impala ALTER VIEW

You can use Impala ALTER VIEW to change base query or RENAME the view. Below is the syntax to ALTER VIEW:

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

Cloudera Impala Alter View Example

You can use ALTER VIEW to change base query. Here is the example of changing the underlying SELECT statement using ALTER view command:

https://gist.github.com/57bb1633979320db59460d7bfe7f55c3

You can even RENAME views using ALTER VIEW statement:

You can use the ALTER view command to rename the Impala view. Here is the example to rename the already created view:

https://gist.github.com/8e10492cc133ff86df49c889f861da44

Cloudera Impala DROP VIEW

Impala DROP VIEW removes the specified view, that was originally created by CREATE VIEW statement. This statement makes changes to metadata to remove specified view. Below is the syntax to drop impala view:

DROP VIEW [IF EXISTS] [db_name.]view_name;

Cloudera Impala DROP VIEW Examples

Below is the example to demonstrate Impala DROP VIEW statement:

DROP VIEW IF EXISTS comedy_movies;

Read: