쿼리 최적화

이 섹션에 설명된 쿼리 최적화 기술을 사용하면 쿼리를 더 빠르게 실행하거나 Athena의 리소스 한도를 초과하는 쿼리 문제를 해결할 수 있습니다.

조인 최적화

분산 쿼리 엔진에서 조인을 실행하는 다양한 전략이 있습니다. 가장 일반적인 두 가지는 분산 해시 조인과 복잡한 조인 조건을 사용하는 쿼리입니다.

분산 해시 조인에서 대형 테이블은 왼쪽에, 소형 테이블은 오른쪽에 배치합니다.

가장 일반적인 유형의 조인은 관계 비교 연산자를 조인 조건으로 사용합니다. Athena는 이 유형의 조인을 분산 해시 조인으로 실행합니다.

분산 해시 조인에서 엔진은 조인 측 중 하나에서 조회 테이블(해시 테이블)을 빌드합니다. 이 조인 측을 빌드 측이라고 합니다. 빌드 측 레코드는 여러 노드에 분산되어 있습니다. 각 노드는 해당 하위 세트에 대한 조회 테이블을 빌드합니다. 그러면 조인의 다른 측(프로브 측)이 노드를 통해 스트리밍됩니다. 프로브 측의 레코드는 빌드 측과 동일한 방식으로 노드에 분산됩니다. 이를 통해 각 노드는 자체 조회 테이블에서 일치하는 레코드를 조회하여 조인을 수행할 수 있습니다.

조인의 빌드 측에서 생성된 조회 테이블이 메모리에 맞지 않으면 쿼리에 실패할 수 있습니다. 빌드 측의 전체 크기가 사용 가능한 메모리보다 작더라도 레코드 분산 편차가 너무 크면 쿼리에 실패할 수 있습니다. 극단적인 경우 모든 레코드의 조인 조건 값이 같고 단일 노드의 메모리에 맞아야 할 수도 있습니다. 값 세트가 동일한 노드로 전송되고 값의 합계가 사용 가능한 메모리를 초과하면 편차가 적은 쿼리라도 실패할 수 있습니다. 노드는 레코드를 디스크로 유출할 수 있지만 유출 시 쿼리 실행 속도가 느려지고 이 방법으로는 쿼리 실패를 막지 못할 수도 있습니다.

Athena는 더 큰 관계를 프로브 측으로 사용하고 더 작은 관계를 빌드 측으로 사용하도록 조인 순서를 변경하려고 합니다. 그러나 Athena는 테이블의 데이터를 관리하지 않기 때문에 정보가 제한적이며 종종 첫 번째 테이블이 더 크고 두 번째 테이블은 더 작다고 가정해야 합니다.

관계 기반 조인 조건을 사용하여 조인을 작성하는 경우 JOIN 키워드 왼쪽의 테이블이 프로브 측이고 오른쪽 테이블이 빌드 측이라고 가정합니다. 오른쪽 테이블(빌드 측)이 더 작은 테이블인지 확인합니다. 조인의 빌드 측 크기를 메모리에 맞도록 작게 설정할 수 없다면 빌드 테이블의 하위 세트를 조인하는 쿼리를 여러 번 실행하는 방법을 고려합니다.

EXPLAIN을 사용하여 복잡한 조인이 있는 쿼리 분석

복잡한 조인 조건의 쿼리(예: LIKE, > 또는 다른 연산자를 사용하는 쿼리)는 종종 계산이 까다롭습니다. 최악의 경우에는 조인의 한쪽에 있는 모든 레코드를 조인 반대쪽에 있는 모든 레코드와 비교해야 합니다. 실행 시간은 레코드 수의 제곱에 비례하여 증가하므로 이러한 쿼리는 최대 실행 시간을 초과할 위험이 있습니다.

Athena에서 쿼리를 실행하는 방법을 미리 확인하려면 EXPLAIN 문을 사용할 수 있습니다. 자세한 내용은 Athena에서 EXPLAIN 및 EXPLAIN ANALYZE 사용 및 Athena EXPLAIN 문 결과 이해 단원을 참조하세요.

