2024.11.14

DuckDBを使い、S3に蓄積しているSESのログを調査

こんにちは。植松です。
前回、AWSのAthenaを使ってS3に蓄積されたログを調査する方法をご紹介しましたが、今回はDuckDBを使った方法をご紹介します。

DuckDBとは

高性能で軽量なデータベース管理システム（DBMS）で、特に分析用途向けに設計
インメモリ型データベースのため、非常に高速なクエリ処理が可能。また、ローカルストレージへの保存も可能
豊富な読み込み可能なデータ群（一部のデータ群では書き込みも可能）
- ローカルのデータファイル（CSV, JSON, parquet, Excel）
- HTTP(S)でアクセスできるデータファイル
- AWS/Azure/Google Cloud のオブジェクトストレージ
- PostgreSQL
- MySQL
オープンソースであり、MITライセンスの下で配布

費用について、S3のデータをDuckDBのローカルテーブルに保存する際のデータダウンロード費用（AWS S3の料金に準拠）だけでよく、Athenaを使う際にネックになる、大規模データを分析する際に発行するクエリの費用を考えなくてよくなります。

手順

前提

事前にIAMにて適切な権限を持つユーザーを作っておきます。
S3にログが蓄積されているものとします。

1:DuckDBのインストール

今回はMacに導入しました。以下の公式サイトからお好みの手順でインストールします。私はbrewを使いました。
https://duckdb.org/docs/installation/

インストール後は以下コマンドでDuckDBが起動します。

$ duckdb

以下、操作はDuckDBにて行います。

2:S3接続情報の設定

事前に作成済みのユーザー情報と、S3バケットのリージョンを指定します。

CREATE SECRET secret1 (
    TYPE S3,
    KEY_ID 'アクセスキーID',
    SECRET 'シークレットアクセスキー',
    REGION 'ap-northeast-1'
);

※間違って作成した場合は、DROP SECRET secret1;　で削除ができます。

3:S3からデータをDLし、テーブルを作成

以下は、2024/11月に発生したeventTypeが'Send'と'Delivery'のSESのログを抽出しています。

CREATE TABLE ses_logs AS
SELECT
    eventType,
    -- mail部分の展開
    mail.timestamp AS mail_timestamp,
    mail.source AS mail_source,
    mail.sourceArn AS mail_sourceArn,
    mail.sendingAccountId AS mail_sendingAccountId,
    mail.messageId AS mail_messageId,
    mail.destination AS mail_destination,
    -- headersを展開（必要に応じて）
    mail.headers[0].name AS header_1_name,
    mail.headers[0].value AS header_1_value,
    mail.headers[1].name AS header_2_name,
    mail.headers[1].value AS header_2_value,
    -- 他のheadersフィールドがあれば展開します（[2], [3]など）
    -- commonHeaders部分の展開
    mail.commonHeaders.from[0] AS commonHeader_from,
    mail.commonHeaders.date AS commonHeader_date,
    mail.commonHeaders.to[0] AS commonHeader_to,
    mail.commonHeaders.messageId AS commonHeader_messageId,
    mail.commonHeaders.subject AS commonHeader_subject,
    -- tags部分の展開
    mail.tags['ses:source-tls-version'][0] AS tag_tls_version,
    mail.tags['ses:operation'][0] AS tag_operation,
    mail.tags['ses:configuration-set'][0] AS tag_configuration_set,
    mail.tags['ses:source-ip'][0] AS tag_source_ip,
    -- delivery部分（deliveryが存在する場合）
    delivery.timestamp AS delivery_timestamp,
    delivery.processingTimeMillis AS delivery_processingTimeMillis,
    delivery.recipients AS delivery_recipients,
    delivery.smtpResponse AS delivery_smtpResponse,
    delivery.remoteMtaIp AS delivery_remoteMtaIp,
    delivery.reportingMTA AS delivery_reportingMTA
FROM read_json_auto('s3://your-bukets/2024/11/*/*/*') AS raw
WHERE raw.eventType IN ('Send', 'Delivery');

4:データ抽出

SELECT * FROM ses_logs;

カラムの数が多いとすべて表示できないので、カラムを指定して抽出するか、CSV形式などでエクスポートするなどします。

COPY (SELECT * FROM ses_logs) TO 'ses_logs.csv' WITH (FORMAT CSV, HEADER TRUE);

上記クエリで出力されたCSVファイルは、現在いるディレクトリに保存されます。

また、フォーマットを変更して抽出も可能です。

.mode table
SELECT * FROM ses_logs;

元に戻す場合

.mode duckbox

詳細は公式サイトをご確認ください。

注意点

インメモリ型なので.exit（DuckDBを閉じること）で、S3への接続情報含め全てのデータは消去されます。
テーブル・データを残しておきたい場合は以下のようにDuckDB起動時、出力先のファイル名を指定しておきます。

duckdb ses_logs.duckdb

もしくはDuckDB起動中の場合は.openでファイルを指定することで生成が可能です。

duckdb ses_logs2.duckdb

※.openで生成されたファイルは、それまでに実行したデータは保存していないので要注意です。
DuckDB起動時に出力先のファイル名を指定する運用をオススメします。不要ならファイルを消すだけで良いので。

次回DuckDB起動時は、保存したファイルを指定することで続きから作業が行えます。

duckdb ses_logs.duckdb

※S3への接続情報は保存されません。保存されるのはテーブルのデータやインデックスなど、テーブルに関連する情報のみです。

最後に

いろんなOSに簡単に導入ができ、SQLが書ければ使えます。
複数のデータと組み合わせて加工や集計を行ったり、結果をCSVやJSON形式で出力する。といったことも可能なので、痒い所に手が届くツールとしても使えそうな気がします。

ITブログの一覧に戻る