アクセスログデータをデータベース(PostgreSQL)に取り込む

2017年2月6日
この記事は連載「アクセスログの生ログ分析」の全 4 ページ中 3 ページ目です。

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

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

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

環境の準備(linux)

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

ユーザを作成

データベースを作成

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

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

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

環境の準備(Windows)

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

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

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

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

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

テーブルの作成

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

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

ログのインポート

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

import_serial.sh

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

import_parallel.sh

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

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

でできる。

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

Amazon CloudFrontのログを取り込む

テーブルの作成

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

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

ログのインポート

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

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

IISのログを取り込む

テーブルの作成

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

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

ログのインポート

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

User-Agentの文字列処理

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

Series Navigation<< アクセスログファイルからのデータ抽出とCSV化、SQLiteへの取込アクセスログデータの前処理、ユーザIDとセッションの生成、URLの集約 >>