Amazon Athena Nested-JSONのSESログファイルを検索する

石川覚

2017.03.06

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon Athenaは、JSONSerDe とSERDEPROPERTIESの「マッピング」を使うことで、Nested-JSON（入れ子のJSON）のデータファイルをテーブルとして定義することができます。今回はその応用例として、SESのログファイルのテーブル定義とその検索方法について解説します。

はじめに

Amazon SES は、メール送信イベントレコードを JSON 形式で Amazon Kinesis Firehose に発行します。そして、最終的に Firehose を経由して出力した S3 上のファイルを Amazon Athena で検索します。以降では、Nested-JSONの定義に必要となる複合型（Complex Type）カラムの定義とマッピングについて解説した後、実際にSESのログを検索する方法をご紹介します。

SES や Firehose の設定については以下を参照してください。

複合型（Complex Type）カラムを定義する方法

Amazon Athenaは、intやstringといった基本的な型（Primitive Type）以外に struct(構造体)、map（マップ）、array（配列）といった複合型（Complex Type）があります。これらは入れ子になった形式やマップ、配列の形式をパースしてカラムに定義するのに必要です。

array (配列)

arrayは「array<型>」で定義します。

jsonファイル

下記の例では、namesという要素名に foo、barという値の配列を表しています。

{"names":["foo", "bar"]}

テーブル定義

テーブル定義では、namesというカラムに対して、namesというarray(配列型)を指定します。

CREATE EXTERNAL TABLE array_sample (names array)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<bucket_name>/array_sample/';

SELECTの例

namesカラムを参照すると配列の内容がそのまま表示されます。

> SELECT names FROM array_sample;
  names
----------
[foo, bar]

namesカラムの配列の値をレコードとして取得するには、「CROSS JOIN UNNEST」構文を利用します。

> SELECT name FROM array_sample CROSS JOIN UNNEST(names) AS t (name);
 name 
----- 
 foo 
 bar

struct (構造体)

structは「struct<要素名:型, …>」で定義します。

jsonファイル

下記の例では、sという要素名に 要素名aと要素名bが定義されています。

{"s":{"a":"foo" ,"b":"bar"}}

テーブル定義

テーブル定義では、sカラムは要素名aと要素名bの２つの要素名とその値を定義します。

