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

4분 분량

Amazon Athena에서 JSON 데이터를 읽으려고 하면 NULL 또는 잘못된 데이터 오류가 발생합니다.

해결 방법

다음과 같은 일반적인 문제가 있습니다.

올바른 JSON SerDe 사용

Athena는 다음 JSON SerDe 중 하나를 사용하여 JSON을 처리합니다.

생성된 테이블에 대해 INSERT INTO 쿼리를 실행해야 하는 경우 Hive JSON SerDe를 사용합니다.

OpenX JSON 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

참고: pretty-print JSON 파일에 대해 Athena 쿼리를 실행하는 경우 Amazon Ion Hive SerDe를 사용할 수 있습니다. Amazon Ion Hive SerDe는 데이터의 각 행이 한 줄에 있을 것으로 예상하지 않습니다. 이 기능을 사용하여 프리티 프린트 형식의 JSON 데이터 세트를 요청하거나 행의 필드를 줄 내림 문자로 분할하세요.

레코드당 한 줄 사용

다음 JSON 레코드는 형식이 올바르게 지정되며 세 가지 JSON SerDe에서 모두 읽을 수 있습니다.

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

다음 JSON 레코드는 Amazon Ion Hive SerDe에서만 읽을 수 있습니다.

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

각 열에 올바른 데이터 유형을 사용하세요.

다음 예제의 두 번째 행 “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 파일을 사용하는 경우 파일은 ".gz" 형식으로 끝나야 합니다. 예를 들어 "myfile.json.gz" 또는 "myfile.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 파일에 대/소문자를 구분하는 중복된 열 이름이 있는 경우 파일을 업데이트하여 중복 열을 제거합니다.

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

JSON SerDe 테이블의 모든 행이 JSON 형식인지 확인하세요.

Athena 테이블에서 유효하지 않은 JSON 행 또는 파일 이름이 있는지 확인하려면 다음을 수행합니다.

입력 파일에 없는 구분 기호를 사용하여 테이블을 생성합니다. 다음과 같은 명령을 실행하세요.

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

다음과 비슷한 쿼리를 실행하여 잘못된 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;