Amazon Athenaを使ってS3サーバーアクセスログをパースしてみた

皆さんこんにちは！

クルトンです。

今回はAmazon Athena(以下、Athena)で、Create Tableをする際にログデータをパースする方法を調べて実際にやってみました。

こちらの記事を参考に進めています。

今回使うデータの説明

今回使用するサンプルのアクセスログのデータはこちらのものを使います。S3バケットのアクセスログです。

5個のレコードがあるので、ローカルにテキストファイル(ファイル拡張子はtxtとして)保存してください。

また、同ページにログデータのフォーマットが記載されています。どういったデータがどのようなフォーマットで1レコードの中に含まれているかが書かれています。

パースする時に重要なのは、ログのフォーマットがどうなっているかを知っておく事です。フォーマットとして、どういったデータが格納されているかを知らなければ、どのようにパースをすれば良いか分からないためです。是非、ご確認ください。

アクセスログデータの格納先

Amazon Athenaは内部でAWS Glueと連携しており、AWS Glueでテーブルの設定やデータベースの作成が出来ます。 テーブルデータの作成にはS3 URIを使っているため、まず最初に、ローカルに保存したアクセスログのデータをS3へ保存してください。

AWS Glue上でマネージメントコンソールを使ってデータベースの作成などの操作をする事も可能ですが、今回はAthena上でデータベース作成やテーブルの作成を行います。

データベースの作成

Athenaの画面上で次のクエリを実行してください。データベースが作成されます。

create database <お好きなデータベース名>

AWS Glue上では次の画像で示している、Data catalogのDatabaseを確認するとデータベースが作成されているか確認が出来ます。

エラー

ちなみに同じ名前で作成しようとすると、次のようなエラーが出ます。

[ErrorCategory:USER_ERROR, ErrorCode:DDL_FAILED], Detail:FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database <作成しようとしたデータベース名> already exists

データベース名を別名にするか、同名の既に存在しているデータベースを削除してから改めてcreate databaseをしてください。

テーブルの作成

CREATE EXTERNAL TABLE `<作成したデータベース名>.<作成したいテーブル名>`(
	`bucketowner` STRING,
	`bucket_name` STRING,
	`requestdatetime` STRING,
	`remoteip` STRING,
	`requester` STRING,
	`requestid` STRING,
	`operation` STRING,
	`key` STRING,
	`request_uri` STRING,
	`httpstatus` STRING,
	`errorcode` STRING,
	`bytessent` BIGINT,
	`objectsize` BIGINT,
	`totaltime` STRING,
	`turnaroundtime` STRING,
	`referrer` STRING,
	`useragent` STRING,
	`versionid` STRING,
	`hostid` STRING,
	`sigv` STRING,
	`ciphersuite` STRING,
	`authtype` STRING,
	`endpoint` STRING,
	`tlsversion` STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
	'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$'
)
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION '<テキストファイルを保存したS3バケット名>'

これで実際にテーブルが作成されました。

それぞれの句の説明は次の通りになります。

• CREATE EXTERNAL TABLE
  • Athena上でテーブルを作成する時に使用
  • 指定した名前でテーブルが作成されます
  • EXTERNALを入れている事でファイルの置き場所を指定するLOCATIONを使う事を意味します
• ROW FORMAT SERDE
  • フォーマットの指定をしています
  • 今回はHiveの正規表現の書き方をするという指定になります
• WITH SERDEPROPERTIES
  • 正規表現の中身を書いています
  • 正規表現の中身については、そもそものデータを格納する際にログフォーマットが指定されているかと思いますので、そちらを参考に書けば良いかと思います。
• STORED AS INPUTFORMAT
  • 元データのファイルフォーマットを選択します
  • 今回はテキストファイルを使うという指定です
• OUTPUTFORMAT
  • 出力時のデータのフォーマットを指定します
• LOCATION
  • 対象ファイルの保存先を指定します
  • ファイル保存先の指定としてS3 URIを使用します。

これでデータベースに紐づいたテーブルが作成できました。実際にデータがどのように入っているか確認したい場合は、SQL文を実行して確認可能です。

作成したテーブルの中身について簡単に確認するには、作成したテーブル名の右端にある⋮をクリックし、次の画像で表示されるメニューの中にある、テーブルをプレビューをクリックしてください。

テーブルをプレビューをクリックすると、次のように10レコード表示されるようなSQL文をクエリエディタ上で実行してくれます。

SELECT * FROM "<データベース名>"."<テーブル名>" limit 10;

終わりに

今回は簡単にですが、アクセスログデータを使って、パースをしてみました。

様々なデータ形式に対応しているので、データを手早く確認したい場合にAthenaは便利そうですね。

今回はここまで。

それでは、また！

参考にしたサイト