Athenaでパースできるログフォーマットを指定する

ELB使ってるのでせっかくならAthenaにログを突っ込んでアクセス解析げなことをしようと思った。
しかしながら下記サンプルにある正規表現が最近のELBのフォーマットと違うのかパースに失敗して空データになった。
だから自分でパースできるログフォーマットを指定することにした。

https://aws.amazon.com/jp/blogs/big-data/analyzing-data-in-s3-using-amazon-athena/

参考にしたのは上記ページのクエリ。
しかしこのクエリをそのまま流しても幾つか違う点がありパース出来ていないようだった。

https://docs.aws.amazon.com/ja_jp/elasticloadbalancing/latest/application/load-balancer-access-logs.html

そこでパース出来ていない部分が何なのか地道に調べつつパースできるようにperlスクリプトで実際のログをパースするという涙なしには語れない面倒くさい作業をすることとなった。
そのperlスクリプトがこちら。
注意点としては本来なら2カラムに分かれているip:portをスペースでjoinする関係で1カラムにしていること。

use strict;
use warnings;

my @arr = (
  { type                    => '([^ ]*)'},
  { timestamp               => '([^ ]*)'},
  { elb_name                => '([^ ]*)'},
  { request_ip_port         => '([^ ]*):([0-9]*)'},
  { backend_ip_port         => '([^ ]*):([0-9]*)'},
  { request_processing_time => '([^ ]*)'},
  { backend_processing_time => '([^ ]*)'},
  { client_response_time    => '([^ ]*)'},
  { elb_response_code       => '([^ ]*)'},
  { backend_response_code   => '([^ ]*)'},
  { received_bytes          => '([^ ]*)'},
  { sent_bytes              => '([^ ]*)'},
  { request_verb            => '\"([^ ]*)'},
  { url                     => '([^ ]*)'},
  { protocol                => '([^"]*)\"'},
  { user_agent              => '\"([^"]*)\"' },
  { ssl_cipher              => '([^ ]*)' },
  { ssl_protocol            => '([^ ]*)' },
  { target_group_arn        => '([^"]*)' },
  { trace_id                => '\"([^"]*)\"' },
  { domain_name             => '\"([^"]*)\"'},
  { chosen_cert_arn         => '\"([^"]*)\"$'},
);

my $regexp = join (' ', map { my @key = keys %$_; $_->{$key[0]} } @arr);

open (FILE, $ARGV[0]) or die "$!";
while () {
  if($_ =~ /$regexp/) {
    print $& . "\n";
  }
}
print $regexp . "\n";

close (FILE);

1;

上記スクリプトを

perl plgrep.pl {ファイル名}

などとしてELBの生ログを食わせてパースできるか確認、出来なければ出来ない部分をログとにらめっこしながら書き加えていく作業を行った。
最終的に出力された正規表現をinput.regexに指定して
結果、サンプルクエリは下記のようになった。先頭のtypeとか末尾のパラメータが増えているようだ。

CREATE EXTERNAL TABLE IF NOT EXISTS elb_logs_raw_native (
  type 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,
  target_group_arn string,
  trace_id string,
  domain_name string,
  chosen_cert_arn string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
         'serialization.format' = '1','input.regex' = '([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*):([0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \"([^ ]*) ([^ ]*) ([^"]*)\" \"([^"]*)\" ([^ ]*) ([^ ]*) ([^"]*) \"([^"]*)\" \"([^"]*)\" \"([^"]*)\"$' )
LOCATION 's3://{ログの保存場所}';

これを流し込むことでパースできた。結構疲れた。

コメントを残す