Hive中将数据一行转多行、多行转一行、再转为以逗号分隔的string类型
背景
表content中有观看内容id(content_id),内容标签id列表(tag_id_list),如下所示:
content_id | tag_id_list |
---|---|
1 | 1000,1001,1002 |
2 | 1000,1003 |
3 | 1002,1003 |
4 | 1002,1004 |
表id_decode中有内容标签id(tagid),内容标签中文(tagname),如下所示:
tagid | tagname |
---|---|
1000 | 游戏 |
1001 | 吃鸡 |
1002 | 精彩操作 |
1003 | 王者荣耀 |
1004 | 大神 |
目标:将原来content表中的内容标签id列表字段转换成内容标签中文列表。
Step 1:将content表的tag_id_list一行转多行
使用lateral view和split结合起来,将一行数据拆分为多行数据。
select content_id, tag_id_list, tag_id
from content
lateral view explode(split(tag_id_list, ',')) myTable as tag_id
得到的表结果为:
content_id | tag_id_list | tag_id |
---|---|---|
1 | 1000,1001,1002 | 1000 |
1 | 1000,1001,1002 | 1001 |
1 | 1000,1001,1002 | 1002 |
2 | 1000,1003 | 1000 |
2 | 1000,1003 | 1003 |
3 | 1002,1003 | 1002 |
3 | 1002,1003 | 1003 |
4 | 1002,1004 | 1002 |
4 | 1002,1004 | 1004 |
Step 2:将step 1中的表中tag_id匹配中文后,进行多行转一行
先将step 1中得到的表格与表id_decode进行left join,实现中文的匹配,之后再利用collset_set()这个函数将多行的中文转为一行。
select content_id, collect_set(tagname) as tags
from(
select content_id, tag_id_list, tag_id, tagname
from (
select content_id, tag_id_list, tag_id
from content
lateral view explode(split(tag_id_list, ',')) myTable as tag_id
) content_list_id
left join (
select tagid, tagname
from id_decode
) tran_id
on content_list_id.tag_id = tran_id.tagid
)
group by content_id
得到的表结果为:
content_id | tags |
---|---|
1 | [游戏,吃鸡,精彩操作] |
2 | [游戏,王者荣耀] |
3 | [精彩操作,王者荣耀] |
4 | [精彩操作,大神] |
至此,其实已经完成了内容标签id列表字段转换成内容标签中文列表的任务。
但是,在我之前的实际操作中,最后需要将A平台利用Hive得到的表写入使用mysql的B平台。而当以上述select语句得到的表进行直接的insert时候,会报错。(具体的报错内容不记得了,只记得报错语句里面有udf和string,依稀记得是关于udf和string的转化错误的报错)
当时细心的我(手动狗头