[Athena] ALB Access Log 를 이용한 국가별 트래픽 비중 확인하기

개요

Request URL에 locale 항목에 어느 국가에서 요청을 하였는지에 대한 정보를 담도록 설계한다. 이를 바탕으로 Athena를 활용하여 국가별 유입 트래픽 분석을 진행해보고자 한다.
 
200 응답대상 국가별 트래픽 비중과, 400 이상 응답대상 국가별 트래픽 비중을 확인해보는 것을 목표로 한다.
 

본문

사전 준비사항으로 ELB Log 분석을 위한 테이블은 생성진행해야 한다. 만약, 아직 구성이 안되어 있다면 아래 링크를 통해 테이블 생성을 진행하자.
https://repost.aws/knowledge-center/athena-analyze-access-logs

Use Athena to analyze Load Balancer logs

 

고려사항 

(1) 쿼리에 모든 locale에 대한 정보를 포함할 수 없는 점
글로벌 서비스를 진행하는 경우 locale에 포함되는 국가정보들을 모두 쿼리문에 포함할 수는 없다. 이러한 고려사항에 대하여 정규식을 이용하여 이를 해결해야 하였다. 
 
(2) RequestURL에 locale 정보가 없는 경우
COALESCE 함수를 통해 해결할 수 있다.  COALESCE 함수는 데이터 형식 우선 순위가 가장 높은 식의 데이터 형식을 반환한다.
 
SELECT 절에 다음과 같은 내용을 포함하였다.
 
regexp_extract 구문에서  

• 성공적으로 두 글자 지역 코드를 추출하면, COALESCE는 해당 값을 반환한다.
• NULL 값을 반환하면(즉, request_url에서 일치하는 패턴을 찾지 못한 경우), COALESCE는 'Unknown'을 반환한다.

COALESCE(regexp_extract(request_url, 'locale=([A-Z]{2})', 1), 'Unknown') AS region

 

쿼리문

필자는 쿼리문은 다음과 같이 작성하였다.
 
200 응답대상 국가별 트래픽 비중

SELECT 
    COALESCE(regexp_extract(request_url, 'locale=([A-Z]{2})', 1), 'Unknown') AS region,
    COUNT(*) AS "total_count"
FROM "<<Database Name>>"."<<Table Name>>"
WHERE
    request_url LIKE '%<<설계 대상 URL 패턴>>%'
    AND elb_status_code = 200
    AND <<Time From>> AND <<Time To>>
GROUP BY 
    COALESCE(regexp_extract(request_url, 'locale=([A-Z]{2})', 1), 'Unknown')
ORDER BY 
    "total_count" DESC;

 
400 이상 응답대상 국가별 트래픽 비중

SELECT 
    COALESCE(regexp_extract(request_url, 'locale=([A-Z]{2})', 1), 'Unknown') AS region,
    COUNT(*) AS "total_count"
FROM "<<database name>>"."<<table name>>"
WHERE
    elb_status_code >= 400
    AND time BETWEEN date_format($__timeFrom(), '%Y-%m-%dT%H:%i:%s.000') AND date_format($__timeTo(), '%Y-%m-%dT%H:%i:%s.000')
GROUP BY 
    COALESCE(regexp_extract(request_url, 'locale=([A-Z]{2})', 1), 'Unknown')
ORDER BY 
    "total_count" DESC;

 
Grafana 대시보드 응용하면 아래와 같이 시각화 할 수 있다.