共计 4462 个字符,预计需要花费 12 分钟才能阅读完成。
在 Hive 中会有很多数据是用 Json 格局来存储的,如开发人员对 APP 上的页面进行埋点时,会将多个字段寄存在一个 json 数组中,因而数据平大数据培训台调用数据时,要对埋点数据进行解析。接下来就聊聊 Hive 中是如何解析 json 数据的。
Hive 自带的 json 解析函数
- 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 这个函数。
- json_tuple
• 语法:json_tuple(json_string, k1, k2 …)
• 阐明:解析 json 的字符串 json_string, 可指定多个 json 数据中的 key,返回对应的 value。如果输出的 json 字符串有效,那么返回 NULL。
• 示例:
select
b.name
,b.age
from tableName a lateral view
json_tuple(‘{“name”:”zhangsan”,”age”:18}’,’name’,’age’) b as name,age;
• 后果:
留神:下面的 json_tuple 函数中没有 $.
如果在应用 json_tuple 函数时加上 $. 就会解析失败:
select
b.name
,b.age
from tableName a lateral view
json_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 构造拆分成多行显示,也被称为列转行函数。
• 示例:
— 解析 array
hive> select explode(array(‘A’,’B’,’C’));
OK
A
B
C
— 解析 map
hive> select explode(map(‘A’,10,’B’,20,’C’,30));
OK
A 10
B 20
C 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’, ”);
OK
fb
上述示例将字符串中的 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 解析进去。
上面咱们就开始解析:
拆分 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 在所有用户中呈现的次数:
- 对趣味 id 进行解析:
SELECT name, hobby_id
FROM hobbies_table
LATERAL VIEW explode(id_list) tmp_table AS hobby_id;
上述 sql 执行后果:
- 依照 hobby_id 进行分组聚合即可:
SELECT hobby_id ,count(name) client_num
FROM hobbies_table
LATERAL VIEW explode(id_list) tmp_table AS hobby_id
GROUP BY hobby_id;
后果:
介绍完 lateral view 之后,咱们再来解决下面遇到的用 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;
留神:上述语句是三个表笛卡尔积的后果,所以此形式实用于数据量不是很大的状况。
上述语句执行后果如下:
如果表中还有其余字段,咱们能够依据其余字段筛选出合乎后果的数据。
总结:lateral view 通常和 UDTF 一起呈现,为了解决 UDTF 不容许在 select 存在多个字段的问题。