Hive 解析 Json 数组超全讲解

在 Hive 中会有很多数据是用 Json 格式来存储的，如开发人员对 APP 上的页面进行埋点时，会将多个字段存放在一个 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":"zhangsan","age":18}','$.name'); 

• 结果：

如果既要解析 name 字段，也解析 age 字段，则可以这样写：

select get_json_object('{"name":"zhangsan","age":18}','$.name'),get_json_object('{"name":"zhangsan","age":18}','$.age');

但是如果要解析的字段有很多，再这样写就太麻烦了，所以就有了 json_tuple 这个函数。

2. json_tuple

• 语法： json_tuple(json_string, k1, k2 ...)
  

• 说明：解析 json 的字符串 json_string,可指定多个 json 数据中的 key，返回对应的 value。如果输入的 json 字符串无效，那么返回 NULL。

• 示例：

select b.name,b.agefrom tableName a lateral viewjson_tuple('{"name":"zhangsan","age":18}','name','age') b as name,age;

• 结果:

注意：上面的json_tuple函数中没有 $.

如果在使用json_tuple函数时加上 $.就会解析失败：

select b.name,b.agefrom tableName a lateral viewjson_tuple('{"name":"zhangsan","age":18}','$.name','$.age') b as name,age;

结果:

字段全是 NULL，所以json_tuple函数不需要加 $.了，否则会解析不到。

总结：json_tuple 相当于 get_json_object 的优势就是一次可以解析多个 json 字段。但是如果我们有个 json 数组，这两个函数都无法处理。

Hive 解析 json 数组

一、嵌套子查询解析 json 数组

如果有一个 hive 表，表中 json_str 字段的内容如下：

我们想把这个字段解析出来，形成如下的结构：

要解析这个 json 数组，仅用上面介绍的两个函数就解析不出来了，还需用到如下介绍的几个函数：

explode 函数

• 语法： explode(Array OR Map)
  

• 说明：explode()函数接收一个 array 或者 map 类型的数据作为输入，然后将 array 或 map 里面的元素按照每行的形式输出，即将 hive 一列中复杂的 array 或者 map 结构拆分成多行显示，也被称为列转行函数。

• 示例：

-- 解析arrayhive> select explode(array('A','B','C'));OKABC-- 解析maphive> select explode(map('A',10,'B',20,'C',30));OKA       10B       20C       30

regexp_replace 函数

• 语法: regexp_replace(string A, string B, string C)

• 说明：将字符串 A 中的符合 java 正则表达式 B 的部分替换为 C。注意，在有些情况下要使用转义字符，类似 oracle 中的 regexp_replace 函数。

• 示例：

hive> select regexp_replace('foobar', 'oo|ar', ''); OKfb

上述示例将字符串中的 oo 或 ar 替换为''。

有了上述几个函数，接下来我们来解析 json_str 字段的内容：

• 先将 json 数组中的元素解析出来，转化为每行显示：

hive> SELECT explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;'));OK{"website":"baidu.com","name":"百度"}{"website":"google.com","name":"谷歌"}

对上述 sql 进行简要说明：

SELECT explode(split(    regexp_replace(        regexp_replace(            '[                {"website":"baidu.com","name":"百度"},                {"website":"google.com","name":"谷歌"}            ]',             '\\[|\\]' , ''), 将json数组两边的中括号去掉                          '\\}\\,\\{' , '\\}\\;\\{'), 将json数组元素之间的逗号换成分号                                 '\\;') 以分号作为分隔符(split函数以分号作为分隔)          );  

为什么要将 json 数组元素之间的逗号换成分号？因为元素内的分隔也是逗号，如果不将元素之间的逗号换掉的话，后面用 split 函数分隔时也会把元素内的数据给分隔，这不是我们想要的结果。

• 上步已经把一个 json 数组转化为多个 json 字符串了，接下来结合 son_tuple 函数来解析 json 里面的字段：

select json_tuple(explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) , 'website', 'name') ;

执行上述语句，结果报错了：FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions

意思是 UDTF 函数不能写在别的函数内，也就是这里的 explode 函数不能写在 json_tuple 里面。

既然 explode 函数不能写在别的 json_tuple 里面，那我们可以用子查询方式，如下所示：

select json_tuple(json, 'website', 'name') from (select explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) as json) t;

执行上述语句，没有报错，执行结果如下：

www.baidu.com   百度google.com      谷歌

二 使用 lateral view 解析 json 数组

hive 表中 goods_id 和 json_str 字段的内容如下：

目的：把 goods_id 字段和 json_str 字段中的 monthSales 解析出来。

下面我们就开始解析：

1. 拆分 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 UDTF's. Error encountered near token 'sale_info'

意思是用 UDTF 的时候，SELECT 只支持一个字段。而上述语句 select 中有两个字段，所以报错了。

那怎么办呢，要解决这个问题，还得再介绍一个 hive 语法：

lateral view

lateral view 用于和 split、explode 等 UDTF 一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view 首先为原始表的每行调用 UDTF，UDTF 会把一行拆分成一行或者多行，lateral view 在把结果组合，产生一个支持别名表的虚拟表。

• 示例：

假设我们有一张用户兴趣爱好表 hobbies_table，它有两列数据，第一列是 name，第二列是用户兴趣爱好的 id_list，是一个数组，存储兴趣爱好的 id 值：

我们要统计所有兴趣 id 在所有用户中出现的次数：

1. 对兴趣 id 进行解析：

SELECT name, hobby_id FROM hobbies_table LATERAL VIEW explode(id_list) tmp_table AS hobby_id;

上述 sql 执行结果：

将 id_list 拆分完之后统计在用户中出现的次数就简单了，按照 hobby_id 进行分组聚合即可：

SELECT hobby_id ,count(name) client_numFROM hobbies_table LATERAL VIEW explode(id_list) tmp_table AS hobby_idgroup by hobby_id;

结果：

介绍完 lateral view 之后，我们再来解决上面遇到的用 UDTF 的时候，SELECT 只支持一个字段的问题：

select good_id,get_json_object(sale_json,'$.monthSales') as monthSalesfrom 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;

注意：上述语句是三个表笛卡尔积的结果，所以此方式适用于数据量不是很大的情况。

上述语句执行结果如下：

如果表中还有其他字段，我们可以根据其他字段筛选出符合结果的数据。

总结：lateral view 通常和 UDTF 一起出现，为了解决 UDTF 不允许在 select 存在多个字段的问题。