为什么会收到下列错误“HIVE_BAD_DATA: Error parsing field value '' for field X: For input string: """ when I query JSON data in Amazon Athena?

概述

HIVE_BAD_DATA 错误有多个版本。错误消息可能会指定空值输入字符串，例如“For input string: ""”。有关此类错误消息，请参阅为什么我的 Amazon Athena 查询失败并显示错误“HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691"”？

在以下任一情况下，将发生指明带有值的输入字符串的错误：

• 表定义中定义的数据类型与实际源数据不匹配。
• 单个字段包含不同类型的数据，例如一条记录为整数值，而另一条记录则为十进制值。

解决方法

最佳做法是在列中仅使用一种数据类型。否则，查询可能会失败。要解决错误，请确保每一列中的值均为相同数据类型，并且值在允许的范围内。

如果仍然存在错误，请将该列的数据类型更改为范围更大的兼容数据类型。如果更改数据类型不能解决问题，请尝试以下示例中的解决方案。

示例 1

• 源格式： JSON
• **问题：**在最后一条记录中，id 键值为“0.54”。 此键值的数据类型是 DECIMAL。对于其他记录，id 键值设置为 INT。

来源数据：

{ "id" : 50, "name":"John" }
{ "id" : 51, "name":"Jane" }
{ "id" : 53, "name":"Jill" }
{ "id" : 0.54, "name":"Jill" }

数据定义语言（DDL）语句：

CREATE EXTERNAL TABLE jsontest_error_hive_bad_data (
    id INT,
    name STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/jsontest_error_hive_bad_data/';

数据操作语言（DML）语句：

SELECT *
FROM jsontest_error_hive_bad_data

错误：

Your query has the following error(s):
HIVE_BAD_DATA: Error parsing field value '0.54' for field 0: For input string: "0.54"
This query ran against the "default" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: bd50793b-94fc-42a7-b131-b7c81da273b2.

要解决此问题，请将 id 列重新定义为 STRING。STRING 数据类型可以正确表示该数据集中的所有值。示例：

CREATE EXTERNAL TABLE jsontest_error_hive_bad_data_correct_id_data_type (
    id STRING,
    name STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/jsontest_error_hive_bad_data/';

DML 语句：

SELECT *
FROM jsontest_error_hive_bad_data_correct_id_data_type

您也可以转换为所需的数据类型。例如，您可以将字符串转换为整数。但是，这可能会返回空值或不准确的结果，具体取决于转换前后的数据类型。无法转换的值将被丢弃。例如，将字符串值“0.54”转换为 INT，返回的结果为空值：

SELECT TRY_CAST(id AS INTEGER)
FROM jsontest_error_hive_bad_data_correct_id_data_type

输出示例：

Results
     _col0
1    50
2    51
3    53
4

输出结果表明，值“0.54”已被丢弃。您不能直接将该值从字符串转换为整数。要解决此问题，请使用 COALESCE（请见 Presto 网站）将混合类型值转换到与输出相同的列中。然后，允许聚合函数在列上运行。示例：

SELECT COALESCE(TRY_CAST(id AS INTEGER), TRY_CAST(id AS DECIMAL(10,2)))
FROM jsontest_error_hive_bad_data_correct_id_data_type

输出：

Results
     _col0
1    50.00
2    51.00
3    53.00
4    0.54

运行聚合函数：

SELECT SUM(COALESCE(TRY_CAST(id AS INTEGER), TRY_CAST(id AS DECIMAL(10,2))))
FROM jsontest_error_hive_bad_data_correct_id_data_type

输出：

     _col0
1    154.54

示例 2

• 源格式： JSON
• **问题：**id 列定义为 INT。Athena 无法解析“49612833315”，因为在 Presto 中，INT 值的范围介于 -2147483648 到 2147483647 之间。

来源数据：

{ "id" : 50, "name":"John" }
{ "id" : 51, "name":"Jane" }
{ "id" : 53, "name":"Jill" }
{ "id" : 49612833315, "name":"Jill" }

DDL 语句：

CREATE EXTERNAL TABLE jsontest_error_hive_bad_data_sample_2 (
    id INT,
    name STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/jsontest_error_hive_bad_data_2/';

DML 语句：

SELECT *
FROM jsontest_error_hive_bad_data_sample_2

错误：

Your query has the following error(s):
HIVE_BAD_DATA: Error parsing field value '49612833315' for field 0: For input string: "49612833315"
This query ran against the "default" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 05b55fb3-481a-4012-8c0d-c27ef1ee746f.

要解决此问题，请将 id 列定义为 BIGINT，它可以读取值“49612833315”。 有关更多信息，请参阅整数类型。

修改后的 DDL 语句：

CREATE EXTERNAL TABLE jsontest_error_hive_bad_data_sample_2_corrected (
    id BIGINT,
    name STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/jsontest_error_hive_bad_data_2/';

示例 3

• 源格式： JSON
• **问题：**输入数据为 DECIMAL，在表定义中，相应列定义为 DECIMAL。但是，范围定义为 2，且 2 与值“0.000054”不匹配。有关更多信息，请参见 DECIMAL 或 NUMERIC 类型。

来源数据：

{ "id" : 0.50, "name":"John" }
{ "id" : 0.51, "name":"Jane" }
{ "id" : 0.53, "name":"Jill" }
{ "id" : 0.000054, "name":"Jill" }

DDL 语句：

CREATE EXTERNAL TABLE jsontest_error_hive_bad_data_sample_3(
    id DECIMAL(10,2),
    name STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/jsontest_error_hive_bad_data_3/';

DML 语句：

SELECT *
FROM jsontest_error_hive_bad_data_sample_3

错误：

Your query has the following error(s):
HIVE_BAD_DATA: Error parsing column '0': target scale must be larger than source scale
This query ran against the "default" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 1c3c7278-7012-48bb-8642-983852aff999.

要解决此问题，请使用可捕获所有输入值的范围重新定义该列。例如，使用 (10,7)，不要使用 (10,2)。

CREATE EXTERNAL TABLE jsontest_error_hive_bad_data_sample_3_corrected(
    id DECIMAL(10,7),
    name STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/jsontest_error_hive_bad_data_3/';