1

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?

animal
  • 994
  • 3
  • 13
  • 35
Sai
  • 97
  • 1
  • 2
  • 17
  • 1
    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 May 23 '16 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 May 23 '16 at 23:50
  • Possible duplicate of [Hive Explode / Lateral View multiple arrays](http://stackoverflow.com/questions/20667473/hive-explode-lateral-view-multiple-arrays) – o-90 May 23 '16 at 23:57
  • this one i tried .do i need to add any jar file? – Sai May 24 '16 at 00:04
  • Ya you'll need the jar mentioned in that question – o-90 May 24 '16 at 02:27

3 Answers3

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 .

sandeep rawat
  • 4,797
  • 1
  • 18
  • 36
  • 1
    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. – Roberto Congiu May 23 '16 at 00:17
  • as per sample he is looking for Cartesian product . – sandeep rawat May 23 '16 at 03:12
  • 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. – Sai May 23 '16 at 19:34
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 );
Jerome Banks
  • 1,620
  • 11
  • 15
1

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.

user2359902
  • 121
  • 1
  • 7