For writing queries, we sometimes find it difficult to frame query when it is nested and complex.
This scenario may often occur in the case of joins and we will be covering the same in this blog to simplify our way of querying in HIVE with help of VIEWS.
When a query becomes long or complicated, a view may be used to hide the complexity
by dividing the query into smaller, more manageable pieces; similar to writing a function in a programming language or the concept of layered design in software. Encapsulating the complexity makes it easier for end users to construct complex queries from reusable parts. For example, consider the following query with a nested subquery:
(SELECT * FROM people JOIN cart
ON (cart.id1=people.id) WHERE last_name=’Henderson’)a
SELECT a.first_name WHERE a.id=’40-8340379′;
It is common for Hive queries to have many levels of nesting. In the following example of Hive View,
the nested portion of the query is turned into a simple one :
Refer the below screenshot for sample data and click here for data.
First for Hive View, we will create a table inside Hive and load the respective data.
Commands for doing so are:
create table people(id string,last_name string,email string,gender string,ip_address string)row format delimited fields terminated by ‘,’;
load data local inpath ‘/home/prateek/Documents/HIVE/people.csv’ into table people;
create table cart(id1 string,first_name string,job_title string)row format delimited fields terminated by ‘,’;
load data local inpath ‘/home/prateek/Documents/HIVE/cart.csv’ into table cart;
Instead of using Hive View, while executing formal nested query, we see the size of a query is more and complex also multiple jobs need to complete in a single query.In this case, most difficult part is to guess the combination of columns and rows.But if the query is correct you will have the result.
Now for the different approach, we create a VIEW named J_View as this Hive View will be showing data after joining table.
NOTE:- Hive View does not store data permanently. It is a logical construct, as it does not store data like a table.
CREATE VIEW J_View AS
SELECT * FROM people JOIN cart
Also, refer below screenshot for to see the complete data stored in this VIEW we created.
Now the Hive View is used like any other table. In this query, we added a WHERE clause to the SELECT statement. This exactly emulates the original query:
SELECT first_name FROM J_View WHERE id=’40-8340379′;
Similarly, we can query this Hive View as many times we want for the different result without making our query complex.