🦔

ALB のアクセスログをRedshiftで分析してみる

2022/07/26に公開

AWS

redshift

tech

Amazon Redshift を使って大量のアクセスログを解析するために現時点のALBアクセスログのフォーマットより環境を作った方法を書き記してみます。

ALB のアクセスログの出力

ALB のアクセスログはS3バケットへ出力することができるのでまずは設定。

一時テーブル作成

アクセスログのフォーマットに従い、Redshift へ S3 のファイルをロードするための一時テーブルを作成。解析用のテーブルは一時テーブルのデータを加工しながらinsert...select...を後で行います。

一時テーブル作成

DROP TABLE tmp_elb_access_log;
CREATE TABLE IF NOT EXISTS tmp_elb_access_log
(
type VARCHAR (20)
,time VARCHAR (64)
,elb VARCHAR (64)
,client_port VARCHAR (32)
,target_port VARCHAR (32)
,request_processing_time FLOAT
,target_processing_time FLOAT
,response_processing_time FLOAT
,elb_status_code INT
,target_status_code INT
,received_bytes BIGINT
,sent_bytes BIGINT
,request VARCHAR (2048)
,user_agent VARCHAR (1024)
,ssl_cipher VARCHAR (64)
,ssl_protocol VARCHAR (64)
,target_group_arn VARCHAR (1024)
,trace_id VARCHAR (64)
,domain_name VARCHAR (64)
,chosen_cert_arn VARCHAR (1024)
,matched_rule_priority INT
,request_creation_time VARCHAR (32)
,actions_executed VARCHAR (64)
,redirect_url VARCHAR (1024)
,error_reason VARCHAR (64)
,target_port_list VARCHAR (64)
,target_status_code_list VARCHAR (64)
,classification VARCHAR (64)
,classification_reason VARCHAR (64)
)
;

データロード

S3 に格納されたALBのアクセスログからRedshift へデータをロードします。

LOAD

COPY tmp_elb_access_log FROM
's3://<S3 Bucket>/elb/alb/AWSLogs/<AccountId>/elasticloadbalancing/ap-northeast-1/2021/12/11/'
iam_role '<IAM Role Arn>'
delimiter ' ' gzip
REMOVEQUOTES NULL AS '-' MAXERROR AS 10000;

gzip
- ALB のアクセスログはgz形式のため、gzip識別子をつける
REMOVEQUOTES
- 一部 "" 囲みのフィールドがあるためフィールドを排除する
NULL AS '-'
- 値が - のフィールドは一旦 NULL としたかったのでこちらの設定
MAXERROR AS xxxx
- 解析用につき、ある程度のエラーは許容したいのでこちらも設定。
ロード中にエラーが起きた場合は、システムテーブル STL_LOAD_ERRORS を確認。

解析テーブル作成

ALB のアクセスログ解析用のテーブルを作成。

解析テーブル作成

DROP TABLE elb_access_log;
CREATE TABLE IF NOT EXISTS elb_access_log
(
type VARCHAR (20)
,time TIMESTAMP
,time_jst TIMESTAMP
,elb VARCHAR (64)
,client_ip VARCHAR (32)
,client_port INT
,target_ip VARCHAR (32)
,target_port INT
,request_processing_time FLOAT
,target_processing_time FLOAT
,response_processing_time FLOAT
,elb_status_code INT
,target_status_code INT
,received_bytes BIGINT
,sent_bytes BIGINT
,request VARCHAR (2048)
,user_agent VARCHAR (1024)
,ssl_cipher VARCHAR (64)
,ssl_protocol VARCHAR (64)
,target_group_arn VARCHAR (1024)
,trace_id VARCHAR (64)
,domain_name VARCHAR (64)
,chosen_cert_arn VARCHAR (1024)
,matched_rule_priority INT
,request_creation_time TIMESTAMP
,actions_executed VARCHAR (64)
,redirect_url VARCHAR (1024)
,error_reason VARCHAR (64)
,target_port_list VARCHAR (64)
,target_status_code_list VARCHAR (64)
,classification VARCHAR (64)
,classification_reason VARCHAR (64)
)
DISTKEY (time)
SORTKEY (time)
;

データ挿入

Redshift では upsert や merge 文は利用できない。そのため、解析用テーブルにあるが一時テーブルにもあるレコードの重複排除のために、事前に解析用テーブルの該当レコードを削除しておく。結果的に一時テーブルのデータで解析用テーブルに対してupsertを行うような結果とできる。

重複レコード排除

delete from elb_access_log using tmp_elb_access_log 
where elb_access_log.trace_id=tmp_elb_access_log.trace_id;

解析用テーブルに対して、先程の一時テーブルのデータを挿入。一部カラムに関してSQLで加工しながら挿入しています。

データ挿入

INSERT INTO elb_access_log
(SELECT
type
,(time::TIMESTAMP)
,CONVERT_TIMEZONE('JST',time::timestamp)
,elb
,SPLIT_PART(client_port, ':', 1)
,(SPLIT_PART(client_port, ':', 2)::INT)
,SPLIT_PART(target_port, ':', 1)
,(SPLIT_PART(target_port, ':', 2)::INT)
,request_processing_time
,target_processing_time
,response_processing_time
,elb_status_code
,target_status_code
,received_bytes
,sent_bytes
,request
,user_agent
,ssl_cipher
,ssl_protocol
,target_group_arn
,trace_id
,domain_name
,chosen_cert_arn
,matched_rule_priority
,(request_creation_time::TIMESTAMP)
,actions_executed
,redirect_url
,error_reason
,target_port_list
,target_status_code_list
,classification
,classification_reason
from tmp_elb_access_log
)
;

まとめ

簡易的な手順で恐縮ですが、これで一通り解析できる環境は整いました。ALBのアクセスログは日単位のプレフィックスでS3上に出力されてくれるので、日次で取り込み処理というような形が取りやすいかもしれませんね。

ALB のアクセスログの出力

一時テーブル作成

データロード

解析テーブル作成

データ挿入

まとめ

Discussion