加载JSON数据集至Hive执行Count时遇RuntimeException错误求助

我需要将JSON数据集加载到Hive中并执行清洗操作，JSON样例如下：

{"_id":{"$oid":"5a1321d5741a2384e802c552"},"reviewerID":"A3HVRXV0LVJN7","asin":"0110400550","reviewerName":"BiancaNicole","helpful":[4,4],"reviewText":"Best phone case ever . Everywhere I go I get a ton of compliments on it. It was in perfect condition as well.","overall":5.0,"summary":"A++++","unixReviewTime":1358035200,"reviewTime":"01 13, 2013","category":"Cell_Phones_and_Accessories","class":1.0}
{"_id":{"$oid":"5a1321d5741a2384e802c557"},"reviewerID":"A1BJGDS0L1IO6I","asin":"0110400550","reviewerName":"cf \"t\"","helpful":[0,3],"reviewText":"ITEM NOT SENT from Blue Top Company in Hong Kong and it's been over two months!  I will report this.  DO NOT use this company.  Not happy at all!","overall":1.0,"summary":"ITEM NOT SENT!!","unixReviewTime":1359504000,"reviewTime":"01 30, 2013","category":"Cell_Phones_and_Accessories","class":0.0}

已添加JsonSerDe jar包：

add jar 'gs://bucket_spam_ham/jars/hive-hcatalog-core-3.1.0.jar';

创建表的代码：

CREATE EXTERNAL TABLE spam_dataset(`_id` struct<`$oid`:string>,reviewerID string,asin string,reviewerName string,helpful array<int>,reviewText string,overall float,summary string,unixReviewTime bigint,reviewTime string,category string,class float)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe';
load data inpath 'gs://bucket_spam_ham/dataset/Cell_Phones_and_Accessories.json' into table spam_test;

执行select count(*) from spam_dataset;时出现错误，关键报错信息：

Caused by: org.codehaus.jackson.JsonParseException: Unexpected character ('Y' (code 89)): expected a valid value (number, String, array, object, 'true', 'false' or 'null')
 at [Source: java.io.ByteArrayInputStream@e1e2e5e; line: 1, column: 2]

同一数据集的前10行子集可正常查询，请求排查问题。

根据错误信息和测试现象，问题核心是完整数据集存在格式不合法的JSON行，或Hive处理大文件时的行分割逻辑错误，以下是具体处理步骤：

1. 定位错误行 #

通过以下方式快速找到异常行：

• 使用Hadoop命令扫描文件，查找以Y开头的异常行：

  hadoop fs -cat gs://bucket_spam_ham/dataset/Cell_Phones_and_Accessories.json | grep -n "^Y"
  

• 或者创建临时表加载原始文本，逐行排查：

  CREATE EXTERNAL TABLE temp_json_raw(line string)
  LOCATION 'gs://bucket_spam_ham/dataset/';
  
  SELECT line FROM temp_json_raw WHERE line LIKE 'Y%';
  

2. 修复JSON格式问题 #

常见异常原因及处理：

• 某行JSON未正确闭合（如缺少}）：直接删除或手动修复该行
• 行首出现非JSON起始字符：过滤掉此类无效行
• 字符串包含未转义的换行符：预处理数据集，将换行符替换为转义符\n

3. 调整SerDe配置容错 #

修改表定义，添加忽略错误行的配置，先加载合法数据：

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

此配置会跳过格式错误的行，确保查询能正常执行，后续再单独处理异常数据。

4. 解决文件分割问题 #

Hive分割大文件时，可能导致split起始位置不是JSON行开头，可通过以下参数强制按行分割：

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
SET mapreduce.input.fileinputformat.split.minsize=1073741824; -- 设置最小split为1G，避免分割行

内容的提问来源于stack exchange，提问作者Vikas Palakkat