アクセスログファイルからのデータ抽出とCSV化、SQLiteへの取込

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

さまざまな形式のアクセスログファイルから、
分析集計しやすいようにCSVファイルへの変換、さらには簡易データベースであるSQLiteへの取り込みを解説する。

CSVファイルにする

テキスト形式のログファイルを扱いやすいCSVに変換する。

NCSA combined形式

テキストファイルにすればExcelなどの表計算ソフトやPostgreSQLなどのデータベースにも取り込むことができる。自由自在になる。

バーチャルホスト名を含まないNCSA combined logから以下の8項目をタブ区切りテキストで書き出す。

  • アクセス元のアドレス
  • ユーザー名
  • 日時
  • ページのパス
  • ステータスコード
  • 送信バイト数
  • 参照元
  • ブラウザ

複数の圧縮されたログファイルがある場合は

簡単にデータの中身を見る

簡単にデータの中身を見るのであれば、テキストファイルを直接SQLの形式で集計できるPythonスクリプト「q」が便利
http://harelba.github.io/q/

タブ区切りテキストなら

でSQLを実行できる(内部ではSQLiteを使っている)

処理の具体例

Googlebotのアクセスについてアクセス数を集計

特定の階層へのボット以外のアクセスについてボット以外のページビュー数(画像やCSSなどのスクリプトは含まない)を集計
パイプを使ってまとめて1回のコマンドで実行する例

Pythonさえインストールされていれば大概機能するので大変便利なツールだが、その都度ファイルを読み込むので処理が低速(内部でSQLiteを使用している)。
やはりデータベースに取り込んだほうが高速にデータ処理できる。

W3C拡張形式(AmazonのCloudFront)

NCSA形式とは異なりフィールドの区切り文字が決まってる(タブ)ので処理自体は簡単である。
半角スペース文字のみデコードすればいい。

全カラムを一旦インポート用のCSVファイルとして書き出す。

あくせすろぐwデータベースに取り込む

SQLiteに取り込む(NCSA拡張形式)

まずは簡単なデータベース(RDB)ということでSQLiteに取り込んでみる。

データ加工

SQLiteでは日付の形式があらかじめ厳格に定められているので、その形式にあわせてCSVの日付の列を変更する必要がある。
先のsedコマンドだけではできないのでawkなどを使うか、あるいはここではログからの抽出処理をまとめてPerlスクリプトで行う。

この「log2csv.pl」の中身は

log2csv.pl

このままコピペして使えばいい。

テーブルの作成~インポート

SQLiteを実行する。

テーブルを作成し、CSVファイルを取り込む。先のフォーマットであれば

処理の具体例・Googlebotのクローラ分析

Googlebotのクローラ分析のみであれば実は多くのケースでSQLiteだけで十分。

log2csv_googlebot.pl

このlog_sqlite_googlebot.csvをSQLiteの.importコマンドで取り込めばいい。

Series Navigation<< サーバログ型アクセス解析の特徴、ログの構造と取得方法、ユーザIDアクセスログデータをデータベース(PostgreSQL)に取り込む >>