창 함수의 범위를 축소 또는 제거

창 함수는 리소스를 많이 사용하는 작업이므로 쿼리 실행 속도가 느려지거나 쿼리에 실패하고 Query exhausted resources at this scale factor 메시지가 표시될 수 있습니다. 창 함수는 결과를 계산하기 위해 작업하는 모든 레코드를 메모리에 보관합니다. 창 크기가 너무 크면 창 함수의 메모리가 부족해질 수 있습니다.

사용 가능한 메모리 한도 내에서 쿼리를 실행하려면 창 함수가 작동하는 창 크기를 줄입니다. 이렇게 하기 위해 PARTITIONED BY 절을 추가하거나 기존 파티셔닝 절의 범위를 좁힐 수 있습니다.

창이 아닌 함수를 사용

창 함수가 있는 쿼리를 창 함수 없이 다시 작성할 수도 있습니다. 예를 들어 상위 N개 레코드를 찾기 위해 row_number를 사용하는 대신, ORDER BY 및 LIMIT를 사용할 수 있습니다. 레코드 중복을 제거하기 위해 row_number 또는 rank를 사용하는 대신, max_by, min_by, arbitrary와 같은 집계 함수를 사용할 수 있습니다.

예를 들어 센서의 업데이트가 포함된 데이터 세트가 있다고 가정합니다. 센서는 주기적으로 배터리 상태를 보고하고 위치와 같은 일부 메타데이터를 포함합니다. 각 센서의 마지막 배터리 상태와 위치를 확인하려는 경우 다음 쿼리를 사용할 수 있습니다.

SELECT sensor_id,
       arbitrary(location) AS location,
       max_by(battery_status, updated_at) AS battery_status
FROM sensor_readings
GROUP BY sensor_id

위치와 같은 메타데이터는 모든 레코드에서 동일하므로 arbitrary 함수를 사용하여 그룹에서 임의의 값을 선택할 수 있습니다.

마지막 배터리 상태를 가져오기 위해 max_by 함수를 사용할 수 있습니다. max_by 함수는 다른 열의 최댓값을 찾은 레코드에서 열의 값을 선택합니다. 이 경우 그룹 내 마지막 업데이트 시간과 함께 레코드에서 배터리 상태를 반환합니다. 이 쿼리는 창 함수를 사용하는 동등한 쿼리보다 실행 속도가 빠르고 메모리 사용량도 적습니다.

집계 최적화

Athena는 집계를 수행할 때 GROUP BY 절의 열을 사용하여 여러 워커 노드에서 레코드를 분산시킵니다. 레코드를 그룹과 일치시키는 작업을 최대한 효율적으로 수행하기 위해 노드에서는 레코드를 메모리에 보관하지만 필요한 경우 디스크로 유출합니다.

GROUP BY 절에서 중복 열을 포함하지 않는 것도 좋은 방법입니다. 열 수가 적을수록 필요한 메모리도 적어지기 때문에 더 적은 열을 사용하여 그룹을 설명하는 쿼리가 보다 효율적입니다. 또한 숫자 열은 문자열보다 메모리를 덜 사용합니다. 예를 들어 숫자 카테고리 ID와 카테고리 이름이 모두 있는 데이터 세트를 집계하는 경우 GROUP BY 절에서 카테고리 ID 열만 사용합니다.

열이 집계 표현식 또는 GROUP BY 절의 일부여야 한다는 점을 처리하기 위해 쿼리에서 GROUP BY 절에 열을 포함하기도 합니다. 이 규칙을 따르지 않으면 다음과 같은 오류 메시지가 나타날 수 있습니다.

EXPRESSION_NOT_AGGREGATE: line 1:8: 'category' must be an aggregate expression or appear in GROUP BY clause

GROUP BY 절에 중복 열을 추가할 필요가 없도록 다음 예제와 같이 arbitrary 함수를 사용할 수 있습니다.

SELECT country_id,
       arbitrary(country_name) AS country_name,
       COUNT(*) AS city_count
