【ALB】Athenaで日本時間でログを検索する

ALBのログを解析したい！

ALBのログを見るかんたんな方法としてAthenaの利用があります。基本はドキュメント通りなのですが、クエリにちょっとした工夫が必要だったのでメモしておきます。

テーブル定義

基本は上の公式ドキュメントを参照すればいいのですが、参考にするのは パーティション射影を使用した Athena での ALB ログ用のテーブルの作成 の方のCREATE TABLEにしましょう。パーティションを利用するとクエリのスキャン量が減り、安く・早くなります。
一応下に該当のSQLを貼っておきます。 PARTITIONED BY(day STRING) で day（日付）でパーティションがはられているのがわかると思います。

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
            )
            PARTITIONED BY
            (
             day 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>/'
            TBLPROPERTIES
            (
             "projection.enabled" = "true",
             "projection.day.type" = "date",
             "projection.day.range" = "2022/01/01,NOW",
             "projection.day.format" = "yyyy/MM/dd",
             "projection.day.interval" = "1",
             "projection.day.interval.unit" = "DAYS",
             "storage.location.template" = "s3://your-alb-logs-directory/AWSLogs/<ACCOUNT-ID>/elasticloadbalancing/<REGION>/${day}"
            )

実際のクエリ

select
    *
from
    alb_logs
where
    day = '2022/10/26'

実際にクエリ流すときは可能であればdayをwhereで指定しましょう。これだけでかなりクエリ量が削減できます。

日本時間（JST）で検索する

ログを詳細に見ていくときは時間も指定する場合があると思います。
このときALBのログはUTC時間なのでそのまま例えば障害があった（日本）時間で検索をかけるとうまくいきません。そういうときは日本時間に直して検索します。

select
    *
from
    alb_logs
where
    day = '2022/10/26'
AND parse_datetime(time, 'yyyy-MM-dd' 'T' 'HH:mm:ss.SSSSSS' 'Z') BETWEEN timestamp '2022-10-26 15:35 Asia/Tokyo' AND timestamp '2022-10-26 15:59 Asia/Tokyo'

parse_datetime(time, 'yyyy-MM-dd' 'T' 'HH:mm:ss.SSSSSS' 'Z')で文字列型のtimeをtimestamp似直します。検索かけるときはBETWEEN timestamp '2022-10-26 15:35 Asia/Tokyo' AND timestamp '2022-10-26 15:59 Asia/Tokyo' と日本時間と明記して検索をかけると思った検索結果が得られます。