🔎

FireLens の JSON ログを Athena でクエリする

2021/05/22に公開

AWS

前回の記事で FireLens 経由で送られたログから S3 に必要なログだけ抽出しました.今回は Athena でそのログをテーブル作ってクエリを投げてみます.

対象のログファイル中身

S3 に保存したファイルは次のような形式で保存されているんですが,このログファイルをクエリできるようにテーブル作ります.

{
    "request_headers": {
        "x-forwarded-for": "xx.xx.xx.xx",
        "x-forwarded-proto": "http",
        "x-forwarded-port": "xxxx",
        "host": "xx.xx.xx.xx:xxxx"
    },
    "remote_addr": "xx.xx.xx.xx",
    "request_uri": "/",
    "request_method": "GET",
    "request_time": "2021/05/20 15:17:02",
    "response_time": "0.0004",
    "status": 200,
    "response_headers": {
        "content-length": "xx",
        "content-type": "application/json"
    }
}

Athena のテーブル作成クエリ

Athena のテーブルは下記のクエリで作りました.JSON の中で内包されている JSON にもクエリできるよう struct 以降で定義しています.また,ログはYYYY/MM/DDのフォルダ配下に入っているので,Partition Projection を使っています.

CREATE EXTERNAL TABLE IF NOT EXISTS `partition_table`(
  `request_headers` struct<`x-forwarded-for`:string,`x-forwarded-proto`:string,`x-forwarded-port`:string,`host`:string>, 
  `remote_addr` string, 
  `request_uri` string , 
  `request_method` string , 
  `request_time` string , 
  `response_time` string , 
  `status` bigint , 
  `response_headers` struct<`content-length`:string, `content-type`:string>
  )
PARTITIONED BY (
  `dateday` string 
)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe'
LOCATION
  's3://バケット名/パス'
TBLPROPERTIES (
  'has_encrypted_data'='false',
  'projection.enabled' = 'true',
  'projection.dateday.type' = 'date',
  'projection.dateday.range' = '2021/05/20,NOW',
  'projection.dateday.format' = 'yyyy/MM/dd',
  'projection.dateday.interval' = '1',
  'projection.dateday.interval.unit' = 'DAYS',
  'storage.location.template' = 's3://バケット名/パス/${dateday}'
);

クエリを投げてみる

作ったテーブルに対してクエリを投げてみます.

SELECT * FROM "xxx"."partition_table" where dateday = '2021/05/20';

こんな感じで返ってきます.

1	{x-forwarded-for=null, x-forwarded-proto=null, x-forwarded-port=null, host=xx.xx.xx.xx:xxxx}	xx.xx.xx.xx	/	GET	2021/05/20 09:41:18	0.0005	200	{content-length=xx, content-type=application/json}	2021/05/20
2	{x-forwarded-for=null, x-forwarded-proto=null, x-forwarded-port=null, host=xx.xx.xx.xx:xxxx}	xx.xx.xx.xx	/	GET	2021/05/20 11:59:25	0.0004	200	{content-length=xx, content-type=application/json}	2021/05/20
3	{x-forwarded-for=null, x-forwarded-proto=null, x-forwarded-port=null, host=xx.xx.xx.xx:xxxx}	xx.xx.xx.xx	/	GET	2021/05/20 09:18:47	0.0004	200	{content-length=xx, content-type=application/json}	2021/05/20

内包されている JSON にクエリしてみます.例えば,request_headerの中にあるhostをクエリしてみます.

SELECT request_headers."host" FROM "xxx"."partition_table" where dateday = '2021/05/20';

これでhostにはいっている IP アドレスだけ返ってきます.これでログから必要な情報をクエリしてきそうです.

1	xx.xx.xx.xx:xxxx
2	xx.xx.xx.xx:xxxx
3	xx.xx.xx.xx:xxxx

FireLens の JSON ログを Athena でクエリする

対象のログファイル中身

Athena のテーブル作成クエリ

クエリを投げてみる

参考情報

Discussion