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

簡短說明

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

錯誤：

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。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

錯誤：

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

修改後的 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 疑難排解