S3に配信したAmazon SESのメールログをDuckDBにて簡単分析

S3に配信したAmazon SESのメールログをDuckDBにて簡単分析

#DuckDB
#Amazon SES
こーへい

こーへい

facebook logohatena logotwitter logo
Clock Icon2025.06.19

こんにちは、こーへいです。

今回は、S3に配信したAmazon SESのメールログをDuckDBにて簡単に分析する方法を紹介します。

DuckDBとは

最近社内でDuckDBの話題がちょくちょく上がっているので気になっていました。

基本的にS3上でログを分析する際は、Amazon Athenaを使用することが多いのですが、個人的にはテーブル作成などの設定の手間や何より料金がかかることが、Athenaを使用する心理的ハードルとなっていました。

そこでDuckDBを使用するとローカルからS3に対して、簡易的にそして無料でログ分析できると聞き、こりゃ使わなイカンということで、今回はAmazon SESのメールログを対象にDuckDBで分析していきます。

やってみた

事前準備

DuckDBのインストール

https://duckdb.org/docs/installation/?version=stable&environment=cli&platform=macos&download_method=direct

上記を参考にインストールしてください。

curl https://install.duckdb.org | sh

自分はMacを使用していたので、上記コマンドでインストールしました。

貼り付けた画像_2025_06_19_17_16

インストールされていますね、コマンド1つで完了して本当に助かります。

Amazon SESのメールログを配信する環境を準備する

Amazon SESでは標準でログを取得することができません。

ログ取得する場合は、SESの設定セットによりメールイベントをData Firehose経由にてS3にログ配信することが多いです。

※CloudWatch Logsに配信したい場合は、Amazon SNSとLambdaを利用するパターンもありますが、個人的にあまりLambdaは保守が必要なので使いたくありません。

https://qiita.com/sugimount-a/items/f9202c676514dcd3d182

上記記事等を参考に、S3にログを配信する環境を準備してください。

テスト配信してログを貯める

https://docs.aws.amazon.com/ja_jp/ses/latest/dg/send-an-email-from-console.html

を参考に、色んなイベントタイプのメールをテスト配信することが可能です。

今回は検証用のログをこちらの機能を使用して貯めていきます。

貼り付けた画像_2025_06_19_17_29

※設定セットの設定を忘れないように注意

大体メールログを確認する場合は、バウンスや苦情のイベントタイプを調べたい時が多いかと思いますので、それらのイベントタイプのテスト配信を行なっておきます。

とりあえず以下のログを貯めました。今回はログの時間帯をある程度絞りつつも複数ファイルに跨ってログ分析をするケースを想定しています。

そのため、2025年6月19日の16時帯と17時帯の2ファイル用意しました。

s3://[bucket名]/2025/06/19/16/PUT-S3-yzbU3-1-2025-06-19-16-21-30-760a70a3-8875-425b-809f-c0e375536ffc

