この日記のアクセスログをmysqlに入れてみた。
この日記のアクセスログをmysqlに入れてみました。はてなカウンターが提供しない方法で、アクセスログを見れるようにするためです。
Windowsマシンからubuntuのサーバへアクセスログをコピーする。
pscpコマンドを使って、Windowsマシンからubuntuのサーバへアクセスログをコピーします。以下のコマンドでコピーします。このコマンドを実行すると、Windowsマシンのファイル「011-2009-02.csv」が、IPアドレスが192.168.1.21サーバのhatenaディレクトリにコピーされます。コピー時には、nattouというユーザでサーバにアクセスしています。
> pscp 011-2009-02.csv nattou@192.168.1.21:hatena
アクセスログを入れるテーブル「access_log」をmysqlに作成する。
以下のDDLでテーブル「access_log」を作成します。
create table access_log (
datetime datetime,
ip_address char(15),
access_from varchar(300),
browser varchar(300),
language varchar(100),
screen char(10),
color char(4),
url varchar(300)
);
テーブルにアクセスログを追加するinsert文を作成する。
CSV形式のアクセスログを加工して、insert文を作ります。insert文を作るのに、phpクライアントを使います。ちなみに、phpクライアントである必然性は全くありません。何となくです。
sql.php
<?php
$name = $argv[1];
$fp = fopen( $name, "r" );
if ( $fp ) {
while ( ! feof( $fp ) ) {
$line = chop( fgets( $fp ) );
$tmp = preg_replace( '/((?:[^,]*"[^"]*"[^,]*)*[^,]*),((?:[^,]*"[^"]*"[^,]*)*[^,]*),((?:[^,]*"[^"]*"[^,]*)*[^,]*),((?:[^,]*"[^"]*"[^,]*)*[^,]*),((?:[^,]*"[^"]*"[^,]*)*[^,]*),((?:[^,]*"[^"]*"[^,]*)*[^,]*),((?:[^,]*"[^"]*"[^,]*)*[^,]*),((?:[^,]*"[^"]*"[^,]*)*[^,]*)/', "insert into access_log values( '$1', '$2', '$3', '$4', '$5', '$6', '$7', '$8' );", $line );
$tmp = str_replace( '"', '', $tmp );
echo $tmp . "\n";
}
fclose( $fp );
}
?>正規表現が長すぎる。。。要するに、カンマで文字列を分割したいだけです。分割時に、ダブルクォーテーション内のカンマでは分割しないように考慮しています。
このphpを、以下のようにコマンドラインで実行します。CSV形式のアクセスログ「011-2009-02.csv」から、insert文を含むファイル「011-2009-02.sql」が作成されます。
$ php sql.php 011-2009-02.csv > 011-2009-02.sql
例えば、011-2009-02.csvが以下の通りだとすると、
2009-02-01 00:22:10,XXX.XXX.XXX.XXX,,Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; ja-JP-mac; rv:1.9.0.5) Gecko/2008120121 Firefox/3.0.5,"ja,en-us;q=0.7,en;q=0.3",1280x800,24,http://d.hatena.ne.jp/nattou_curry_2/20090131/1233414993 2009-02-01 00:23:55,XXX.XXX.XXX.XXX,,"Mozilla/5.0 (X11; U; Linux x86_64; c) AppleWebKit/528.5+ (KHTML, like Gecko, Safari/528.5+) WebShot",,640x480,8,http://d.hatena.ne.jp/nattou_curry_2/20090131/1233414993 2009-02-01 00:57:19,XXX.XXX.XXX.XXX,,Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; ja-JP-mac; rv:1.9.0.5) Gecko/2008120121 Firefox/3.0.5,"ja,en-us;q=0.7,en;q=0.3",1280x800,24,http://d.hatena.ne.jp/nattou_curry_2/20090101/1230775710
上記のphpを実行して作成される011-2009-02.sqlは、以下になります。
insert into access_log values( '2009-02-01 00:22:10', 'XXX.XXX.XXX.XXX', '', 'Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; ja-JP-mac; rv:1.9.0.5) Gecko/2008120121 Firefox/3.0.5', 'ja,en-us;q=0.7,en;q=0.3', '1280x800', '24', 'http://d.hatena.ne.jp/nattou_curry_2/20090131/1233414993' ); insert into access_log values( '2009-02-01 00:23:55', 'XXX.XXX.XXX.XXX', '', 'Mozilla/5.0 (X11; U; Linux x86_64; c) AppleWebKit/528.5+ (KHTML, like Gecko, Safari/528.5+) WebShot', '', '640x480', '8', 'http://d.hatena.ne.jp/nattou_curry_2/20090131/1233414993' ); insert into access_log values( '2009-02-01 00:57:19', 'XXX.XXX.XXX.XXX', '', 'Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; ja-JP-mac; rv:1.9.0.5) Gecko/2008120121 Firefox/3.0.5', 'ja,en-us;q=0.7,en;q=0.3', '1280x800', '24', 'http://d.hatena.ne.jp/nattou_curry_2/20090101/1230775710' );
