我的 Amazon Athena 查询失败,并显示错误“HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691"”

上次更新时间:2020 年 3 月 30 日

当我在 Amazon Athena 中查询数据时,遇到了类似于以下之一的错误:

“HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691"”

“HIVE_BAD_DATA: Error parsing column '0': target scale must be larger than source scale”

简短描述

HIVE_BAD_DATA 错误有多个版本。如果错误消息指出输入字符串为 null 或为空(例如,“For input string: ""”),请参阅当我在 Athena 中查询 CSV 数据时,遇到了错误“HIVE_BAD_DATA: Error parsing field value '' for field X: For input string: ""”

当以下条件之一为 true 时,将发生指明带有值的输入字符串的错误:

  • 在表定义中所定义的数据类型与实际的源数据不匹配。
  • 一个字段包含不同的数据类型(例如,一条记录的整数值和另一条记录的十进制值)。

解决方法

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

如果错误仍然存在,请将该列的数据类型更改为具有更高范围的兼容数据类型。如果您无法通过更改数据类型来解决问题,请尝试以下示例中的解决方法。

示例 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

您还可以 CAST 到所需的数据类型。例如,您可以将字符串转换为整数。但是,根据您转换的源数据类型和目标数据类型,这可能返回 null 或不正确的结果。无法转换的值将被丢弃。例如,将字符串值“0.54”转换为 INT 将返回 null 结果:

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 将同一列中的混合类型值 CAST 为输出。然后,允许在该列上运行聚合函数。示例:

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,与“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/';

这篇文章对您有帮助吗?

我们可以改进什么?


需要更多帮助?