Hive - Split and count values by delimiter - lateral view explode

Question

I have a following Hive table

CustID    item_val

 10        sb1|sb2  
 11        sb2|sb3
 12        sb1|sb4
 13        sb2|sb3|sb5
 14        None
 15        sb1

I need to split and get the count values of each item in item_val column

Expected output

Item   count
sb1     3
sb2     2
sb3     2
sb4     1
sb5     1
None    1

I tired below count but values are not getting delimited properly.

 SELECT msg, count(*) FROM (SELECT distinct split(item_val,'\\|') AS msg FROM hive_table) t GROUP BY msg;

Please help!

Answer 1

In Hive, one option is to use the lateral view syntax:

select item, count(*) cnt
from mytable 
lateral view explode(split(item_val, '|')) item_val as item  
group by item