Amazon Athena, 개념 및 사용법 정리

Amazon Athena

• Amazon Athena는 아마존이 제공하는 서버리스 대화식 데이터 쿼리 서비스 상품이다. Presto 0.172 기반의 ANSI SQL을 지원하여 다양한 데이터 소스에 대해 일관된 질의가 가능하다.

Amazon Athena 특징

• 질의하고자 하는 데이터 소스(CSV, JSON, Apache Parquet)을 Amazon S3에 업로드한 후, Athena에서 해당 데이터 파일에 대해 RDBMS와 동일하게 데이터베이스, 테이블, 뷰, 조인, 피벗 개념을 적용하여 굉장히 친숙한 느낌으로 표준 SQL 쿼리를 질의할 수 있다. (오픈 소스 진영에서는 Apache Drill이 유사한 위치에 있다.)
• S3에 업로드한 파일은 사전에 Amazon Glue에서 크롤러를 생성하여 해당 파일을 분석하여 메타 데이터를 자동 생성하도록 하면, 훨씬 편리하게 질의할 수 있다.
• Athena는 JDBC 드라이버를 제공한다. 따라서, 이를 지원하는 써드파티 클라이언트를 사용하면 편리하게 질의할 수 있다. 질의 자체가 목적이라면 Athena를 네이티브하게 지원하는 DBeaver 커뮤니티 에디션을 추천한다. (나는 대부분의 질의를 DBeaver에서 해결한다.) 시각화와 리포팅이 목적이라면 Microsoft PowerBI와 같은 외부 BI 솔루션에서도 연동이 가능하다.

Amazon Athena 제약사항

• AWS 계정 당 최대 5개로 동시 실행 쿼리 수가 제한된다. 5개가 초과될 경우 나머지 쿼리는 큐에 누적되어 연결을 유지한 채 대기하게 된다. 이러한 이유로 요청량이 많은 실시간 서비스에서는 적합하지 않다.
• AWS 계정 당 최대 100개로 S3 버킷 생성이 제한된다. 별도 요청시 최대 1,000개까지 허용될 수 있다.
• AWS 계정 당 최대 10,000개로 데이터베이스 생성이 제한된다. 각 데이터베이스는 최대 200,000개로 테이블 생성이 제한된다.

구조체, 배열 데이터 형식 처리

• Athena가 RDBMS와 다르게 생소한 점은 구조체와 배열을 지원하는 것이다. 구조체의 경우, foo.bar와 같이 dot 기호를 사용하여 질의에 사용할 수 있다. 배열은 조금 까다로운데 질의의 필터 또는 조회 결과에 사용하려면 UNNEST 기능을 이용해야 한다. 사용 예는 아래와 같다.

# foo_array 배열의 각 요소를 별도의 로우로 분리
SELECT
    id,
    foo.bar_1,
    foo.bar_2
FROM
    "foo_db".bar_table,
    UNNEST(foo_array) AS T(foo)
WHERE
    foo.bar_1 = 'something'
LIMIT 10;

• 구조체와 배열은 필요에 따라 적절하게 뷰를 제작하면 데이터 분석시 유용하게 활용할 수 있다. 뷰 생성 예는 아래와 같다.

# 특정 쿼리에 대한 뷰를 생성
CREATE VEIW "foo_db".bar_view AS
SELECT ...

날짜/시간 데이터 형식 처리

• Athena는 날짜/시간으로 DATE, TIMESTAMP 데이터 타입만 지원한다. 따라서 추가 작업을 최소화하려면, 데이터를 생성하는 시점에 UTC+0 기준으로 Athena가 지원하는 TIMESTAMP 형식으로 가공하여 저장하면 추후 작업이 편리해진다.

# DATE 타입의 예 (YYYY-MM-DD)
2020-11-09

# TIMESTAMP 타입의 예 (YYYY-MM-DD HH:MM:SS.fffffffff)
2020-11-09 07:18:42.425

# Java에서 LocalDateTime 객체를 TIMESTAMP 문자열로 변환한 예
String timestamp = localDateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS"));

• Athena는 SQL 질의시 TIMESTAMP 타입에 대해 타임존과 함께 처리할 수 있는 다양한 기능을 지원한다.

# 2020-11-09 07:18:42.425 UTC
SELECT current_timestamp;

# 2020-11-09 16:18:42.425 Asia/Seoul
SELECT current_timestamp AT TIME ZONE 'Asia/Seoul';

# 2020-11-09 16:18:42.425 Asia/Seoul
SELECT at_timezone(current_timestamp, 'Asia/Seoul');

# 2020-11-09 16:15:29.964 Asia/Seoul
SELECT from_iso8601_timestamp('2020-11-09T07:15:29.964Z') AT TIME ZONE 'Asia/Seoul';

