Amazon #QuickSight で ALBログを見える化する

ALBのアクセスログを見える化して何かに役立てたい。ありがちな話だろ思います。

ALBログ

ALBログはS3に保存されている。ここまではぐぐってどうぞ。

s3://alb_logs_bucket/2018/04/19

Athena

Athenaでログをテーブルに。NewQueryから下記Query文を入力して実行するとalb_logsというテーブルが作成される。(左カラム Create tableを実行しているのと同じ)

CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs_tbl (
	type string,
	request_timestamp string,
	elb_name string,
	client_addrport string,
	client_ip string,
	client_port int,
	target_addrport string,
	target_ip string,
	target_port int,
	request_processing_time double,
	target_processing_time double,
	response_processing_time double,
	elb_status_code string,
	target_status_code string,
	received_bytes int,
	sent_bytes int,
	request 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)
	ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
	WITH SERDEPROPERTIES (
	  'serialization.format' = '1',
	  'input.regex'='([^ ]*) ([^ ]*) ([^ ]*) (([^ ]*):([^ ]*)|-) (([^ ]*):([^ ]*)|-) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \"([^\\\"]*)\" \"([^\\\"]*)\" ([^ ]*) ([^ ]*) ([^ ]*) \"([^\\\"]*)[ ]*\" \"([^ ]*)\" \"([^ ]*)\" ([^ ]*) ([^ ]*) \"([^ ]*)\" \"([^\\\"]*)\".*$'
) 
LOCATION 's3://alb_logs_bucket/2018/04/19/'

LOCATIONを/で閉じるのを忘れがち。
もちろん下記のように指定してもOK。

LOCATION 's3://alb_logs_bucket/'

null行の入ったログがあることがある。そのとき、QuickSightで解析時にエラーが出てdbを読み込むことができなくなる。そのためにはnull行を除外する。

CREATE OR REPLACE VIEW alb_logs_view AS
SELECT *
FROM alb_logs.alb_logs_tbl
WHERE request_timestamp IS NOT NULL;

ビューを確認する。

select * from alb_logs_view

下部Resultsに内容が表示され、null行がなければ成功。

ログなので件数が多く実行に時間がかかることもある。そんなときはひとまず10件、100件だけ実行するというのも一つの手。ただし絞った先にnull行が存在せず、外にnull行がある、なんてこともあるが・・・。

LIMIT 100

QuickSight

QuickSightを使う。

New Data set→Athenaを選択

適当な名前を決めて、Create data source

作成したいデータを選択してSelect

取り込めましたよーと言われるのでVisualize

QuickSightのメイン画面に移り、データの読み込みが開始される。データ量によってはだい～～～ぶ時間がかかる。

ログの数が多すぎてエラー吐いた。SPICEの容量が足りませんですよ、って。

データ取り込み最中のキャプチャ。600万件超えたあたりまでは目視で確認してたけど。ログを絞ってやり直そう。

テーブル作成時に複数フォルダを選択する方法が分からなかった。

LOCATION 's3://for-production-alb-log/tss/AWSLogs/882785560366/AWSLogs/882785560366/elasticloadbalancing/ap-northeast-1/2018/[10-12]/'

こんなことしたけど駄目だったので一先ず各月のテーブルを作ってからビュー作成時に結合した。

CREATE OR REPLACE VIEW alb_logs_08to10 AS
SELECT *
FROM alb_logs_tbl_08
UNION
SELECT *
FROM alb_logs_tbl_09
UNION
SELECT *
FROM alb_logs_tbl_10;

#AWS #Athena #QuickSight #Amazon #見える化