LATERAL VIEW in Hive

Some you will be asked a question in Hive like, we have a table in which one of the columns has data of type Array of Integers, now you have to display all the values of that array column one in each row corresponding to the primary key. Please look at the below tables.

 

We have the data in Hive table as shown below and Author column is of type Array of strings.

                           Author                                 Book_Name
ABC [Book1, Book2, Book3]
DEF [Book4, Book5, Book6]
GHI [Book7, Book8, Book9]

And the output should be:

                           Author                             Book_Name
ABC Book1
ABC Book2
ABC Book3
DEF Book4
DEF Book5
DEF Book6
GHI Book7
GHI Book8
GHI Book9

 

To accomplish this task we need to write a Hive query as below:

SELECT author, book_name_alias from Books_table LATERAL VIEW explode(Book_Name) book as book_name_alias;

The clause LATERAL VIEW is used along with the user defined tables generating functions like explode().

 

The same command can be applicable even for the columns of type Map. Suppose we have a table with a column if type Map and you want to display all the key values pairs one in each row, then you can used the below Hive query:

Input table:

 

Column_name
{1:”abc”}
{2:”def”}
{3:”ghi”}

 

Output will be:

                                   Key                              Value
1 “abc”
2 “def”
3 “ghi”

 

SELECT key, value FROM table_name LATERAL VIEW explode(column_name) dummy_name as key, value;

 

Please give us your feedback of this post..

Leave a Reply

Your email address will not be published. Required fields are marked *