AthenaでELBログ解析

AthenaでELBログ解析する必要があったので方法をメモしておきます。

まず前提条件としてELBでアクセスログをS3に保存するよう設定しておく必要があリます。
また、Athenaで実効するクエリ結果をS3に保存する必要があるようなので、バケットを１つ用意しておきます。

テーブル作成

次に、下記クエリを実行してテーブルを作成します
<S3_Backet_URL> には アクセスログが保存されているS3のURLを指定しますが
特定の期間だけでいいのであれば、年月のフォルダまでURLで指定します。
s3://<S3バケット>/AWSLogs/<アカウントID>/elasticloadbalancing/ap-northeast-1/2021/07/
全ログを対象とする場合はリージョンまで指定します。
s3://<S3バケット>/AWSLogs/<アカウントID>/elasticloadbalancing/ap-northeast-1/

CREATE EXTERNAL TABLE IF NOT EXISTS elb_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,
            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_Backet_URL>';

DBを指定しないと、defaultデータベース内に作成されました。
あまり広範囲のログを対象にするとDB容量が多くなって料金が高くなります。

SELECT実行

特定時間帯の分ごとのページ別アクセス数を取得してみました。

SELECT
  datetime, 
  request_url, 
  count(*) count
FROM
(
  SELECT 
    format_datetime(parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') AT TIME ZONE 'Asia/Tokyo', 'YYYY-MM-dd HH:mm') datetime,
    request_url
  FROM elb_logs
  WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') 
    BETWEEN parse_datetime('2021-07-30-01:40:00','yyyy-MM-dd-HH:mm:ss') 
    AND parse_datetime('2021-07-30-01:46:00','yyyy-MM-dd-HH:mm:ss') 
)
GROUP BY datetime, request_url
ORDER BY datetime;