Hive学习小记-（16）hive加载解析json文件

json文件hive解析落表

不同于Hive学习小记-（5）表字段变动频繁时用json格式 那种简单存成string再解析，参考：

https://www.cnblogs.com/30go/p/8328869.html

https://blog.csdn.net/lsr40/article/details/103020021

（1）json数据准备，xftp到Linux

# test_json_load
{"student": {"name":"king","age":11,"sex":"M"},"sub_score":[{"subject":"语文","score":80},{"subject":"数学","score":80},{"subject":"英语","score":80}]}
{"student": {"name":"king1","age":11,"sex":"M"},"sub_score":[{"subject":"语文","score":81},{"subject":"数学","score":80},{"subject":"英语","score":80}]}
{"student": {"name":"king2","age":12,"sex":"M"},"sub_score":[{"subject":"语文","score":82},{"subject":"数学","score":80},{"subject":"英语","score":80}]}
{"student": {"name":"king3","age":13,"sex":"M"},"sub_score":[{"subject":"语文","score":83},{"subject":"数学","score":80},{"subject":"英语","score":80}]}
{"student": {"name":"king4","age":14,"sex":"M"},"sub_score":[{"subject":"语文","score":84},{"subject":"数学","score":80},{"subject":"英语","score":80}]}
{"student": {"name":"king5","age":15,"sex":"M"},"sub_score":[{"subject":"语文","score":85},{"subject":"数学","score":80},{"subject":"英语","score":80}]}
{"student": {"name":"king5","age":16,"sex":"M"},"sub_score":[{"subject":"语文","score":86},{"subject":"数学","score":80},{"subject":"英语","score":80}]}
{"student": {"name":"king5","age":17,"sex":"M"},"sub_score":[{"subject":"语文","score":87},{"subject":"数学","score":80},{"subject":"英语","score":80}]}

（2）建表

分析json格式数据源，student字段使用map结构，sub_score字段使用array嵌套map的格式，

这样使用的好处是如果数据源中只要第一层字段不会改变，都不会有任何影响，兼容性较强。

创建表语句如下, 注意row format serde中org.apache.hive.hcatalog.data.JsonSerDe这个json包，这样解析json出错时不至于程序挂掉。

tips：对于解析异常时报错的处理，可以加上一下属性：ALTER TABLE dw_stg.stu_score SET SERDEPROPERTIES ( "ignore.malformed.json" = "true");这里暂不涉及。

sc.sql(""" create table if not exists test_youhua.test_json_load(
student map<string,string> comment "学生信息",
sub_score array<map<string,string>> comment '成绩表'
) 
comment "json_学生成绩表"
row format serde 'org.apache.hive.hcatalog.data.JsonSerDe' """)

# 这样直接使用JsonSerDe类，是会报错的，因为这个类并没有在初始化的时候加载到环境中，报错如下
AnalysisException: 'org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException: MetaException(message:java.lang.ClassNotFoundException Class org.apache.hive.hcatalog.data.JsonSerDe not found);'

（3）JsonSerDe类加载

这里执行ADD JAR ${HIVE_HOME}/hcatalog/share/hcatalog/hive-hcatalog-core....jar. 不同版本的jar包路径可能有些差别

[root@hadoop02 hive]# add jar ../hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar 
-bash: add: 未找到命令
-- 注意这个add jar是在hive里执行的，而不是bash命令
[root@hadoop02 hive]# bin/hive
ls: 无法访问/opt/module/spark/lib/spark-assembly-*.jar: 没有那个文件或目录
-- JsonSerDe这个类并没有在初始化的时候加载到环境中
Logging initialized using configuration in jar:file:/opt/module/hive/lib/hive-common-1.2.1.jar!/hive-log4j.properties 
hive> add jar /opt/module/hive/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar; 
Added [/opt/module/hive/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar] to class path 
Added resources: [/opt/module/hive/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar]

（4）再建表成功

（5）将数据上传HDFS表目录，查询成功

#将文件上传HDFS表目录
[root@hadoop02 hive]# hdfs dfs -put /opt/module/hive/my_input/test_json_load  hdfs:///user/hive/warehouse/test_youhua.db/test_json_load;
# 登录hive
[root@hadoop02 hive]# bin/hive
ls: 无法访问/opt/module/spark/lib/spark-assembly-*.jar: 没有那个文件或目录
Logging initialized using configuration in jar:file:/opt/module/hive/lib/hive-common-1.2.1.jar!/hive-log4j.properties

# 这里发现每次都要重复导入这个JsonSerDe类，否则会报错，常用的话还是要加一下默认路径避免每次重复操作
hive> select * from test_youhua.test_json_load;
FAILED: RuntimeException MetaException(message:java.lang.ClassNotFoundException Class org.apache.hive.hcatalog.data.JsonSerDe not found)

hive> add jar /opt/module/hive/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar
    > ;
Added [/opt/module/hive/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar] to class path
Added resources: [/opt/module/hive/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar]
# 查询成功
hive> select * from test_youhua.test_json_load;
OK
{"name":"king","age":"11","sex":"M"}    [{"subject":"语文","score":"80"},{"subject":"数学","score":"80"},{"subject":"英语","score":"80"}]
{"name":"king1","age":"11","sex":"M"}    [{"subject":"语文","score":"81"},{"subject":"数学","score":"80"},{"subject":"英语","score":"80"}]
{"name":"king2","age":"12","sex":"M"}    [{"subject":"语文","score":"82"},{"subject":"数学","score":"80"},{"subject":"英语","score":"80"}]
{"name":"king3","age":"13","sex":"M"}    [{"subject":"语文","score":"83"},{"subject":"数学","score":"80"},{"subject":"英语","score":"80"}]
{"name":"king4","age":"14","sex":"M"}    [{"subject":"语文","score":"84"},{"subject":"数学","score":"80"},{"subject":"英语","score":"80"}]
{"name":"king5","age":"15","sex":"M"}    [{"subject":"语文","score":"85"},{"subject":"数学","score":"80"},{"subject":"英语","score":"80"}]
{"name":"king5","age":"16","sex":"M"}    [{"subject":"语文","score":"86"},{"subject":"数学","score":"80"},{"subject":"英语","score":"80"}]
{"name":"king5","age":"17","sex":"M"}    [{"subject":"语文","score":"87"},{"subject":"数学","score":"80"},{"subject":"英语","score":"80"}]
Time taken: 0.518 seconds, Fetched: 8 row(s)
hive>