CLI를 사용하여 Amazon Athena에 쿼리를 실행해보고 알게 된 것

안녕하세요 DA부 인테그레이션부의 송영진입니다.

오늘은 AWS CLI를 이용해서 Athena에 쿼리를 실행해보고 알게된 주의할 포인트 몇 개가 보여서 블로그를 작성했습니다.

Amazon Athena란?

Amazon Athena는 표준 SQL을 사용해 Amazon S3에 저장된 데이터를 간편하게 분석할 수 있는 대화식 쿼리 서비스입니다. Athena는 서버리스 서비스이므로 관리할 인프라가 없으며 실행한 쿼리에 대해서만 비용을 지불하면 됩니다.

Athena는 사용이 쉽습니다. Amazon S3에 저장된 데이터를 가리키고 스키마를 정의한 후 표준 SQL을 사용하여 쿼리를 시작하기만 하면 됩니다. 그러면 대부분 결과가 수 초 이내에 제공됩니다. Athena에서는 데이터 분석을 준비하기 위한 복잡한 ETL 작업이 필요 없습니다. 따라서 SQL을 다룰 수 있는 사람은 누구나 신속하게 대규모 데이터 세트를 분석할 수 있습니다.

Athena는 AWS Glue 데이터 카탈로그와 즉시 통합되므로, 다양한 서비스에 걸쳐 통합된 메타데이터 리포지토리를 생성하고, 데이터 원본을 크롤링하여 스키마를 검색하고 카탈로그를 신규 및 수정된 테이블 정의와 파티션 정의로 채우며, 스키마 버전을 관리할 수 있습니다.

Athena는 S3에 있는 데이터를 빠르고 간편하게 분석할 수 있는 서비스입니다. S3 Select와도 비슷하다고 생각하실 수 있겠지만 여러 파일들에 대해 적용이 가능한 Athena쪽이 강력합니다.

AWS CLI란?

AWS 명령줄 인터페이스(CLI)는 AWS 서비스를 관리하는 통합 도구입니다. 도구 하나만 다운로드하여 구성하면 여러 AWS 서비스를 명령줄에서 제어하고 스크립트를 통해 자동화할 수 있습니다.

쉘에서 가벼운 명령어로 매니지먼트 콘솔을 사용하지 않고 AWS의 서비스를 사용 할 수 있는 기능입니다. 적응이 되면 반복되는 작업의 경우 콘솔보다 CLI를 사용한 스크립트를 사용하는게 더 빠르기 때문에 이쪽을 더 선호하게 됩니다.

Athena의 CLI 명령어는 이 링크에서 확인 하실 수 있습니다.

어떻게 사용했나?

저는 이번에 순서가 뒤죽박죽인 두 파일의 내용물을 정렬해서 파일 내용이 같은지를 확인하는 작업을 위해서 Athena를 사용하였습니다.

이런 파일들이 다수 있기 때문에 여러번 실행을 위해서 사용할 SQL을 작성하고 쉘 스크립트를 이용하여 실행하도록 하였습니다.

작업순서는 다음과 같습니다.

S3에 파일 업로드 -> Athena에 외부 참조 테이블로 생성 -> 테이블 정렬 -> 쿼리 실행 결과의 ETag 확인

S3의 오브젝트는 파일의 내용물을 갖고 ETag라는 해시값을 갖게 되는데요. 이 값이 동일하고 파일의 크기가 같다면 같은 내용의 파일이라고 생각하셔도 좋습니다.

무엇이 포인트인가?

자 이제 제가 느꼈던 포인트들을 알아보겠습니다.

SQL 파일을 매개변수로 실행 할 수 없다

CLI에서 Athena에 쿼리를 실행하기 위해서는 start-query-execution 명령어가 필요합니다. 그런데 이 명령어로 매개변수를 받아야하는데 그 매개변수로 문자열만 가능합니다.

예를 들어서 다음과 같은 코드는 실행이 되지 않습니다.

aws athena start-query-execution --query-string query_file.sql

그래서 어떻게 처리했냐하면 다음과 같은 쉘 스크립트를 사용했습니다.

#!/usr/bin/env bash
set -eu

SQL=$(<file_path/query_file.sql)
aws athena start-query-execution --query-string "$SQL"

쉘 스크립트에서 파일의 내용을 읽어서 변수에 저장하는 방법은 다음과 같습니다.

변수=$(<파일경로)

변수의 내용을 문자열로 바꾸는 방법은 다음과 같습니다.

"$변수"

어렵지 않죠? 변수에 파일의 내용을 읽어서 변수의 내용을 문자열로 만들어서 실행을 하도록 하면 SQL을 파일로 만들어놓고 사용하실 수 있겠습니다.

SQL에 변수 사용이 되지 않는다

