Hive对JSON格式的支持研究

Z时代
2024-01-10
分类：技术分享

一、背景

JSON是一种通用的存储格式，在半结构化存储中十分常见，部分场景已经开始存在以JSON格式贴源存储的数据，作为下游数据使用方，我们亟需对JSON格式的数据进行加工和处理，以提取出我们需要的数据，以对外提供更完善的数据服务。

经过调研，目前hive已对JSON格式的数据提供了相应的支持，但在一些复杂场景可能无法达到我们的需求以及理想的性能，具体介绍如下。

二、解析JSON对象的方法

（一）get_json_object(string json_string, string path)

返回值：String

说明：解析json的字符串json_string，返回path指定的内容。如果输入的json字符串无效，那么返回NUll，这个函数每次只能返回一个数据项。

测试a：

--1 测试get_json_object()
select get_json_object('{"name":"小明","age":"18"}','$.name');
输出：小明

测试b：

数据json.txt

{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}
{"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"}
{"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"}
{"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"}
{"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"}
{"movie":"1197","rate":"3","timeStamp":"978302268","uid":"1"}
{"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"}
{"movie":"2804","rate":"5","timeStamp":"978300719","uid":"1"}
{"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"}

create table json(data string);
load data local inpath '/home/hadoop/json.txt' into table json;
select * from json;
select get_json_object(data,'$.movie') as movie from json;

输出：
Hive对JSON格式的支持研究

限制

该方法只能返回一个JSON属性的数据，不能同时返回多个。若为了获取多个JSON属性的数据而多次调用get_json_object方法，相当于对JSON数据进行重复多次解析，性能会有所损耗。那么，是否有一个方法能够解析一次JSON数据，返回多个JSON属性的数据呢？有，那就是json_tuple()。

（二）json_tuple(jsonStr, k1, k2, ...)

返回值：tuple

说明：参数为json字符串和一组键k1，k2，…，返回值的元组。因为可以在一次调用中输入多次键，一次可以解析多个Json字段，因此该方法比get_json_object高效。

测试：

select b.b_movie,b.b_rate,b.b_timeStamp,b.b_uid from json a lateral view json_tuple(a.data,'movie','rate','timeStamp','uid') b as b_movie,b_rate,b_timeStamp,b_uid;

限制

该方法和上面介绍的get_json_object()方法都无法对JSON数组进行解析。我们将在第三节对JSON数组的解析进行专题介绍。

（三）自定义函数解析json对象

自定义函数

1.
2.	package com.data;
3.
4.	import org.apache.commons.lang3.StringUtils;
5.	import org.apache.hadoop.hive.ql.exec.UDF;
6.	import org.json.JSONException;
7.	import org.json.JSONObject;
8.	import org.json.JSONTokener;
9.
10.	/**
11.	 *
12.	 *  add jar jar/getJsonObjectUDF.jar;
13.	 * create temporary function getJsonObject as 'com.data.JsonObjectParsing';
14.	 * Json对象解析UDF
15.	 * @Author:
16.	 * @Date: 2020/9/25
17.	 */
18.	public class JsonObjectParsing extends UDF {
19.	    public static String evaluate(String jsonStr, String keyName) throws JSONException {
20.	        if(StringUtils.isBlank(jsonStr) || StringUtils.isBlank(keyName)){
21.	            return null;
22.	        }
23.	        JSONObject jsonObject = new JSONObject(new JSONTokener(jsonStr));
24.	        Object objValue = jsonObject.get(keyName);
25.	        if(objValue==null){
26.	            return null;
27.	        }
28.	        return objValue.toString();
29.	    }
30.	}

测试：

