[AWS] Athena를 조금 더 효율적으로 쓰기 위한 쿼리가이드

Athena(Presto) Query를 조금 더 효율적으로 쓰기 위한 가이드.
주로 하단의 [참조] 링크들을 참조, 번역하여 취합하였음.

Athena

• 주로 S3의 데이터를 query하기 위한 AWS의 interactive query service.
• Facebook에서 개발한 오픈소스 인메모리 분산쿼리엔진인 Presto를 랩핑해서 AWS Cloud에 구현한 것이 Athena.
  따라서 Athena는 Presto의 Function과 ANSI SQL을 (대부분) 지원한다.
• 특징
  serverless architecture로 사용이 간편하고 초기 비용이 저렴
  MPP(Massive Parallel Query)
  Performs full table scans instead of using indexes
• Pricing
  $5 per 1TB data scan

Service Quotas

• Athena의 리소스는 계정당 별도의 서버나 리소스가 할당되어 활용하는 것이 아니라 AWS 내부적으로 공유되는 자원이므로, 특정 계정에서 점유하지 못하도록 root 계정당 Service Quota가 설정되어 있다.
• 예를 들어 기본 DML query quota는 다음과 같다. AWS에 request를 통해 조절할 수 있다. (잘 안해준다고 한다..)

DML query quota — 20 DML active queries. DML queries include SELECT and CREATE TABLE AS (CTAS) queries.

DML query timeout — The DML query timeout is 30 minutes.

Per query data usage control

• AWS 운영자는 workgroup별로 Athena query 1회당 data scan size에 제한 및 모니터링 기준을 세팅할 수 있으며, 필요에 따라 조정 가능하다.
  예를 들어 현재 운영중인 Athena는 아래와 같이 세팅하였다.

• Data scan limits : 1TB
• Data scan notification limits : 2TB per 1hour
  (the limit for the maximum amount of data queries are allowed to scan within a specific period.)
• notification limit을 넘는 경우 notification을 slack또는 email으로 수신할 수 있도록 세팅
• limit과 noti조건은 각자 실제 사용하는 데이터 특성, 환경과 use case에 맞게 설정하면 될 듯 하다.

위와 같이 limit를 세팅하게 된 계기는,
Athena query 비용을 모니터링 해보니 가끔 평소의 수십배에 달하는 비용이 부과되는 날이 있었고(일 $20 부과되던 게 어느날 일 $700 로 평균대비 30배 부과된다던가..), 원인을 찾아보니 대부분 데이터에 익숙치 않은 사용자가 partition 조건 없이 전체 S3 table 조회를 수회~수십회 반복해서 시도한 경우가 원인이었다.

이러한 실수를 원천적으로 막기 위해서,
위의 limit 설정을 추가하고, 그럼에도 과다한 사용이 일어날 경우 운영자가 알 수 있도록 notification을 추가하였다.
또한 Athena는 상대적으로 가벼운 ad-hoc query 목적에 충실할 수 있도록 이러한 활용 가이드를 작성하여 공유하였다.

Query Guide

Approximate Functions

100% 정확한 Unique Count가 필요한 케이스가 아니라면(추세를 보는 것으로 충분한 경우) distinct() 대신 approx_distinct()를 활용해 Approximate Unique Count를 활용할 수 있다.
이 방식은 hash값을 활용해 전체 string값을 읽는 것보다 훨씬 적은 메모리를 활용해 훨씬 빠르게 계산할 수 있도록 해준다. 표준오차율은 2.3%.

Example :
SELECT approx_distinct(l_comment) FROM lineitem;

SELECT

asterisk(*)를 사용한 전체 선택은 지양하고 필요한 컬럼을 지정하여 SELECT

Parquet file 특성 상 컬럼별로 지정하여 원하는 컬럼만 read 하므로 불필요한 연산을 크게 줄일 수 있다.

JOIN

Issue : Left에 작은 테이블, Right에 큰 테이블을 두면 Presto는 Right의 큰 테이블을 Worker node에 올리고 Join을 수행한다. (Presto는 join reordering을 지원하지 않음)

Best Practice : Left에 큰 테이블, Right에 작은 테이블을 두면 더 작은 메모리를 사용하여 더 빠르게 쿼리할 수 있다.

JOIN (Big Tables)

Issue : Presto는 Index 없이 fast full table scan 방식. Big table간 join은 아주 느리다. 따라서 AWS 가이드는 이런 류의 작업에 Athena 사용을 권장하지 않는다.

