Amazon Athena で JSON をクエリすると「HIVE_BAD_DATA: フィールド X のフィールド値を解析中にエラーが発生しました: 入力文字列: ""」のエラーメッセージで失敗するのはなぜですか?

簡単な説明

「HIVE_BAD_DATA」エラーにはいくつかのバージョンがあります。エラーメッセージには、「入力文字列: ""」など、NULL または空の入力文字列が指定されている場合があります。このタイプのエラーメッセージについては、「Amazon Athena をクエリすると、『HIVE_BAD_DATA: フィールド X のフィールド値を解析中にエラーが発生しました: 入力文字列: "12312845691"』というエラーで失敗するのはなぜですか？」を参照してください。

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

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

解決策

1 つの列に 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

目的のデータ型にキャストすることもできます。たとえば、文字列を整数としてキャストできます。ただし、キャスト元とキャスト先のデータ型によっては、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」が破棄されたことがわかります。その値を文字列から整数に直接キャストすることはできません。この問題を解決するには、(Presto の Web サイトから) 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

出力:

     _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] と定義します。これにより、「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」の値と一致しません。詳細については、「10 進数型または数値型」を参照してください。

ソースデータ:

{ "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/';