Hive SQL——技能 - 解析json列表，直至字段维度

小白问题描述：

通过类似word2vec等方法，利用商品的历史订单，我们可以获取一些商品之间的关联关系。通常这些关联关系是以json串的形式存储在Hive表中，且一个关联关系下包含多个商品，通常的格式为：

商品	关联商品列表
1001	[{"product_id":"1002","confidence":0.86},{"product_id":"1003","confidence":0.72}]
1004	[{"product_id":"1005","confidence":0.89},{"product_id":"1001","confidence":0.63}]

其中，product_id代表商品的id，confidence代表当前商品与第一列商品的关联程度。

 

但是，在我们平时使用或查询某商品的关联商品时，只能获取“关联商品列表”这一坨列表化的json数据，十分繁琐。因此，为了方便查询和使用我们需要将其解析为如下形式：

商品	关联商品	置信度（关联程度）
1001	1002	0.86
1001	1003	0.72
1004	1005	0.89
1004	1001	0.63

 

秉承着从0开始的思想，我们先建立Hive表，写入几行测试数据：

create
	table `tmp.tmp_product_relation_info`
	(
        `product_id` string COMMENT '商品id',
		`relation_list` string COMMENT '相关商品列表'
	)
	row format delimited fields terminated by '\t' stored as textfile;

insert into tmp.tmp_product_relation_info values ('1001', '[{"product_id":"1002","confidence":0.86},{"product_id":"1003","confidence":0.72}]') ;

insert into tmp.tmp_product_relation_info values ('1004', '[{"product_id":"1005","confidence":0.89},{"product_id":"1001","confidence":0.63}]') ;

Hive表构建完成插入数据后，查看下当前数据的存储情况：

select * from tmp.tmp_product_relation_info;

 

如上图中的relation_list所示，为了将其拆分，我们首先想到的就是划分为多个json形式的串。

考虑如果使用逗号分隔符使用split拆分，会拆得十分乱，达不到拆分目的。

此时，小trick驾到：

• 1. 将'},'替换为'}-'，则有‘[{"product_id":"1002","confidence":0.86},{"product_id":"1003","confidence":0.72}]’变为‘[{"product_id":"1002","confidence":0.86}-{"product_id":"1003","confidence":0.72}]’；

• 2. 将首尾中括号去掉，则有‘{"product_id":"1002","confidence":0.86}-{"product_id":"1003","confidence":0.72}’。

 

从而得到了可split的数据，之后使用lateral view explode完美划分为我们需求的格式。上代码：

select
	product_id,
    related_product_id,
    confidence
from
	(
		select
        	product_id,
			relation_list,
			regexp_replace(regexp_replace(regexp_replace(relation_list, '},', '}-'), '\\[', ''), '\\]', '') as json_list
		from
			tmp.tmp_product_relation_info
	)
	t lateral view explode(split(json_list, '-')) lat1 as product
    lateral view json_tuple(product, 'product_id', 'confidence') lat2 as related_product_id, confidence;

一对一商品间的关联关系

 

其中，json_tupe函数是Hive自带的解析正常json串的函数，可直接解析出一个串中的多个字段，得到的结果如上表所示，方便我们后续使用和查询。