# JSON 데이터 쿼리 Amazon Athena를 사용하면 JSON 인코딩된 데이터를 쿼리하고 중첩된 JSON에서 데이터를 추출하고, 값을 검색하고, JSON 어레이의 길이와 크기를 찾을 수 있습니다. Athena에서 JSON 데이터를 쿼리하는 방법에 대한 기본 정보를 알아보려면 다음 샘플 행성 데이터를 고려해 보세요. ``` {name:"Mercury",distanceFromSun:0.39,orbitalPeriod:0.24,dayLength:58.65} {name:"Venus",distanceFromSun:0.72,orbitalPeriod:0.62,dayLength:243.02} {name:"Earth",distanceFromSun:1.00,orbitalPeriod:1.00,dayLength:1.00} {name:"Mars",distanceFromSun:1.52,orbitalPeriod:1.88,dayLength:1.03} ``` 각 레코드(기본적으로 테이블의 각 행)가 어떻게 별도의 줄에 있는지 확인하세요. JSON 데이터를 쿼리하려면 다음과 같은 간단한 `CREATE TABLE` 문을 사용할 수 있습니다. ``` CREATE EXTERNAL TABLE `planets_json`( `name` string, `distancefromsun` double, `orbitalperiod` double, `daylength` double) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat' LOCATION 's3://amzn-s3-demo-bucket/json/' ``` 데이터를 쿼리하려면 다음 예제와 같은 간단한 `SELECT` 문을 사용할 수 있습니다. ``` SELECT * FROM planets_json ``` 쿼리 결과는 다음과 같이 나타납니다. **** | \$1 | name | distancefromsun | orbitalperiod | daylength | | --- | --- | --- | --- | --- | | 1 | 수성 | 0.39 | 0.24 | 58.65 | | 2 | 금성 | 0.72 | 0.62 | 243.02 | | 3 | 지구 | 1.0 | 1.0 | 1.0 | | 4 | 화성 | 1.52 | 1.88 | 1.03 | `CREATE TABLE` 문에서 각 JSON 레코드가 별도의 줄에 있을 것을 요구하는 [OpenX JSON SerDe](openx-json-serde.md)를 어떻게 사용하는지 확인하세요. JSON이 예쁜 인쇄 형식으로 되어 있거나 모든 레코드가 한 줄에 있는 경우 데이터를 제대로 읽을 수 없습니다. 예쁜 인쇄 형식의 JSON 데이터를 쿼리하려면 OpenX JSON SerDe 대신 [Amazon Ion Hive SerDe](ion-serde.md)를 사용할 수 있습니다. 이전 데이터가 예쁜 인쇄 형식으로 저장되어 있다고 가정해 보겠습니다. ``` { name:"Mercury", distanceFromSun:0.39, orbitalPeriod:0.24, dayLength:58.65 } { name:"Venus", distanceFromSun:0.72, orbitalPeriod:0.62, dayLength:243.02 } { name:"Earth", distanceFromSun:1.00, orbitalPeriod:1.00, dayLength:1.00 } { name:"Mars", distanceFromSun:1.52, orbitalPeriod:1.88, dayLength:1.03 } ``` 형식을 다시 지정하지 않고 이 데이터를 쿼리하려면 다음과 같은 `CREATE TABLE` 문을 사용할 수 있습니다. 이 문은 OpenX JSON SerDe를 지정하는 대신 `STORED AS ION`을 지정하는 것을 볼 수 있습니다. ``` CREATE EXTERNAL TABLE `planets_ion`( `name` string, `distancefromsun` DECIMAL(10, 2), `orbitalperiod` DECIMAL(10, 2), `daylength` DECIMAL(10, 2)) STORED AS ION LOCATION 's3://amzn-s3-demo-bucket/json-ion/' ``` 쿼리 `SELECT * FROM planets_ion`은 이전과 동일한 결과를 생성합니다. Amazon Ion Hive Serde를 사용하여 이러한 방식으로 테이블을 생성하는 방법에 대한 자세한 내용은 [Amazon Ion 테이블 생성](ion-serde-using-create-table.md) 섹션을 참조하세요. 위의 예제 JSON 데이터에는 중첩된 배열 또는 구조체와 같은 복잡한 데이터 유형이 포함되어 있지 않습니다. 중첩된 JSON 데이터의 쿼리에 대한 자세한 내용은 [예제: 중첩 JSON 역직렬화](openx-json-serde.md#nested-json-serde-example) 섹션을 참조하세요. **Topics** + [JSON 데이터 읽기 모범 사례](parsing-json-data.md) + [문자열에서 JSON 데이터 추출](extracting-data-from-JSON.md) + [JSON 배열에서 값 검색](searching-for-values.md) + [JSON 배열의 길이 및 크기 가져오기](length-and-size.md) + [JSON 쿼리 문제 해결](json-troubleshooting.md) # JSON 데이터 읽기 모범 사례 JavaScript Object Notation(JSON)은 데이터 구조를 텍스트로 인코딩하는 일반적인 방법입니다. 많은 애플리케이션과 도구가 JSON 인코딩 데이터를 출력합니다. Amazon Athena에서 외부 데이터로 테이블을 만들고 JSON 인코딩 데이터를 포함할 수 있습니다. 이러한 유형의 소스 데이터에는 [JSON SerDe 라이브러리](json-serde.md)와 함께 Athena를 사용합니다. JSON 인코딩 데이터를 읽을 때는 다음 팁을 참조합니다. + 기본 JSON SerDe `org.apache.hive.hcatalog.data.JsonSerDe` 또는 OpenX SerDe `org.openx.data.jsonserde.JsonSerDe` 중 올바른 SerDe를 선택합니다. 자세한 내용은 [JSON SerDe 라이브러리](json-serde.md) 섹션을 참조하세요. + 각각의 JSON 인코딩 레코드가 가독성 좋게 꾸며서 표시되는 것이 아니라 별도의 라인에 표시되는지 확인합니다. **참고** SerDe는 각 JSON 문서가 레코드의 필드를 구분하는 줄 종료 문자가 없는 한 줄의 텍스트에 있을 것으로 예상합니다. JSON 텍스트가 가독성 좋게 꾸민 형식이면 테이블을 만든 후 쿼리하려고 할 때 HIVE\$1CURSOR\$1ERROR: 행이 유효한 JSON 객체가 아님(HIVE\$1CURSOR\$1ERROR: Row is not a valid JSON Object) 또는 HIVE\$1CURSOR\$1ERROR: JsonParseException: 예기치 않은 입력 종료: OBJECT의 닫기 마커 필요(HIVE\$1CURSOR\$1ERROR: JsonParseException: Unexpected end-of-input: expected close marker for OBJECT) 같은 오류 메시지가 나타날 수 있습니다. 자세한 내용은 GitHub의 OpenX SerDe 문서에서 [JSON 데이터 파일](https://github.com/rcongiu/Hive-JSON-Serde#json-data-files)을 참조하세요. + 대/소문자 열에서 JSON 인코딩 데이터를 생성합니다. + 이 예제에서와 같이 형식이 잘못된 레코드를 무시할 수 있는 옵션이 제공됩니다. ``` CREATE EXTERNAL TABLE json_table ( column_a string, column_b int ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' WITH SERDEPROPERTIES ('ignore.malformed.json' = 'true') LOCATION 's3://amzn-s3-demo-bucket/path/'; ``` + 스키마가 정해지지 않은 소스 데이터의 필드를 Athena의 JSON 인코딩 문자열로 변환합니다. Athena가 JSON 데이터 기반의 테이블을 만들면 기존 스키마와 미리 정의된 스키마를 기반으로 데이터를 구문 분석합니다. 그러나 모든 데이터에 사전 정의된 스키마가 있는 것은 아닙니다. 이러한 경우 스키마 관리를 단순화하려면 보통 스키마가 정해지지 않은 소스 데이터의 필드를 Athena의 JSON 문자열로 변환한 다음 [JSON SerDe 라이브러리](json-serde.md)를 사용하는 방법이 유용합니다. 예를 들어, 서로 다른 센서의 공통 필드를 사용하여 이벤트를 게시하는 IoT 애플리케이션을 가정해 보겠습니다. 이러한 필드 중 하나는 이벤트를 보내는 센서에 고유한 사용자 지정 페이로드를 저장해야 합니다. 이 경우 스키마를 알 수 없기 때문에 이 정보를 JSON 인코딩 문자열로 저장하는 것이 좋습니다. 이를 위해 다음 예에서와 같이 Athena 테이블의 데이터를 JSON으로 변환합니다. JSON 인코딩 데이터를 Athena 데이터 형식으로 변환할 수도 있습니다. **Topics** + [Athena 데이터 형식을 JSON으로 변환](converting-native-data-types-to-json.md) + [JSON을 Athena 데이터 형식으로 변환](converting-json-to-native-data-types.md) # Athena 데이터 형식을 JSON으로 변환 Athena 데이터 형식을 JSON으로 변환하려면 `CAST`를 사용합니다. ``` WITH dataset AS ( SELECT CAST('HELLO ATHENA' AS JSON) AS hello_msg, CAST(12345 AS JSON) AS some_int, CAST(MAP(ARRAY['a', 'b'], ARRAY[1,2]) AS JSON) AS some_map ) SELECT * FROM dataset ``` 이 쿼리가 반환하는 값: ``` +-------------------------------------------+ | hello_msg | some_int | some_map | +-------------------------------------------+ | "HELLO ATHENA" | 12345 | {"a":1,"b":2} | +-------------------------------------------+ ``` # JSON을 Athena 데이터 형식으로 변환 JSON 데이터를 Athena 데이터 형식으로 변환하려면 `CAST`를 사용합니다. **참고** 이 예에서 문자열을 JSON 인코딩 문자열로 나타내려면 `JSON` 키워드로 시작하고 `JSON '12345'`처럼 작은따옴표를 사용합니다. ``` WITH dataset AS ( SELECT CAST(JSON '"HELLO ATHENA"' AS VARCHAR) AS hello_msg, CAST(JSON '12345' AS INTEGER) AS some_int, CAST(JSON '{"a":1,"b":2}' AS MAP(VARCHAR, INTEGER)) AS some_map ) SELECT * FROM dataset ``` 이 쿼리가 반환하는 값: ``` +-------------------------------------+ | hello_msg | some_int | some_map | +-------------------------------------+ | HELLO ATHENA | 12345 | {a:1,b:2} | +-------------------------------------+ ``` # 문자열에서 JSON 데이터 추출 Athena의 테이블로 역직렬화할 필요가 없는 JSON 인코딩 문자열을 포함하는 원본 데이터가 있을 수 있습니다. 이 경우에도 Presto에 제공된 JSON 함수를 사용하여 이 데이터에 대해 SQL 작업을 실행할 수 있습니다. 이 JSON 문자열을 예제 데이터 세트로 간주합니다. ``` {"name": "Susan Smith", "org": "engineering", "projects": [ {"name":"project1", "completed":false}, {"name":"project2", "completed":true} ] } ``` ## 예제: 속성 추출 JSON 문자열에서 `name` 및 `projects` 속성을 추출하려면 다음 예제에서와 같이 `json_extract` 함수를 사용합니다. `json_extract` 함수는 JSON 문자열을 포함하는 열을 가져오고 `JSONPath`처럼 점 `.` 표기법으로 이루어진 식을 검색합니다. **참고** `JSONPath`가 단순한 트리 순회를 수행합니다. 그리고 `$` 기호를 사용해 JSON 문서의 루트를 나타내고, 그 뒤에는 마침표와 루트 바로 아래에 중첩되는 요소가 이어집니다(예: `$.name`). ``` WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false}, {"name":"project2", "completed":true}]}' AS myblob ) SELECT json_extract(myblob, '$.name') AS name, json_extract(myblob, '$.projects') AS projects FROM dataset ``` 반환되는 값은 기본 Athena 데이터 유형이 아닌 JSON 인코딩 문자열입니다. ``` +-----------------------------------------------------------------------------------------------+ | name | projects | +-----------------------------------------------------------------------------------------------+ | "Susan Smith" | [{"name":"project1","completed":false},{"name":"project2","completed":true}] | +-----------------------------------------------------------------------------------------------+ ``` JSON 문자열에서 스칼라 값을 추출하려면 `json_extract_scalar(json, json_path)` 함수를 사용합니다. 이것은 `json_extract`와 비슷하지만 JSON 인코딩 문자열 대신 `varchar` 문자열 값을 반환합니다. *json\$1path* 파라미터의 값은 스칼라(부울, 숫자 또는 문자열)여야 합니다. **참고** 어레이, 맵 또는 구조체에 `json_extract_scalar` 함수를 사용하지 마세요. ``` WITH dataset AS ( SELECT '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}' AS myblob ) SELECT json_extract_scalar(myblob, '$.name') AS name, json_extract_scalar(myblob, '$.projects') AS projects FROM dataset ``` 이 쿼리가 반환하는 값: ``` +---------------------------+ | name | projects | +---------------------------+ | Susan Smith | | +---------------------------+ ``` 예제 어레이에서 `projects` 속성의 첫 번째 요소를 얻으려면 `json_array_get` 함수를 사용하고 인덱스 위치를 지정합니다. ``` WITH dataset AS ( SELECT '{"name": "Bob Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}' AS myblob ) SELECT json_array_get(json_extract(myblob, '$.projects'), 0) AS item FROM dataset ``` JSON 인코딩 어레이에서 지정된 인덱스 위치에 있는 값을 반환합니다. ``` +---------------------------------------+ | item | +---------------------------------------+ | {"name":"project1","completed":false} | +---------------------------------------+ ``` Athena 문자열 유형이 반환되게 하려면 `JSONPath` 식 내에 `[]` 연산자를 사용한 다음 `json_extract_scalar` 함수를 사용합니다. `[]`에 대한 자세한 정보는 [배열 요소에 액세스](accessing-array-elements.md) 섹션을 참조하십시오. ``` WITH dataset AS ( SELECT '{"name": "Bob Smith", "org": "engineering", "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}' AS myblob ) SELECT json_extract_scalar(myblob, '$.projects[0].name') AS project_name FROM dataset ``` 다음 결과를 반환합니다. ``` +--------------+ | project_name | +--------------+ | project1 | +--------------+ ``` # JSON 배열에서 값 검색 특정 값이 JSON 인코딩 배열 내에 있는지 알아보려면 `json_array_contains` 함수를 사용합니다. 다음 쿼리는 "project2"에 참여하는 사용자의 이름을 나열합니다. ``` WITH dataset AS ( SELECT * FROM (VALUES (JSON '{"name": "Bob Smith", "org": "legal", "projects": ["project1"]}'), (JSON '{"name": "Susan Smith", "org": "engineering", "projects": ["project1", "project2", "project3"]}'), (JSON '{"name": "Jane Smith", "org": "finance", "projects": ["project1", "project2"]}') ) AS t (users) ) SELECT json_extract_scalar(users, '$.name') AS user FROM dataset WHERE json_array_contains(json_extract(users, '$.projects'), 'project2') ``` 이 쿼리는 사용자 목록을 반환합니다. ``` +-------------+ | user | +-------------+ | Susan Smith | +-------------+ | Jane Smith | +-------------+ ``` 다음 쿼리 예제는 완료된 프로젝트의 총 개수와 함께 프로젝트를 완료한 사용자의 이름을 나열합니다. 그리고 이러한 작업을 수행합니다. + 명확성을 위해 중첩 `SELECT` 설명을 사용합니다. + 프로젝트의 배열을 추출합니다. + `CAST`를 사용하여 배열을 키-값 페어의 기본 배열로 변환합니다. + `UNNEST` 연산자를 사용하여 각각의 배열 요소를 추출합니다. + 획득한 값을 완료된 프로젝트별로 필터링하고 개수를 셉니다. **참고** `MAP`에 `CAST`를 사용할 때 키 요소를 `VARCHAR`(Presto의 기본 문자열)로 지정할 수 있지만 값은 JSON으로 남겨 둡니다. `MAP`의 값 유형이 서로 다르기 때문입니다(첫 번째 키-값 페어는 문자열, 두 번째는 부울). ``` WITH dataset AS ( SELECT * FROM (VALUES (JSON '{"name": "Bob Smith", "org": "legal", "projects": [{"name":"project1", "completed":false}]}'), (JSON '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project2", "completed":true}, {"name":"project3", "completed":true}]}'), (JSON '{"name": "Jane Smith", "org": "finance", "projects": [{"name":"project2", "completed":true}]}') ) AS t (users) ), employees AS ( SELECT users, CAST(json_extract(users, '$.projects') AS ARRAY(MAP(VARCHAR, JSON))) AS projects_array FROM dataset ), names AS ( SELECT json_extract_scalar(users, '$.name') AS name, projects FROM employees, UNNEST (projects_array) AS t(projects) ) SELECT name, count(projects) AS completed_projects FROM names WHERE cast(element_at(projects, 'completed') AS BOOLEAN) = true GROUP BY name ``` 이 쿼리는 다음 결과를 반환합니다. ``` +----------------------------------+ | name | completed_projects | +----------------------------------+ | Susan Smith | 2 | +----------------------------------+ | Jane Smith | 1 | +----------------------------------+ ``` # JSON 배열의 길이 및 크기 가져오기 JSON 배열의 길이와 크기를 가져오려면 `json_array_length` 및 `json_size` 함수를 사용합니다. ## 예시: `json_array_length` JSON 인코딩 배열의 길이를 얻으려면 `json_array_length` 함수를 사용합니다. ``` WITH dataset AS ( SELECT * FROM (VALUES (JSON '{"name": "Bob Smith", "org": "legal", "projects": [{"name":"project1", "completed":false}]}'), (JSON '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project2", "completed":true}, {"name":"project3", "completed":true}]}'), (JSON '{"name": "Jane Smith", "org": "finance", "projects": [{"name":"project2", "completed":true}]}') ) AS t (users) ) SELECT json_extract_scalar(users, '$.name') as name, json_array_length(json_extract(users, '$.projects')) as count FROM dataset ORDER BY count DESC ``` 이 쿼리는 다음 결과를 반환합니다. ``` +---------------------+ | name | count | +---------------------+ | Susan Smith | 2 | +---------------------+ | Bob Smith | 1 | +---------------------+ | Jane Smith | 1 | +---------------------+ ``` ## 예시: `json_size` JSON 인코딩 배열이나 객체의 크기를 얻으려면 `json_size` 함수를 사용하고 JSON 문자열과 `JSONPath` 표현식이 포함된 열을 배열이나 객체에 지정합니다. ``` WITH dataset AS ( SELECT * FROM (VALUES (JSON '{"name": "Bob Smith", "org": "legal", "projects": [{"name":"project1", "completed":false}]}'), (JSON '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project2", "completed":true},{"name":"project3", "completed":true}]}'), (JSON '{"name": "Jane Smith", "org": "finance", "projects": [{"name":"project2", "completed":true}]}') ) AS t (users) ) SELECT json_extract_scalar(users, '$.name') as name, json_size(users, '$.projects') as count FROM dataset ORDER BY count DESC ``` 이 쿼리는 다음 결과를 반환합니다. ``` +---------------------+ | name | count | +---------------------+ | Susan Smith | 2 | +---------------------+ | Bob Smith | 1 | +---------------------+ | Jane Smith | 1 | +---------------------+ ``` # JSON 쿼리 문제 해결 JSON 관련 쿼리 문제 해결에 대한 도움말은 [JSON 관련 오류](troubleshooting-athena.md#troubleshooting-athena-json-related-errors) 또는 다음 리소스를 참조하세요. + [Amazon Athena에서 JSON 데이터를 읽으려고 할 때 오류가 발생합니다.](https://aws.amazon.com/premiumsupport/knowledge-center/error-json-athena/) + [Athena에서 AWS Config의 파일을 읽을 때 "HIVE\$1CURSOR\$1ERROR: Row is not a valid JSON Object - JSONException: Duplicate key"를 어떻게 해결해야 합니까?](https://aws.amazon.com/premiumsupport/knowledge-center/json-duplicate-key-error-athena-config/) + [입력 JSON 파일에 다수의 레코드가 있는 경우에도 Amazon Athena의 SELECT COUNT 쿼리가 레코드를 1개만 반환합니다.](https://aws.amazon.com/premiumsupport/knowledge-center/select-count-query-athena-json-records/) + [Athena 테이블의 행에 대한 Amazon S3 원본 파일을 확인하려면 어떻게 해야 합니까?](https://aws.amazon.com/premiumsupport/knowledge-center/find-s3-source-file-athena-table-row/) 또한 [Amazon Athena의 SQL 쿼리에 대한 고려 사항 및 제한 사항](other-notable-limitations.md) 단원도 참조하세요.