DuckDB で S3 のサーバーアクセスログを集計できた

Amazon S3 にはサーバーアクセスログというものがあって、バケットへのリクエストの詳細が記録されています。ログは指定した S3 のバケットの、指定したプレフィックス以下に出力されます。

https://docs.aws.amazon.com/ja_jp/AmazonS3/latest/userguide/LogFormat.html

このログから、どのアクセス元（リクエスタ）が、どれだけリクエストしているのかを知りたかったので、DuckDB を使って集計してみました……と、もっともらしく言ってみましたが、とにかく DuckDB の利用実績を積みたかったという気持ちが割と強いです。

DuckDB について簡単に説明しておくと、色々なところの様々な形式のファイルに SQL っぽくアクセスできて、しかも高速で処理できるというすごいやつです。全てこれで賄えるんじゃないかって思いながら、色々な集計作業を置き換えるのが最近の楽しみです。

結論のクエリ

いきなりですが、結論のクエリを示します。このクエリは 2024-10-25 のアクセスログからリクエスタごとの件数を出力します。

INSTALL httpfs;
LOAD httpfs;

CREATE SECRET secret (
    TYPE S3,
    PROVIDER CREDENTIAL_CHAIN
);

SELECT column05, COUNT(1)
FROM read_csv('s3://bucketname/path/to/2024-10-25*', delim=' ', header=false, types={'column13': 'VARCHAR'})
GROUP BY column05;

ここからは細かいところを説明していきます。

Amazon S3 のファイルを DuckDB で読み込む

これはもう公式ドキュメントが丁寧なので、特に説明することがありません。httpfs を読み込んでシークレットを作るだけです。

https://duckdb.org/docs/extensions/httpfs/s3api

これで読めそうではあるのですが、次のクエリを実行しても Table with name ... does not exist! で読めません。

SELECT * FROM 's3://path/to/log';

read_csv を使うと、一応出るようになりますが、ログは CSV ではないので、巨大な1つの列の CSV として解釈されます。

SELECT * FROM read_csv('s3://path/to/log');

さてどうしましょう。次に続きます。

delim=' ' でスペース区切りとして読む

S3 のサーバーアクセスログは、次のような形で出力されます。

79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be amzn-s3-demo-bucket1 [06/Feb/2019:00:00:38 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be 3E57427F3EXAMPLE REST.GET.VERSIONING - "GET /amzn-s3-demo-bucket1?versioning HTTP/1.1" 200 - 113 - 7 - "-" "S3Console/0.4" - s9lzHYrFp76ZVxRcpX9+5cjAnEH2ROuNkd2BHfIa6UkFVdtjf5mKR3/eTPFvsiP/XV/VLi31234= SigV4 ECDHE-RSA-AES128-GCM-SHA256 AuthHeader amzn-s3-demo-bucket1.s3.us-west-1.amazonaws.com TLSV1.2 arn:aws:s3:us-west-1:123456789012:accesspoint/example-AP Yes

これを read_csv か他の手段でなんとかして読み込めないか？

公式ドキュメントの File Formats を見ても、特にそれらしい手段は見当たりません。

困ったなと思いながら CSV Import のリファレンスを読んでいると、delim というオプションを発見！ delim=' ' とすれば読めるんじゃないかと試したところ、すんなり読めました。

この時点でこうです。

SELECT * FROM read_csv('s3://path/to/log', delim=' ');

型違いの壁を types で乗り越える

1ファイルは読めたので、globbing で読み込む範囲を拡大していきます。こうやって気軽に * を使えるのが DuckDB のいいところです。

SELECT * FROM read_csv('s3://bucketname/path/to/2024-10-25-*', delim=' ');

範囲を広げていくうちに、運悪く次のようなエラーが出ることがあります。

Column with name: "column13" is expected to have type: BIGINT But has type: VARCHAR

column13 の内容をよく見てみると、普段は数字なのに、稀に - になることがあるようです。

こういうときは types オプションを使い、文字列として固定してしまうといいです。types={'column13': 'VARCHAR'} を read_csv のオプションとして指定して実行すると、うまく読み込めました！

ヘッダーが無いことを明示する（2024-10-28 追記）

たまに1行目がヘッダーとして解釈されてしまうことがあるので、そうじゃないことを明示します。

read_csv のオプションに header=false を追加するだけです。

まとめ

DuckDB を使って、Amazon S3 のサーバーアクセスログを集計することができました。いくつかハマりがありましたが、どれも解消できて、DuckDB を使うときの引き出しを増やせました。速度にも特に不満はないので、なるべく使っていこうと思います。