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

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

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 services

We are well known for our interview books and have 70+ e-book across Amazon and The Data Monk e-shop page . Following are best-seller combo packs and services that we are providing as of now

1. YouTube channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
   Link – The Data Monk Youtube Channel
   
2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
   Link – The Data Monk website
   
3. E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions. Do check it out
   Link – The Data E-shop Page
4. Instagram Page – It covers only Most asked Questions and concepts (100+ posts). We have 100+ most asked interview topics explained in simple terms
   Link – The Data Monk Instagram page
5. Mock Interviews/Career Guidance/Mentorship/Resume Making
   Book a slot on Top Mate

The Data Monk e-books

We know that each domain requires a different type of preparation, so we have divided our books in the same way:

1. 2200 Interview Questions to become Full Stack Analytics Professional – 2200 Most Asked Interview Questions
2.Data Scientist and Machine Learning Engineer -> 23 e-books covering all the ML Algorithms Interview Questions
3. 30 Days Analytics Course – Most Asked Interview Questions from 30 crucial topics

You can check out all the other e-books on our e-shop page – Do not miss it

For any information related to courses or e-books, please send an email to [email protected]