为什么我的 Athena 查询失败并显示错误 "HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691""？

简短描述

HIVE_BAD_DATA 错误有多个版本。如果错误消息指定了诸如 "For input string: """ 的空值输入字符串，请参阅在 Amazon Athena 中查询 JSON 数据时，为什么会收到错误 "HIVE_BAD_DATA: Error parsing field value for field X for input string:"？

在以下任一场景中，会出现指定带有值的输入字符串的错误：

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

解决方法

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

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

示例 1

在以下示例中，源格式为 JSON。问题在于最后一条记录，因为 id 键值是 DECIMAL 数据类型 (0.54)。但是，其他记录的 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 将混合类型值转换到与输出相同的列中。然后，允许聚合函数在列上运行。

示例：

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

输出：

_col01    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，因为 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/';

示例 4

在以下示例中，源格式为 CSV。在架构中，attribute2 包含 STRING，格式为 date。表架构将其定义为 DATE 类型。

源数据：

"attribute1","attribute2","attribute3","attribute4"
"1","2018-01-01","10.01","Hello!"
"2","2018-01-02","20.02","Hi!"
"3","2018-01-03","30.03","How are you"

DDL 语句：

CREATE EXTERNAL TABLE test (attribute1 int, attribute2 date, attribute3  float, attribute4 string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
LOCATION 's3://awsexamplebucket/csvtest_error_hive_bad_data/'
TBLPROPERTIES ("skip.header.line.count"="1");

错误：

"HIVE_BAD_DATA: Error reading field value: Cannot convert value 2018-01-01 of type String to a LONG value"

要解决此问题，请将 attribute2 类型从 DATE 更改为 STRING。

修改后的源数据：

"attribute1","attribute2","attribute3","attribute4"
"1","17532","10.01","Hello!"
"2","17533","20.02","Hi!"
"3","17534","30.03","How are you"

修改后的 DDL 语句：

CREATE EXTERNAL TABLE test (attribute1 int, attribute2 date, attribute3  float, attribute4 string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
LOCATION 's3://awsexamplebucket/csvtest_error_hive_bad_data2/'
TBLPROPERTIES ("skip.header.line.count"="1");

相关信息

Amazon Athena 中的数据类型

在 Athena 中进行故障排除