FROM world_cities
GROUP BY country_id

ARBITRARY 함수는 그룹에서 임의의 값을 반환합니다. 이 함수는 그룹 내 모든 레코드에서 열 값이 같지만 값으로 그룹을 식별하지 못하는 경우에 유용합니다.

상위 N개 쿼리 최적화

ORDER BY 절은 정렬된 순서로 쿼리 결과를 반환합니다. Athena는 분산 정렬을 사용하여 여러 노드에서 정렬 작업을 병렬로 실행합니다.

결과를 정렬할 필요가 없는 경우 ORDER BY 절을 추가하지 않습니다. 또한 필요한 쿼리가 아닌 경우 내부 쿼리에 ORDER BY를 추가하지 않습니다. 대부분의 경우 쿼리 플래너에서 중복 정렬을 제거할 수 있지만, 보장되지는 않습니다. 이 규칙의 예외는 내부 쿼리가 상위 N개 작업(예: 최근 N개 또는 가장 일반적인 N개 값 찾기)을 수행하는 경우입니다.

Athena에서 ORDER BY가 LIMIT와 함께 나타나는 경우 상위 N개 쿼리를 실행 중이고 적절히 전용 작업을 사용하는 것입니다.

참고

Athena에서 상위 N개를 사용하는 row_number와 같은 창 함수를 종종 탐지할 수 있어도 ORDER BY 및 LIMIT를 사용하는 더 간단한 버전을 사용하는 것이 좋습니다. 자세한 내용은 창 함수의 범위를 축소 또는 제거 단원을 참조하십시오.

필수 열만 포함

열이 필요하지 않은 경우 쿼리에 포함하지 않습니다. 쿼리에서 처리해야 하는 데이터가 적을수록 실행 속도가 빨라집니다. 그러면 필요한 메모리 양과 노드 사이에서 전송해야 하는 데이터 양이 모두 줄어듭니다. 열 기반 파일 형식을 사용하는 경우 열 수를 줄이면 Amazon S3에서 읽는 데이터 양도 줄어듭니다.

Athena에서 결과의 열 수에 대한 구체적인 한도는 없지만 쿼리 실행 방식에 따라 가능한 총 열 크기가 제한됩니다. 총 열 크기에는 이름 및 유형이 포함됩니다.

예를 들어 관계 설명자의 크기 제한을 초과하는 관계로 인해 다음 오류가 발생합니다.

GENERIC_INTERNAL_ERROR: io.airlift.bytecode.CompilationException

이 문제를 해결하려면 쿼리의 열 개수를 줄이거나 하위 쿼리를 만들고 더 적은 양의 데이터를 검색하는 JOIN을 사용합니다. 가장 바깥쪽 쿼리에서 SELECT *를 수행하는 쿼리가 있는 경우 *를 필요한 열만 포함하는 목록으로 변경해야 합니다.

근사치를 사용하여 쿼리 최적화

Athena에서는 개별 값, 가장 빈번한 값, 백분위수(근사 중앙값 포함) 계산 및 히스토그램 생성을 위한 근사 집계 함수를 지원합니다. 정확한 값이 필요하지 않은 경우 이 함수를 사용합니다.

COUNT(DISTINCT col) 연산과 달리 approx_distinct는 메모리를 훨씬 적게 사용하고 실행 속도도 더 빠릅니다. 마찬가지로 histogram 대신 numeric_histogram을 사용하면 근사치 계산 방법을 사용하므로 메모리가 적게 소비됩니다.

LIKE 최적화

LIKE를 사용하여 일치하는 문자열을 찾을 수 있지만 문자열이 길면 컴퓨팅 용량을 많이 소비합니다. regexp_like 함수는 대부분의 경우에 더 빠른 대안이며 유연성이 더 뛰어납니다.

찾고 있는 하위 문자열을 고정하여 검색을 최적화할 수도 있습니다. 예를 들어 접두사를 찾으려면 '%substr% 대신 'substr%'를 사용하는 것이 훨씬 좋습니다. 또는 regexp_like를 사용하는 경우 '^substr'을 사용합니다.

