hive 将两列按所在位置同时拆分成多行

Question

hive中有2个列表字段valuelist和sourcelist,形如:

[7fjv56lqblm6jhd, zm91ry6rctntum8, bqyvs09wsfnymts, 227206400, 2gq2tsqu993gi0i, kn7jk6npfmgejif]     [腾讯视频, 腾讯视频, 腾讯视频, 爱奇艺视频, 腾讯视频, 腾讯视频]

我要怎么样才能把它拆分成这样的格式:

7fjv56lqblm6jhd 腾讯视频
zm91ry6rctntum8 腾讯视频
bqyvs09wsfnymts 腾讯视频
....
kn7jk6npfmgejif 腾讯视频

我自己试过LATERAL VIEW explode但得到的结果却是这样的:

7fjv56lqblm6jhd tencent
7fjv56lqblm6jhd  tencent
7fjv56lqblm6jhd  tencent
7fjv56lqblm6jhd  iqiyi
7fjv56lqblm6jhd  tencent
7fjv56lqblm6jhd  tencent
 zm91ry6rctntum8    tencent
 zm91ry6rctntum8     tencent
 zm91ry6rctntum8     tencent
 zm91ry6rctntum8     iqiyi
 zm91ry6rctntum8     tencent
 zm91ry6rctntum8     tencent
 bqyvs09wsfnymts    tencent
 bqyvs09wsfnymts     tencent
 bqyvs09wsfnymts     tencent
 bqyvs09wsfnymts     iqiyi
 bqyvs09wsfnymts     tencent
 bqyvs09wsfnymts     tencent
 227206400  tencent
 227206400   tencent
 227206400   tencent
 227206400   iqiyi
 227206400   tencent
 227206400   tencent
 2gq2tsqu993gi0i    tencent
 2gq2tsqu993gi0i     tencent
 2gq2tsqu993gi0i     tencent
 2gq2tsqu993gi0i     iqiyi
 2gq2tsqu993gi0i     tencent
 2gq2tsqu993gi0i     tencent
 kn7jk6npfmgejif    tencent
 kn7jk6npfmgejif     tencent
 kn7jk6npfmgejif     tencent
 kn7jk6npfmgejif     iqiyi
 kn7jk6npfmgejif     tencent
 kn7jk6npfmgejif     tencent

这是我的查询语句:

select value_list,regexp_replace(regexp_replace(regexp_replace(regexp_replace(source_list,'腾讯视频','tencent'),'优酷视频','youku'),'爱奇艺视频','iqiyi'),'芒果视频','manguo') 
from tv_ai_voice_h 
LATERAL VIEW explode(split(regexp_replace(regexp_replace(valuelist,'\\[',''),']',''),','))myTable1 as value_list 
LATERAL VIEW explode(split(regexp_replace(regexp_replace(sourcelist,'\\[',''),']',''),','))myTable2 as source_list 
limit 140;

Answer 1

何不将两个字段分开做lateral view作为两个子查询然后对每行用row_number() over(）进行编号再根据编号做连接呢