在hive中解析json数据,一般会想到get_json_object函数,当然json数据的复杂程度不一样,解析方法也会不一样,本文总结一下简单json和复杂json的解析过程。
1、简单json的解析
这里把只包含map(可以嵌套map)的json定义为简单json,这种数据比较容易解析,直接调用get_json_object函数就可以。
一个map情况:{“bssid”:“6C:59:40:21:05:C4”,“ssid”:“MERCURY_05C4”}
select get_json_object(‘{“bssid”:“6C:59:40:21:05:C4”,“ssid”:“MERCURY_05C4”}’,‘$.bssid’) as bssid from dual;
运行结果:
bssid
6C:59:40:21:05:C4
map嵌套的情况:{“person”:“tom”,“food”:{“fruit”:“apple”,“meat”:“pig”}}
select get_json_object(‘{“person”:“tom”,“food”:{“fruit”:“apple”,“meat”:“pig”}}’,‘
.
p
e
r
s
o
n
′
)
a
s
p
e
r
s
o
n
,
g
e
t
_
j
s
o
n
_
o
b
j
e
c
t
(
′
"
p
e
r
s
o
n
"
:
"
t
o
m
"
,
"
f
o
o
d
"
:
"
f
r
u
i
t
"
:
"
a
p
p
l
e
"
,
"
m
e
a
t
"
:
"
p
i
g
"
′
,
′
.person') as person, get\_json\_object('{"person":"tom","food":{"fruit":"apple","meat":"pig"}}','
.person′)asperson,get_json_object(′"person":"tom","food":"fruit":"apple","meat":"pig"′,′.food.fruit’) as fruit
from dual ;
运行结果:
person fruit
tom apple
通过上面两个例子发现,对于类似这样简单json的解析,不管字段是在第一层map(第一个例子中的bssid)还是在嵌套的map(第二个例子中的fruit)里面,解析起来都挺简单。
2、复杂json的解析
这里主要讨论map数组的解析,比如[{“bssid”:“6C:59:40:21:05:C4”,“ssid”:“MERCURY_05C4”},{“bssid”:“AC:9C:E4:04:EE:52”,“appid”:“10003”,“ssid”:“and-Business”}]。
假如要解析bssid和ssid字段,调用get_json_object返回的是map数组,因为有多个数据项,无法直接解析出bssid和ssid。按照一般的逻辑,有多少数据项就产生多少条记录,相当于一条记录变成多条记录,这样的话解析起来就变得没那么简单,下面提供两种方法来处理这样的数据。
a.笛卡尔积方法
假如待解析表的字段(map数组型json)的数据项都是一样多的,也就是说数组的长度一样,比如长度都为2,数据样例[{“bssid”:“6C:59:40:21:05:C4”,“ssid”:“MERCURY_05C4”},{“bssid”:“AC:9C:E4:04:EE:52”,“appid”:“10003”,“ssid”:“and-Business”}]
set hive.mapred.mode=nostrict ; --打开笛卡尔积的开关
select
case when b.ind=0 then get_json_object(a.appinfo,‘KaTeX parse error: Undefined control sequence: \[ at position 1: \̲[̲0\].bssid') els…[1].bssid’) end bssid,
case when b.ind=0 then get_json_object(a.appinfo,‘KaTeX parse error: Undefined control sequence: \[ at position 1: \̲[̲0\].ssid') else…[1].ssid’) end ssid
from (
select ‘[{“bssid”:“6C:59:40:21:05:C4”,“ssid”:“MERCURY_05C4”},{“bssid”:“AC:9C:E4:04:EE:52”,“appid”:“10003”,“ssid”:“and-Business”}]’ as appinfo
) a
join (select 0 as ind from dual union all
select 1 as ind from dual) b ;
运行结果:
bssid ssid
6C:59:40:21:05:C4 MERCURY_05C4
AC:9C:E4:04:EE:52 and-Business
这种方法有两个缺点,其一map数组的长度很难保证一样长;其二数组长度大于10代码会很长,写起来麻烦也容易出错,所以有了下面的方法。
b.explode函数
hive中自带了explode函数,从而让解析map数组变得简单灵活,这里先介绍一下explode的使用方法。
explode(array)
select explode(array(‘A’,‘B’,‘C’)) as col;
或
select tf.* from (select 0 from dual) t lateral view explode(array(‘A’,‘B’,‘C’)) tf as col;
运行结果:
col
C
B
A
函数说明:explode的参数是数组,提供了类似于列转的功能;假如参数数组长度为3,则返回的记录会是3行,且每列为各个数组项,如上。
回到[{“bssid”:“6C:59:40:21:05:C4”,“ssid”:“MERCURY_05C4”},{“bssid”:“AC:9C:E4:04:EE:52”,“appid”:“10003”,“ssid”:“and-Business”}],怎么解析出bssid思路是通过explode把原数据变成2行数据({“bssid”:“6C:59:40:21:05:C4”,“ssid”:“MERCURY_05C4”}和{“bssid”:“AC:9C:E4:04:EE:52”,“appid”:“10003”,“ssid”:“and-Business”}),然后再使用get_json_object解析。
具体代码如下:
select
case when ss.col regexp ‘^\{’ and not ss.col regexp ‘\}KaTeX parse error: Expected 'EOF', got '}' at position 23: …concat(ss.col,'}̲') when not ss.…’ then concat(‘{’,ss.col)
when ss.col regexp ‘^\{’ and ss.col regexp ‘\}KaTeX parse error: Got function '\\' with no arguments as superscript at position 75: …ct(a.appinfo,'^\̲\̲\[(.+)\\\]’,1),‘\}\,\{’) as str
from
(
select ‘[{“bssid”:“6C:59:40:21:05:C4”,“ssid”:“MERCURY_05C4”},{“bssid”:“AC:9C:E4:04:EE:52”,“appid”:“10003”,“ssid”:“and-Business”}]’ as appinfo
) a ) pp
lateral view explode(pp.str) ss as col ;
运行结果:
appinfo
{“bssid”:“6C:59:40:21:05:C4”,“ssid”:“MERCURY_05C4”}
{“bssid”:“AC:9C:E4:04:EE:52”,“appid”:“10003”,“ssid”:“and-Business”}
说明:因为原数据是string(并不是真正的数组类型)类型的,所以无法直接使用explode函数。
1.regexp_extract(‘xxx’,‘^\[(.+)\]$’,1) 这里是把需要解析的json数组去除左右中括号,需要注意的是这里的中括号需要两个转义字符\[。
2.使用split函数拆分成数组,分隔符为’\}\,\{',其实就是},{。
3.lateral view explode处理2中返回的数组。
4.因为使用},{为分隔符,导致了拆分出来的数据可能头部少了{,或者尾部少了},需要填充中括号。