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 数组 第一步：先将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数组元素之间的逗号换成分号？ 因为元素内的分隔也是逗号，如果不将元素之间的逗号换掉的话，后面用split函数分隔时也会把元素内的数据给分隔，这不是我们想要的结果。 第二步、上步已经把一个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数组

样例数据如下

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
1	4900
1	2090
1	6987
2	4900
2	2090
2	6987
3	4900
3	2090
3	6987