Athena クエリを実行できず、次のエラーが発生する原因を教えてください。"HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691""

簡単な説明

HIVE_BAD_DATA エラーにはいくつかのバージョンがあります。エラーメッセージに "For input string: """ のように NULL または空の入力文字列が指定されている場合は、「Why do I get the error "HIVE_BAD_DATA: Error parsing field value for field X for input string:" when I query JSON data in Amazon Athena?」(Amazon Athena で JSON データをクエリすると、"HIVE_BAD_DATA: Error parsing field value for field X for input string:" というエラーが表示される原因を教えてください) を参照してください。

値を含む入力文字列を指定するエラーは、次のいずれかのシナリオで発生します。

• テーブル定義で定義されているデータ型が実際のソースデータと一致しません。
• 1 つのフィールドには、あるレコードの整数値と別のレコードの 10 進値など、さまざまな型のデータが含まれます。

解決策

1 つの列に 1 つのデータ型のみを使用するのがベストプラクティスです。そうしないと、クエリが失敗する可能性があります。エラーを解決するには、各列に同じデータ型の値が含まれ、値が許容範囲内にあることを確認してください。

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

例 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

Error:

"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 または不正確な結果が返される場合があり、その結果から from と to をキャストできます。キャストできない値は破棄されます。例えば、文字列値 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

Error:

"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

Error:

"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");

Error:

"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 の問題をトラブルシューティングする