今さらだけど、AWS ELBのログ解析にAthenaを用いたら、簡単で便利だった

#きっかけ

トラブルの原因を調べるなどの理由で、ELBのアクセスログを解析する必要に迫られることは多いと思います。
私は、当初、ログを格納しているS3から、ファイルをLinuxサーバーにダウンロードしてgrepやawkで集計したり、Windows PCに移してExcelのピボットで集計したりしていました。
ただ、ログファイルは、ノードや時間ごとに細かく分かれており、意外と手間がかかります。

AWSには、S3上のデータを、S3に格納したままSQLで操作できるAthenaというサービスがあります。このAthenaを使って、ログ解析を試みたところ、非常に簡単で便利でした。
SQLクエリーを工夫することで、毎時ごとのアクセス数なども手軽に集計できます。

##ALBとCLB

Application Load BalancerとClassic Load Balancerとでは、アクセスログに記録される項目や、項目名が異なります。ここでは、Application Load Balancerで説明しますが、ここで紹介するSQLクエリーは、項目名を変更すれば、Classic Load Balancerでも利用できます。

#テーブル定義

ELBのアクセスログは、スペース区切りの単なるテキストファイルです。まずは、SQLで扱うために、テーブルの構造を定義しましょう。
具体的には、CREATE EXTERNAL TABLE文を使います。

CREATE EXTERNAL TABLE IF NOT EXISTS <table_name> (
    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://<bucket_name>/AWSLogs/<account_id>/elasticloadbalancing/<region>/yyyy/mm/dd/';

最初のCREATE EXTERNAL TABLE文と、最後のLOCATION文は、お使いの環境に合わせて修正してください（それぞれテーブル名とログのリソースURIを指定します）。

なお、リソースURIですが、s3://<bucket_name>/AWSLogs/<account_id>/elasticloadbalancing/<region>/であれば、そのELBの全ログが対象になり、日付を加えてs3://<bucket_name>/AWSLogs/<account_id>/elasticloadbalancing/<region>/yyyy/mm/dd/とすると、その日付（UTC）に保管されたログだけに限定されます。Athenaはスキャンするデータの容量で課金されるため、私は、日付を指定し、スキャン対象を限定して使っています。

#SQLクエリーの実行

準備ができたら、SQLクエリーを実行しましょう。

▼指定時間内で、アクセスが多いIPのトップ100

SELECT client_ip,
       count(*) AS request_count
  FROM <table_name>
 WHERE time >= '2018-10-01T03:00'
   AND time <  '2018-10-01T09:00'
 GROUP BY client_ip
 ORDER BY request_count DESC limit 100;

▼ターゲット処理時間が1秒以上のURL（指定時間内／処理時間が大きい順）

SELECT time,
       client_ip,
       request_url,
       target_processing_time
  FROM <table_name>
 WHERE time >= '2018-10-01T03:00'
   AND time <  '2018-10-01T09:00'
   AND target_processing_time >= 1
 ORDER BY target_processing_time DESC;

##毎時ごとの集計

ネットで調べて簡単には見つからなかったのが、この「毎時ごと」の集計でした。
Athenaの場合、substr関数が利用できるため、timeフィールドのyyyy/mm/dd:hhを切り出してGROUP BY句を使っています。

▼毎時ごとのターゲット処理時間とアクセス数（タイムアウト分は除外）

SELECT substr(time,1,13) AS every_hour,
       avg(target_processing_time) AS average_time,
       count(*) AS request_count
  FROM <table_name>
 WHERE target_processing_time > 0
 GROUP BY substr(time,1,13)
 ORDER BY every_hour;

▼毎時ごとのタイムアウト数

SELECT substr(time,1,13) AS every_hour,
       count(*) AS request_count
  FROM <table_name>
 WHERE target_processing_time < 0
 GROUP BY substr(time,1,13)
 ORDER BY every_hour;

##ダウンロード用CSVファイル

どうしても手元にログファイルが必要になったら、下記のSQLクエリーで1つのCSVファイルとしてダウンロードすることもできます。

▼指定時間内の全項目

SELECT *
 FROM <table_name>
WHERE time >= '2018-10-01T03:00'
  AND time <  '2018-10-01T09:00'
ORDER BY time;

#まとめ

Athenaを使うことで、さまざまな集計が手軽に行えます。
SQLクエリー次第では、さらに複雑な集計も可能です。
今後、役立つクエリーができたら、ここで紹介したいと思っています。

#参考

▼Amazon Athena ユーザーガイド - Application Load Balancer ログのクエリ
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/application-load-balancer-logs.html