{"eventType":"Delivery","mail":{"timestamp":"2025-06-19T07:21:29.360Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["success@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"success@simulator.amazonses.com"},{"name":"Subject","value":"配信の成功"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["success@simulator.amazonses.com"],"messageId":"----","subject":"配信の成功"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:outgoing-tls-version":["TLSv1.3"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-."],"ses:outgoing-ip":["..."]}},"delivery":{"timestamp":"2025-06-19T07:21:30.033Z","processingTimeMillis":673,"recipients":["success@simulator.amazonses.com"],"smtpResponse":"250 Ok","remoteMtaIp":"...","reportingMTA":"-.smtp-out.ap-northeast-1.amazonses.com"}}
{"eventType":"Send","mail":{"timestamp":"2025-06-19T07:21:40.657Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["bounce@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"bounce@simulator.amazonses.com"},{"name":"Subject","value":"バウンス"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["bounce@simulator.amazonses.com"],"messageId":"----","subject":"バウンス"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-."]}},"send":{}}
{"eventType":"Send","mail":{"timestamp":"2025-06-19T07:21:29.360Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["success@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"success@simulator.amazonses.com"},{"name":"Subject","value":"配信の成功"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["success@simulator.amazonses.com"],"messageId":"----","subject":"配信の成功"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-."]}},"send":{}}
{"eventType":"Bounce","bounce":{"feedbackId":"----","bounceType":"Permanent","bounceSubType":"General","bouncedRecipients":[{"emailAddress":"bounce@simulator.amazonses.com","action":"failed","status":"5.1.1","diagnosticCode":"smtp; 550 5.1.1 As requested: user unknown bounce@simulator.amazonses.com"}],"timestamp":"2025-06-19T07:21:41.717Z","remoteMtaIp":"...","reportingMTA":"dns; ***-.smtp-out.ap-northeast-1.amazonses.com"},"mail":{"timestamp":"2025-06-19T07:21:40.657Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["bounce@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"bounce@simulator.amazonses.com"},{"name":"Subject","value":"バウンス"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["bounce@simulator.amazonses.com"],"messageId":"----","subject":"バウンス"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-."]}}}
{"eventType":"Complaint","complaint":{"feedbackId":"----","complaintSubType":null,"complainedRecipients":[{"emailAddress":"complaint@simulator.amazonses.com"}],"timestamp":"2025-06-19T07:21:55.483Z","userAgent":"Amazon SES Mailbox Simulator","complaintFeedbackType":"abuse","arrivalDate":"2025-06-19T07:21:55.483Z"},"mail":{"timestamp":"2025-06-19T07:21:52.166Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["complaint@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"complaint@simulator.amazonses.com"},{"name":"Subject","value":"苦情"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["complaint@simulator.amazonses.com"],"messageId":"----","subject":"苦情"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-."]}}}
{"eventType":"Delivery","mail":{"timestamp":"2025-06-19T07:21:52.166Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["complaint@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"complaint@simulator.amazonses.com"},{"name":"Subject","value":"苦情"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["complaint@simulator.amazonses.com"],"messageId":"----","subject":"苦情"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:outgoing-tls-version":["TLSv1.3"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-."],"ses:outgoing-ip":["..."]}},"delivery":{"timestamp":"2025-06-19T07:21:55.936Z","processingTimeMillis":3770,"recipients":["complaint@simulator.amazonses.com"],"smtpResponse":"250 Ok","remoteMtaIp":"...","reportingMTA":"-.smtp-out.ap-northeast-1.amazonses.com"}}
{"eventType":"Send","mail":{"timestamp":"2025-06-19T07:21:52.166Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["complaint@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"complaint@simulator.amazonses.com"},{"name":"Subject","value":"苦情"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["complaint@simulator.amazonses.com"],"messageId":"----","subject":"苦情"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-.****"]}},"send":{}}

s3://[bucket名]/2025/06/19/17/PUT-S3-yzbU3-1-2025-06-19-17-05-12-6af6b705-fa9e-4d68-a0d7-29b69a525c28

{"eventType":"Send","mail":{"timestamp":"2025-06-19T08:05:11.104Z","source":"@.co.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.co.jp","sendingAccountId":"","messageId":"----","destination":["complaint@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.co.jp"},{"name":"To","value":"complaint@simulator.amazonses.com"},{"name":"Subject","value":"苦情3"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.co.jp"],"to":["complaint@simulator.amazonses.com"],"messageId":"----","subject":"苦情3"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".co.jp"],"ses:sender-identity":["@.co.jp"],"ses:caller-identity":["-."]}},"send":{}}
{"eventType":"Complaint","complaint":{"feedbackId":"----","complaintSubType":null,"complainedRecipients":[{"emailAddress":"complaint@simulator.amazonses.com"}],"timestamp":"2025-06-19T08:05:14.812Z","userAgent":"Amazon SES Mailbox Simulator","complaintFeedbackType":"abuse","arrivalDate":"2025-06-19T08:05:14.812Z"},"mail":{"timestamp":"2025-06-19T08:05:11.104Z","source":"@.co.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.co.jp","sendingAccountId":"","messageId":"----","destination":["complaint@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.co.jp"},{"name":"To","value":"complaint@simulator.amazonses.com"},{"name":"Subject","value":"苦情3"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.co.jp"],"to":["complaint@simulator.amazonses.com"],"messageId":"----","subject":"苦情3"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".co.jp"],"ses:sender-identity":["@.co.jp"],"ses:caller-identity":["-."]}}}
{"eventType":"Bounce","bounce":{"feedbackId":"----","bounceType":"Permanent","bounceSubType":"General","bouncedRecipients":[{"emailAddress":"bounce@simulator.amazonses.com","action":"failed","status":"5.1.1","diagnosticCode":"smtp; 550 5.1.1 As requested: user unknown bounce@simulator.amazonses.com"}],"timestamp":"2025-06-19T08:05:26.616Z","remoteMtaIp":"...","reportingMTA":"dns; ***-.smtp-out.ap-northeast-1.amazonses.com"},"mail":{"timestamp":"2025-06-19T08:05:25.593Z","source":"@.co.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.co.jp","sendingAccountId":"","messageId":"----","destination":["bounce@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.co.jp"},{"name":"To","value":"bounce@simulator.amazonses.com"},{"name":"Subject","value":"バウンス3"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.co.jp"],"to":["bounce@simulator.amazonses.com"],"messageId":"----","subject":"バウンス3"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".co.jp"],"ses:sender-identity":["@.co.jp"],"ses:caller-identity":["-."]}}}
{"eventType":"Send","mail":{"timestamp":"2025-06-19T08:05:25.593Z","source":"@.co.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.co.jp","sendingAccountId":"","messageId":"----","destination":["bounce@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.co.jp"},{"name":"To","value":"bounce@simulator.amazonses.com"},{"name":"Subject","value":"バウンス3"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.co.jp"],"to":["bounce@simulator.amazonses.com"],"messageId":"----","subject":"バウンス3"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".co.jp"],"ses:sender-identity":["@.co.jp"],"ses:caller-identity":["-."]}},"send":{}}
{"eventType":"Delivery","mail":{"timestamp":"2025-06-19T08:05:11.104Z","source":"@.co.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.co.jp","sendingAccountId":"","messageId":"----","destination":["complaint@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.co.jp"},{"name":"To","value":"complaint@simulator.amazonses.com"},{"name":"Subject","value":"苦情3"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.co.jp"],"to":["complaint@simulator.amazonses.com"],"messageId":"----","subject":"苦情3"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:outgoing-tls-version":["TLSv1.3"],"ses:source-ip":["..."],"ses:from-domain":[".co.jp"],"ses:sender-identity":["@.co.jp"],"ses:caller-identity":["-."],"ses:outgoing-ip":["..."]}},"delivery":{"timestamp":"2025-06-19T08:05:15.288Z","processingTimeMillis":4184,"recipients":["complaint@simulator.amazonses.com"],"smtpResponse":"250 Ok","remoteMtaIp":"...","reportingMTA":"*-.smtp-out.ap-northeast-1.amazonses.com"}}

DuckDBで分析する

https://duckdb.org/docs/stable/sql/statements/load_and_install

上記を参考にS3と連携するための拡張機能「httpfs」をインストールしてロードします。

D INSTALL httpfs;
D LOAD httpfs;

credential_chainプロバイダーを使用して、認証情報を取得します(これを使用することでaws sts get-caller-identityで表示される認証情報でログ分析できるようになります)。

D CREATE SECRET secret2 (
      TYPE s3,
      PROVIDER credential_chain
  );
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true    │
└─────────┘

では、ログ分析していきましょう。
基本的にはeventTypeがBounceやComplaintに対するログの確認が多いと思いますのでそれをメインで確認しましょう。

eventTypeがComplaintのものをタイムスタンプの降順で検索します(必要な情報をSELECTなりして下さい)。

FROM read_json_auto('s3://[bucket名]/2025/06/19/*/*')
  WHERE eventType = 'Complaint'
  ORDER BY complaint.timestamp DESC;

貼り付けた画像_2025_06_19_18_55

eventTypeがBounceのものをタイムスタンプの降順で検索します(必要な情報をSELECTなりして下さい)。

FROM read_json_auto('s3://[bucket名]/2025/06/19/*/*')
  WHERE eventType = 'Bounce'
  ORDER BY complaint.timestamp DESC;

貼り付けた画像_2025_06_19_18_58

サクッと分析できて最高ですね。

まとめ

DuckDBを使用することで手軽にS3のデータを検索することができましたね。

他にもVPCフローログやWAFのアクセスログで試している記事がありますので、ぜひご参照ください。

https://dev.classmethod.jp/articles/analyze-aws-waf-logs-with-duckdb/

https://dev.classmethod.jp/articles/query-vpc-flow-logs-by-duckdb/

[おまけ]MCPサーバーを活用して自然言語でクエリする

今ならMCPサーバーを試さないわけにはいかないでしょう。

https://dev.classmethod.jp/articles/motherduck-duckdb-mcp-with-claude-code/

こちらの記事でMCPサーバーをセットアップして自然言語でクエリしてみます。

s3://[bucket名]/2025/06/19/のログにて、eventType`が`Bounce`のものをタイムスタンプの降順で検索してください

貼り付けた画像_2025_06_19_19_16

認証情報のエラーや、FROMのエラーなど紆余曲折を経て検索できました！

※ファイル名がs3://[bucket名]/2025/06/19/17/PUT-S3-yzbU3-1-2025-06-19-17-05-12-6af6b705-fa9e-4d68-a0d7-29b69a525c28という構造なので、s3://[bucket名]/2025/06/19/*ではなくs3://[bucket名]/2025/06/19/*/*で検索しないとエラーが出る。

貼り付けた画像_2025_06_19_19_16

.
.
.

流石にこれくらいなら普通にクエリ検索した方が楽か？まあいい感じにMCPサーバーを活用していきましょう。

終わり！

Share this article

facebook logohatena logotwitter logo

関連記事

DuckDBを使ってS3に格納されているParquetファイルを調査してみた

DuckDBを使ってS3に格納されているParquetファイルを調査してみた

User avatar
kasama
2025.07.05
「コカコーラ大好き」と彼は何回言ったのか？WebスクレイピングとDuckDBで集計してみた

「コカコーラ大好き」と彼は何回言ったのか？WebスクレイピングとDuckDBで集計してみた

DuckDBでイベント履歴からダウンロードしたAWS CloudTrailのイベントを分析するための取り込み方法と簡単なクエリを作ってみた

DuckDBでイベント履歴からダウンロードしたAWS CloudTrailのイベントを分析するための取り込み方法と簡単なクエリを作ってみた

User avatar
臼田佳祐
2025.05.31
【登壇資料】 「DuckDBのデータエンジニア観点での使い道 (2025年5月版)」というタイトルで社内で登壇しました

【登壇資料】 「DuckDBのデータエンジニア観点での使い道 (2025年5月版)」というタイトルで社内で登壇しました

User avatar
kasama
2025.05.29
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.