セッションやユーザの識別にはSQLiteでは対応していないウィンドウ関数が必要になるし、データのサイズが大きくなるとSQLiteでは不安なところもある。
ということで、データサイズがある程度大きくなっても対応可能で、オープンソースでは珍しくウィンドウ関数など集計・分析機能に優れたPostgreSQLへアクセスログを取り込む方法を解説する。

アクセスログをPostgreSQLに取り込む

NCSA拡張形式のログを取り込む

環境の準備（linux）

SQLiteと違って環境の準備が必要になる。
PostgreSQLのインストール、initdbが完了している前提で

ユーザを作成

$ su postgres
$ createuser xxx

データベースを作成

ログにはそもそもマルチバイト文字は含まれないので文字コードSQL_ASCIIを強制してデータベースを作成する。

通常initdbで文字コードUTF-8が暗黙のうち指定されるので、新しく作るデータベースの文字コードはデフォルトでUTF-8となる。
データベースの文字コードがUTF-8だとデータの文字列にバックスラッシュを含むときにエスケープシーケンスと勘違いする。
そうなるとバックスラッシュを含むデータをインポート時にエラーを起こして取り込めない。

文字コードSQL_ASCIIを明示してcreatedbし、すべてのデータを非マルチバイト文字として扱うことを強制する必要がある。

$ createdb -E SQL_ASCII -l C -T template0 web_analysis
$ psql web_analysis

環境の準備（Windows）

Windowsではわざわざインストールしなくても

http://www.enterprisedb.com/products-services-training/pgbindownload

から非インストーラー版をダウンロードして展開すればいい。
展開先を

d:\psql

とするとコマンドラインで

pushd d:\pgsql
bin\pg_ctl init -D pgdata
bin\pg_ctl start -D pgdata
bin\createdb web_analysis
bin\psql web_analysis

とするだけで使えるので意外と簡単である。

テーブルの作成

PostgreSQLのコンソールでテーブルを作る。必要に応じてスキーマも作る。

スキーマ名：myschema
ログテーブル名：log

create schema myschema;
set search_path to myschema; 

drop table if exists log;

create table log (
    remote_host VARCHAR(100),
    remote_user VARCHAR(100),
    str_time VARCHAR(100),
    request_path VARCHAR(10000),
    status INTEGER NULL,
    bytes INTEGER NULL,
    referer VARCHAR(10000),
    user_agent VARCHAR(10000)
);

ログのインポート

複数の圧縮されたログファイルの展開→必要なカラムの抽出→一括取り込み

import_serial.sh

#!/bin/sh
export FTMP='/tmp/pg_import.csv'
export SCHEMA='myschema'
for f in `find /var/log/httpd/ -type f -name "access_*.gz"`; do gzip $f -dc | 
sed -e '/^.* .* .* \[.*\] ".* .* .*" .* .* ".*" ".*"$/!d' \
-E 's/^(.*) .* (.*) \[(.*)\] ".* (.*) .*" (.*) (.*) "(.*)" "(.*)"$/\1\t\2\t\3\t\4\t\5\t\6\t\7\t\8/g' > $FTMP
psql web_analysis -c "copy $SCHEMA.log from '$FTMP' with delimiter E'\t' null as '-'"
rm -f $FTMP; done

複数の圧縮ログファイルに対してシリアルに処理を実行するので時間がかかる。
強引に並列処理をすると

import_parallel.sh

#!/bin/sh
SCHEMA='myschema_parallel'
for f in `find /var/log/httpd/ -type f -name "access_*.gz"`; do (gzip $f -dc |
sed -e '/^.* .* .* \[.*\] ".* .* .*" .* .* ".*" ".*"$/!d' \
-E 's/^(.*) .* (.*) \[(.*)\] ".* (.*) .*" (.*) (.*) "(.*)" "(.*)"$/\1\t\2\t\3\t\4\t\5\t\6\t\7\t\8/g' > /tmp/$f.out
psql web_analysis -c "copy $SCHEMA.log from '/tmp/$f.out' with delimiter E'\t' null as '-'"
rm -f /tmp/$f.out) & done; wait
#rm -f /tmp/$f.out) & if (($i % 5 == 0)); then wait fi; done; wait

高速にできるが、ファイル数だけ同時に処理が走るので無茶はしないように。

SQLiteなど他のDBのダンプデータを取り込む場合は

psql -f sqlite3.dump web_analysis

でできる。

最後に文字列型で取り込んだ日時データをタイムスタンプ型に変換する。

alter table log add column datetime timestamp;
update log set datetime = cast(str_time as timestamp);

Amazon CloudFrontのログを取り込む

テーブルの作成

PostgreSQLのコンソールでテーブルを作る。必要に応じてスキーマも作る。

スキーマ名：myschema
ログテーブル名：log

drop table if exists myschema.log;
create table myschema.log (
    date varchar(16), 
    time varchar(16), 
    x_edge_location varchar(16), 
    sc_bytes integer, 
    c_ip varchar(32), 
    cs_method varchar(32), 
    cs_Host varchar(256), 
    cs_uri_stem varchar(256), 
    sc_status integer, 
    cs_Referer varchar(256), 
    cs_User_Agent varchar(256), 
    cs_uri_query varchar(256), 
    cs_Cookie varchar(256), 
    x_edge_result_type varchar(16), 
    x_edge_request_id varchar(32), 
    x_host_header varchar(128), 
    cs_protocol varchar(8), 
    cs_bytes integer, 
    time_taken float, 
    x_forwarded_for varchar(256), 
    ssl_protocol varchar(8), 
    ssl_cipher varchar(32), 
    x_edge_response_result_type varchar(32)
);

ログのインポート

タブ区切りテキストからの取り込みになる。

copy log from '/tmp/log.csv' with delimiter E'\t' null as '-';

最後に日付と時刻とが分かれている日時データからタイムスタンプの列を作成する

alter table log rename column date to str_date;
alter table log rename column time to str_time;
alter table log add column date date;
alter table log add column time time;
update log set date = cast(str_date as date);
update log set time = cast(str_time as time);
alter table log add column datetime timestamp;
update log set datetime = date + time;

IISのログを取り込む

テーブルの作成

PostgreSQLのコンソールでテーブルを作る。必要に応じてスキーマも作る。

スキーマ名：myschema
ログテーブル名：log

drop table if exists myschema.log;
create table myschema.log (
    date varchar(1000), 
    time varchar(1000), 
    s_ip varchar(1000), 
    cs_method varchar(1000), 
    cs_uri_stem varchar(1000), 
    cs_uri_query varchar(5000), 
    s_port integer, 
    cs_username varchar(1000), 
    c_ip varchar(1000), 
    cs_User_Agent varchar(1000), 
    cs_Referer varchar(10000), 
    sc_status integer, 
    sc_substatus integer, 
    sc_win32_status integer, 
    time_taken integer, 
    x_forwarded_for varchar(1000)
);

ログのインポート

Amazon CloudFrontと異なり、区切り文字は半角スペース。

copy log from '/var/lib/pgsql/merged.csv' with delimiter ' ';

User-Agentの文字列処理

update log set cs_User_Agent = replace(cs_User_Agent, '+', ' ');

最後に日付と時刻とが分かれている日時データからタイムスタンプの列を作成する

alter table log rename column date to str_date;
alter table log rename column time to str_time;
alter table log add column date date;
alter table log add column time time;
update log set date = cast(str_date as date);
update log set time = cast(str_time as time);
alter table log add column datetime timestamp;
update log set datetime = date + time;