hive解析json数据⽅法
json是常见的⼀种数据格式，⼀般通过埋点程序获取⾏为⽤户⾏为数据，将多个字段存放在⼀个json数组中，因此数据平台调⽤数据时，要对json数据进⾏解析处理。接下来介绍下Hive中是如何解析json数据的。
hive 解析 json 数据函数
1、get_json_object
语法：get_json_object(json_string, '$.key')
说明：解析json的字符串json_string，返回path指定的内容。如果输⼊的json字符串⽆效，那么返回NULL。这个函数每次只能返回⼀个数据项。
实例
select get_json_object('{"name":"令狐冲","age":29}', '$.name') user_name;
结果
解析⼆个字段⽰例：
select get_json_object('{"name":"依琳","age":16}', '$.name') user_name,
get_json_object('{"name":"依琳","age":16}', '$.age') user_age;
get_json_object解析json多个字段有很多会太⿇烦，可以使⽤ json_tuple。
2、json_tuple
语法： json_tuple(json_string, k1, k2 ...)
说明：解析json的字符串json_string，可指定多个json数据中的key，返回对应的value。如果输⼊的json字符串⽆效，那么返回NULL。
⽰例
select b.user_name, b.age
from (select*from temp.jc_test_coalesce_nvl where c1 =1) i lateral view
json_tuple('{"name":"依琳","age":18}', 'name', 'age') b as user_name, age;
json_tuple 使⽤细节：与 get_json_object 不同，使⽤ json_tuple 获取数据不需要使⽤ $，如果使⽤ $ 反⽽获取不到数据。
select b.user_name, b.age
from (select*from temp.jc_test_coalesce_nvl where c1 =1) i lateral view
json_tuple('{"name":"依琳","age":18}', '$.name', '$.age') b as user_name, age;
结果：使⽤json_tuple时需注意这⼀点
⼩结：json_tuple相当于get_json_object的优势就是⼀次可以解析多个json字段。但是这两个函数都⽆法处理json数组。
hive 解析 json 数组
1、使⽤嵌套⼦查询解析json数组
场景：⼀个hive表有 json_str 字段的内容如下：
json_str
[{"title":"笑傲镜湖","author":"⾦庸"},{"title":"⼩李飞⼑","author":"古龙"}]
希望解析出以下数据：
title author
笑傲镜湖⾦庸
⼩李飞⼑古龙
实现思路：
explode函数
语法：explode(Array OR Map)
说明：explode()函数接收⼀个array或者map类型的数据作为输⼊，然后将array或map⾥⾯的元素按照每⾏的形式输出，即将hive⼀列中复杂的array或者map结构拆分成多⾏显⽰，也被称为列转⾏函数。
⽰例
select array('A','B','C') ;
select explode(array('A','B','C'));
regexp_replace函数
语法: regexp_replace(string A, string B, string C)
说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使⽤转义字符，类
似oracle中的regexp_replace函数。
⽰例：将 ve_sp 替换成 @
select regexp_replace('hive_spark', 've_sp', '@');
下⾯我们试着解析 json 数组
hive 字符串转数组第⼀步：先将json数组中的元素解析出来，转化为每⾏显⽰
select explode(split(regexp_replace(regexp_replace('[{"title":"笑傲镜湖","author":"⾦庸"},{"title":"⼩李飞⼑","author":"古龙"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;'));
结果：
"{""title"":""笑傲镜湖"",""author"":""⾦庸""}"
"{""title"":""⼩李飞⼑"",""author"":""古龙""}"
上⾯SQL看着很长，但是⼀步⼀步看也很 esay
select explode(split(
regexp_replace(
regexp_replace(
'[
{"title":"笑傲镜湖","author":"⾦庸"},
{"title":"⼩李飞⼑","author":"古龙"}
]',
'\\[|\\]' , ''), --将json数组两边的中括号去掉
'\\}\\,\\{' , '\\}\\;\\{'),--将json数组元素之间的逗号换成分号
'\\;') --以分号作为分隔符(split函数以分号作为分隔)
)
;
说明：为什么要将json数组元素之间的逗号换成分号？
因为元素内的分隔也是逗号，如果不将元素之间的逗号换掉的话，后⾯⽤函数分隔时也会把元素内的数据给分隔，这不是我们想要的结果。第⼆步、上步已经把⼀个json数组转化为多个json字符串了，接下来使⽤json_tuple函数来解析json⾥⾯的字段：
select
json_tuple(explode(split(regexp_replace(regexp_replace('[{"title":"笑傲镜湖","author":"⾦庸"},{"title":"⼩李飞⼑","author":"古龙"}]', '\\[|\\]', ''),'\\}\\,\\{', '\\}\\;\\{'), '\\;'))
, 'title', 'author') ;
执⾏上述语句，结果报错了：
UDTF's are not supported outside the select clause, nor nested in expressions:17:16,
explode函数不能写在别的json_tuple⾥⾯，更正使⽤⼦查询⽅式
select json_tuple(json, 'title', 'author')
from (
select explode(split(regexp_replace(regexp_replace('[{"title":"笑傲镜湖","author":"⾦庸"},{"title":"⼩李飞⼑","author":"古龙"}]', '\\[|\\]', ''),'\\}\\,\\{', '\\}\\;\\{'), '\\;'))
as json) o
2、使⽤ lateral view 解析json数组
样例数据如下
goods_id json_str
1,2,3[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"}, {"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"}, {"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
期望结果：把 goods_id 字段和 json_str 字段中的monthSales解析出来。
⾸先：拆分goods_id字段及将json数组转化成多个json字符串
select
explode(split(goods_id,',')) as good_id,
explode(split(regexp_replace(regexp_replace(json_str , '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;'))
as sale_info
from tableName;
执⾏上述语句，结果报错：
FAILED: SemanticException 3:0 Only a single expression in the SELECT clause is supported with UD
TF's. Error encountered near token 'sale_info'
⽤UDTF的时候，SELECT 只⽀持⼀个字段。⽽上述语句select中有两个字段，所以报错了。
那怎么办呢，要解决这个问题，还得再介绍⼀个hive语法：
lateral view
lateral view⽤于和split、explode等UDTF⼀起使⽤的，能将⼀⾏数据拆分成多⾏数据，在此基础上可以对拆分的数据进⾏聚合，lateral view ⾸先为原始表的每⾏调⽤UDTF，UDTF会把⼀⾏拆分成⼀⾏或者多⾏，lateral view在把结果组合，产⽣⼀个⽀持别名表的虚拟表。
⽰例：⼀张⽤户⼤侠门派 user_table，它有两列数据，第⼀列是party_name，第⼆列是门派成员user_name，是⼀个数组，存储⼤侠的姓名：
party_name user_name
⽇⽉神教[东⽅不败，任盈盈，曲阳]
五岳剑派[令狐冲，依琳，刘正风]
数据准备
create table temp.jc_t_test_json
(
party_name string,
user_name array<string>
) row format delimited fields terminated by','-- 字段之间⽤','分隔
collection items terminated by'_'-- 集合中的元素⽤'_'分隔
map keys terminated by':'-- map中键值对之间⽤':'分隔
lines terminated by'\n';-- ⾏之间⽤'\n'分隔
insert into temp.jc_t_test_json select "⽇⽉神教", array("东⽅不败", "任盈盈", "曲阳");
insert into temp.jc_t_test_json select "五岳剑派", array ("令狐冲", "依琳","刘正风" );
select*from temp.jc_t_test_json;
select party_name, user_name_
from temp.jc_t_test_json
lateral view explode(user_name) tmp_table as user_name_;
按照 user_name_ 进⾏分组聚合即可：
select user_name_ ,count(party_name) name_cnt
from temp.jc_t_test_json
lateral view explode(user_name) tmp_table as user_name_
group by user_name_;
下⾯看下刚才遇到的⽤UDTF的时候，SELECT 只⽀持⼀个字段的问题
select good_id,get_json_object(sale_json,'$.monthsales') as monthsales
from tablename
lateral view explode(split(goods_id,','))goods as good_id
lateral view explode(split(regexp_replace(regexp_replace(json_str , '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) sales as sale_json;
goods_id monthSales
14900
12090
16987
24900
22090
26987
34900
32090 36987
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论