Hive table Array Columns - explode using array_index

Question

Hi i have a Hive table

select a,b,c,d from riskfactor_table 
In the above table B, C and D columns are array columns. Below is my Hive DDL 
Create external table riskfactor_table 
(a string, 
b array<string>, 
c array<double>, 
d array<double> ) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '~'  
stored as textfile location 'user/riskfactor/data';

Here is my table data:

ID400S,["jms","jndi","jaxb","jaxn"],[100,200,300,400],[1,2,3,4]
ID200N,["one","two","three"],[212,352,418],[6,10,8]

If i want to split array columns how can i split?
If i use explode function i can split array values for only one column

select explode(b) as b from riskfactor_table;

Output:

jms  
jndi  
jaxb  
jxn  
one  
two  
three

But i want all the columns to be populated using one select statement below-

Query - select a,b,c,d from risk_factor;

Output:

row1-  ID400S    jms    100    1  
row2-  ID400S    jndi   200    2  
row3-  ID400S    jaxb    300    3  
row4-  ID400S    jaxn    400    4

How can i populate all the data?

It is unclear what your desired output is. Where did ID400A, ID401S, and ID402E come from? They appear magically at the end of your question. — o-90
– o-90, Commented May 23, 2016 at 21:51
Hi, I have corrected my output. Can you please help me how can i populate data? Also if i get a null array value or values less than 4 fields in my input how to map the array columns? — Sai
– Sai, Commented May 23, 2016 at 23:50
Possible duplicate of Hive Explode / Lateral View multiple arrays — o-90
– o-90, Commented May 23, 2016 at 23:57

sandeep rawat · Accepted Answer · 2016-05-22 08:21:34Z

1

You can achieve this using LATERAL VIEW

       SELECT Mycoulmna, Mycoulmnb ,Mycoulmnc
                 FROM  riskfactor_table
             LATERAL VIEW explode(a) myTablea AS Mycoulmna
             LATERAL VIEW explode(a) myTableb AS Mycoulmnb
             LATERAL VIEW explode(a) myTablec AS Mycoulmnc ;

for more detail go throw it .

answered May 22, 2016 at 8:21

sandeep rawat

4,9571 gold badge22 silver badges40 bronze badges

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

3 Comments

Roberto Congiu Over a year ago

I don't think that's what @sai asked. The 3 lateral views will yield basically the cartesian product of the three array - that is, all the combinations of all the elements of each array with all the elements in the other arrays. In the example, he'd like only the tuples obtained taking the i-th element of each array.

sandeep rawat Over a year ago

as per sample he is looking for Cartesian product .

Sai Over a year ago

Hi I am not looking for cartesian product of three array.Basically i want to take out all the values in the array and load the data in Netezza DB using sqoop export.

Jerome Banks · Accepted Answer · 2016-08-18 22:27:46Z

1

Use the 'numeric_range' UDF from Brickhouse. Here is a blog posting describing the details.

https://brickhouseconfessions.wordpress.com/2013/03/07/exploding-multiple-arrays-at-the-same-time-with-numeric_range/

In your case, your query would be something like

SELECT a, 
       array_index( b, i ),
       array_index( c, i ),
       array_index( d, i )
FROM risk_factor_table
 LATERAL VIEW numeric_range( 0, 3 );

edited Aug 18, 2016 at 22:27

answered Aug 18, 2016 at 22:20

Jerome Banks

1,63011 silver badges15 bronze badges

Comments

user2359902 · Accepted Answer · 2016-08-20 03:59:42Z

I was also looking for same question's solution. Thanks Jerome, for this Brickhouse solution.

I had to make a slight change (addition of alias "n1 as n") as below to make it work for my case:

hive> describe test;
OK
id              string
animals     array<string>
cnt         array<bigint>

hive> select * from test;
OK
abc     ["cat","dog","elephant","dolphin","snake","parrot","ant","frog","kuala","cricket"]      [10597,2027,1891,1868,1804,1511,1496,1432,1305,1299]

hive> select `id`, array_index(`animals`,n), array_index(`cnt`,n) from test lateral view numeric_range(0,10) n1 as n;
OK
abc     cat             10597
abc     dog             2027
abc     elephant        1891
abc     dolphin         1868
abc     snake           1804
abc     parrot          1511
abc     ant             1496
abc     frog            1432
abc     kuala           1305
abc     cricket         1299

The only thing is I have to know beforehand that there are 10 elements to be exploded.

Collectives™ on Stack Overflow

Hive table Array Columns - explode using array_index

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related