Hive中如何处理JSON格式数据
Hive 处理json数据总体来说有三个办法：
-
使用内建的函数get_json_object、json_tuple
-
使用自定义的UDF(一进一出)，自定义UDTF(一进多出)
-
第三方的SerDe--》JSONSerder
1、使用内建函数处理
get_json_object(string json_string, string path)
返回值：String
说明：解析json字符串json_string，返回path指定的内容；如果输入的json字符串无效，那么返回NUll；函数每次只能返回一个数据项；
json_tuple(jsonStr, k1, k2, ...)
返回值：所有的输入参数、输出参数都是String；
说明：参数为一组键k1，k2，。。。。。和json字符串，返回值的元组。该方法比get_json_object高效，因此可以在一次调用中输入多个键；
explode，使用explod将Hive一行中复杂的 array 或 map 结构拆分成多行。
测试数据：
user1;18;male;{"id": 1,"ids": [101,102,103],"total_number": 3}
user2;20;female;{"id": 2,"ids": [201,202,203,204],"total_number": 4}
user3;23;male;{"id": 3,"ids": [301,302,303,304,305],"total_number": 5}
user4;17;male;{"id": 4,"ids": [401,402,403,304],"total_number": 5}
user5;35;female;{"id": 5,"ids": [501,502,503],"total_number": 3}
建表加载数据：
CREATE TABLE IF NOT EXISTS jsont1(
username string,
age int,
sex string,
json string
)
row format delimited fields terminated by ';';
load data local inpath '/root/data/weibo.json' overwrite into table jsont1;
json的处理：
-- get 单层值
select username, age, sex, get_json_object(json, "$.id") id,
get_json_object(json, "$.ids") ids,
get_json_object(json, "$.total_number") num
from jsont1;
-- get 数组
select username, age, sex, get_json_object(json, "$.id") id,
get_json_object(json, "$.ids[0]") ids0,
get_json_object(json, "$.ids[1]") ids1,
get_json_object(json, "$.ids[2]") ids2,
get_json_object(json, "$.ids[3]") ids3,
get_json_object(json, "$.total_number") num
from jsont1;
-- 使用 json_tuple 一次处理多个字段
select json_tuple(json, 'id', 'ids', 'total_number')
from jsont1;
-- 有语法错误
select username, age, sex, json_tuple(json, 'id', 'ids', 'total_number')
from jsont1;
-- 使用 explode + lateral view
-- 在上一步的基础上，再将数据展开
-- 第一步，将 [101,102,103] 中的 [ ] 替换掉
-- select "[101,102,103]"
-- select "101,102,103"
select regexp_replace("[101,102,103]", "\\[|\\]", "");
-- 第二步，将上一步的字符串变为数组
select split(regexp_replace("[101,102,103]", "\\[|\\]", ""), ",");
-- 第三步，使用explode + lateral view 将数据展开
select username, age, sex, id, ids, num
from jsont1
lateral view json_tuple(json, 'id', 'ids', 'total_number') t1 as id, ids, num;
with tmp as(
select username, age, sex, id, ids, num
from jsont1
lateral view json_tuple(json, 'id', 'ids', 'total_number') t1 as id, ids, num
)
select username, age, sex, id, ids1, num
from tmp
lateral view explode(split(regexp_replace(ids, "\\[|\\]", ""), ",")) t1 as ids1;
小结：json_tuple 优点是一次可以解析多个json字段，对嵌套结果的解析操作复杂；
2、使用UDF处理
自定义UDF处理json串中的数组。自定义UDF函数：
-
输入：json串、数组的key
-
输出：字符串数组
pom文件
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>2.3.7</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.1.23</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>2.3.2</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
<plugin>
<artifactId>maven-assembly-plugin</artifactId>
<configuration>
<descriptorRefs>
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
</configuration>
<executions>
<execution>
<id>make-assembly</id>
<phase>package</phase>
<goals>
<goal>single</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
package cn.lagou.dw.hive.udf;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Strings;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.junit.Test;
import java.util.ArrayList;
public class ParseJsonArray extends UDF {
public ArrayList<String> evaluate(String jsonStr, String arrKey){
if (Strings.isNullOrEmpty(jsonStr)) {
return null;
}
try{
JSONObject object = JSON.parseObject(jsonStr);
JSONArray jsonArray = object.getJSONArray(arrKey);
ArrayList<String> result = new ArrayList<>();
for (Object o: jsonArray){
result.add(o.toString());
}
return result;
} catch (JSONException e){
return null;
}
}
使用自定义 UDF 函数：
-- 添加开发的jar包（在Hive命令行中）
add jar /root/edu_jars/my_udf.jar;
-- 创建临时函数。指定类名一定要完整的路径，即包名加类名
create temporary function lagou_json_array as "com.lagou.edu.ParseJsonArray";
-- 执行查询
-- 解析json串中的数组
select username, age, sex, lagou_json_array(json, "ids") ids
from jsont1;
-- 解析json串中的数组，并展开
select username, age, sex, ids1
from jsont1
lateral view explode(lagou_json_array(json, "ids")) t1 as ids1;
-- 解析json串中的id、num
select username, age, sex, id, num
from jsont1
lateral view json_tuple(json, 'id', 'total_number') t1 as id, num;
-- 解析json串中的数组，并展开
select username, age, sex, ids1, id, num
from jsont1
lateral view explode(lagou_json_array(json, "ids")) t1 as ids1
lateral view json_tuple(json, 'id', 'total_number') t1 as id, num;
3、 使用UDTF处理
UDTF返回结果如下：
| id | ids | total_number |
|---|---|---|
| 1 | 101 | 3 |
| 1 | 102 | 3 |
| 1 | 103 | 3 |
package com.lagou.edu.udtf;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import java.util.List;
/**
* 官网编写UDTF地址：https://cwiki.apache.org/confluence/display/Hive/DeveloperGuide+UDTF
*/
public class ParseJsonArrayUDTF extends GenericUDTF {
private String[] obj = new String[3];
/**
* 输出字段名称及数据类型
* @param argOIs
* @return
* @throws UDFArgumentException
*/