CREATE EXTERNAL TABLE struct_sample (s struct<a:string, b:string>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<bucket_name>/struct_sample/';

SELECTの例

sカラムを参照すると構造体の内容がそのまま表示されます。

> SELECT s FROM struct_sample;
       s
--------------
{a=foo, b=bar}

sカラムの中の要素の値を参照するにはs.aやs.bのようにカラムを指定して、それぞれの値を取得します。

> SELECT s.a, s.b FROM struct_sample;
  a  |  b
-----+-----
 foo | bar

map(マップ)

mapは「map<キーの型, 値の型>」で定義します。

jsonファイル

下記の例では、sという要素名に 要素名aと要素名bが定義されています。このjsonファイルはstruct(構造体)のものと同様です。

{"s":{"a":"foo", "b":"bar"}}

テーブル定義

struct(構造体)のときはstructとして定義しましたが、マップでは要素名をキー、値をバリューとして定義します。

CREATE EXTERNAL TABLE map_sample (s map<string, string>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<bucket_name>/map_sample/';

SELECTの例

sカラムを参照すると構造体の内容がそのまま表示されます。

> SELECT s FROM map_sample;
      s
--------------
{a=foo, b=bar}

マップのデータとして定義することで、sの連想配列として値が取得できるようになります。

> SELECT s['a'] as a, s['b'] as b FROM map_sample;
  a  |  b
-----+-----
 foo | bar

Nested-JSON（入れ子JSON）ファイル検索

早速、Nested-JSON（入れ子JSON）ファイルを検索してみましょう。ここでは基本的な例と、カラムの「マッピング」の定義が必要な例を紹介します。

1. 基本的なNested-JSONの例

jsonファイル

下記の例では、要素名sの下に要素名a、更にその下に要素名bが入れ子になっているjsonファイルです。

{"s":{"a":{"b":"foo"}}}

テーブル定義

ご想像の通り、一般的なNested-JSONのパースは、「s struct<a:struct>」のように、struct(構造体)を階層的に定義することで定義可能です。

CREATE EXTERNAL TABLE nested_sample1 (s struct<a:struct<b:string>>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<bucket_name>/nested_json1/';

SELECTの例

データの参照は、「ｓ」や「s.a」や「s.a.b」とカラム指定することで、それぞれ参照したい階層のデータにアクセスできます。

> SELECT s FROM nested_sample1;
     s
-----------
{a={b=foo}}

> SELECT s.a FROM nested_sample1;
   a
-------
{b=foo}

> SELECT s.a.b FROM nested_sample1;
   b
------
 foo

2. カラムの「マッピング」を利用したNested-JSONの例

下記の例は、要素名「a:1」や「b:1」の用に要素名に「：」コロンを含むNested-JSONの例です。

jsonファイル

{"s":{"a:1":{"b:1":"foo"}}}

テーブル定義

これまでと同じように、struct(構造体)を階層的に定義を当てはめると、「s struct<a:1:struct<b:1:string>>」の様になり、要素名に「：」コロンに含まれ、要素名と値を区別できない問題が生じます。

そこで登場するのがカラムの「マッピング」です。SERDEPROPERTIES セクションで要素名「a:1」は要素名a1、要素名「b:1」は要素名b1という別名に再マップができます。この再マップした別名を使って、struct(構造体)を階層的に定義を当てはめて「s struct<a1:struct<b1:string>>」のように定義して問題を回避します。

CREATE EXTERNAL TABLE nested_sample2 (s struct<a1:struct<b1:string>>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  "mapping.a1"="a:1",
  "mapping.b1"="b:1"
  )
LOCATION 's3://<bucket_name>/nested_json2/';

SELECTの例

カラムの「マッピング」で指定した別の要素名で階層的にデータを参照できます。

 > SELECT s FROM nested_sample2;
      s 
-------------
{a1={b1=foo}}

> SELECT s.a1 FROM nested_sample2;
   a1 
--------
{b1=foo} 

> SELECT s.a1.b1 FROM nested_sample2;
 b1 
---- 
foo

SESログの検索の応用

送信イベントのデータセット以下の形式のjsonファイルです。(実際のjsonファイルはインデントや改行を含まないminify形式のjsonファイル ^*1です。)

{
	"eventType": "Send",
	"mail": {
		"timestamp": "2017-01-18T18:08:44.830Z",
		"source": "youraddress@example.com",
		"sourceArn": "arn:aws:ses:us-west-2:111222333:identity/youraddress@example.com",
		"sendingAccountId": "111222333",
		"messageId": "01010159b2c4471e-fc6e26e2-af14-4f28-b814-69e488740023-000000",
		"destination": ["success@simulator.amazonses.com"],
		"headersTruncated": false,
		"headers": [{
				"name": "From",
				"value": "youraddress@example.com"
			}, {
				"name": "To",
				"value": "success@simulator.amazonses.com"
			}, {
				"name": "Subject",
				"value": "Bounced Like a Bad Check"
			}, {
				"name": "MIME-Version",
				"value": "1.0"
			}, {
				"name": "Content-Type",
				"value": "text/plain; charset=UTF-8"
			}, {
				"name": "Content-Transfer-Encoding",
				"value": "7bit"
			}
		],
		"commonHeaders": {
			"from": ["youraddress@example.com"],
			"to": ["success@simulator.amazonses.com"],
			"messageId": "01010159b2c4471e-fc6e26e2-af14-4f28-b814-69e488740023-000000",
			"subject": "Test"
		},
		"tags": {
			"ses:configuration-set": ["Firehose"],
			"ses:source-ip": ["54.55.55.55"],
			"ses:from-domain": ["amazon.com"],
			"ses:caller-identity": ["root"]
		}
	},
	"send": {}
}

これまで通り、jsonファイルは org.openx.data.jsonserde.JsonSerDe を使って、データのパースを行います。要素名mailは最大３階層に構造体として定義しています。また、timestamp カラムや from カラムは Presto の予約語なのでバックォート「`」でエスケープする必要があります。「ses:」からはじまる要素名については、SERDEPROPERTIES のマッピングセクションで再マッピングして、クエリを投げることができるように定義しています。

Firehose に発行された Amazon SES イベントデータの例のデータに対して、以下のテーブル定義を指定したとします。

CREATE EXTERNAL TABLE sesmaster (
  eventType string,
  complaint struct<arrivaldate:string, 
                   complainedrecipients:array<struct<emailaddress:string>>,
                   complaintfeedbacktype:string, 
                   feedbackid:string, 
                   `timestamp`:string, 
                   useragent:string>,
  bounce struct<bouncedrecipients:array<struct<action:string, diagnosticcode:string, emailaddress:string, status:string>>,
                bouncesubtype:string, 
                bouncetype:string, 
                feedbackid:string,
                reportingmta:string, 
                `timestamp`:string>,
  mail struct<`timestamp`:string,
              source:string,
              sourceArn:string,
              sendingAccountId:string,
              messageId:string,
              destination:string,
              headersTruncated:boolean,
              headers:array<struct<name:string,value:string>>,
              commonHeaders:struct<`from`:array<string>,to:array<string>,messageId:string,subject:string>,
              tags:struct<ses_configurationset:string,ses_source_ip:string,ses_outgoing_ip:string,ses_from_domain:string,ses_caller_identity:string>
              >,
  send string,
  delivery struct<processingtimemillis:int,
                  recipients:array<string>, 
                  reportingmta:string, 
                  smtpresponse:string, 
                  `timestamp`:string>
  )           
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  "mapping.ses_configurationset"="ses:configuration-set",
  "mapping.ses_source_ip"="ses:source-ip", 
  "mapping.ses_from_domain"="ses:from-domain", 
  "mapping.ses_caller_identity"="ses:caller-identity",
  "mapping.ses_outgoing_ip"="ses:outgoing-ip"
  )
LOCATION 's3://<bucket_name>/seslog/';

月曜日のキャンペーンで、宛先不明で戻ってきたメッセージはどれか？

SELECT eventtype as Event,
       mail.destination as Destination, 
       mail.messageId as MessageID,
       mail.timestamp as Timestamp
FROM sesmaster
WHERE eventType = 'Bounce' and mail.timestamp like '2016-10-14%';
;

20170306-ses-nested-json-query1

あるドメインで、宛先不明で戻ってきたメッセージは何件か？

SELECT COUNT(*) as Bounces 
FROM sesmaster
WHERE eventType = 'Bounce' and mail.destination like '%example.com%';

20170306-ses-nested-json-query2

example.com ドメインに戻ってきたメッセージはどれか？

SELECT eventtype as Event,
       mail.destination as Destination, 
       mail.messageId as MessageID 
FROM sesmaster
WHERE eventType = 'Bounce' and mail.destination like '%example.com%';

20170306-ses-nested-json-query3

全ての情報引き出すには

SELECT 
       -- Event
       eventtype as Event,
       -- Complaint
       complaint.arrivaldate as Arrivaldate, 
       complaint.complainedrecipients as ComplainedRecipients, 
       complaint.complaintfeedbacktype as ComplaintFeedbackType, 
       complaint.feedbackid as FeedbackID, 
       complaint."timestamp" as ComplaintTimestamp, 
       -- Bounce
       bounce.bouncedrecipients as BounceDrecipients,
       bounce.bouncesubtype as Bouncesubtype,
       bounce.bouncetype as Bouncetype,
       bounce.feedbackid as Feedbackid,
       bounce.reportingmta as Reportingmta,
       bounce."timestamp" as BounceTimestamp,
       -- Mail
       mail."timestamp" as MailTimestamp, 
       mail.source as source, 
       mail.sourceArn as Destination, 
       mail.sendingAccountId as sourceArn, 
       mail.messageId as MessageID, 
       mail.destination as Destination, 
       mail.headersTruncated as headersTruncated, 
       mail.headers as headers, 
       mail.commonHeaders as commonHeaders, 
       mail.tags.ses_configurationset as ses_configurationset,
       mail.tags.ses_source_ip as ses_source_ip,
       mail.tags.ses_from_domain as ses_from_domain,
       mail.tags.ses_caller_identity as ses_caller_identity,
       mail.tags.ses_outgoing_ip as ses_outgoing_ip,
       -- Send
       send,
       -- Delivery
       delivery.processingtimemillis as ProcessingTimemillis,
       delivery.recipients as Recipients,
       delivery.reportingmta as ReportingMTA,
       delivery.smtpresponse as SMTPResponse,
       delivery.timestamp as DeliveryTimestamp
FROM sesmaster;

20170306-ses-nested-json-query4

最後に

Amazon Athenaで SESのような複雑なjsonファイルを検索するには以下の点の考慮が必要でした。

複合型（Complex Type）カラムと定義
Nested-JSONのカラムの「マッピング」
予約語のエスケープ

Athenaで SESのログのような複雑なjsonファイルの検索ができるようになれば、どのようなjsonファイルでも検索できるといっても過言ではありません。

脚注

minify形式のjsonファイル Amazon Athenaがクエリーできるjsonファイルはminify形式のみです。prettyfy形式のjsonファイルはjqコマンド等を利用して変換が必要です。例えば、
```
cat <source_json> | jq -c > <dest_json>;
```
のように変換できます。 ↩

Amazon Athena Nested-JSONのSESログファイルを検索する

目次

はじめに

複合型（Complex Type）カラムを定義する方法

array (配列)

jsonファイル

テーブル定義

SELECTの例

struct (構造体)

jsonファイル

テーブル定義

SELECTの例

map(マップ)

jsonファイル

テーブル定義

SELECTの例

Nested-JSON（入れ子JSON）ファイル検索

1. 基本的なNested-JSONの例

jsonファイル

テーブル定義

SELECTの例

2. カラムの「マッピング」を利用したNested-JSONの例

jsonファイル

テーブル定義

SELECTの例

SESログの検索の応用

月曜日のキャンペーンで、宛先不明で戻ってきたメッセージはどれか？

あるドメインで、宛先不明で戻ってきたメッセージは何件か？

example.com ドメインに戻ってきたメッセージはどれか？

全ての情報引き出すには

最後に

脚注

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS / システムアーキテクト案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

関連記事

Amazon AthenaのクエリをAmazon S3 Object Lambdaアクセスポイントに対して実行してみました

JSONL ファイルをもとに Amazon Athena で INSERT INTO してみる

Amazon Athenaのクエリを別アカウントのS3アクセスポイントに対して実行してみました

AWS入門ブログリレー2024〜 Amazon Athena 編〜

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