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

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

Clock Icon2017.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の2つの要素名とその値を定義します。

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」や「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は最大3階層に構造体として定義しています。 また、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ファイルでも検索できるといっても過言ではありません。

脚注

  1. minify形式のjsonファイル Amazon Athenaがクエリーできるjsonファイルはminify形式のみです。prettyfy形式のjsonファイルはjqコマンド等を利用して変換が必要です。例えば、

    cat <source_json> | jq -c > <dest_json>;

    のように変換できます。

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.