S3のアクセスログをAthenaで分析してみた

概要

S3のアクセスログをAthenaで分析してみます。

  1. S3のロギング設定をします。
    http://docs.aws.amazon.com/ja_jp/AmazonS3/latest/dev/ServerLogs.html

    ロギングはデフォルトでは無効になっています。ロギングを有効にするには、以下を実行する必要があります。
    Amazon S3 によるアクセスログ配信の対象とするバケットにロギング設定を追加することにより、ログ配信をオンにします。このバケットをソースバケットと呼びます。
    Amazon S3 Log Delivery グループに、アクセスログを保存するバケット上での書き込み許可を与えます。このバケットをターゲットバケットと呼びます。

  2. AthenaでS3のアクセスログを分析します。
    https://aws.amazon.com/jp/athena/faqs/

    Amazon Athena はインタラクティブなクエリサービスで、Amazon S3 内のデータを標準 SQL を使用して簡単に分析できるようになります

Athenaの設定

公式に沿ってやってみます。
http://docs.aws.amazon.com/athena/latest/ug/getting-started.html

データベース作成

まずはデータベースを作成します。

CREATE DATABASE s3_AccessLogsDB;

テーブル作成

次にテーブルを作成します。

S3アクセスログ形式を参考に作っていきます。
https://docs.aws.amazon.com/ja_jp/AmazonS3/latest/dev/LogFormat.html

が、以下サイトに良いサンプルがありましたのでそのまま使わせて頂きました。
http://aws.mannem.me/?p=1462

CREATE TABLE DDL :

末尾のLOCATIONにロギング設定したS3のバケット名を記載します。

CREATE EXTERNAL TABLE IF NOT EXISTS s3_AccessLogsDB.Accesslogs(
  BucketOwner string,
  Bucket string,
  RequestDateTime string,
  RemoteIP string,
  Requester string,
  RequestID string,
  Operation string,
  Key string,
  RequestURI_operation string,
  RequestURI_key string,
  RequestURI_httpProtoversion string,
  HTTPstatus string,
  ErrorCode string,
  BytesSent string,
  ObjectSize string,
  TotalTime string,
  TurnAroundTime string,
  Referrer string,
  UserAgent string,
  VersionId string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
 'serialization.format' = '1',
  'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\") ([^ ]*)$'
) LOCATION 's3://s3-server-access/logs/'

クエリ実行

PUT回数をカウントしてみました。

SELECT RequestURI_operation, count(*) AS cnt 
FROM Accesslogs
WHERE RequestURI_operation LIKE '%PUT%' 
GROUP BY RequestURI_operation
ORDER BY cnt DESC;

f:id:onoxeve:20170714002936p:plain