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..
A formidable share, I simply given this onto a colleague who was doing a bit evaluation on this. And he in actual fact purchased me breakfast as a result of I discovered it for him.. smile. So let me reword that: Thnx for the deal with! However yeah Thnkx for spending the time to discuss this, I feel strongly about it and love studying extra on this topic. If attainable, as you turn out to be experience, would you mind updating your weblog with more details? It’s highly helpful for me. Big thumb up for this weblog publish!