UNION 대신 UNION ALL 사용

UNION ALL 및 UNION은 두 쿼리의 결과를 하나의 결과로 결합하는 두 가지 방법입니다. UNION ALL은 첫 번째 쿼리의 레코드를 두 번째 쿼리와 연결합니다. UNION은 동일한 작업을 수행하되 중복된 항목도 제거합니다. UNION은 모든 레코드를 처리하고 중복을 찾아야 하므로 메모리와 컴퓨팅 용량을 많이 소비하지만, UNION ALL은 비교적 속도가 빠른 연산입니다. 레코드의 중복을 제거해야 하는 경우가 아니라면 최상의 성능을 위해 UNION ALL을 사용합니다.

결과 세트가 큰 경우 UNLOAD 사용

쿼리 결과가 커질 것으로 예상되는 경우(예: 수만 행 이상) UNLOAD를 사용하여 결과를 내보냅니다. 대부분의 경우 이 방법이 일반 쿼리를 실행하는 것보다 빠르며 UNLOAD를 사용하는 경우 출력을 더 잘 제어할 수 있습니다.

쿼리 실행이 완료되면 Athena는 결과를 압축되지 않은 단일 CSV 파일로 Amazon S3에 저장합니다. 단, 결과가 압축되지 않고 연산을 병렬화할 수 없으므로 UNLOAD보다 시간이 오래 걸립니다. 반면 UNLOAD는 워커 노드에서 직접 결과를 쓰고 컴퓨팅 클러스터의 병렬 처리를 최대한 활용합니다. 또한 결과를 압축된 형식과 JSON, Parquet 등의 다른 파일 형식으로 기록하도록 UNLOAD를 구성할 수 있습니다.

자세한 내용은 UNLOAD 단원을 참조하십시오.

CTAS 또는 Glue ETL을 사용하여 자주 사용되는 집계 구체화

쿼리 '구체화'는 사전 계산된 복잡한 쿼리 결과(예: 집계 및 조인)를 후속 쿼리에서 재사용할 수 있도록 저장하여 쿼리 성능을 가속화하는 방법입니다.

많은 쿼리에 동일한 조인 및 집계가 포함된 경우 공통 하위 쿼리를 새 테이블로 구체화하고 해당 테이블에 대해 쿼리를 실행할 수 있습니다. 쿼리 결과에서 테이블 생성(CTAS) 또는 Glue ETL과 같은 전용 ETL 도구를 사용하여 새 테이블을 생성할 수 있습니다.

예를 들어 주문 데이터 세트의 다양한 측면을 보여주는 위젯을 포함하는 대시보드를 가정합니다. 각 위젯에는 자체 쿼리가 있지만 모든 쿼리가 동일한 조인 및 필터를 공유합니다. 주문 테이블은 라인 품목 테이블과 결합되며 지난 3개월만 표시하는 필터가 있습니다. 이러한 쿼리의 일반적인 기능을 식별하면 위젯에서 사용할 수 있는 새 테이블을 생성할 수 있습니다. 그러면 중복이 줄어들고 성능이 향상됩니다. 단점은 새 테이블을 최신 상태로 유지해야 한다는 점입니다.

쿼리 결과 재사용

동일한 쿼리는 짧은 시간 안에 여러 번 실행하는 것이 일반적입니다. 예를 들어 여러 사람이 같은 데이터 대시보드를 열 때 이런 상황이 발생할 수 있습니다. 이 경우 쿼리를 실행할 때 이전에 계산된 결과를 재사용하도록 Athena에 지시할 수 있습니다. 재사용할 결과의 최대 수명을 지정합니다. 이전에 동일한 쿼리가 해당 기간 안에 실행된 경우 Athena는 쿼리를 다시 실행하는 대신 해당 결과를 반환합니다. 자세한 내용은 Amazon Athena 사용 설명서의 Athena에서 쿼리 결과 재사용 섹션 및 AWS 빅 데이터 블로그의 Reduce cost and improve query performance with Amazon Athena Query Result Reuse를 참조하세요.