はじめに
ウェブアプリケーションの運用で、ログを解析するとき、
解析専用サーバを構築したり、有料の解析ソフトを使うケースが一般的だと思うが、
生ログをエクセルなどで解析するケースも稀ではないように思う。
たとえば、ALBのアクセスログはS3に保存することができるが、
ファイルは5分毎に分割され、gz形式で圧縮されているため、ログの中身を見るには多少手間がかかる。
Amazon Athenaでは、S3のログを、分割され圧縮された状態でも、
とくにそういったファイルの状態を意識することなくデータの抽出を行うことができる。
そこで本稿では、S3に出力されたログに対し、Amazon Athenaを使ってクエリを実行する方法を紹介する。
Amazon Athenaとは
S3内のデータを、SQLクエリを使って直接抽出できる。
サーバレスにクエリを実行でき、Apache Sparkと併用することも可能。
大規模データでも高速な処理が可能で、料金はクエリ実行時にスキャンしたデータ量に対してのみ発生。
東京リージョンでは、1TBあたり5.00ドルが課金される。
Amazon Athenaを利用するには
AWSマネジメントコンソールで、Amazon Athenaを開く。
クエリエディタを開き、まずはデータベースおよびテーブルの作成を行う。
クエリ結果の保存場所を作成
S3に、クエリの保存先用の場所を作成する。
クエリエディタの、[設定] 、 [管理]から設定する。
データベースの作成
Amazon Athenaでは、データベースを定義することができる。
データベースを分けておくと、それぞれのデータベースに同じ名前のテーブルを作成できるため、
クエリを使いまわすときなどは便利だと思うが、作成しなくても特に問題はない。
データベースを作成する場合は、クエリエディタから下記の様なCREATE文を実行する。
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
テーブルの作成
クエリエディタから次のようなDDLステートメントを実行する。
下記は、パーティション射影（Partition Projection）を使用したテーブル作成の例文。
パーティション射影によってパーティション管理を自動化することができる。
CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs (
type string,
time string,
elb string,
client_ip string,
client_port int,
target_ip string,
target_port int,
request_processing_time double,
target_processing_time double,
response_processing_time double,
elb_status_code int,
target_status_code string,
received_bytes bigint,
sent_bytes bigint,
request_verb string,
request_url string,
request_proto string,
user_agent string,
ssl_cipher string,
ssl_protocol string,
target_group_arn string,
trace_id string,
domain_name string,
chosen_cert_arn string,
matched_rule_priority string,
request_creation_time string,
actions_executed string,
redirect_url string,
lambda_error_reason string,
target_port_list string,
target_status_code_list string,
classification string,
classification_reason string
)
PARTITIONED BY
(
day STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1',
'input.regex' =
'([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) (.*) (- |[^ ]*)\" \"([^\"]*)\" ([A-Z0-9-_]+) ([A-Za-z0-9.-]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^\"]*)\" ([-.0-9]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^ ]*)\" \"([^\s]+?)\" \"([^\s]+)\" \"([^ ]*)\" \"([^ ]*)\"')
LOCATION 's3://your-alb-logs-directory/AWSLogs/<ACCOUNT-ID>/elasticloadbalancing/<REGION>/'
TBLPROPERTIES
(
"projection.enabled" = "true",
"projection.day.type" = "date",
"projection.day.range" = "2022/01/01,NOW",
"projection.day.format" = "yyyy/MM/dd",
"projection.day.interval" = "1",
"projection.day.interval.unit" = "DAYS",
"storage.location.template" = "s3://your-alb-logs-directory/AWSLogs/<ACCOUNT-ID>/elasticloadbalancing/<REGION>/${day}"
)
パーティション射影を使用するには、テーブルプロパティでパーティションキーのルールやフォーマットを指定する。
上の例では、projection.day.rangeで対象とするデータの期間を2022年1月1日から現在まで指定し、
strage.location.templateで、S3のログファイルの場所を指定している。
また、データベースを指定したい場合は、クエリエディタ付近にあるプルダウンから
任意のデータベースを指定するか、下記の様にデータベース名を指定してもよい。
CREATE EXTERNAL TABLE IF NOT EXISTS database_name.alb_logs
テーブル作成のDDLステートメントに関する詳細はAWS公式ドキュメントから確認できる。
パーティション射影を使用した Athena での ALB ログ用のテーブルの作成
パーティションを使用するメリット
パーティション化することで、下記2点に対してメリットを得られる。
- 検索速度
- コスト
検索速度
パーティションを指定しない場合、クエリを実行する際はすべてのデータが検索対象となる。
対象データが多いと、検索結果を得られるまで時間がかかる場合がある。
コスト
前項で述べた通りテーブルをパーティション化すると、クエリ実行時のスキャン対象データを限定できるようになる。
Amazon Athenaの料金はスキャン対象のデータ量に対して課金されるため、
スキャン対象のデータが少なければ当然請求金額も少なくなり、コスト面でのメリットになる。
クエリを実行してみる
それではここでいくつかのクエリを紹介してみたいと思う。
日時の範囲指定
まずは、WHERE句で、抽出したいログの時間帯を範囲指定するクエリを紹介する。
日付型への変換とタイムゾーンの変更
LBのログに出力される日時は、タイムゾーンがUTCで、文字列型であるため、
WHERE句で時間や日時を指定するには、parse_datetimeを使用しdate型に変換する。
また、WHERE句で日時指定する際に日本時間を使用したい場合は、タイムゾーンにAsia/Tokyoを指定する。
下記は、日本時間2023年1月1日から1月10日までを指定するクエリ。
SELECT parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') AT TIME ZONE 'Asia/Tokyo' as time, request_verb, request_url, client_ip, elb_status_code, target_status_code, target_processing_time, response_processing_time, user_agent FROM alb_logs WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') AT TIME ZONE 'Asia/Tokyo' BETWEEN timestamp '2023-01-01 00:00 Asia/Tokyo' AND timestamp '2023-01-10 23:59 Asia/Tokyo' ORDER BY time;
1時間ごとのアクセス数を集計
次に紹介するのは時間毎のアクセス数を集計するクエリ。
substrでtimeのYYYY-MM-DDTHH部分を抽出
substr関数を使用し、アクセス日時の先頭から13文字を抽出する。
同じ時刻のデータをcountし、1時間当たりのアクセス数を集計する。
下記例では、2023年1月1日から1月11日までの、ステータスコードが504および460の、1時間ごとのアクセス数を集計する。
SELECT substr(time,1,13) AS time_every_hour, count(target_status_code) FROM alb_logs WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') AT TIME ZONE 'Asia/Tokyo' BETWEEN timestamp '2023-01-01 00:00 Asia/Tokyo' AND timestamp '2023-01-11 23:59 Asia/Tokyo' AND (elb_status_code = 504 OR elb_status_code = 460) group BY substr(time,1,13) order by time_every_hour
array型のカラムをWHERE句やSELECTで指定
ALBのログでは該当しないが、WAFのログの様にarrayデータ型のカラムから部分的にデータを取得したり、
WHERE句でarrayデータ型のカラムを条件指定の対象に使用したいときは、UNNEST演算子を使用する。
配列型の要素を1行のデータに変換
例えばWAFのログのlabelsは、下記の様な内容になっている。
"labels":[{"name":"awswaf:managed:token:absent"},{"name":"awswaf:managed:aws:bot-control:bot:verified"},{"name":"awswaf:managed:aws:bot-control:bot:category:search_engine"},{"name":"awswaf:managed:aws:bot-control:bot:name:googlebot"}]
下記は、任意のWAFルールに該当したリクエストをWHERE句で指定し抽出するクエリで、
labelsをネストし、labelsのnameをWHERE句で指定できるようにしている。
事前に、テーブル名が「waf_logs」というAWS WAFログのテーブルを作成しているものとする。
SELECT from_unixtime(timestamp / 1000, 'Asia/Tokyo') as jst, action, terminatingrulematchdetails, httprequest, label.name FROM waf_logs, UNNEST(labels) t(label) WHERE from_unixtime(timestamp / 1000, 'Asia/Tokyo') BETWEEN timestamp '2023-01-01 00:00' AND timestamp '2023-01-31 23:59' AND (label.name LIKE '%HostingProviderIPList%' OR label.name LIKE '%Custom_MicrosoftAds_NotMicrosoftAds%') ORDER BY timestamp;
さいごに
ファイルの中身をダイレクトにクエリできるAmazon Athenaは、とても便利で使い勝手がいいサービスだと思う。
対象の複数ファイルをダウンロードしたり、解凍したりする必要がなく、
慣れ親しんだSQLでクエリできるため、S3にあるファイルでも時間をかけずに解析できる。
利用したことがない人はぜひ試してみてほしい。
