概要

S3に保存したJSON形式のログファイルをAthenaのクエリで取得します。Athenaを初めて使ったので自分用のメモです。

準備

検索対象となるJSONログのフォーマット

以下のような形式です。ログイン履歴のようなファイルです。

log01.json

{
    "userName": "user01",
    "logBody": {
        "account": "123456789012",
        "time": "2018-08-30T00:45:28Z",
        "detail": {
            "clientIpAddress": "0.0.0.0",
            "actionType": "successfulLogin"
        }
    }
}

S3には「logs」フォルダを作成して、その中に以下の通りログを保存します。

Athenaの操作

1. workgroupの作成

Create workgroupから作成します。
次のページでの必須項目は以下の2つ
- Workgroup name
  - 任意の名前
- Query result location
  - 実行結果を保存する場所です。S3のバケットなどを指定します。分析したいログがあるフォルダを選ぶと、その次のクエリ実行時に実行結果ファイルも取得対象になってしまいます。

別のワークグループがある場合、Switch workgroupボタンで切り替えます。
ワークグループを切り替えても、右上のsettingsでQuery result locationを確認しましょう。変わっていないことがあります。変わっていない場合には、こちらも変更します。

2. DBとテーブルの作成

コンソールとクエリの2通りで作成できます。但し、JSONで用いるstructタイプはコンソールからだと作成できません。なので、クエリで作成して実行します。
ここでは、sampleというDBにlogin_logsという名前のテーブルを作成しています。

tableCreate

CREATE EXTERNAL TABLE IF NOT EXISTS sample.login_logs (
         `userName` string,
         `logBody` struct<
            `account`: string,
            `time`: string,
            `detail`: struct<
                `clientIpAddress`: string,
                `actionType`: string
             >
          >
  )
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ('ignore.malformed.json' = 'true' )
LOCATION 's3://<Bucket Name>/logs/'
TBLPROPERTIES ('has_encrypted_data'='false' );

ポイントになるのは以下の点です。
- LOCATIONでクエリ対象としたいログがあるバケット/フォルダを指定します。サブフォルダがある場合、再帰的に検索対象になるため、トップのフォルダを指定します。
- JSONで入れ子になっている箇所はstructを用います。struct内は:で項目名と定義を挟んでいます。
- `(バッククォート)で囲っているのは、予約語と被らないようにするためです。大半は被りませんが、囲った方が安全でしょう。
- 'ignore.malformed.json' = 'true'は誤った形式のレコードを無視するためです。詳細は以下公式を参照。
  - JSON データ読み取りのベストプラクティス
実行すると以下のようなテーブルができます。JSON部分は構造体として作成されるため、カラムは2つとなります。

3. クエリの実行

以下のようなselectクエリを作って実行します。

select

select
    userName as Username,
    logBody.account as Account,
    logBody.time as Time,
    logBody.detail.clientIpAddress as ClientIpAddress,
    logBody.detail.actionType as ActionType
from sample.login_logs

実行結果

例として、「ログイン失敗のみ」という条件で絞るなら、以下のようなwhere句をつけます。

select

select
    userName as Username,
    logBody.account as Account,
    logBody.time as Time,
    logBody.detail.clientIpAddress as ClientIpAddress,
    logBody.detail.actionType as ActionType
from sample.login_logs
where logBody.detail.actionType = 'failureLogin'

実行結果

その他注意事項

先述の通り、Query result locationを検索したいログと同じフォルダにした場合、そこに作成された結果のフォルダ(Unsaved)も検索対象となってしまい、クエリの実行結果に余計な結果が混ざることになります。
例として、以下のようにjsonファイルと同じディレクトリにUnsaved等が作成されると、Unsavedも検索対象となります。
ログの形式が合わないため、大半は空白行が混ざります。同じ症状の方は結果の出力先を確認ください。

概要

準備

検索対象となるJSONログのフォーマット

Athenaの操作

1. workgroupの作成

2. DBとテーブルの作成

3. クエリの実行

その他注意事項

参考