ELBのアクセスログを MySQLにロードする

やりたいこと

ELBのアクセスログから集計を行う手段として　awkでやるよりも 小慣れた MySQLで行った方が自由に集計を行えるので、ELBのログをMySQLに投入します。

ELB のアクセスログ

フィールド	意味
timestamp	ロードバランサーがクライアントからリクエストを受け取った時刻（ISO 8601 形式）
elb	ロードバランサーの名前
client:port	リクエストを送信したクライアントの IP アドレスとポート。
backend:port	このリクエストを処理した登録済みインスタンスの IP アドレスとポート。クライアントがリクエスト全体を送信しなかった場合、ロードバランサーは登録済みインスタンスにリクエストをディスパッチできず、この値が - に設定されます。
request_processing_time	ロードバランサーがリクエストを受け取った時点から登録済みインスタンスに送信するまでの合計経過時間 (秒単位)。ロードバランサーがリクエストを登録されたインスタンスにディスパッチできない場合、この値は -1 に設定されます。
backend_processing_time	ロードバランサーが登録済みインスタンスにリクエストを送信した時点から、そのインスタンスが応答ヘッダーの送信を開始した時点までの合計経過時間（秒単位）。
response_processing_time	ロードバランサーが登録済みインスタンスから応答ヘッダーを受け取った時点から、クライアントへの応答の送信を開始した時点までの合計経過時間（秒単位）。これには、ロードバランサーでの待機時間と、ロードバランサーからバックエンドへの接続の取得時間の両方が含まれます。
elb_status_code	ロードバランサーからの応答のステータスコード。
backend_status_code	登録済みインスタンスからの応答のステータスコード。
received_bytes	クライアント（リクエスタ）から受け取ったリクエストのサイズ（バイト単位）。この値にはリクエストの本文が含まれますがヘッダーは含まれません。
sent_bytes	クライアント（リクエスタ）に返される応答のサイズ（バイト単位）。（HTTP リスナーの場合）この値には応答の本文が含まれますがヘッダーは含まれません。
リクエスト	クライアントからのリクエスト行は二重引用符で囲まれており、次の形式でログに記録されます。HTTP メソッド + プロトコル://ホストヘッダー:ポート + パス + HTTP バージョン。
user_agent	リクエスト元のクライアントを特定する User-Agent 文字列。この文字列は、1 つ以上の製品 ID（製品[/バージョン]）から構成されます。文字列が 8 KB より長い場合は切り捨てられます。
ssl_cipher	SSL 暗号。正常なネゴシエーションの後に受信 SSL/TLS 接続が確立した場合にのみ、この値が記録されます。それ以外の場合、値は - に設定されます。
ssl_protocol	SSL プロトコル。正常なネゴシエーションの後に受信 SSL/TLS 接続が確立した場合にのみ、この値が記録されます。それ以外の場合、値は - に設定されます。

投入先のテーブル

ログファイルを投入するテーブルのDDLです。カラム名はログファイルのフィールド名を用います。indexは特定の時間帯でのアクセスを集計したいのでタイムスタンプだけに設定しました。

create-eld_log.sql

CREATE TABLE `elb_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `access_timestamp` varchar(32)  NOT NULL,
  `elb` varchar(45)  DEFAULT NULL,
  `client_port` varchar(22)  DEFAULT NULL,
  `backend_port` varchar(22)  DEFAULT NULL,
  `request_processing_time` double DEFAULT NULL,
  `backend_processing_time` double DEFAULT NULL,
  `response_processing_time` double DEFAULT NULL,
  `elb_status_code` char(3)  DEFAULT NULL,
  `backend_status_code` char(3)  DEFAULT NULL,
  `received_bytes` int(11) DEFAULT NULL,
  `sent_bytes` int(11) DEFAULT NULL,
  `request` varchar(128)  DEFAULT NULL,
  `user_agent` varchar(1024)  DEFAULT NULL,
  `ssl_cipher` varchar(32)  DEFAULT NULL,
  `ssl_protocol` varchar(16) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `elb_log_idx1` (`access_timestamp`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

load dataでデータ投入

• LOAD DATA LOCAL FILE なので、投入したいログファイルはクライアントに置いておきます。
• /path/to/elb_log でログファイルをフルパスで指定します。
• FIELDS TERMINATED BY ' ' ENCLOSED BY '"' で区切り文字が半角スペース、囲み文字が「"」であることを指定します。
• ( @ access_timestamp 〜 @ ssl_protocol ) でログファイルのフィールドに名称を設定します。
• SET テーブルのカラムに対応するCSVのフィールドを指定します。
• access_timestampカラムには、ELBのログのタイムスタンプを設定します。タイムスタンプはUTCなので、日本との時差9時間を加算します。

load-log.sql

LOAD DATA LOCAL INFILE '/path/to/elb_log'  
INTO TABLE elb_log
FIELDS TERMINATED BY ' ' ENCLOSED BY '"'  
( @access_timestamp, @elb, @client_port, @backend_port,
  @request_processing_time, @backend_processing_time,
  @response_processing_time, @elb_status_code, @backend_status_code,
  @received_bytes, @sent_bytes, @request, @user_agent, @ssl_cipher,
  @ssl_protocol ) 
SET 
  access_timestamp = DATE_ADD(str_to_date( @access_timestamp , '%Y-%m-%dT%H:%i:%s.%fZ' ) , interval 9 hour),
  elb = @elb,
  client_port = @client_port, 
  backend_port = @backend_port, 
  request_processing_time = @request_processing_time, 
  backend_processing_time = @backend_processing_time,
  response_processing_time = @response_processing_time, 
  elb_status_code = @elb_status_code, 
  backend_status_code = @backend_status_code, 
  received_bytes = @received_bytes, 
  sent_bytes = @sent_bytes, 
  request = @request, 
  user_agent = @user_agent,
  ssl_cipher = @ssl_cipher,
  ssl_protocol = @ssl_protocol
;