impala creates a view

The view is simply a statement stored in the Impala query language in the database with the associated name. I t is a combination of tables in the form of predefined SQL queries.

A view can contain all or selected rows of a table. V iews can be created from one or more tables. V iews allow users to -

• Structure data in a natural or intuitive way that a user or user class discovers.

• Restrict access to data so that users can see and (sometimes) completely modify what they need without changing it.

• Summarize the data in various tables that you can use to generate reports.

You can use Impala's Creative View statement to create a view.

Grammar

The following is the syntax of the creative view statement. I F NOT EXISTS is an optional clause. I f you use this clause, a table with a given name is created only if there are no existing tables with the same name in the specified database.

Create View IF NOT EXISTS view_name as Select statement

Cases

For example, suppose you have my_db table named Customers in the database in Impala that contains the following data.

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

The following is an example of a Creative View statement. I n this example, we create a view for the customers table, which contains columns, names, and ages.

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

When you perform the query above, a view with the required columns is created and the following message is displayed.

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

Verify

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

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

This will result in the following results.

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

Create a view with Hue

Open the Impala query editor, select the context my_db, type the Creative View statement in it, and then click the execute button, as shown in the screenshot below.

After you execute the query, if you scroll down, you can see the view created in the table list calledample, as shown below.