Amazon Athena をクエリすると、「HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691"」というエラーで失敗するのはなぜですか？

簡単な説明

HIVE_BAD_DATA エラーには複数のバージョンがあります。エラーメッセージで null または空の入力文字列 ("入力文字列： """ など) が指定されている場合は、Amazon Athena で CSV データをクエリすると、フィールド X：入力文字列： """ のエラー「HIVE_BAD_DATA: フィールド値の解析エラー」が発生するのはなぜですか？を参照してください。

値を持つ入力文字列を指定するエラーは、次のいずれかの条件で発生します：

• テーブル定義で定義されたデータタイプが、実際の出典データと一致しません。
• 1 つのフィールドに異なるタイプのデータが含まれます (あるレコードの整数値、別のレコードの 10 進値など)。

解決方法

列には 1 つのデータタイプのみを使用することをお勧めします。そうしないと、クエリが失敗する可能性があります。エラーを解決するには、各列に同じデータタイプの値が含まれていること、および値が許容範囲内にあることを確認します。

それでもエラーが発生する場合は、より広い範囲で、互換性のあるデータタイプに列のデータタイプを変更します。データタイプを変更しても問題を解決できない場合は、以下の例で解決策をお試しください。

例 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 として定義されています。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

エラー:

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
• 問題: 入力データは 10進法 で、列はテーブル定義で 10進法 として定義されています。ただし、スケールは 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/';

---