--1 将getJsonObjectUDF.jar上传到hdfs
source ${HADOOP_CLIENT}/bigdata_env
kinit -k etluser/hadoop -t ${WORK_ROOT}/etl_tools/config/etluser.keytab
hadoop fs -rm -f -r /user/etluser/pgm/tempUDF
hadoop fs -mkdir -f -r /user/etluser/pgm/tempUDF
hdsoop fs -put ${WORK_ROOT}/pgm/tempUDF/getJsonObjectUDF.jar /user/etluser/pgm/tempUDF/
--2 创建临时函数getJsonObject()
use udf;
set hive.security.temporary.function.need.admin=false;
create temporary function getJsonObject as 'com.data.JsonObjectParsing' using jar '/user/etluser/pgm/tempUDF/getJsonObjectUDF.jar';
--3 建表db.json
DROP TABLE IF EXISTS DB.JSON;
CREATE TABLE IF NOT EXISTS DB.JSON (
JSON_DATA STRING COMMENT 'json'
)
COMMENT 'hive json'
PARTITIONED BY (pt_dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\27'
STORED AS TEXTFILE;
--4 插入测试数据
INSERT INTO TABLE DB.JSON VALUES('{"id":"3","age":"12"}');
--5 使用临时函数getJsonObject()
SELECT getJsonObject(JSON_DATA,'id') as id, getJsonObject(JSON_DATA,'age') as age FROM DB.JSON WHERE pt_dt='2020-09-25';
输出：3  12

三、解析JSON数组的方法

（一）使用Hive自带的函数解析Json数组

说明：Hive的内置的explode函数，explode()函数接收一个 array或者map 类型的数据作为输入，然后将 array 或 map 里面的元素按照每行的形式输出。其可以配合 LATERAL VIEW 一起使用。

测试：

select explode(array('A','B','C'));
输出： A
B
C
select explode(map('A',10,'B',20,'C',30));
输出：A       10
B       20
C       30

这个explode函数和我们解析json数据是有关系的，我们可以使用explode函数将json数组里面的元素按照一行一行的形式输出：

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

结合 get_json_object 或 json_tuple 来解析里面的字段：

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

（二）自定义函数解析JSON数组

虽然可以使用Hive自带的函数类解析Json数组，但是使用起来有些麻烦。Hive提供了强大的自定义函数（UDF）的接口，我们可以使用这个功能来编写解析JSON数组的UDF。

自定义函数

1.
2.	package com.data;
3.
4.	import org.apache.hadoop.hive.ql.exec.Description;
5.	import org.apache.hadoop.hive.ql.exec.UDF;
6.	import org.json.JSONArray;
7.	import org.json.JSONException;
8.	import java.util.ArrayList;
9.
10.
11.	public class JsonArray extends UDF{
12.	        public ArrayList<String> evaluate(String jsonString) {
13.	            if (jsonString == null) {
14.	                return null;
15.	            }
16.	            try {
17.	                JSONArray extractObject = new JSONArray(jsonString);
18.	                ArrayList<String> result = new ArrayList<String>();
19.	                for (int ii = 0; ii < extractObject.length(); ++ii) {
20.	                    result.add(extractObject.get(ii).toString());
21.	                }
22.	                return result;
23.	                } catch (JSONException e) {
24.	                return null;
25.	                } catch (NumberFormatException e) {
26.	                return null;
27.	            }
28.	        }
29.
30.	}

将上面的代码进行编译打包,jar包名为：getJsonArrayUDF.jar

--1 将getJsonArrayUDF.jar上传到hdfs
source ${HADOOP_CLIENT}/bigdata_env
kinit -k etluser/hadoop -t ${WORK_ROOT}/etl_tools/config/etluser.keytab
hadoop fs -rm -f -r /user/etluser/pgm/tempUDF
hadoop fs -mkdir -f -r /user/etluser/pgm/tempUDF
hdsoop fs -put ${WORK_ROOT}/pgm/tempUDF/getJsonArrayUDF.jar /user/etluser/pgm/tempUDF/
--2 创建临时函数getJsonObject()
use udf;
set hive.security.temporary.function.need.admin=false;
create temporary function getJsonArray as 'com.data.JsonArray' using jar '/user/etluser/pgm/tempUDF/getJsonArrayUDF.jar';
--3 使用临时函数getJsonObject()
select explode(getJsonArray('[{"website":"www.baidu.com","name":"百度"},{"website":"google.com"name":"谷歌"}]'));
输出：www.baidu.com   百度
google.com      谷歌

四、对where条件的支持

（一）get_json_object(string json_string, string path)对where条件的支持

是否支持：是

实验

SELECT  b.b_movie,b.b_rate,b.b_timeStamp,b.b_uid from db.json a
lateral view json_tuple(a.json_data, 'movie', 'rate', 'timeStamp', 'uid') b as b_movie,b_rate,b_timestamp,b_uid
where a.pt_dt='2020-09-25'
and get_json_object(a.json_data,'$.movie')='661';

（1）输出：

661 3 9978302109 1

（2）源表数据量：9条JSON格式数据，每条JSON数据又4个属性。

（3）耗时：35.39s

（二）json_tuple(jsonStr, k1, k2, ...)对where条件的支持

是否支持：是

实验

SELECT  b.b_movie,b.b_rate,b.b_timeStamp,b.b_uid from db.json a
lateral view json_tuple(a.json_data, 'movie', 'rate', 'timeStamp', 'uid') b as b_movie,b_rate,b_timestamp,b_uid
where a.pt_dt='2020-09-25'
and b.b_movie='661' and b.b_rate='3';

（1）输出

661 3 978302109 1