hive实现json数组拆解

最新推荐文章于 2023-12-22 16:28:50 发布

原创最新推荐文章于 2023-12-22 16:28:50 发布 · 958 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#json hive

hive hadoop 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种从PAYCHANNELDETAIL表中拆解并处理JSON格式数据的方法，通过SQL语句实现了对复合型数据结构的有效解析，从而方便进一步的数据分析。

PAYCHANNELDETAIL表中间字段acquire_type是json格式如下


[{"payAmount":"375000","payChannelCode":"BOC"},{"payAmount":"376000","payChannelCode":"ABC"}]

下面是数据拆解操作


 select pay_order_id,tag1,bill_date
from (
select pay_order_id,substr(acquire_type,2,length(acquire_type)-2) tags,bill_date from dd.PAYCHANNELDETAIL
) ta  lateral view explode(split(tags, '},')) r1 as tag1
;

查询结果如下:


1603150000007617360	{"payAmount":"375000","payChannelCode":"BOC"}	2016-03-15
1603150000007617360	{"payAmount":"376000","payChannelCode":"ABC"}	2016-03-15

json属性拆解


SELECT 
pay_order_id,
get_json_object(subjson,'$.payAmount') as payAmount,
get_json_object(subjson,'$.payChannelCode') as payChannelCode,
bill_date
FROM
dd.PAYCHANNELDETAIL_SUBJSON