❮❮ PREV

Lateral Views

Since the explode() function in HIVE does not allow us use any other columns with it in the 'SELECT' statement. Lateral views comes into rescue.

Lateral views creates a virtual table when we use the explode() function with a list or a map. Then you can combine it with the actual table and write any complex queries.

Example :

Let's use the same table we have used for explode() example.

CREATE TABLE student_location (
city string,
names map <int,string>
)
row format delimited fields terminated by ','
collection items terminated by '*'
map keys terminated by ':';

Rows returned by the 'SELECT' statement are:

SELECT * FROM student_location;

city	names
Hydrabad	{1:"Vinay",3:"Ajay",5:"Darshil"}
Delhi	{2:"Sahil",4:"Peter"}

We have seen the limitation of explode() where we could have only displayed the contents of the map (i.e. names in the above case). But the contents of 'city' column couldn't be shown.We will overcome the limitation using lateral views.

SELECT city, roll, name FROM student_location LATERAL VIEW explode(names) namesTable as roll,name;

city	roll	name
Hydrabad	1	Vinay
Hydrabad	3	Ajay
Hydrabad	5	Darshil
Delhi	2	Sahil
Delhi	4	Peter

In the above example we have used the LATERAL VIEW with explode(names) and created a virtual table 'namesTable'. As usual the explode(names) method splits the map (i.e. names) to two columns 'roll' and 'name'. Now, when this LATERAL VIEW statement is clubbed with the SELECT statement, the virtual table 'namesTable' created by LATERAL VIEW acts as the actual table and the contents of 'student_location' table and 'namesTable' is returned.

❮❮ PREV