Amazon Athena에서 JSON 데이터를 쿼리할 때 "HIVE_BAD_DATA: Error parsing field value for field X for input string:"이라는 오류가 발생하는 이유는 무엇입니까?

간략한 설명

HIVE_BAD_DATA 오류에는 여러 버전이 있습니다. 오류 메시지는 "For input string: """ 같은 null 또는 빈 입력 문자열을 명시할 수 있습니다. 이러한 유형의 오류 메시지에 대해서는 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" }

Data Definition Language(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/';

Data Manipulation Language(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

이 문제를 해결하려면 "49612833315" 값을 읽을 수 있는 BIGINT로 id 열을 정의합니다. 자세한 내용을 보려면 정수 형식을 참조하십시오.

수정된 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는 id 키가 INT로 정의된 STRING 레코드를 구문 분석할 수 없습니다.

소스 데이터

{ "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에서 문제 해결