S3アクセスログに追加された項目(署名バージョンなど)をAthenaでクエリできるようにする

S3において署名バージョン2(SigV2)が廃止される（廃止されない）

AWS署名バージョン２が2019年6月24日に廃止されることになりました
https://docs.aws.amazon.com/ja_jp/AmazonS3/latest/dev/UsingAWSSDK.html#UsingAWSSDK-sig2-deprecation

2019/07/10更新
SigV2は廃止されないことになったみたいです
今後SigV2は移行を強く推奨するといったスタンスみたいです

そこで、社内のプロダクトにおいてS3のアクセスに使用しているライブラリおよび署名バージョンの調査が必要になりました

S3のアクセスログに項目が追加

それにともなってか、S3のアクセスログに項目が追加されました
https://docs.aws.amazon.com/AmazonS3/latest/dev/LogFormat.html

Athenaでテーブルを作る

以下を参考にアクセスログの分析をしました
https://docs.aws.amazon.com/AmazonS3/latest/dev/LogFormat.html

上記のドキュメントでは、まだS3に追加された以下の項目に対応できていませんので
CRATE TABLEは少し追記する必要があります
参考： https://dev.classmethod.jp/cloud/aws/s3-accesslog-new-fields/

項目	例	内容
Host Id	s9lzHYxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx	リクエストID
Signature Version	SigV2	署名バージョン
Cipher Suite	ECDHE-RSA-AES128-GCM-SHA256	暗号スイート
Authentication Type	AuthHeader	認証タイプ
Host Header	s3-us-west-2.amazonaws.com	接続先S3エンドポイント
Ssl Protocol	TLSv1.2	暗号化プロトコル

以下のようにテーブルを作成しました

 CREATE EXTERNAL TABLE IF NOT EXISTS database.hogehoge(
   bucketowner STRING,
   bucket STRING,
   requestdatetime STRING,
   remoteip STRING,
   requester STRING,
   requestid STRING,
   operation STRING,
   key STRING,
   request STRING,
   httpstatus STRING,
   errorcode STRING,
   bytessent BIGINT,
   objectsize BIGINT,
   totaltime STRING,
   turnaroundtime STRING,
   referrer STRING,
   useragent STRING,
   versionid STRING,
   hostid STRING,
   signatureversion STRING,
   ciphersuite STRING,
   authenticationtype STRING,
   hostheader STRING,
   sslprotocol STRING)
 ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
 WITH SERDEPROPERTIES (
 'serialization.format' = '1',
   'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \\"(.*?)\\" (- |[^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \\"(.*?)\\" \\"(.*?)\\" ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*)$'
 )LOCATION 's3://bucket/accesslog/'

正規表現があまり理解できてないのですが とりあえず動きそうです

以上