こんにちは。植松です。
AWSで構築している場合、メール送信にはAWS SESを使うことになるのが一般的です。
SESのログはVirtual Deliverability Managerを使うことで一定期間保持することが可能です。
ただし、30日間と短いため長期的なログ保管としては使えません。
そのため弊社ではKinesis Firehoseを使い、S3にログを蓄積するようにしています。
S3に蓄積した複数のログから効率よく知りたい情報を取得するため、今回はAthenaを使ってログから情報を取得する方法をご紹介します。

Athenaとは

S3に保存されたデータに対してSQLを使って直接クエリを実行できます。Athenaはインフラの管理をすることなく大規模なデータセットの分析を行えるため、データ分析やデータの可視化に広く利用されています。

手順

前提

今回は既にS3にSESのログが蓄積されているものとします。

1.S3にバケットを作成

Athenaでクエリを実行すると実行結果がS3に保存される仕組みになっています。
そのため、Athenaを初めて利用する際は保存するS3を設定する必要があります。
バケットの作成方法については省略します。

以降、操作はAthenaにて実施します。

2.初期設定を行う

クエリエディタを起動　＞　設定　＞　管理をクリックします。

  • クエリの実行結果出力先は、先ほど作成したS3バケットを指定
  • バケットオーナーに現在のAWSアカウントIDを指定
  • バケットオーナーへのフルコントロールと、暗号化の両方にチェックして保存します

blog_1030_1.png

3.テーブルの作成

SESログの構造に合わせてAthenaでクエリ用のテーブルを作成します。

CREATE EXTERNAL TABLE IF NOT EXISTS ses_logs (
    eventType STRING,
    mail STRUCT<
        timestamp: STRING,
        source: STRING,
        sourceArn: STRING,
        sendingAccountId: STRING,
        messageId: STRING,
        destination: ARRAY<STRING>
    >,
    delivery STRUCT<
        timestamp: STRING,
        processingTimeMillis: BIGINT,
        recipients: ARRAY<STRING>,
        smtpResponse: STRING,
        remoteMtaIp: STRING,
        reportingMTA: STRING
    >,
    bounce STRUCT<
        bounceType: STRING,
        bounceSubType: STRING,
        bouncedRecipients: ARRAY<STRUCT<
            emailAddress: STRING,
            action: STRING,
            status: STRING,
            diagnosticCode: STRING
        >>,
        timestamp: STRING
    >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://your-ses-log-bucket-path/'
TBLPROPERTIES ('has_encrypted_data'='false');
  • LOCATIONには、SESログが保存されているS3バケットのパスを指定します。
  • このクエリで、JSON形式のSESログデータがAthenaから検索可能なテーブルとして定義されます。

4.クエリの実行

Athenaで目的の情報を取得するクエリを実行します。例えば、特定の日時範囲内で送信されたメールを検索する場合、以下のクエリを実行します。

SELECT * FROM ses_logs WHERE mail.timestamp BETWEEN '2024-10-30T00:00:00Z' AND '2024-10-30T23:59:59Z';
  • mail.timestampを指定して、UTC形式での時刻範囲を設定できます。

blog_1030_2.png

ちなみにですが、バケットには以下のように実行結果が保存されます。
blog_1030_3.png

おわりに

RDBが使える方なら簡単に使えるようになっています。
また、テーブル作成した後にS3に置かれたファイル（今回の場合だとSESのログ）もテーブルデータとして反映されるので、テーブルの再作成は不要です。
Athenaの料金ですが、DDLには料金がかかりません。かかるのはDMLのみです。
1TBスキャンごとに料金がかかるようになっているので、発行するクエリを必要なデータのみに絞り込むようにすればスキャン量は減ります。
また、dryrunなど、テストでクエリを投げることはできないので巨大データをスキャンする場合は要注意です。