Amazon Mobile AnalyticsでエクスポートしたS3のデータを、Amazon Athenaで集計する #serverless #adventcalendar

2017.12.15

はじめに

このエントリは 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-idcustom-name を記録します。

Amazon Athenaでクエリを実行する

eventsテーブルを作成

Create Table から Manually を選択してテーブルを定義します。

テーブルの基本情報

  • Database : データベース名
  • Table Name : テーブル名
  • Location of Input Data Set : エクスポート先のS3のURL

今回はデータベース名を awsma 、 テーブル名を events に設定します。

入力データのフォーマット

入力データのフォーマットは JSON を選択します。

構成列を定義

S3に出力されるJSONのフォーマットは、公式ドキュメントに記載されています。

各項目を参照できるように、テーブルの構成列を定義します。 applicationclient など、入れ子の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://<export-bucket-name>/awsma/events/<app-id>/2017/12/13/01'
    PARTITION
        (created_at='2017-12-13_02')
    LOCATION
        's3://<export-bucket-name>/awsma/events/<app-id>/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のデータにクエリを実行することができました。 日時のバッチ集計などにも利用できそうなので、積極的に活用していきたいですね!