Athenaを使ってAWSのログを集計する

AWSのログ（ELB, CloudFront, CloudTrail）をS3に保存し、Athenaで集計してみました。
DDL定義も載せています。ドキュメント記載のものとは異なり、使いやすい形に変更しました。

Athena概要

• S3に保管したデータに対してクエリを実行できる
• インスタンスを起動せずにS3のデータに対して直接SQLのクエリ実行が可能
• ANSI SQLをサポート
• 内部的にはPrestoが実行されているため、Prestoで使えるものは大抵使える
• 課金はクエリ単位　１TBにつき$5

まだバージニアとオレゴンでしか使えない

• 東京リージョンではまだ使えません
• AthenaはS3を参照するので、Athena(バージニア)→S3(東京)にすると微妙に遅くなります

やってみる

データベースを作る

• 最初にデータベースを作ります
• デフォルトのやつでもいいです

sample

CREATE DATABASE aws_logs;

テーブルを作る

• GUIで入力もできますが、SQLを書いていきます
  • GUIでテーブル定義を入力するのが結構面倒です
  • 以下、DDL定義です

ELB

ELB

CREATE EXTERNAL TABLE IF NOT EXISTS aws_logs.elb_log (
  request_protocol string,
  request_timestamp string,
  elb_name string,
  request_ip string,
  request_port int,
  backend_ip string,
  backend_port int,
  request_processing_time double,
  backend_processing_time double,
  client_response_time double,
  elb_response_code string,
  backend_response_code string,
  received_bytes bigint,
  sent_bytes bigint,
  request_verb string,
  url string,
  protocol string,
  user_agent string,
  ssl_cipher string,
  ssl_protocol string,
  arn string,
  extra string )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'serialization.format' = '1','input.regex' = '([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*):([0-9]*) (-1|[.0-9]*) (-1|[.0-9]*) (-1|[.0-9]*) (-|[0-9]*) (-|[0-9]*) ([-0-9]*) ([-0-9]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (\"[^\"]*\") ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*)$' )
LOCATION 's3://*******/********/'

CloudFront

cloudfront

CREATE EXTERNAL TABLE IF NOT EXISTS aws_logs.cloudfront_log (
  date STRING,
  time STRING,
  xEdgeLocation STRING,
  scBytes INT,
  cIp STRING,
  csMethod STRING,
  csHost STRING,
  csUriStem STRING,
  scStatus INT,
  csReferer STRING,
  csUserAgent STRING,
  csUriQuery STRING,
  csCookie STRING,
  xEdgeResultType STRING,
  xEdgeRequestId STRING,
  xHostHeader STRING,
  csProtocol STRING,
  csBytes INT,
  timeTaken INT,
  xForwardedFor STRING,
  sslProtocol STRING,
  sslCipher STRING,
  xEdgeResponseResultType STRING,
  csProtocolVersion STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1','input.regex' = '([^ ]*)\t([^ ]*)\t([^ ]*)\t([0-9]*)\t([.0-9]*)\t([^ ]*)\t([^ ]*)\t([^ ]*)\t([0-9]*)\t([^ ]*)\t([^ ]*)\t([^ ]*)\t([^ ]*)\t([^ ]*)\t([^ ]*)\t([^ ]*)\t([^ ]*)\t([0-9]*)\t([^ ]*)\t([^ ]*)\t([^ ]*)\t([^ ]*)\t([^ ]*)\t([^ ]*)$'
) LOCATION 's3://****/******/'

CloudTrail

cloudtrail

CREATE EXTERNAL TABLE IF NOT EXISTS sample.records (
  records ARRAY<STRUCT<eventTime:STRING,eventSource:STRING,eventName:STRING>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://********/***/'

SELECTする

• ANSI SQL対応なので普通にSQLを実行してください
• CloudFrontとCloudTrailだけ注意してください

CloudFrontのSELECT注意点

• 上記のDDLを実行すると一部何も入ってないレコードができあがります
  • CloudFrontのログファイルは先頭2行にコメント&ヘッダーが入っています
  • 現状、Athenaではこれを除いて取り込むことができません
  • Prestoで使えるorg.apache.hadoop.hive.serde2.OpenCSVSerdeが使えるようになるのを気長に待ちましょう
• selectする際にはwhere句でdate NOT LIKE '#%'を指定するようにしましょう

sample-select

SELECT count(*) 
FROM aws_logs.cloudfront_log 
WHERE date NOT LIKE '#%'

CloudTrailのSELECT注意点

• CloudTrailのログはJSON形式のため、カラムを分けることが難しいです(方法あれば教えてください)
• selectするときは以下のようにする。面倒・・・

sample-trail-select

SELECT record.eventTime
FROM aws_logs.traillog
CROSS JOIN unnest(records) AS t(record)
WHERE record.eventname='ConsoleLogin' limit 10

sample-trail-select2

SELECT record
FROM aws_logs.traillog
CROSS JOIN unnest(records) AS t(record)
WHERE record.eventtime LIKE '2017-02-08%' limit 10

まとめ

• ELBとCloudFrontのログを投入するだけで、5XX系エラーなどの調査がかなり楽になります
• Trailログはもう一歩ですね。キレイにカラムで入れば、調査しやすい。
• 時刻が基本UTCなので、ちょっと注意が必要