Best Practice : Big Table은 ETL을 통해 pre-join된 형태로 활용하는 것이 좋다.

ORDER BY

Issue : Presto는 모든 row의 데이터를 한 worker로 보낸 후 정렬하므로 많은 양의 메모리를 사용하며, 오랜 시간 동안 수행하다가 Fail이 나기도 함.

Best Practice :
1. LIMIT 절과 함께 ORDER BY를 사용. 개별 worker에서 sorting 및 limiting을 가능하게 해줌.
2. ORDER BY절에 String 대신 Number로 컬럼을 지정. ORDER BY order_id 대신 ORDER BY 1

GROUP BY

Issue : GROUPING할 데이터를 저장한 worker node로 데이터를 보내서 해당 memory의 GROUP BY값과 비교하며 Grouping한다.

Best Practice : GROUP BY절의 컬럼배치순서를 높은 cardinality부터 낮은 cardinality 순(unique count가 큰 순부터 낮은 순으로)으로 배치한다. 같은 의미의 값인 경우 가능하면 string 컬럼보다 number 컬럼을 활용해 GROUP BY한다.

Example :

SELECT state, gender, count(*) FROM census GROUP BY state, gender;

LIKE

Issue : 여러개의 string 컬럼에 like검색을 써야하는 경우 regular expression을 사용하는 것이 더 좋다.

Example :
SELECT count(*) FROM lineitem WHERE regexp_like(l_comment, 'wake|regular|express|sleep|hello')

Data Preparation Guide

Metadata-Driven Read Optimization

• parquet, ORC format의 경우 data section별로 metadata를 보관한다. 따라서 filter(where) 조건을 잘 주면 data read를 skip할 수도 있다.
  e.g., values < 5 조건을 주었는데 metadata에 해당 stripes는 between 100 and 500 사이라고 기록되어 있는 경우, 해당 data read를 skip 가능함.
• 즉, 필터조건에 따라 필요없는 block을 제외하고 읽을 수 있도록 하기 위해 data 적재 시 가장 사용성이 높은 필터 조건 중심으로 data를 sorting 해두는 것이 좋다.(e.g. sorting by value)

Partitioning Data

• 적절한 파티셔닝을 통해 필요한 데이터만 선택적으로 read할 수 있도록 한다.
• Hive의 MSCK REPAIR TABLE 커맨드를 지원한다.
• yy=2019 혹은 dt=2019–10–20 와 같은 format을 지켜서 partition directory를 만들면 partition도 일괄생성으로 편하게 만들 수 있다.

Compress and Split Files

• Use splittable format like Apache Parquet or Apache ORC.
• BZip2, Gzip로 split해서 쓰기를 권장하며 LZO, Snappy는 권장하지 않는다. (압축효율 대비 컴퓨팅 속도를 감안)

Optimize File Size

• optimal S3 file size is between 200MB-1GB
• file size가 아주 작은 경우(128MB 이하), executor engine이 S3 file을 열고, object metadata에 접근하고, directory를 리스팅하고, data transfer를 세팅하고, file header를 읽고, compression dictionary를 읽는 등의 행동을 해야하는 오버헤드로 인해 효율이 떨어지게 된다.
• file size가 아주 크고 splittable하지 않은 경우, query processor는 한 파일을 다 읽을때까지 대기해야 하고 이는 athena의 강력한 parallelism 기능을 활용할 수 없게 한다.
• Example

Join Big Tables in the ETL Layer

• Athena는 index 없이 full table scan을 사용한다. 따라서 작은 규모의 데이터를 join할 때는 아무 문제 없지만 큰 데이터를 조인할 때는 ETL을 활용해 pre-join된 형태로 활용하는 것이 좋다.

Optimize Columnar Data Store Generation

The stripe size or block size parameter : ORC stripe size, Parquet block size는 block당 최대 row수를 의미한다. ORC는 64MB, Parquet는 128MB를 default로 가진다. 효과적인 sequential I/O를 고려하여 column block size를 정의할 것.

참조

https://aws.amazon.com/ko/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/
https://www.upsolver.com/blog/aws-athena-performance-best-practices-performance-tuning-tips
https://www.upsolver.com/blog/6-data-preparation-tips-querying-big-data-aws-athena
https://searchaws.techtarget.com/tip/Improve-query-performance-cut-costs-with-Amazon-Athena