Impala - Create View

A view is nothing more than a statement of Impala query language that is stored in the database with an associated name. It is a composition of a table in the form of a predefined SQL query.

A view can contain all the rows of a table or selected ones. A view can be created from one or many tables. Views allow users to −

Structure data in a way that users or classes of users find natural or intuitive.

Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.

Summarize data from various tables which can be used to generate reports.

You can create a view using the Create View statement of Impala.

Syntax

Following is the syntax of the create view statement. IF NOT EXISTS is an optional clause. If we use this clause, a table with the given name is created, only if there is no existing table in the specified database with the same name.

Create View IF NOT EXISTS view_name as Select statement

Example

For example, assume we have a table named customers in the my_db database in Impala, with the following data.

ID  NAME      AGE   ADDRESS     SALARY
--- --------- ----- ----------- --------
1   Ramesh    32    Ahmedabad   20000
2   Khilan    25    Delhi       15000
3   Hardik    27    Bhopal      40000
4   Chaitap  25    Mumbai      35000
5   kaushik   23    Kota        30000
6   Komal     22    MP          32000

Following is an example of Create View Statement. In this example, we are creating a view as customers table which contains the columns, name, and age.

[quickstart.cloudera:21000] > CREATE VIEW IF NOT EXISTS customers_view AS 
select name, age from customers;

On executing the above query, a view with the desired columns is created, displaying the following message.

Query: create VIEW IF NOT EXISTS sample AS select * from customers 
Fetched 0 row(s) in 0.33s

Verification

You can verify the contents of the view just created, using the select statement as shown below.

[quickstart.cloudera:21000] > select * from customers_view;

This will produce the following result.

Query: select * from customers_view 
+----------+-----+ 
| name     | age | 
+----------+-----+ 
| Komal    | 22  | 
| Khilan   | 25  | 
| Ramesh   | 32  | 
| Hardik   | 27  | 
| Chaitap | 25  | 
| kaushik  | 23  | 
+----------+-----+ 
Fetched 6 row(s) in 4.80s

Creating a View using Hue

Open Impala Query editor, select the context as my_db, and type the Create View statement in it and cpck on the execute button as shown in the following screenshot.

After executing the query, if you scroll down, you can see the view named sample created in the pst of tables as shown below.