當我在 Amazon Athena 中查詢 JSON 資料時，為什麼我會收到錯誤 "HIVE_BAD_DATA: Error parsing field value for field X for input string:"？

簡短描述

HIVE_BAD_DATA 錯誤有多個版本。錯誤訊息可能會指定空的輸入字串，例如「對於輸入字串：""」。如需了解此類型的錯誤訊息，請參閱為什麼我的 Amazon Athena 查詢失敗，並顯示如下錯誤：「HIVE_BAD_DATA： 剖析欄位 X 的欄位值時發生錯誤： 對於輸入字串： "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

錯誤：

HIVE_BAD_DATA: Error reading field value: 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: add6f434-3397-4c80-a55f-1672a0102e32

若要解決此問題，請將 id 資料欄重新定義為 STRING。STRING 資料類型可以正確地表示此資料集中的所有值。

修改後的 DDL 陳述式：

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。因為 Presto 中的 INT 值範圍是 -2147483648 到 2147483647，因此 Athena 無法剖析 "49612833315"。

來源資料：

{ "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

錯誤：

HIVE_BAD_DATA: Error reading field value: 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: 25be5ca1-0799-4c21-855d-b9171aadff47

若要解決此問題，請將 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

錯誤：

HIVE_BAD_DATA: Error Parsing a column in the table: 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: 97789f62-d2a5-4749-a506-26480a91a1db

若要解決此問題，請使用可擷取所有輸入值的比例來重新定義資料欄。例如，使用 (10,7) 而不是 (10,2)。

修改後的 DDL 陳述式：

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

• 來源格式： JSON
• **問題：**輸入資料為 STRING，且資料欄在表定義中定義為 INT。但是，Athena 無法解析 STRING 記錄，因為 id 鍵定義為 INT。

來源資料

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

DDL 陳述式：

CREATE EXTERNAL TABLE jsontest_error_hive_bad_data_sample_4(    
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_4/';

DML 陳述式：

SELECT * FROM jsontest_error_hive_bad_data_sample_4

錯誤：

HIVE_BAD_DATA: Error reading field value: For input string: "one"
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: 291b69f6-48f9-4381-af72-6786b3a90826

發生此錯誤的原因是實際來源資料具有不相容的資料類型定義。

若要解決此問題，請將 id 資料欄重新定義為 STRING。STRING 資料類型可以正確地表示此資料集中的所有值。範例：

修改後的 DDL 陳述式：

CREATE EXTERNAL TABLE jsontest_error_hive_bad_data_sample_4_corrected (    
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_4/';

相關資訊

JSON 相關錯誤

Athena 疑難排解