S3にあるALBログの調査はAthenaよりDuckDBのほうが簡単

AWSのALB(Application Load Balancer)のログはS3に置かれるが、この中身をサクッと調べたいとき、Athenaを使う方法が標準的で、下記で案内されているようにパーティション射影(Partition Projection)でテーブルを作ってAthenaからクエリする。

パーティション射影を使用して Athena で ALB アクセスログ用テーブルを作成する - Amazon Athena

私も従来はその方法を使っていたが、Athenaはブラウザから使うと動作がもっさりしているし、決まったクエリを1回きり実行して結果を取得したいだけのときならまだしも、探索的にクエリを何発も実行したいときには使い勝手が悪い。

最近他のプロジェクトでDuckDBを使うようになって、使い勝手の良さに感動していたが、DuckDBはALBのログを探索的に調べたいときにもめっちゃ使えると思った。

DuckDBのインストールは公式 あたりを参照。S3のファイルをクエリする準備としてはAWS Extension – DuckDBやS3 API Support – DuckDBを参照。 端的にいうと、シェルでS3にアクセスできるクレデンシャルの環境変数がロードされている状態でDuckDBを起動して、

INSTALL aws;
LOAD aws;
INSTALL httpfs;
LOAD httpfs;
CREATE SECRET (
    TYPE S3,
    PROVIDER CREDENTIAL_CHAIN
);

これでロードしてあるクレデンシャルがアクセス権限を持つS3のファイルに対してクエリできるようになる。 例えば2024年11月のログをロードするなら下記のようにする。Blobのパターンを変えれば任意の期間のデータをロードできる。 この定義にあたってはパーティション射影を使用して Athena で ALB アクセスログ用テーブルを作成する - Amazon Athena にある定義を参考にした。

CREATE TABLE alb_log_202411 AS
SELECT *
FROM read_csv(
    's3://[YOUR_S3_BUCKET_NAME]/AWSLogs/[YOUR_ACCOUNT_ID]/elasticloadbalancing/[YOUR_REGION]/2024/11/**/*.log.gz',
    columns={
        'type': 'VARCHAR',
        'timestamp': 'TIMESTAMP',
        'elb': 'VARCHAR',
        'client_ip_port': 'VARCHAR',
        'target_ip_port': 'VARCHAR',
        'request_processing_time': 'DOUBLE',
        'target_processing_time': 'DOUBLE',
        'response_processing_time': 'DOUBLE',
        'elb_status_code': 'INTEGER',
        'target_status_code': 'VARCHAR',
        'received_bytes': 'BIGINT',
        'sent_bytes': 'BIGINT',
        'request': 'VARCHAR',
        'user_agent': 'VARCHAR',
        'ssl_cipher': 'VARCHAR',
        'ssl_protocol': 'VARCHAR',
        'target_group_arn': 'VARCHAR',
        'trace_id': 'VARCHAR',
        'domain_name': 'VARCHAR',
        'chosen_cert_arn': 'VARCHAR',
        'matched_rule_priority': 'VARCHAR',
        'request_creation_time': 'TIMESTAMP',
        'actions_executed': 'VARCHAR',
        'redirect_url': 'VARCHAR',
        'error_reason': 'VARCHAR',
        'target_port_list': 'VARCHAR',
        'target_status_code_list': 'VARCHAR',
        'classification': 'VARCHAR',
        'classification_reason': 'VARCHAR',
        'conn_trace_id': 'VARCHAR'
    },
    delim=' ',
    quote='"',
    escape='"',
    header=False,
    auto_detect=False
);

S3の対象ファイル数が多い場合テーブルができるまでにそこそこ時間はかかるが、いったんローカルにロードされればあとは速い。

• テーブルの存在を確認

D show tables;
┌────────────────┐
│      name      │
│    varchar     │
├────────────────┤
│ alb_log_202411 │
└────────────────┘

• スキーマを確認

D describe alb_log_202411;
┌──────────────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│       column_name        │ column_type │  null   │   key   │ default │  extra  │
│         varchar          │   varchar   │ varchar │ varchar │ varchar │ varchar │
├──────────────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ type                     │ VARCHAR     │ YES     │         │         │         │
│ timestamp                │ TIMESTAMP   │ YES     │         │         │         │
│ elb                      │ VARCHAR     │ YES     │         │         │         │
│ client_ip_port           │ VARCHAR     │ YES     │         │         │         │
│ target_ip_port           │ VARCHAR     │ YES     │         │         │         │
│ request_processing_time  │ DOUBLE      │ YES     │         │         │         │
│ target_processing_time   │ DOUBLE      │ YES     │         │         │         │
│ response_processing_time │ DOUBLE      │ YES     │         │         │         │
│ elb_status_code          │ INTEGER     │ YES     │         │         │         │
│ target_status_code       │ VARCHAR     │ YES     │         │         │         │
│ received_bytes           │ BIGINT      │ YES     │         │         │         │
│ sent_bytes               │ BIGINT      │ YES     │         │         │         │
│ request                  │ VARCHAR     │ YES     │         │         │         │
│ user_agent               │ VARCHAR     │ YES     │         │         │         │
│ ssl_cipher               │ VARCHAR     │ YES     │         │         │         │
│ ssl_protocol             │ VARCHAR     │ YES     │         │         │         │
│ target_group_arn         │ VARCHAR     │ YES     │         │         │         │
│ trace_id                 │ VARCHAR     │ YES     │         │         │         │
│ domain_name              │ VARCHAR     │ YES     │         │         │         │
│ chosen_cert_arn          │ VARCHAR     │ YES     │         │         │         │
│ matched_rule_priority    │ VARCHAR     │ YES     │         │         │         │
│ request_creation_time    │ TIMESTAMP   │ YES     │         │         │         │
│ actions_executed         │ VARCHAR     │ YES     │         │         │         │
│ redirect_url             │ VARCHAR     │ YES     │         │         │         │
│ error_reason             │ VARCHAR     │ YES     │         │         │         │
│ target_port_list         │ VARCHAR     │ YES     │         │         │         │
│ target_status_code_list  │ VARCHAR     │ YES     │         │         │         │
│ classification           │ VARCHAR     │ YES     │         │         │         │
│ classification_reason    │ VARCHAR     │ YES     │         │         │         │
│ conn_trace_id            │ VARCHAR     │ YES     │         │         │         │
├──────────────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤
│ 30 rows                                                              6 columns │
└────────────────────────────────────────────────────────────────────────────────┘

あとはいろいろクエリしてお好きなように。

• select * from alb_log_202411 where elb_status_code != 200 LIMIT 1;
• select timestamp, request, elb_status_code, target_status_code, domain_name from alb_log_202411 where elb_status_code != 200 LIMIT 100
• select timestamp, request, elb_status_code, target_status_code, domain_name from alb_log_202411 where elb_status_code != 200 and domain_name != 'foobar.com' LIMIT 100

みたいに探索的に調べるのが高速にやれるのが最高。

※補足

30カラムもあるので、デフォルトではコマンドラインで全部のカラムを表示できない。 なので、SELECT句でカラムを絞るか、　.mode line とか .mode box とかで全カラム表示できる。 デフォルトに戻すには .mode duckbox。 どういう選択肢があるかはOutput Formats – DuckDB見ると分かる。

※補足2

ググったら似たことしている記事あったのでリンクしとく

Analysing AWS Application Load Balancer Logs with DuckDB: Unleashing Performance Insights