この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
このエントリは Serverless Advent Calendar 2017 15日目の記事です。
Amazon Mobile Analytics はアプリケーションの利用に関する情報を収集・分析するためのサービスです。Auto Export機能を有効にすると、分析用のデータをS3およびRedshiftにエクスポートできます。高頻度でクエリを実行する場合はRedshiftが便利ですが、1日に数回クエリをかけるぐらいの利用頻度だとオーバースペックかもしれません。
今回はサーバレスなクエリサービス Amazon Athena を使って、 S3にエクスポートしたデータにクエリをかけてみます。
Amazon Mobile Analyticsのデータ
Amazon Mobile AnalyticsでS3へのAuto Export機能を有効にすると、任意のバケットにJSON形式のファイルを定期的に出力します。
今回は確認用のWEBページを用意してカスタムイベントを送信しました。
イベント送信処理
index.html
mobileAnalyticsClient.recordEvent('content-loaded', {
'custom-id': 1, 'custom-name': 'index_page',
}, {});
mobileAnalyticsClient.submitEvents();
another.html
mobileAnalyticsClient.recordEvent('content-loaded', {
'custom-id': 2, 'custom-name': 'another_page',
}, {});
mobileAnalyticsClient.submitEvents();
既定の項目に加えて、 custom-id
と custom-name
を記録します。
Amazon Athenaでクエリを実行する
eventsテーブルを作成
Create Table
から Manually
を選択してテーブルを定義します。
テーブルの基本情報
- Database : データベース名
- Table Name : テーブル名
- Location of Input Data Set : エクスポート先のS3のURL
今回はデータベース名を awsma
、 テーブル名を events
に設定します。
入力データのフォーマット
入力データのフォーマットは JSON
を選択します。
構成列を定義
S3に出力されるJSONのフォーマットは、公式ドキュメントに記載されています。
各項目を参照できるように、テーブルの構成列を定義します。 application
や client
など、入れ子のJSONを含むため、下記いずれかの方法で内側の項目を参照できるようにします。
- MAP型やSTRUCT型で定義する
- STRING型として定義して、射影するときに
json_extract_scalar
関数を使う
今回は後者の方法を試してみます。前者の方法は下記の記事を参照してください。
構成列が多いので、 Bulk add columns
で一括定義します。
下記の列を定義します。
event_type string,
event_timestamp bigint,
arrival_timestamp bigint,
event_version string,
application string,
client string,
device string,
session string,
monetization string,
attributes string,
metrics string
パーティション列を定義
S3にエクスポートされるデータは、下記の構造で配置されます。
年月日・時間ごとのフォルダを参照するため、パーティション列を追加します。今回は created_at
列として追加します。
パーティションについては下記の記事を参照してください。
ここまでで events
テーブルの作成完了です。
パーティションの追加
S3にエクスポートされるデータは、Amazon Athenaが期待するパーティションの形式 <パーティション列>=<値>
と異なる構造で配置されるので、 ALTER TABLE ADD PARTITION
で明示的に配置場所を指定してパーティションを追加します。
ALTER TABLE
awsma.events
ADD
PARTITION
(created_at='2017-12-13_01')
LOCATION
's3:///awsma/events//2017/12/13/01'
PARTITION
(created_at='2017-12-13_02')
LOCATION
's3:///awsma/events//2017/12/13/02'
;
クエリ実行
検索できるようになったか確認します。
生データの確認
SELECT
*
FROM
awsma.events
WHERE
created_at BETWEEN '2017-12-13_01' AND '2017-12-13_02'
;
定義した通りの内容で検索できるようになりました。
各項目を検索
タイムスタンプや入れ子のJSONはそのままだと確認しづらいので、わかりやすい形に変換します。
SELECT
from_unixtime(event_timestamp % 1000) AT TIME ZONE 'Asia/Tokyo' event_timestamp, -- (1) タイムスタンプ変換
from_unixtime(arrival_timestamp % 1000) AT TIME ZONE 'Asia/Tokyo' arrival_timestamp,
json_extract_scalar(application, '$.sdk.name') sdk_name, -- (2) 入れ子JSONから任意の項目取り出し
json_extract_scalar(application, '$.sdk.version') sdk_version,
json_extract_scalar(device, '$.platform.name') platform_name,
json_extract_scalar(device, '$.platform.version') platform_version,
json_extract_scalar(attributes, '$["custom-id"]') custom_id, -- (3) 入れ子JSONから任意の項目取り出し
json_extract_scalar(attributes, '$["custom-name"]') custom_name
FROM
awsma.events
WHERE
created_at BETWEEN '2017-12-13_01' AND '2017-12-13_02'
ORDER BY
event_timestamp
;
- (1) エポックからの経過ミリ秒をタイムスタンプに変換します。今回はミリ秒以下を切り捨てていますが、 parse_duration 関数と組み合わせるとミリ秒も含めることができます。
- (2) json_extract_scalar 関数を使って任意の項目を取り出します。
- 第1引数: 列名を指定します
- 第2引数: JSONPath 形式で指定します
- (3) (2)と同様ですが、
.
区切りで指定できない文字-
を含むので、キーを文字列で指定してアクセスします。
カスタムイベントを集計
カスタムイベントのイベント名ごとに数を集計します。
SELECT
q.custom_name,
COUNT(1) event_count
FROM
(
SELECT
json_extract_scalar(attributes, '$["custom-name"]') custom_name
FROM
awsma.events
WHERE
created_at BETWEEN '2017-12-13_01' AND '2017-12-13_02'
AND
event_type = 'content-loaded'
) q
GROUP BY
q.custom_name
ORDER BY
event_count DESC
;
おわりに
Amazon Athenaを活用して、 Amazon Mobile AnalyticsがエクスポートしたS3のデータにクエリを実行することができました。 日時のバッチ集計などにも利用できそうなので、積極的に活用していきたいですね!