ALB Access Log 분석하기!

시작

업무를 하는도중 네트워크 이슈로 각종 로그를 분석하게 되었다. 일단 VPC FlowLog는 Wireshark로 충분했다. 물론 마음같아선 ETL하여 쿼리를 하고 싶었지만 급하니 Wireshark에 밀어 넣고 필터로 분석하여 이상한 트래픽을 찾아서 처리했다.

문제는 ALB의 로그였는데 요놈이 조금 골치 아프다. Wireshark로 밀어 넣지도 못하고 S3 Select를 사용하면 내가 필요한 쿼리를 할 수 없다. 그렇다고 CSV로 뽑아서 Xlsx로 만들어서 처리하긴 좀 구식같고...

 

그래서 개인적으로 좋아하는 Athena와 Glue를 사용하기로 했다.

요롷게 간단하게 만들어 보기로!!

 

일단 Ahtena 콘솔로 들어가자. 아마 처음 진행하면 죽어도 쿼리가 실행되지 않을거다. 일단 쿼리 결과를 저장하는 S3버킷을 지정해 주어야 한다.

이렇게 만들어 주었다면 테이블을 생성해보자. 아마 Default Data Store가 있을것이다. 나는 이것을 그냥 쓸거다.

Data Store가 뭐냐하면 데이터를 지속적으로 저장하기 위한 저장소 이다. S3와 무엇이 다른가 궁금할텐데 S3는 일단 비 정형화 되어 있는 데이터 이다. Ahtena는 정형화 되어 있는 데이터를 관계식으로 쿼리한다. 따라서 ELT을 해 주던가 Json, Parquet등의 포멧을 스키마로 정의 해 주어야 한다.

 

Default Data Store인 AwsDataCatalog는 Glue를 사용한다.

참고로 위의 저장소들을 사용해서 Data Store를 정의할 수 있다. CloudWatch Logs로도 되니까 나중에 VPC FlowLog도 이거로 카타로그화 해서 분석해봐지...

 

일단 위처럼 데이터스토어와 쿼리 결과를 저장하는 버킷이 데이터베이스를 만들수 있다. 데이터베이스는 Glue Console 또는 Athena 쿼리를 통해 생성이 가능하다.

나는 그냥 Glue에서 데이터베이스를 생성했다.

생성이 완료 되면 이처럼 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
            )
            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>/';

ALB AccessLog의 포맷에 맞게 컬럼을 지정해주고 테이블을 생성해준다. 근데 중요한건 이렇게 만들어진 테이블은 파티셔닝이 되어 있지 않다는 점이다. 쿼리하면 풀스캔이 되고 스캔한 데이터에 알맞게 돈이 매우 많이 과금된다. 참고로 저거 만들고 실수로 쿼리 실행했는데 60테라를 스캔해버려서...다음달에 300달러가 추가 과금될 예정이다.

 

그래서 매우 중요한 파티션 테이블을 만들어보자

 

CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs_partition_projection (
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 string,
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
)
PARTITIONED BY(year int, month int, day int)
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://my_log_bucket/AWSLogs/1111222233334444/elasticloadbalancing/us-east-1/'
TBLPROPERTIES (
'has_encrypted_data'='false',
'projection.day.digits'='2',
'projection.day.range'='01,31',
'projection.day.type'='integer',
'projection.enabled'='true',
'projection.month.digits'='2',
'projection.month.range'='01,12',
'projection.month.type'='integer',
'projection.year.digits'='4',
'projection.year.range'='2020,2021',
'projection.year.type'='integer',
"storage.location.template" = "s3://my_log_bucket/AWSLogs/1111222233334444/elasticloadbalancing/us-east-1/${year}/${month}/${day}"
)

위처럼 프로젝션으로 파티셔닝 할 수 있고

 

CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs_partitioned (
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 string,
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
)
PARTITIONED BY(year int, month int, day int)
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://my_log_bucket/AWSLogs/1111222233334444/elasticloadbalancing/us-east-1/'

위처럼 아에 파티션 테이블 설정후

ALTER TABLE alb_logs_partitioned ADD
  PARTITION (year = '2020', month ='05', day= '21')
  LOCATION's3://my_log_bucket/AWSLogs/1111222233334444/elasticloadbalancing/us-east-1/2020/05/21/'

이런식으로 해당 파티션을 주입하셔 사용도 가능하다.

이렇게 해서 총 3개의 테이블이 만들어졌다.

그리고 아래처럼 쿼리하면 잘 나온다

파티션 한 쿼리와 하지 않은 쿼리의 속도 차이.

 

같은 규모의 데이터를 스캔하여 처리하는데 시간이 두배정도 차이난다. 이제 시각화로 quicksight를 붙이고 싶지만 이건 좀 비싸니까...고민을 좀 해보고 문제가 생겼을때 그때그때 쿼리로 해결해야겠다.