AWS Athena로 S3에 있는 CSV파일 데이터 쿼리하기 (feat. partitioning)

AWS Athena란?

S3 에 저장되어 있는 데이터를 DB처럼 쿼리할 수 있는 서비스이다. s3에 csv, tsv, json 등의 형식으로 저장한 파일의 내용을 SQL 쿼리를 통해 select, insert 할 수 있다.

update, delete는 기본적으로는 안되는데, 일부 되는것처럼 소개하는 글도 있다. S3는 파일 수정이 불가하기 때문에 되더라도 비효율적인 방식일꺼라 예상한다. (실제 가능여부는 좀더 살펴보고 업데이트 예정)

언제 사용할까?

• Log와 같은 비정형 데이타 혹은 DB에 저장되지 않은 정형 데이터 등을 SQL을 통해 분석이 필요한 경우
• 분석 빈도가 많지 않아 데이터 분석을 위한 별도의 인프라 구성이 부담스러운 경우

요금 정책

요금정책은 아주 심플하다. 쿼리 실행시 스캔한 데이터의 사이즈만큼 비용을 청구한다 (5$/TB). 스캔한 데이터의 사이즈를 최소화 하기 위해서 데이터 압축, 파티션, 열 기반 데이터 형식 등이 중요하다.

AWS Athena 사용해보기

S3에 csv 파일을 업로드 해서 Athena로 조회해보자. 이를 위해서 아래와 같은 작업을 해야한다.

1. S3에 csv 파일 업로드
2. Athena에서 database 및 table 생성
3. 데이터 조회
4. 테스트 - 파일 추가 업로드, 압축, Partition

1. S3에 csv 파일 업로드

S3에 bucket을 생성하고, csv 파일을 업로드 한다. csv 파일 샘플은 링크에서 하나를 선택하면 되고 나는 이중  snake_count_10000.csv 를 사용했다

2. Athena에서 database 및 table 생성

2-1) Setting

Setting > Manage 선택 > 쿼리 결과가 저장될 s3 경로 입력 > Save

2-2) Database 생성

Athena > Query editor > Query 입력칸에 아래 입력후 실행

CREATE DATABASE test

Database 콤보박스에 test 가 생성된것을 확인

2-3) table 생성

Query 입력칸에 아래 입력후 실행. 아래 [s3 업로드경로]는 파일명이 아닌 디렉토리의 위치이다. 쿼리 실행시 해당 디렉토리에 있는 모든  파일을 대상으로 하기 때문에 데이터 파일 외에 다른 파일은 없어야 한다.

CREATE EXTERNAL TABLE IF NOT EXISTS snakes_csv (
  `Game Number` INT,
  `Game Length` INT
  ) 

ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
LOCATION [s3 업로드경로]
TBLPROPERTIES ("skip.header.line.count"="1");

3. 데이터 조회

Query 입력칸에 아래 입력후 실행

SELECT * FROM "test"."snakes_csv" LIMIT 10;

아래와 같이 결과가 출력됨을 확인할 수 있다. game number, game length만 보이는게 정상이다. 이 쿼리를 실행하기 위해서 246.63KB 의 데이터를 스캔한 것을 확인할 수 있다. (오른쪽 가운데) 5$/TB 기준으로 비용을 계산해보면  0.000001$ 이다.

4. 테스트 - 파일 추가 업로드, 압축, Partitioning

이것만으로는 조금 부족한듯 하여 몇가지 추가 테스트를 해봤다.

4-1) 파일 추가 업로드

table 생성 이후에 추가로 파일을 업로드 하면 어떻게 될까? 현재 아래 쿼리를 실행하면 10000 이다. csv 파일을 일부 편집해서 create table 할때 입력한 경로에 다른 파일로 업로드 해보자. 파일명은 상관없다. 기존 csv 파일과 형식(=스키마)만 동일하면 된다. 업로드 후 재실행하면 count 수가 2배(20000)가 됨을 확인할 수 있다.

SELECT COUNT(*) FROM "test"."snakes_csv"

4-2) 압축

Athena의 비용은 스캔한 데이터의 크기를 기준으로 발생한다. 따라서 스캔한 데이터의 크기를 줄이는게 중요하며 이를 위해서 압축, Partition, 컬럼형식의 데이터로 변환 등을 사용할 수 있다. 가능한 압축 포맷은 링크 및 아래 사진을 참고한다. csv 파일은 텍스트 파일에 포함된다.

이중 gzip 을 사용해보자. 기존 csv 파일을 일부 편집하고 다른 파일명으로 저장한후 아래 커맨드를 실행시키면 .gz 파일이 생성된다.

gzip [파일명]

파일을 S3 업로드후 count 쿼리를 실행하면 숫자가 늘어났음을 확인할 수 있다.

4-3) Partition (파티션)

Partition 을 간단하게 설명하면, 데이터를 분할 저장하여 조건에 따라 일부 데이터만 검색할수 있도록 하는 기법이다. (링크 참고)

위를 모두 수행했다면 현재 저장되어 있는 csv 파일이 3개일텐데 count 쿼리를 실행하면 Data scanned과 3개 csv 파일 사이즈의 합이 동일함을 확인할 수 있다. 즉 쿼리 실행시마다 모든 csv 파일을 검색하는 것이다. 이는 곧 비용이므로 조건에 따라 일부 csv 파일만 검색하도록 partitioning 을 해보자.

이를 위해서는 아래 작업을 수행해야 한다. Athena는 S3를 기반으로 동작하기 때문에 결국에는 csv 파일의 경로를 다르게 하고 해당 정보를 table의 partition 정보로 활용하는 것이다

1. csv 파일을 디렉토리별로 그룹핑 해서 저장
2. 디렉토리명을 partition 조건으로 활용

먼저 create table의 s3 경로에 그룹핑할 디렉토리를 생성하고 해당 디렉토리에 csv 파일을 적절하게 업로드 한다. (아래 사진은 이름으로 생성했지만 날짜, 시간 등을 사용할 수 있다.)

create table 수행시 아래와 같이 수행한다. 여기서 달라지는 부분은 "PARTITIONED BY" 이다. 위의 사진에서 kim, park이 name이 되는 것이고, 필요에 따라서 적절하게 입력하면 된다. [s3 업로드경로] 는 park, kim이 위치한 s3 업로드경로를 의미한다.

CREATE EXTERNAL TABLE IF NOT EXISTS snakes_csv (
  `Game Number` INT,
  `Game Length` INT
  ) 

PARTITIONED BY(`name` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
LOCATION [s3 업로드경로]
TBLPROPERTIES ("skip.header.line.count"="1");

이후에 table에 아래와 같이 partition location 정보를 추가한다.

ALTER TABLE snakes_csv ADD
  PARTITION (name = 'park') LOCATION [s3 park경로]
  PARTITION (name = 'kim') LOCATION [s3 kim경로];

이후에 SELECT를 할때 WHERE 조건으로 name을 입력할 수 있고, 출력결과에 아래와 같이 name 칼럼이 있음을 확인할 수 있다.

SELECT * FROM "test"."snakes_csv" WHERE name='kim';

WHERE 조건에 맞춰서 해당 s3 경로에 있는 csv 파일만 검색하기 때문에 입력 조건에 따라서 Data scanned 이 줄어듦을 확인할 수 있다.