Amazon SESのメールログを簡単に分析する、AWS AthenaでSQLを使う方法

Amazon SESのメールログを簡単に分析する、AWS AthenaでSQLを使う方法

Amazon SESから出力されるメール送信ログは、そのままでは分析しにくいJSON形式で保存されます。AWS Athenaを使って、このログをSQLで検索できるようにして、バウンス率の分析やメール開封数の確認を簡単にします。

ログをAthenaで読み込むための「テーブル」を作る

Athenaでログを扱うには、まずログの構造をテーブルを作成して、Athenaに教えてあげます。

SESのログはJSON形式なので、ROW FORMAT SERDE org.openx.data.jsonserde.JsonSerDeと指定して、JSONを読み込めるように指定します。

CREATE EXTERNAL TABLEというコマンドで、ログが保存されているS3バケットを「外部テーブル」として定義します。

CREATE EXTERNAL TABLE ses_logs (
  eventType string,
  bounce struct<
    feedbackId:string,
    bounceType:string,
    bounceSubType:string,
    bouncedRecipients:array<
      struct<
        emailAddress:string,
        action:string,
        status:string,
        diagnosticCode:string
      >
    >,
    timestamp:string,
    remoteMtaIp:string,
    reportingMTA:string
  >,
  delivery struct<
    timestamp:string,
    processingTimeMillis: int,
    recipients: array<string>,
    smtpResponse: string,
    remoteMtaIp: string,
    reportingMTA: string
  >,
  mail struct<
    timestamp:string,
    source:string,
    sourceArn:string,
    sendingAccountId:string,
    messageId:string,
    destination:array<string>,
    headersTruncated:boolean,
    headers:array<
      struct<
        name:string,
        value:string
      >
    >,
    tags:map<string,array<string>>
  >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://[SESのログのあるバケット名]'
TBLPROPERTIES ('has_encrypted_data'='false');

ses_logs: テーブルの名前です。自由に設定できます。
eventType: イベントの種類（Bounce, Deliveryなど）が入るカラムです。
bounce struct<...>: bounceイベントの詳細情報を格納するための構造体（struct）です。
delivery struct<...>: deliveryイベントの詳細情報を格納するための構造体です。
mail struct<...>: メール自体の情報（送信元、送信先など）を格納する構造体です。
LOCATION 's3://[SESのログのあるバケット名]': SESのログが保存されているS3バケットのパスを指定します。

bounceやdeliveryのように、イベントごとに中身が異なるJSONの階層構造を、このようにstructを使って定義することで、SQLでアクセスできるようになります。

作成したテーブルをSQLで検索する

テーブルの作成が終われば、あとは普通のデータベースのようにSELECT文を使ってデータを検索できます。

bounce構造体の中のbounceTypeを取得したい場合は、bounce.bounceTypeのように.（ドット）でつなげて指定します。

また、tagsのようにキーと値のペアで構成されたmap形式のデータは、mail.tags['キー名']のように角括弧 [] を使ってアクセスします。

SELECT
 eventType
 ,bounce.bounceType    as bounce_bounceType
 ,bounce.bounceSubType as bounce_bounceSubType
 ,delivery.timestamp   as delivery_timestamp
 ,delivery.recipients  as delivery_recipients
 ,mail.source          as mail_source
 ,mail.destination     as mail_destination
 ,mail.tags['ses:source-tls-version']  as ses_source_tls_version
 ,mail.tags['ses:from-domain']         as ses_from_domain
FROM
  ses_logs;

結果

SESのJSONログをSQLで簡単に扱い、分析が可能になります。