파일로 쿼리를 실행은 성공했습니다 그러나 같은 쿼리의 내용으로 다른 테이블에도 쓰고 싶을 경우가 있죠 이럴 때 사용하는 것이 변수인데요 Athena의 CLI에서는 문자열으로만 지원하기 때문에 이번에도 쉘 스크립트로 해결을 해보겠습니다.

#query_file.sql
select
  id,
  value
from
  table1
order by
  1, 2
;

예로 table1의 내용을 id와 value의 순서로 정렬을 한 결과를 얻는 쿼리입니다. 이 내용을 table2에 적용을 하고 싶습니다.

#!/usr/bin/env bash
set -eu

SQL=$(<file_path/query_file.sql)
SQL=${SQL/table1/table2}
aws athena start-query-execution --query-string "$SQL"

쉘 스크립트의 문자열 치환 기능을 이용하여 table2를 지정하는 쿼리 문자열을 만들어서 실행합니다. 문자열 치환 방법은 다음과 같습니다.

${변수명/찾을패턴/바꿀문자}    # 처음 매칭되는 1개만 변경
${변수명//찾을패턴/바꿀문자}   # 매칭되는 전체 변경

쿼리가 끝났다고 쿼리 결과가 바로 S3에 저장되는 것이 아니다

저는 쿼리의 결과 파일을 이용하기 때문에 Athena에게 쿼리를 실행하라는 명령을 내린 뒤에 결과가 출력 될 때까지 기다려야 할 필요가 있었습니다.

이걸 몰랐기 때문에 어떤 파일은 바로 ETag를 가져오는데 왜 다른 파일은 안나오지?? 같은 코드인데 이건 왜 되고 왜 저건 안되는거야?? 하면서 무수한 물음표를 띄우고 있었는데요, 결국 데이터가 크기 때문에 아직 결과 파일이 출력이 되지 않았기 때문이었다는 결과를 알게 되었습니다.

따라서 결과가 S3 버킷에 출력 될 때까지 확인을 하며 기다리는 코드가 필요하게 됩니다.

어떻게 확인을 해야하냐면, 쿼리를 실행시킬때 사용했던 명령어인 start-query-execution의 리턴값은 실행한 쿼리의 ID입니다. 쿼리의 결과는 실행된 쿼리 ID를 이름으로 저장이 되기 때문에 이 이름을 가지고 S3의 CLI 명렁어를 사용해서 검색하실 수 있게 됩니다.

저 같은 경우에는 쿼리가 종료되었는지, 쿼리의 결과가 S3에 저장이 되었는지를 확인하는 코드를 사용하였습니다.

첫번째는 쿼리가 종료되었는지 확인하는 코드입니다.

while :
do
    QUERY_ID=`aws athena start-query-execution \
        --query-string "QUERY" \
        --OutputLocation="OUTPUT_BUCKET"`
    if [ ${QUERY_ID} == "SUCCEED" ] then
        break
    else
        sleep 5
    fi
done

여기서 OutputLocation 옵션은 결과 파일이 저장될 S3 경로를 지정합니다.

다음으로 쿼리의 결과가 출력이 완료되었는지 확인하는 코드입니다.

ETAG=`aws s3api list-objects-v2 --bucket "OUTPUT_BUCKET" \
        --prefix ${QUERY_ID} | jq '.Contents | .[0].ETag'`
while [ -z ${ETAG} ]; do
    sleep 5
    ETAG=`aws s3api list-objects-v2 --bucket "OUTPUT_BUCKET" \
        --prefix ${QUERY_ID} | jq '.Contents | .[0].ETag'`
done

저 같은 경우에는 결과 파일의 ETag가 필요했기 때문에 S3의 CLI 명령어 중에서 list-objects-v2를 사용하였고 그 리턴으로 돌아오는 json에서 ETag값만 가져오도록 jq를 사용했습니다.

반복문의 조건의 내용은 ETAG라는 문자열 변수의 문자열의 길이가 0이면 true가 됩니다. 결과가 저장이 되었다면 파일의 ETag를 가져올테고 아직 저장이 되지 않았다면 빈 문자열이 변수에 들어가기 때문에 길이가 0이 됩니다.

이렇게 제가 원하던 결과를 가져올 수 있게 되었습니다!

끝으로

코드를 쓰다보니까 Athena보다는 쉘스크립트에 대한 내용이 되어버렸네요 ㅎㅎㅎ;;; 그래도 조금이라도 도움이 되었으면 기쁘겠습니다. 아직 파일 가지고 쿼리를 실행하는 부분이 지원하지 않아서 아쉽지만 이 부분도 금방 추가되지 않을까 싶습니다! 빠르고 값싼 Athena 여러분들도 사용해보시는건 어떤가요?