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?

The Data Monk Interview Books – Don’t Miss

Now we are also available on our website where you can directly download the PDF of the topic you are interested in. At Amazon, each book costs ~299, on our website we have put it at a 60-80% discount. There are ~4000 solved interview questions prepared for you.

10 e-book bundle with 1400 interview questions spread across SQL, Python, Statistics, Case Studies, and Machine Learning Algorithms – Ideal for 0-3 years experienced candidates

23 E-book with ~2000 interview questions spread across AWS, SQL, Python, 10+ ML algorithms, MS Excel, and Case Studies – Complete Package for someone between 0 to 8 years of experience (The above 10 e-book bundle has a completely different set of e-books)

12 E-books for 12 Machine Learning algorithms with 1000+ interview questions – For those candidates who want to include any Machine Learning Algorithm in their resume and to learn/revise the important concepts. These 12 e-books are a part of the 23 e-book package

Individual 50+ e-books on separate topics

Important Resources to crack interviews (Mostly Free)

There are a few things which might be very useful for your preparation

The Data Monk Youtube channel – Here you will get only those videos that are asked in interviews for Data Analysts, Data Scientists, Machine Learning Engineers, Business Intelligence Engineers, Analytics Manager, etc.
Go through the watchlist which makes you uncomfortable:-

All the list of 200 videos
Complete Python Playlist for Data Science
Company-wise Data Science Interview Questions – Must Watch
All important Machine Learning Algorithm with code in Python
Complete Python Numpy Playlist
Complete Python Pandas Playlist
SQL Complete Playlist
Case Study and Guesstimates Complete Playlist
Complete Playlist of Statistics

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