[Athena] ELB Access Log 분석이 정상적으로 되어지지 않는 현상

Issue

저장되어지는 ELB Access Log 를 Athena로 분석하는 기능은 널리 알려져 있다. 이를 바탕으로 이슈 분석 혹은 통계에 이용되어지기도 한다.

 

그러나, 5월 특정 시점으로부터 통계가 이상해지는 부분이 확인이 되어져 있으며 쿼리 결과가 NULL 값으로 반환되어지는 행들이 점진적으로 늘어나지게 되면서 5월 말에는 모든 행들이 NULL 값으로 반환이 되면서 분석이 어려워지게 되었다.

 

Root Cause

AWS ELB 로그 포맷이 별도의 공지도 없이 변경이 되어졌다.

24.05.17 이후 로그에 TID라는 항목이 추가되어진 부분이 확인되어졌다.

2024.05.17 이전
arn:aws:elasticloadbalancing:ap-northeast-1:344571588965:targetgroup/xxxxxx-xxxx-xxxx/xxxxxxxxxxxxxxx "Root=1-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" "xxxxxxxxxxxxx" "arn:aws:acm:ap-northeast-1:344571588965:certificate/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" 0 2024-05-21T23:54:59.985000Z "forward" "-" "-" "10.xxx.xxx.xx:80" "200" "-" "-"

2024.05.17 이후
arn:aws:elasticloadbalancing:ap-northeast-1:344571588965:targetgroup/xxxxxx-xxxx-xxxx/xxxxxxxxxxxxxxx "Root=1-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" "xxxxxxxxxxxxx" "arn:aws:acm:ap-northeast-1:344571588965:certificate/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" 0 2024-05-21T23:54:59.985000Z "forward" "-" "-" "10.xxx.xxx.xx:80" "200" "-" "-" TID_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

Solution

분석에 있어 기 생성한 Table 대상으로 스키마를 변경해야 한다.

필자는 아래 공식문서 링크를 참조하여 생성을 진행하였다. 

(1) https://repost.aws/knowledge-center/athena-analyze-access-logs

Use Athena to analyze Load Balancer logs

 

현재 AWS 영어 공식 문서에는 변경된 로그 포맷에 대한 생성 쿼리가 업데이트가 되어져 있으나 한국어 버전은 아직 업데이트 되어져 있지 않다. (24.06.09 기준)

참조. https://docs.aws.amazon.com/athena/latest/ug/application-load-balancer-logs.html

 

이전 로그 포맷,  변경된 로그 포맷을 모두 호환할 수 있는 테이블 생성 쿼리는 아래와 같다. (1) tid 속성값 추가  (2) Regex 패턴에 대한 수정이 되어져야 한다.

 

Athena 테이블 생성 쿼리

CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs (
   type string,
    time string,
    elb string,
    client_ip string,
    client_port int,
    target_ip string,
    target_port int,
    request_processing_time double,
    target_processing_time double,
    response_processing_time double,
    elb_status_code int,
    target_status_code string,
    received_bytes bigint,
    sent_bytes bigint,
    request_verb string,
    request_url string,
    request_proto string,
    user_agent string,
    ssl_cipher string,
    ssl_protocol string,
    target_group_arn string,
    trace_id string,
    domain_name string,
    chosen_cert_arn string,
    matched_rule_priority string,
    request_creation_time string,
    actions_executed string,
    redirect_url string,
    lambda_error_reason string,
    target_port_list string,
    target_status_code_list string,
    classification string,
    classification_reason string
    tid string
)
PARTITIONED BY (day STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'serialization.format' = '1',
	'input.regex' = '([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) (.*) (- |[^ ]*)\" \"([^\"]*)\" ([A-Z0-9-_]+) ([A-Za-z0-9.-]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^\"]*)\" ([-.0-9]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^ ]*)\" \"([^\s]+?)\" \"([^\s]+)\" \"([^ ]*)\" \"([^ ]*)\" ?([^ ]*)?( .*)?')
)
LOCATION 's3://<your-alb-logs-directory>/AWSLogs/<ACCOUNT-ID>/elasticloadbalancing/<REGION>/'
TBLPROPERTIES (
    "projection.enabled" = "true",
    "projection.day.type" = "date",
    "projection.day.range" = "2022/01/01,NOW",
    "projection.day.format" = "yyyy/MM/dd",
    "projection.day.interval" = "1",
    "projection.day.interval.unit" = "DAYS",
    "storage.location.template" = "s3://<your-alb-logs-directory>/AWSLogs/<ACCOUNT-ID>/elasticloadbalancing/<REGION>/${day}"
)