# 2020-11-09 16:15:29.964 +09:00
SELECT from_iso8601_timestamp('2020-11-09T16:15:29.964+09:00')

• DATE, TIMESTAMP 형식 컬럼에 대해 날짜를 기준으로 조회하는 방법은 아래와 같다.

# 2020-11-09 00:00:00 이후의 모든 로우를 조회
SELECT * FROM foobar WHERE created_at > date('2020-11-09')

연속된 날짜 컬럼 생성

• 통계 지표를 제작하다보면 x 축에 날짜 단위를, y 축에 그룹핑된 통계 데이터를 출력하는 것이 일반적이다. 아래는 최근 14일간의 연속된 날짜 컬럼을 임의로 생성하는 예이다. 뷰로 만들어두고 다른 데이터를 조인하는 형태로 제작하면 편리하게 활용할 수 있다.

select 
  cast(date_column as date) as "날짜" 
from 
  (
    values 
      (
        sequence(
          date(at_timezone(current_timestamp, 'Asia/Seoul')) - interval '14' day, 
          date(at_timezone(current_timestamp, 'Asia/Seoul')), 
          interval '1' day
        )
      )
  ) as t1(date_array)
cross join unnest(date_array) as t2(date_column)

• 실행 결과는 아래와 같다.

|날짜      |
|----------|
|2020-12-08|
|2020-12-09|
|2020-12-10|
|2020-12-11|
|2020-12-12|
|2020-12-13|
|2020-12-14|
|2020-12-15|
|2020-12-16|
|2020-12-17|
|2020-12-18|
|2020-12-19|
|2020-12-20|
|2020-12-21|
|2020-12-22|

연속된 주 컬럼 생성

• 앞서 하루 단위의 날짜 컬럼을 생성했다면, 주 단위의 날짜 컬럼을 생성할 수도 있다. 아래는 일월화수목금토를 한 주로 간주하고 각 주의 첫 날짜만 출력하는 예이다. [관련 링크]

# 뷰로 생성해두면 다른 집계 쿼리에서 편리하게 조인으로 사용 가능
# create or replace view "crolo-data-lake-prod".week_view as
select 
  distinct date_trunc('week', "날짜" + interval '1' day) - interval '1' day as "주차" 
from 
  (
    select 
      cast(date_column as date) as "날짜" 
    from 
      (
        values 
          (
            sequence(
              date(at_timezone(current_timestamp, 'Asia/Seoul')) - interval '2' month, 
              date(at_timezone(current_timestamp, 'Asia/Seoul')), 
              interval '1' day
            )
          )
      ) as t1(date_array)
      cross join unnest(date_array) as t2(date_column)
  )

• 실행 결과는 아래와 같다.

|날짜      |
|----------|
|2020-10-25|
|2020-11-01|
|2020-11-08|
|2020-11-15|
|2020-11-22|
|2020-11-29|
|2020-12-06|
|2020-12-13|
|2020-12-20|
|2020-12-27|

최초 값, 마지막 값 출력

• 특정 기간 가입한 회원의 첫 로그인 날짜만 뽑아 통계 지표를 생성해야 한다면? fisrt_value(), last_value()를 적절히 활용하면 아래와 같이 쉽게 지표를 뽑아낼 수 있다. [관련 링크1] [관련 링크2]

# user_login_log.user_id로 그룹핑하여 각 user_id마다 최초의 created_at만 출력
select 
  # 4. 중복 제거하여 user_id와 함께 출력
  distinct user_id,
  first_value(
    # 3. 최초의 created_at 값을
    created_at
  ) over (
    # 1. user_id 마다
    partition by user_id 
    # 2. created_at 기준으로 오름 차순 정렬하여
    order by created_at
  ) as "첫 로그인 일시" 
from
  user_login_log
where 
  ...

퍼센트 계산

• 특정 컬럼 간의 연산을 통해 퍼센트를 구하는 커스텀 컬럼을 생성할 수 있다.

select
    (cast(1 as double) / coalesce("some_column_1", 1) * coalesce("some_column_2", 0)) as some_percent
from
    some_table

조건식 적용

• 아래와 같이 특정 컬럼에 대해 조건식을 적용할 수 있다.

select
    (case when some_column > 1 then 1 else some_column end) as some_column
from
    some_table

참고 글

• 9 Things To Consider When Choosing Amazon Athena
• Athena를 조금 더 효율적으로 쓰기 위한 쿼리가이드
• How to Improve AWS Athena Performance: The Complete Guide
• Apache Parquet: How to be a hero with the open-source columnar data format on Google, Azure, and Amazon cloud
• Optimizing QuickSight using Athena Queries and SPICE: Operating cost analysis
• Stack Overflow - SQL Retention Cohort Analysis
• Stack Overflow - What does SQL clause “GROUP BY 1” mean?
• Stack Overflow - Presto: Getting days interval (not date)