CODEWITHSUNDEEP
hivehiveql
S M
S M

Reputation: 101

How to extract values from array along with other columns in HIVE

Hi, I have a hive table with following columns partid string, store string and location array in table, values are save in this order 

part123, store123, location 
[{"position":1,"price":124.0,"card_pos":"External","clicked":0},
 {"position":2,"price":94.78,"card_pos":"Cbox","clicked":0},
 {"position":3,"price":94.77,"card_pos":"External","clicked":0}] 
>

I can see values of arry in one line which is fine, I would also like to see values from partid, store, location values in this way. >

+---------+-----+----------+--------+-----------+----------+  
partid |store   | position | price  | card_pos  | clicked  |  
+---------+-----+----------+--------+-----------+----------+  
part123|store123| 1        | 124.0    | External | 0       |     
part123|store123| 2        | 94.78    | Cbox     | 0       |    
part123|store123| 3        | 94.77    | External | 0       |  
+---------+-----+----------+----------+----------+---------+

> So far I can only see array values by using inline function in this way.

select inline(location) as (position, price, card_pos, clicked) from table;  

>+---------+--------+-----------+----------+  
| position | price  | card_pos  | clicked  |  
+----------+--------+-----------+----------+  
| 1        | 124.0    | External | 0       |   
| 2        | 94.78    | Cbox     | 0       |  
| 3        | 94.77    | External | 0       |   
+----------+----------+----------+---------+

>

Upvotes: 0

Views: 4481

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use lateral view in conjunction with inline and select the other columns.

select partid,store,t.position,t.price,t.card_pos,t.clicked
from table
lateral view inline(location) t as position, price, card_pos, clicked

Edit: Per OP's request to handle cases when the array column is null, use lateral view outer option.

select partid,store,t.position,t.price,t.card_pos,t.clicked
from table
lateral view outer inline(location) t as position, price, card_pos, clicked

Upvotes: 1

Related Questions