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.
|ABC||[Book1, Book2, Book3]|
|DEF||[Book4, Book5, Book6]|
|GHI||[Book7, Book8, Book9]|
And the output should be:
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:
Output will be:
SELECT key, value FROM table_name LATERAL VIEW explode(column_name) dummy_name as key, value;
Please give us your feedback of this post..