ほんじゃーねっと

おっさんがやせたがったり食べたがったりする日常エッセイ

はてなカウンターのログを分析できるようにデータベースに取り込むスクリプト【Python/Clojure】

プログラミング

f:id:piro_suke:20160716001330j:plain

はてなカウンターの「ログ」画面で

ログを月単位でダウンロードできることを発見したので、

ダウンロードしたログを分析用にデータベースに保存するスクリプトを作成する。

ログファイルのフォーマット

ログファイルはCSV形式で

下記のような列構成になっている。

  1. アクセス日時
  2. IPアドレス
  3. アクセス元URL
  4. クライアント環境
  5. クライアントの言語
  6. 解像度
  7. 色解像度？
  8. アクセスURL

文字コードはUTF-8。

データにカンマが含まれる場合はダブルクオートで囲まれる。

データベーステーブルを用意する

ほぼそのままログ内容を取り込めるように

PostgreSQLにテーブルを作成する。

解像度と色解像度は「x」で結合してdisplayカラムにまとめる。

drop table if exists hatena_blog_logs;
create table hatena_blog_logs (
    id serial not null,
    accessed timestamp,
    ip varchar(50),
    refer_url varchar(255),
    client varchar(255),
    lang varchar(255),
    display varchar(100),
    url varchar(255),
    primary key (id)
);

インポート用スクリプトを作成する

続いてCSVファイルの内容をインポートするスクリプトを作成する。

CSVファイルの内容を読み込んでテーブルにinsertするだけなので、

割とシンプルな内容になった。

CSVファイルのパスのリストを用意して、

それをループで回してテーブルに取り込む。

インポート前にテーブルをクリアする処理を入れる。

Pythonスクリプトを作成する

Python3.4でsqlalchemyとcsvモジュールを使って書いたのがこちら：

# -*- coding: utf-8 -*-

import csv 
import sqlalchemy

csv_path_list = [ 
    "＜CSVファイルのパス＞"
]

engine = sqlalchemy.create_engine("postgresql+psycopg2://＜DBユーザー名＞:＜DBパスワード＞@/＜DB名＞", client_encoding="utf8")

def create_table_defs(conn, meta):
    tables = { 
        "blog_logs": sqlalchemy.Table("hatena_blog_logs", 
            meta, 
            autoload=True, 
            autoload_with=conn, 
            postgresql_ignore_search_path=True)
    }   

    return tables

def clear_tables(tables):
    for n, table in tables.items():
        table.delete().execute()

def import_csv(csv_path, log_table):
    with open(csv_path) as csv_file:
        reader = csv.reader(csv_file, delimiter=",", quotechar='"')
        for row in reader:
            log = { 
                "accessed": row[0],
                "ip": row[1],
                "refer_url": row[2],
                "client": row[3],
                "lang": row[4],
                "display": "%sx%s" % (row[5], row[6],),
                "url": row[7]
            }   
            log_table.insert().execute(**log)

with engine.connect() as conn:
    conn.execute("SET search_path TO public")
    meta = sqlalchemy.MetaData()
    meta.bind = engine
    tables = create_table_defs(conn, meta)
    clear_tables(tables)
    for csv_path in csv_path_list:
        import_csv(csv_path, tables["blog_logs"])

Clojureでも同じ処理を書いてみる

上記のPythonスクリプトを使って当初の目的は達成できたのだけど、

なぜだか急にClojureで書いてみたいという抗えない衝動が湧いてきたので、

調べ調べ書いた。

leiningenで作成したので、project.cljから。

(defproject hatena-blog-accesslog "0.1.0-SNAPSHOT"
  :description "FIXME: write description"
  :url "http://example.com/FIXME"
  :license {:name "Eclipse Public License"
            :url "http://www.eclipse.org/legal/epl-v10.html"}
  :dependencies [[org.clojure/clojure "1.8.0"]
                 [org.clojure/data.csv "0.1.3"]
                 [korma "0.4.2"]
                 [org.clojure/java.jdbc "0.4.2"]
                 [clj-time "0.12.0"]
                 [org.postgresql/postgresql "9.4-1203-jdbc42"]]
  :main ^:skip-aot hatena-blog-accesslog.core
  :target-path "target/%s"
  :profiles {:uberjar {:aot :all}})

メインスクリプト

(ns hatena-blog-accesslog.core
  (:gen-class)
  (:require [clojure.data.csv :as csv])
  (:require [clojure.java.io :as io])
  (:require [clj-time.core :as t]) 
  (:require [clj-time.local :as l]) 
  (:require [clj-time.coerce :as c]) 
  (:require [korma.db])
  (:require [korma.core :as kc]))

;; database defs
(korma.db/defdb db
                {:user "＜DBユーザー名＞"
                 :password "＜DBパスワード＞"
                 :subname "//localhost/＜DB名＞"
                 :port "5432"
                 :subprotocol "postgresql"})
(kc/defentity blog_logs
              (kc/table :hatena_blog_logs))

(def csv-path-list ["＜CSVファイルのパス＞"])

(defn import-csv
  [csv-path]
  (with-open [in-file (io/reader csv-path)]
    (doseq [line (csv/read-csv in-file)]
      (let [log (-> [:accessed :ip :refer_url :client :lang :display :display2 :url]
                  (zipmap line)
                  (as-> x
                        (update x :display #(str %1 "x" %2) (:display2 x)))
                  (update :accessed #(c/to-sql-time (l/to-local-date-time %)))
                  (dissoc :display2))]
        (kc/insert blog_logs 
                   (kc/values log))))))

(defn -main
  [& args]
  (kc/delete blog_logs)
  (doseq [csv-path csv-path-list]
    (import-csv csv-path)))

どっちの言語も良い。

自分の書き方のせいか、結構似たコードになった。

データ参照用Viewを作成する

いくつかデータを集計して確認するためのViewを作成した。

日別アクセス数ビュー：

drop view if exists daily_counts;
create view daily_counts
as
select
    to_char(accessed, 'YYYYMMDD') as day,
    count(*) as count
from
    hatena_blog_logs
group by
    to_char(accessed, 'YYYYMMDD')
order by
    to_char(accessed, 'YYYYMMDD') asc 
;

週別アクセス数ビュー：

drop view if exists weekly_counts;
create view weekly_counts
as
select
    to_char(accessed, 'YYYYWW') as day,
    count(*) as count
from
    hatena_blog_logs
group by
    to_char(accessed, 'YYYYWW')
order by
    to_char(accessed, 'YYYYWW') asc 
;

日別URL別アクセス数ビュー：

drop view if exists daily_article_counts;
create view daily_article_counts                                                                                                                                                       
as
select
    to_char(accessed, 'YYYYMMDD') as day,
    replace(url, 'http://＜ドメイン名＞', '/') as url,                                                                                                                              
    count(*) as count
from
    hatena_blog_logs
group by
    to_char(accessed, 'YYYYMMDD'),
    replace(url, 'http://＜ドメイン名＞', '/')
order by
    to_char(accessed, 'YYYYMMDD') asc
;

これらを使ってグラフ出力とかしたらそれっぽくなりそうだ。

おわり

Clojureでもっと色々書いてみたくなったので勉強しよう。

おいしいClojure入門 (Software Design plus)

おいしいClojure入門 (Software Design plus)