How to convert comma separated array to rows in hive

Question

I try to pull anchorurl summary for each transactionid in my table, so I need convert comma separated array to rows.

This is running in Hive, mostly use sql command. (In DDC of Dbeaver actually)

SELECT      eventdate
            ,transactionid
            ,platform
            ,storeid
            ,StringTokenizerDelim(anchorurls,",") 
                over (PARTITION by eventdate,transactionid,platform,storeid)
FROM        scribe.tblscriberelatedsearchresults
order by    1,2,3
;


SELECT      eventdate
            ,transactionid
            ,platform
            ,storeid
            ,url
FROM        scribe.tblscriberelatedsearchresults lateral view explode(split(anchorurls,",")) anchorurls as url
order by    1,2,3
;

SELECT      eventdate
            ,transactionid
            ,platform
            ,storeid
            ,split_part(anchorurls,",") 
                over (PARTITION by eventdate,transactionid,platform,storeid)
FROM        scribe.tblscriberelatedsearchresults
order by    1,2,3
;

SELECT eventdate ,transactionid ,platform ,storeid ,split(anchorurls,",") FROM scribe.tblscriberelatedsearchresults order by 1,2,3 ;

One more thing is: I find command "explode" can get those array out perfectly, but I don't know how to also keep the other columns such as transactionid with them (which is the foreign key for joining another table)

Originally it was:

eventdate   transactionid   platform   storeid   "url1","url2","url3"

I expect it to be:

eventdate   transactionid   platform   storeid   url1
eventdate   transactionid   platform   storeid   url2
eventdate   transactionid   platform   storeid   url3

Error message:

Reason: SQL Error [42601]: ERROR: ('HY000', "[HY000] [Cloudera][HiveODBC] (35) Error from Hive: error code: '40000' error message: 'Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.\nUnderlying error: Invalid function split_part'. (35) (SQLExecDirectW)") Position: 13

Naga Naga · Accepted Answer · 2019-11-08T03:26:14

You can use lateral view explode to get output you're looking for

Please follow this link explained in detail with the examples

How to convert comma separated array to rows in hive

1 Answers