Amazon Athena에서 JSON 데이터를 읽으려고 할 때 오류가 발생하는 이유는 무엇입니까?

3분 분량

Amazon Athena에서 JSON 데이터를 읽으려고 할 때 NULL 또는 부정확한 데이터 오류가 표시됩니다.

해결 방법

다음과 같은 일반적인 문제를 확인합니다.

올바른 JSON SerDe 사용

Athena는 2개 JSON SerDes 중 하나를 사용하여 JSON 데이터를 처리합니다.

네이티브 Apache Hive/HCatalog JsonSerDe(org.apache.hive.hcatalog.data.JsonSerDe)
OpenX SerDe(org.openx.data.jsonserde.JsonSerDe)

어느 SerDe를 사용했는지 잘 모르는 경우 두 가지 SerDe 버전을 모두 사용해 보세요. OpenX SerDe를 사용하는 경우, 다음 예와 같이 잘못된 형식의 레코드를 무시하여 오류를 일으키는 줄을 식별할 수 있습니다. ignore.malformed.json이 true로 설정된 경우, 잘못된 형식의 레코드가 NULL로 반환됩니다.

CREATE EXTERNAL TABLE json (
    id int,
    name string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/';

새 테이블을 쿼리하여 잘못된 형식의 레코드가 있는 파일을 식별합니다. 예를 들어 다음과 같이 명령을 수행합니다.

SELECT "$PATH", * FROM your_table where your_column is NULL

레코드당 한 줄 사용

다음은 올바른 형식의 JSON 레코드입니다.

{ "id" : 50, "name":"John" }
{ "id" : 51, "name":"Jane" }
{ "id" : 53, "name":"Jill" }

다음은 잘못된 형식의 JSON 레코드입니다.

{
  "id" : 50,
  "name":"John"
},
{
  "id" : 51,
  "name":"Jane"
}
{
  "id" : 53,
  "name":"Jill"
}

다음 레코드도 형식이 잘못되었습니다.

{ "id" : 50, "name":"John" } { "id" : 51, "name":"Jane" } { "id" : 53, "name":"Jill" }

각 열에서 올바른 데이터 형식 사용

다음 예의 두 번째 줄에는 "age"에 대한 잘못된 데이터 형식이 포함되어 있습니다. 열 값은 "eleven"이 아니라 "11"이어야 합니다. 이로 인해 **HIVE_BAD_DATA: Error parsing field value 'eleven' for field 1: For input string: "eleven"**이라는 오류 메시지가 표시됩니다.

{"name":"Patrick","age":35,"address":"North Street"}
{"name":"Carlos","age":"eleven","address":"Flowers Street"}
{"name":"Fabiana","age":22,"address":"Main Street"}

압축된 JSON 파일에 올바른 확장명 사용

압축된 JSON 파일을 사용하는 경우 파일은 ".json"으로 끝나고 ".gz"와 같은 압축 형식의 확장명이 이어져야 합니다. 예를 들어 gzip 파일의 올바른 확장명 형식은 "myfile.json.gz"입니다.

대소문자를 구분하지 않는 열을 사용하거나 case.insensitive 속성을 false로 설정

Athena는 기본적으로 대소문자를 구분하지 않습니다. 열 이름이 대소문자만 다른 경우(예: “Column” 및 “column”), Athena에서 오류("HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key")가 발생하고 Athena에 데이터가 표시되지 않습니다. 이 문제를 방지하는 가장 쉬운 방법은 대소문자를 구분하지 않는 열을 사용하여 데이터를 생성하는 것입니다.

OpenX SerDe를 사용하는 경우, 대소문자를 구분하는 키 이름을 사용할 수 있습니다. 이렇게 하려면 case.insensitive SerDe 속성을 false로 설정하고 대문자 키에 대한 매핑을 추가합니다. 예를 들어 다음과 같은 대문자 및 소문자 열을 사용하려면:

{"Username": "bob1234", "username": "bob" }

다음의 SerDe 속성을 사용합니다.

CREATE external TABLE casesensitive_json (user_name String,username String)
ROW FORMAT serde 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'mapping.user_name' = 'Username','case.insensitive'='false')
LOCATION 's3://awsexamplebucket/';

JSON SerDE 테이블의 모든 행이 JSON 형식인지 확인

Athena 테이블에 잘못된 JSON 행이나 파일 이름이 있는지 확인하려면 다음을 수행합니다.

1. 입력 파일에 없는 구분 기호로 테이블을 만듭니다. 다음과 유사한 명령을 실행합니다.

CREATE EXTERNAL TABLE IF NOT EXISTS json_validator (jsonrow string) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '%'
location 's3://awsexamplebucket/';

2. 다음과 유사한 쿼리를 실행하여 잘못된 JSON 행에 대한 파일 이름, 행 세부 정보 및 Amazon S3 경로를 반환합니다.

WITH testdataset AS (SELECT "$path" s3path,jsonrow,try(json_parse(jsonrow)) isjson FROM json_validator)
SELECT * FROM testdataset WHERE ISJSON IS NULL;