AWS AthenaでALBのアクセスログを集計する

　============ 2018-10-05 に追記 ===============
今回検証で使ったデータ量は100MBもなかったので良いですが
Athenaはスキャンされたデータ量で料金がかかります

データ量が増えてきた場合はパーティション追加したりクエリでwhereをかけるなどする必要があるかと思います
docs.aws.amazon.com

ブックマークでコメントいただいた方ありがとうございました

　================================================

f:id:hatappi1225:20181004221118p:plain

↑最近のSearchConsoleでの個人サービスのページダウンロード時間
9月過ぎてからダウンロード時間が伸びている

ページダウンロード時間伸びるとクローラーの回遊率が〜みたいな話もあるけど、そもそもユーザーにコンテンツを届ける時間がのびてる！！！
なんとなくあそこかなーと思ったけどisuconの記憶が頭をよぎる

最近個人で作ってるサービスがサーチコンソールで見るとダウンロード時間が2倍になってる
たぶんあそこだろうなと思ったけど「推測するな、計測せよ」が頭をよぎってログ収集してる
— はたっぴ (@hatappi) 2018年10月3日

ということでまずは計測しよう

計測

今つくってるサービスはAWS上で構築しているので、LoadBalancerにALBを使っているので、まずアクセスログをS3に出力する設定を行います
有効にする方法などは公式のドキュメントを参照します↓

docs.aws.amazon.com

数日放置します

集計

ALBのアクセスログはS3に s3://log-bucket/access-log/AWSLogs/111111111/elasticloadbalancing/ap-northeast-1/[年]/[月]/[日] というディレクトリで格納するので、手元に落として集計するのもちょっと面倒
ということでAthenaを使って集計しました 💪
人生初Athena 🎉

aws.amazon.com

そしてALBのログ集計の仕方はドキュメントにのってるのでそれに従うだけ！
かんたーん

docs.aws.amazon.com

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 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,
    new_field 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]*) ([^ ]*) \"([^\"]*)\"($| \"[^ ]*\")(.*)')
    LOCATION 's3://your-alb-logs-directory/AWSLogs/<ACCOUNT-ID>/elasticloadbalancing/region';

LOCATIONの部分だけ自分のS3のパスにあわせてあげて後は作成するだけ
それぞれのカラムはどんな値が入るか気になる時は↓に記載してある

Application Load Balancer のアクセスログ - Elastic Load Balancing

実行

とりあえず時間のかかっているURLを出してみる

SELECT request_url, target_processing_time
FROM alb_logs
ORDER BY target_processing_time DESC
LIMIT 50;

UserAgentごとにまとめてリクエスト回数の多いものから並べる

SELECT user_agent, COUNT(*) as cnt
FROM alb_logs
GROUP BY user_agent
ORDER BY cnt DESC
LIMIT 50;

ロードバランサーからの応答ステータスコードごとに集計したリクエスト回数

SELECT elb_status_code, COUNT(*) as cnt
FROM alb_logs
GROUP BY elb_status_code
ORDER BY cnt DESC;

最後に

Athena便利〜