Amazon Athena로 S3 데이터 쿼리하기

brillog·2023년 8월 19일

Analytics aws log

AWS

목록 보기

5/7

로그 보관정책 준수를 위해 VPC Flowlog를 S3에 적재하고 있습니다. S3에 저장된 VPC Flowlog들을 Amazon Athena를 이용해 쿼리 하는 방법에 대해 알아보겠습니다.

데이터는 S3에 year>month>day 순 depth로 저장되며 Athena에서는 이를 활용해 Partitioning 하여 쉽게 원하는 대로 로그 쿼리가 가능합니다.

Table 생성 및 파티셔닝

S3에 적재되는 데이터 형식을 알아보기 위해 실제로 S3에 적재된 VPC Flowlog 데이터 예시를 살펴보겠습니다.

version account-id interface-id srcaddr dstaddr srcport dstport protocol packets bytes start end action log-status
2 711676373963 eni-xxxxxxxxxxxxxxxxx 10.x.x.x 192.x.x.x 80 54452 6 5 884 1690848063 1690848091 ACCEPT OK
2 711676373963 eni-xxxxxxxxxxxxxxxxx 192.x.x.x 10.x.x.x 54452 80 6 7 3145 1690848063 1690848091 ACCEPT OK
...

S3에 저장된 VPC Flowlog 로그를 쿼리하기 위해서는 사전에 Athena Table을 생성하고 파티셔닝하는 과정이 필요합니다. Table 생성 및 파티셔닝하는 과정은 다음과 같습니다.

CREATE EXTERNAL TABLE IF NOT EXISTS `<ATHENA_TABLE_NAME>` (
	version int,
    account_id string, 
    interface_id string, 
    srcaddr string, 
    dstaddr string, 
    srcport int, 
    dstport int, 
    protocol bigint, 
    packets bigint, 
    bytes bigint, 
    start bigint, 
    end bigint, 
    action string, 
    log_status string
)
PARTITIONED BY (`month` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
LOCATION 's3://...<S3_PATH>.../year=2023/month=08/'
TBLPROPERTIES ("skip.header.line.count"="1")

Table 업데이트

LOCATION 's3://...<S3_PATH>.../year=2023/month=08/'을 보면 알 수 있듯, 위 예시는 2023년 8월의 VPC Flowlog 확인을 위해 생성된 Table입니다. 만약 8월 15일에 Table을 생성했다면 쿼리 가능한 데이터는 8월 1일 ~ 8월 15일간의 데이터일 것입니다. 만약 8월 20일이 되어 이 테이블에서 쿼리를 시도할 경우, 8월 16일 ~ 8월 20일까지의 데이터는 쿼리가 불가능합니다. 따라서 생성된 Table의 데이터의 업데이트가 필요할 경우 아래와 같이 ALTER를 활용합니다.

ALTER TABLE <ATHENA_TABLE_NAME>
ADD PARTITION (`month`='2023-08')
LOCATION 's3://...<S3_PATH>.../year=2023/month=08/'

Athena 데이터 쿼리

이제 내가 원하는 정보를 쿼리해봅시다.

(예시) 저는 destination address가 '192.4'로 시작하지 않으면서 && destination port는 '80'이 아니면서 && interface id가 eni-1234xxxxxxxxxxxxx 이면서 && action은 'ACCEPT'인 '2023년 8월의 VPC Flowlog'만을 쿼리하고 싶습니다. 또한 쿼리 결과에서는 month, interface_id, srcaddr, srcport, dstaddr, dstport, action 만을 확인하고 싶습니다.

위와 같은 조건을 충족시키면서 데이터를 쿼리하려면 아래와 같이 SQL문을 작성하면 됩니다.

SELECT month, interface_id, srcaddr, srcport, dstaddr, dstport, action
FROM <ATHENA_TABLE_NAME>
WHERE dstaddr NOT LIKE '192.4.%'
	AND dstport != 80
    AND interface_id = 'eni-1234xxxxxxxxxxxxx'
    AND action = 'ACCEPT'