Hive Query: working with String Array

Question

I have a HIVE Table with following schema like this:

hive>desc books;
gen_id                  int                                         
author                  array<string>                               
rating                  double                               
genres                  array<string>  

hive>select * from books;

| gen_id         | rating    | author          |genres
+----------------+-------------+---------------+----------
| 1              | 10        | ["A","B"]       | ["X","Y"]  
| 2              | 20        | ["C","A"]       | ["Z","X"]
| 3              | 30        | ["D"]           | ["X"]

Is there a query where I can perform some SELECT operation and that returns individual rows, like this:

| gen_id      |  rating        | SplitData
+-------------+---------------+-------------
| 1           | 10            | "A"
| 1           | 10            | "B"
| 1           | 10            | "X"
| 1           | 10            | "Y"
| 2           | 20            | "C"
| 2           | 20            | "A"
| 2           | 20            | "Z"
| 2           | 20            | "X"
| 3           | 30            | "D"
| 3           | 30            | "X"

Can someone guide me how can get to this result. Thanks in advance for any kind of help.

www · Accepted Answer · 2015-03-13 13:39:26Z

3

You need to do Lateral view and explode,i.e.

SELECT
    gen_id,
    rating,
    SplitData 
FROM (
    SELECT  
       gen_id,
       rating, 
       array (ex_author,ed_genres) AS ar_SplitData  
    FROM 
       books 
       LATERAL VIEW explode(books.author) exploded_authors AS ex_author
       LATERAL VIEW explode(books.genres) exploded_genres AS ed_genres  
) tab
LATERAL VIEW explode(tab.ar_SplitData) exploded_SplitData AS SplitData;

I had no chance to test it but it should show you general path. GL!

edited Mar 13, 2015 at 13:39

answered Mar 13, 2015 at 9:56

www

4,4011 gold badge26 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hive Query: working with String Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related