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