為什麼我的 Athena 查詢會因為錯誤「HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691"」而失敗？

簡短說明

HIVE_BAD_DATA 錯誤有多個版本。如果錯誤訊息指定了 null 或空的輸入字串，例如「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

您也可以轉換為您偏好的資料類型。例如，您可以將字串轉換為整數。不過，對於某些資料類型，您從某一類型轉換到另一類型時，可能會回傳 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，將同一欄中的混合類型值轉換為輸出。接著，讓彙總函數在該欄上執行。

範例：

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,2)，改用 (10,7)。

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 疑難排解