Register Now

Login

Lost Password

Lost your password? Please enter your email address. You will receive a link and will create a new password via email.

Login

Register Now

It will take less than 1 minute to register for lifetime. Bonus Tip - We don't send OTP to your email id Make Sure to use your own email id for free books and giveaways

Cross Join Unnest and Lateral View Explode

Cross Join Unnest and Lateral View Explode
This is the growing age of data and it’s a challenge to store the data efficiently. What would you do if you have to store phone numbers or courses in a table against every student in your class?
There is also a possibility that one student can have 1/2/3/4 phone numbers.

Will you create 4/5 columns with the name Phone number 1, Ph_2,Ph_3 ?
Or will you just ask for the most used phone number?

Right now in all the industries, people are storing data in arrays and maps.
When you create an array type column then you can put as many values in it as you have.

Ex – Phone[12345,13443,5456]

But how to extract these values while querying the table?

We will learn this in Cross Join Unnest and Lateral View Explode

Youtube video on the UNNEST and EXPLODE using the same example – https://youtu.be/vTv8AJis1aE

NameEmp_idSubject(Array)
Rizwan123[C,JAVA,SQL]
Nitin4231[Hive,Presto]
Gaurab322[Ruby,Perl]
Amod421[Python,R]

The column subject is of Array type. How will you query the above table?
What if you want to know which person is reading Perl?



Select * from table where Subject = ‘Perl’ won’t work for obvious reasons !!



Depending upon what tool you use, you can explore the command.
In this article, we will be taking PrestoSQL and Hive into account

Basically, we need to flatten this column Subject and we will be creating as many rows as there are Subject elements in the filed. Our final output for the above should be

NameEmp_idexpertise
Rizwan123C
Rizwan123JAVA
Rizwan123SQL
Nitin4231Hive
Nitin4231Prest
Gaurab322Ruby
Gaurab322Perl
Amod421Python
Amod421R

Now you can easily query the

CROSS JOIN UNNEST

Select Name,Emp_id,expertise
from Employee
CROSS JOIN UNNEST(Subject) as t(expertise)

If there are multiple arrays

Select Name,Emp_id,expertise,phone_num
from Employee
CROSS JOIN UNNEST(Subject,Phone) as t(expertise,phone_num)

What we are doing here is that we are cross joining the table with all the value present in every row depending on the number of elements in the array

HIVE

Select Name,Emp_id,expertise
from Employee
LATERAL VIEW explode(Subject) myTable1 as expertise

If there are two columns to be unnested then

Select Name,Emp_id,expertise,Phone
from Employee
LATERAL VIEW explode(Subject) myTable1 as expertise
LATERAL VIEW explode(Phone) myTable2 as Phone

When you just explode a column like

EXPLODE(Subject) then it will provide you the list of all the elements present in the column, like
C,JAVA,SQL,etc.

Practice a few questions

What is CROSS JOIN UNNEST?
What is LATERAL VIEW EXPLODE in SQL?

Cross Join Unnest and Lateral View Explode

About TheDataMonkGrand Master

I am the Co-Founder of The Data Monk. I have a total of 6+ years of analytics experience 3+ years at Mu Sigma 2 years at OYO 1 year and counting at The Data Monk I am an active trader and a logically sarcastic idiot :)

Follow Me

Leave a reply