社区编辑申请
注册/登录
Hive 完美解析 Json 数组的函数
数据库 其他数据库
这里将会介绍 Hive 中常用于 json 数组的解析函数及详细使用方法。

背景

大数据的 ETL(Extract-Transfer-Load) 过程的 Transfer 阶段,需要对 json 串数据进行转换“拍平”处理。

亲测!超好用 ​​Hive 内置的 json 解析函数​​ 一文中详细介绍过 get_json_object 和 json_tuple 函数如何对 json 串进行有效解析,但美中不足的是这两个函数都无法解析 json 数组,只能解析单个 json 串。

这里将会介绍 Hive 中常用于 json 数组的解析函数及详细使用方法。

json数组解析:需求1

数据准备

例如:Hive中有一张 test_json 表,表中 json_data 字段的内容如下:

基于以上的 json_data 数据,现需要将以上 json 串数据解析为如下结构数据:

在进行解析之前,先来了解下面两个函数的使用方法。

函数运用

1、explode函数

语法

explode(Array|Map)

说明

explode()函数接收一个 array 或者 map 类型的数据作为输入,然后将 array 或 map 里面的元素按照每行的形式输出。

即将 Hive 一列中复杂的 array 或者 map 结构拆分成多行显示,也被称为列转行函数。

举例

array测试sql语句:

select explode(array('user_id','name','age'));

执行结果:

map测试sql语句:

select explode(map('user_id',1,'name','rocky','age',18));

执行结果:

2、regexp_replace函数

语法

regexp_replace(str A, str B, str C)

说明

语法含义:将字符串 A 中的符合正则表达式 B 的部分替换为 C。

注意:当字符串 A 中有一些特殊字符时,在正则表达式 B 中要使用转义字符。

举例

sql语句:

select regexp_replace('hello world!', '\\ |\\!', '');

执行结果:

3、 具体函数运用

了解 explode 函数与 regexp_replace 函数的使用规则后,现在来完成上面数据准备中提出的解析需求。

第一步解析:json数组拆分成多行

sql语句:

SELECT explode(split(
regexp_replace(
regexp_replace(
'[
{"user_id":"1","name":"小琳","age":16},
{"user_id":"2","name":"小刘","age":18},
{"user_id":"3","name":"小明","age":20}
]',
'\\[|\\]' , ''), 将json数组两边的中括号去掉

'\\}\\,\\{' , '\\}\\;\\{'), 将json数组元素之间的逗号换成分号

'\\;') 以分号作为分隔符(split函数以分号作为分隔)
);

执行结果:

第二步解析:json数组key转列字段

sql语句:

select json_tuple(json, 'user_id', 'name', 'age') 
from (select explode(split(
regexp_replace(
regexp_replace(
'[
{"user_id":"1","name":"小琳","age":16},
{"user_id":"2","name":"小刘","age":18},
{"user_id":"3","name":"小明","age":20}
]',
'\\[|\\]' , ''),
'\\}\\,\\{' , '\\}\\;\\{'),
'\\;')
)as json) tmp;

执行结果:

json数组解析:需求2

数据准备

例如:

Hive中有一张 data_json 表,表中 goods_id 和 str_data 字段的内容如下:

基于以上的 goods_id 和 str_data 数据,现需要将以上 json 串数据解析为如下结构数据:

在进行解析之前,先来了解下面两个函数的使用方法。

函数运用

1、 lateral view函数

说明

lateral view 用于和 split, explode 等 UDTF 一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。

lateral view 首先为原始表的每行调用 UDTF,UDTF 会把一行拆分成一行或者多行,lateral view 在把结果组合,产生一个支持别名表的虚拟表。

举例

例如:Hive 中有一张 page_ads 表,表数据结构如下:

page_name 代表页面名称,ads_id 代表投放广告的所属 id,多个 id之间使用逗号分隔。

需求:统计所有广告 id 在所有页面中出现的次数。

第一步解析:拆分广告id

拆分sql语句:

SELECT page_name, ads_id 
FROM page_ads LATERAL VIEW explode(ads_id) adTable AS adid;

拆分结果:

第二步解析:聚合统计

聚合统计sql语句:

SELECT adid, count(1) 
FROM page_ads LATERAL VIEW explode(ads_id) adTable AS adid
GROUP BY adid;

统计结果:

2、 具体函数运用

解析 data_json 表的sql语句如下:

select goods_id,get_json_object(sale_json,'$.sold') as sold
from data_json
LATERAL VIEW explode(split(goods_id,','))goods as goods_id
LATERAL VIEW explode(split(
regexp_replace(
regexp_replace(json_str , '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) sales as sale_json;

注意:

上述语句是 3*3 笛卡尔积的结果,所以此方式适用于数据量不是很大的情况。

执行结果如下:

责任编辑:姜华 来源: 今日头条

同话题下的热门内容

开发一套高容错分布式系统多套环境的数据库隔离,域名访问,差异化配置,香!快解锁!挑战ArkUI复刻手机备忘录(Rdb数据库)不加机器,如何抗住每天百亿级高并发流量?自研ES+HBase+纯内存的高性能毫秒级查询引擎聊聊 SQL 中的 For Xml PathUlimits不生效导致数据库启动失败和相关设置说明百亿流量全链路99.99%高可用架构优秀实践

编辑推荐

几款开源的图形化Redis客户端管理软件推荐NoSQL数据库概览及其与SQL语法的比较为什么MongoDB敢说“做以前你从未能做的事”Python操作MongoDB看这一篇就够了一文看懂分布式数据库原理和 PostgreSQL 分布式架构
我收藏的内容
点赞
收藏

AISummit人工智能大会