Hive Explode / Lateral View multiple arrays

Question

I have a hive table with the following schema:

COOKIE  | PRODUCT_ID | CAT_ID |    QTY     1234123   [1,2,3]    [r,t,null]  [2,1,null] 

How can I normalize the arrays so I get the following result

COOKIE  | PRODUCT_ID | CAT_ID |    QTY  1234123   [1]          [r]         [2]  1234123   [2]          [t]         [1]   1234123   [3]          null       null  

I have tried the following:

select concat_ws('|',visid_high,visid_low) as cookie ,pid ,catid  ,qty from table lateral view explode(productid) ptable as pid lateral view explode(catalogId) ptable2 as catid  lateral view explode(qty) ptable3 as qty 

however the result comes out as a Cartesian product.

Answer 1

I found a very good solution to this problem without using any UDF, posexplode is a very good solution :

SELECT COOKIE , ePRODUCT_ID, eCAT_ID, eQTY FROM TABLE  LATERAL VIEW posexplode(PRODUCT_ID) ePRODUCT_IDAS seqp, ePRODUCT_ID LATERAL VIEW posexplode(CAT_ID) eCAT_ID AS seqc, eCAT_ID LATERAL VIEW posexplode(QTY) eQTY AS seqq, eDateReported WHERE seqp = seqc AND seqc = seqq;

Answer 2

You can use the numeric_range and array_index UDFs from Brickhouse ( http://github.com/klout/brickhouse ) to solve this problem. There is an informative blog posting describing in detail over at http://brickhouseconfessions.wordpress.com/2013/03/07/exploding-multiple-arrays-at-the-same-time-with-numeric_range/

Using those UDFs, the query would be something like

select cookie,    array_index( product_id_arr, n ) as product_id,    array_index( catalog_id_arr, n ) as catalog_id,    array_index( qty_id_arr, n ) as qty from table lateral view numeric_range( size( product_id_arr )) n1 as n;