Athena 쿼리가 다음 오류와 함께 실패하는 이유는 무엇입니까? "HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691""?

간략한 설명

HIVE_BAD_DATA 오류에는 여러 버전이 있습니다. 오류 메시지에 "For input string: """ 같은 null 또는 빈 입력 문자열이 명시된 경우 Amazon Athena에서 JSON 데이터를 쿼리할 때 "HIVE_BAD_DATA: Error parsing field value for field X for input string:"라는 오류가 발생하는 이유는 무엇인가요?를 참조하십시오.

값이 있는 입력 문자열이 명시된 오류는 다음 시나리오 중 하나에서 발생합니다.

• 테이블 정의에 정의된 데이터 형식이 실제 소스 데이터와 일치하지 않습니다.
• 한 레코드에 정수 값이 포함되고 다른 레코드에 소수 값이 포함되는 등, 단일 필드에 다른 데이터 형식이 포함되어 있습니다.

해결 방법

한 열에 하나의 데이터 형식만 사용하는 것이 좋습니다. 그렇지 않으면 쿼리가 실패할 수 있습니다. 오류를 해결하려면 각 열에 동일한 데이터 형식의 값이 포함되어 있고 값이 허용 범위내에 있는지 확인합니다.

그래도 오류가 발생하면 넓은 범위의 호환 가능한 데이터 형식으로 열의 데이터 형식을 변경합니다. 데이터 유형을 변경해도 문제가 해결되지 않으면 다음 예의 해결 방법을 시도해 보십시오. 

예 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" }

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

오류:

"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가 무시되었음을 보여줍니다. 이 값은 문자열에서 정수로 곧바로 변환할 수 없습니다. 이 문제를 해결하려면 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

오류:

"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."

BIGINT 데이터 유형은 "49612833315" 값을 읽을 수 있으므로 이 문제를 해결하려면 id 열을 bigint로 정의하십시오. 자세한 내용을 보려면 정수 형식을 참조하십시오.

수정된 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

오류:

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

오류:

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