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 Chaitali 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 |
| Chaitali | 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 click 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 list of tables as shown below.
