Amazon Athena Nested-JSONのSESログファイルを検索する
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": "[email protected]",
"sourceArn": "arn:aws:ses:us-west-2:111222333:identity/[email protected]",
"sendingAccountId": "111222333",
"messageId": "01010159b2c4471e-fc6e26e2-af14-4f28-b814-69e488740023-000000",
"destination": ["[email protected]"],
"headersTruncated": false,
"headers": [{
"name": "From",
"value": "[email protected]"
}, {
"name": "To",
"value": "[email protected]"
}, {
"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": ["[email protected]"],
"to": ["[email protected]"],
"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%';
;
あるドメインで、宛先不明で戻ってきたメッセージは何件か？
SELECT COUNT(*) as Bounces FROM sesmaster WHERE eventType = 'Bounce' and mail.destination like '%example.com%';
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%';
全ての情報引き出すには
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;
最後に
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>;
のように変換できます。 ↩
![[Tips] Amazon Athena 6億レコードのサンプルデータ「SSB」を準備する](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-764c44f07bcd41184fe62a432ae120ab/512a4cdcd05a0ed3ddea950fdf619fa0/amazon-athena)
