Amazon Athena LTSV形式のログファイルを探索する

はじめに

本日は、Amazon Athenaを利用して、LTSV形式のログファイルをクエリする方法をご紹介します。、LTSVフォーマットバージョンのWebサーバのアクセスログを例にテーブル定義・クエリを実行してみます。

LTSVとは

LTSVは、Labeled Tab-Separated Values の略で、key:value形式のタブ区切りのテキストフォーマットです。主にログ、特に httpd のアクセスログなどに利用されるのが一般的です。

LTSVフォーマットはもともとWebサーバのアクセスログに焦点を当てているので、従来のCombined Log Formatのアクセスログと、LTSVフォーマットバージョンと同じログを例として示します。

ApacheのこれまでのCombinedログ形式の設定は次のとおりです：

LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-agent}i\"" combined

上記の設定で出力されるアクセスログは、次のようになります（参考文献http://httpd.apache.org/docs/2.2/logs.html）

127.0.0.1 - frank [10/Oct/2000:13:55:36 -0700] "GET /apache_pb.gif HTTP/1.0" 200 2326 "http://www.example.com/start.html" "Mozilla/4.08 [en] (Win98; I ;Nav)"

上記と同じ情報を持つLTSV形式の設定は次のようになります:

LogFormat "host:%h\tident:%l\tuser:%u\ttime:%t\treq:%r\tstatus:%>s\tsize:%b\treferer:\%{Referer}i\tua:%{User-Agent}i" combined_ltsv

上記の設定で出力されるアクセスログは次のようになります(はタブ文字に置きかえます。）:

host:127.0.0.1ident:-user:franktime:[10/Oct/2000:13:55:36 -0700]req:GET /apache_pb.gif HTTP/1.0status:200size:2326referer:http://www.example.com/start.htmlua:Mozilla/4.08 [en] (Win98; I ;Nav)

テーブル定義

上記の形式をAthenaのテーブルとして定義します。(参考：Using a SerDe)

CREATE EXTERNAL TABLE accesslog_ltsv (
record map<string,string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\n'
COLLECTION ITEMS TERMINATED BY '\t'
MAP KEYS TERMINATED BY ':'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3:///accesslog_ltsv/';

• ltsv形式の1レコードを 1項目の map<string,string> 型（Presto のMAP型）として定義する
• 項目の区切り文字を TAB '\t' とする
• MAP KEYを ':' とする

クエリーでレコードを参照する

SELECTすると以下のように、1レコード分がすべて1項目に出力されてしまいます。

SELECT * FROM accesslog_ltsv;

{referer=http://www.example.com/start.html, size=2326, ident=-, host=127.0.0.1, time=[10/Oct/2000:13:55:36 -0700], ua=Mozilla/4.08 [en] (Win98; I ;Nav), user=frank, req=GET /apache_pb.gif HTTP/1.0, status=200}

以下のように、map型の項目にKEY指定して選択すると各項目ごとに抽出が可能です。

SELECT
record['host'] AS host,
record['ident'] AS ident,
record['user'] AS user,
record['time'] AS time,
record['req'] AS req,
record['status'] AS status,
record['referer'] AS referer,
record['ua'] AS ua
FROM accesslog_ltsv;

最後に

LTSVのテーブル定義は、MAP型の単一のカラムとして定義しますので、テーブル定義は共通になりますが、クエリを実行する際にkeyを指定して各項目ごとデータを取り出します。keyに指定する項目は、ログの出力フォーマットに指定した項目名になります。LTSV用の汎用的なテーブルを事前に作成しておいて、必要なときにファイルを配置、クエリーを実行できるようにしておくと便利だと思います。