读取 Hive 表中的 JSON 数据

Question

我可以使用 JSON Serde org.openx.data.jsonserde.JsonSerDe 创建 Hive 表，但是当我从 Hive 表中读取数据时，我无法读取。

hive> create table emp (EmpId int , EmpFirstName string , EmpLastName string) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';
OK
Time taken: 2.148 seconds

hive> LOAD DATA INPATH '/user/cloudera/EmpData/emp.json' INTO table emp;
Loading data to table employee.emp
chgrp: changing ownership of 'hdfs://quickstart.cloudera:8020/user/hive/warehouse/employee.db/emp/emp.json': User does not belong to supergroup
Table employee.emp stats: [numFiles=1, totalSize=4163]
OK
Time taken: 1.141 seconds

hive> select * from emp;
OK
Failed with exception java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException: Row is not a valid JSON Object - JSONException: A JSONObject text must end with '}' at 2 [character 3 line 1]
Time taken: 0.504 seconds

Answer 1

错误:失败，出现异常 java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException: Row is not a valid JSON Object - JSONException: A JSONObject text must end with ' }' 在 2 [字符 3 第 1 行]

检查/user/cloudera/EmpData/emp.json中提供的json是否有效

您可以通过

消除无效行

ALTER TABLE table emp SET SERDEPROPERTIES ( "ignore.malformed.json" = "true"); 

检查此链接 -> https://github.com/rcongiu/Hive-JSON-Serde

编辑:这是无效的 json

{ "cols": [ "EmpId", "EmpFirstName", "EmpLastName"], "data": [ [ 1, "Hannah", "Walton"], [ 2, "Barrett", "门多萨"], [ 3, "卡姆登", "基德"], [ 4, "伊利安娜", "科利尔"] ] }

你提供的json有

key:cols 和 value:[ "EmpId", "EmpFirstName", "EmpLastName"]

和

key :data and value :[ [ 1, "Hannah", "Walton"], [ 2, "Barrett", "Mendoza"], [ 3, "Camden", "Kidd"], [ 4、“伊莲娜”、“科利尔”]

json应该是这样的

{"EmpId":1,"EmpFirstName":"Hannah","EmpLastName":"Walton"}
{"EmpId":2,"EmpFirstName":"Barrett","EmpLastName":"Mendoza"}
{"EmpId":3,"EmpFirstName":"Camden","EmpLastName":"Kidd"}