Amazon Athena에서 JSON 데이터를 쿼리할 경우, "HIVE_BAD_DATA: Error parsing field value for field X for input string:"라는 오류가 발생하는 이유는 무엇인가요?

간략한 설명

HIVE_BAD_DATA 오류에는 여러 버전이 있습니다. 오류 메시지는 null 또는 "입력 문자열의 경우: """와 같은 빈 입력 문자열을 지정할 수 있습니다. 이러한 유형의 오류 메시지에 대해서는 Amazon Athena 쿼리가 “HIVE\ _BAD\ _DATA” 오류와 함께 실패하는 이유를 참조하세요. 필드 X의 필드 값을 구문 분석하는 중 오류: 입력 문자열의 경우: "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로 정의됩니다. Athena는 Presto에서 INT 값의 범위는 -2147483648~2147483647이므로 "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에서의 문제 해결