Hive中将数据一行转多行、多行转一行、再转为以逗号分隔的string类型

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

注：如若有上方代码还需要设置其他where的筛选条件（例：content_id不为4），筛选条件应放在lateral view这一行之后（即在这一行之后写where content_id != 4）。

得到的表结果为：

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，实现中文的匹配，之后再利用collect_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的转化错误的报错）

当时细心的我(手动狗头🐶)就发现上面表格得到的tags字段含有“[ ]”，而存入的B平台对tags字段的类型设定为string，就怀疑是因为这里所以存入失败。

故而，我还需要进行step 3。

Step 3：将step 2得到的表格转为以逗号分隔的形式

利用concat_ws将step 2中得到的tags字段转为以逗号分隔的形式，以方便存入使用mysql的B平台。

select content_id, concat_ws(',', 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	精彩操作,大神

之后再存入就可以成功存入了😉

参考资料

[1] https://blog.csdn.net/kwu_ganymede/article/details/53435656

[2] https://blog.csdn.net/yeweiouyang/article/details/41286469?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase