ブラウザの閲覧履歴で人生の棚卸し（embulk + elasticsearch + kibanaで可視化）

この記事は「[エムスリー Advent Calendar 2015](http://qiita.com/advent-calendar/2015/m3)」の 22日目の記事です。

## はじめに

こんにちわ。年末ですね。年末といえば一年を振り返るいい機会ですね。意識の高い人たちが「人生を棚卸しするいい機会ダヨッ★」なんて言っていたような気もします。

過去の自分を振り替えってみると、あーネットサーフィンに無駄な時間を過ごしたなぁ。。なんて後悔の念が湧いてきます。しかし、よく考えてみると、自分がブラウザで何を閲覧していたのか、きちんと振り返ったことがありませんでした。いい機会ですので、この記事では

> 自分の閲覧履歴を可視化して分析する基盤

を構築してみたいと思います。

ただ、一点ネックなのは、私は

- chrome => メインブラウザ
- firefox => ある特定の用途

といったように、2つのブラウザを戦略的に使い分けております。つまり、閲覧履歴データが分散しているわけです。分散してしまったchromeとfirefoxの閲覧ログを統合することが必須要件となります。

## chromeとfirefoxの閲覧履歴

少し調べてみると、macですと、chromeとfirefoxの閲覧履歴はsqliteのDBに保存されていることがわかりました。

- chrome
  - `/Users/自分のディレクトリ/Library/Application Support/Google/Chrome/Default/History`
- firefox
  - `/Users/自分のディレクトリ/Library/Application Support/Firefox/Profiles/ユーザーごとに固有の値？.default/places.sqlite`

### chrome

そのままデータベースをいじるのはなんだか怖いので、それぞれ作業ディレクトリにコピーして中身をみてみます

```
$ cd {作業dir}
$ cp '/Users/自分のディレクトリ/Library/Application Support/Google/Chrome/Default/History' ./
```

```
$ sqlite3 History

テーブル一覧

sqlite> .tables
downloads             meta                  urls
downloads_url_chains  segment_usage         visit_source
keyword_search_terms  segments              visits

閲覧日時と閲覧urlを取得

sqlite> select datetime(v.visit_time/1000000-11644473600,'unixepoch','localtime') as visit_time, u.url
   ...> from visits as v
   ...> left outer join urls as u on v.url = u.id
   ...> order by v.id
   ...> limit 1;
2015-09-24 22:05:25|https://www.google.co.jp/
```

visit_timeが1601/1/1 UTCからのmicrosecondsなので、datetime関数でよろしく処理してあげる必要がありますが、sqlで閲覧履歴が取得できることが確認できました。

参考：http://stackoverflow.com/questions/20458406/what-is-the-format-of-chromes-timestamps

### firefox

firefoxもみてみます

```
$ cd {作業dir}
$ cp '/Users/自分のディレクトリ/Library/Application Support/Firefox/Profiles/ユーザーごとに固有の値？.default/places.sqlite' ./
```

```
$ sqlite3 places.sqlite

テーブル一覧

sqlite> .tables
downloads             meta                  urls
downloads_url_chains  segment_usage         visit_source
keyword_search_terms  segments              visits

閲覧日時と閲覧urlを取得

sqlite> select datetime(h.visit_date/1000000,'unixepoch','localtime'), p.url
   ...> from moz_historyvisits as h
   ...> left outer join moz_places as p on h.place_id = p.id
   ...> order by h.id
   ...> limit 1;
2012-04-22 13:40:29|http://www.google.co.jp/
```

firefoxの方のvisit_dateは、1970/1/1 UTCからのmicrosecondsです。こちらもsqlで閲覧ログを取得できることが確認できました

## embulk + elasticsearch + kibanaで閲覧履歴を可視化

さて、可視化といえば、elasticsearch + kibanaで決まりでしょう。kibanaのあの黒いダッシュボードにはなんだかムラムラくるものがあります。しかしながら、elasticsearchに突っ込むには

- sqlでjoinとかして必要なデータを抽出する
- urlはhostの部分だけ抜き出すよう加工処理
- elasticsearchのAPIへhttpでPOSTする

みたいなことをチマチマやる必要があります。う〜ん。なんだか面倒臭そうです


### そこでembulk!

そこで、前から気になっていた[embulk](https://github.com/embulk/embulk)について調べてみました。

> Embulk は、リアルタイムなログ収集では常識となった fluentd のバッチ版のようなツールで、ファイルやデータベースからデータを吸い出し、別のストレージやデータベースにロードするためのコンパクトなツールです。
>
>fluentd と同様にプラグイン型のアーキテクチャを採用 しているため、RubyやJavaで簡単なコードを書くことで、様々なファイルフォーマットやストレージに対応することができます。一方で fluentd とは異なり、高速性やトランザクション制御、スキーマを使ったデータのバリデーション などにこだわっており、1発実行、あるいは日次や1時間毎に実行するバルク処理に特化しています。
>
> http://frsyuki.hatenablog.com/entry/2015/02/16/080150

とのことです。なんだか今回の用途に使えそうな気がします。

embulkのプラグインについてざっと調べてみると、

- sqliteからの入力 => embulk-input-jdbcプラグイン
- urlからhostの部分抜き出し => embulk-filter-evalプラグイン
- elasticsearchへの出力 => embulk-output-elasticsearchプラグイン

といったものがすでに存在していることがわかりました。以下のように、chromeとfirefoxの閲覧履歴をelasticsearchに放り込む構成にできそうです。


![スクリーンショット 2015-12-22 23.17.27.png](https://qiita-image-store.s3.amazonaws.com/0/4506/4b7815fa-bbdd-e98b-e607-bf69d9316571.png "スクリーンショット 2015-12-22 23.17.27.png")



## それぞれダウンロードしていきましょう

事前にさらっと調べたところ、embulkのelasticsearch outputプラグインの最新verがまだelasticsearchの2系に対応していないようなので、1系の最新を使うことにします。
  - https://github.com/muga/embulk-output-elasticsearch

- elasticsearch 1.7.4
- kibana 4.1.4

でいくことにします。なお、以下動かすにはjava（1.8以上推奨）が必要です

### embulk

githubのreadmeに書いてあるとおりにダウンロードします。embulkの最新版（2015/12/22現在、0.7.10）がダウンロードできます。

```
$ curl --create-dirs -o ~/.embulk/bin/embulk -L "http://dl.embulk.org/embulk-latest.jar"
$ chmod +x ~/.embulk/bin/embulk
$ echo 'export PATH="$HOME/.embulk/bin:$PATH"' >> ~/.bashrc
$ source ~/.bashrc
```

以下のコマンドでヘルプが表示されればOKです

```
$ embulk -h
```


### elasticsearch 1.7.4

次に全文検索エンジンであるelasticsearchをインストールします

https://www.elastic.co/downloads/elasticsearch

```
$ wget https://download.elastic.co/elasticsearch/elasticsearch/elasticsearch-1.7.4.zip
$ unzip elasticsearch-1.7.4.zip
$ cd elasticsearch-1.7.4

headプラグインを入れます。インデックスの状況をブラウザでさらっと見れるので
$ ./bin/plugin install mobz/elasticsearch-head

embulk_sandboxというクラスタ名で起動してみます
$ ./bin/elasticsearch --cluster.name=embulk_sandbox
```

以下にアクセスできればokです

http://localhost:9200/_plugin/head/


### kibana 4.1.4

elasticsearchのインデックスをカッコよく可視化できるkibanaをダウンロードします。

mac版があるので、それをダウンロードします

https://www.elastic.co/downloads/kibana

```
$ wget https://download.elastic.co/kibana/kibana/kibana-4.1.4-darwin-x64.zip
$ tar zxvf kibana-4.1.4-darwin-x64.zip
$ cd kibana-4.1.4-darwin-x64
$ ./bin/kibana
```

以下にアクセスできればok

http://localhost:5601/

## embulkで閲覧履歴をelasticsearchに投入

さて、いよいよembulkを使ってデータをelasticsearchに投入してみます。まずはchromeの閲覧履歴をelasticsearchに突っ込んでみます

### embulk-input-jdbc

sqliteからデータを抽出する部分には、embulk-input-jdbcというプラグインを使います。

embulk gemコマンドでインストール完了です

```
$ embulk gem install embulk-input-jdbc
```

jdbcを使うので、sqlite3-jdbcのjarをダウンロードします。

```
$ wget https://bitbucket.org/xerial/sqlite-jdbc/downloads/sqlite-jdbc-3.8.11.2.jar
```

この状態で、以下のymlファイルを作ります。query項目に任意のsqlを指定することができます。便利ですね

```yml:chrome_config.yml
in:
  type: jdbc
  driver_path: /Users/hidenorimaehara/sandbox/browser_history_search/sqlite-jdbc-3.8.11.2.jar
  driver_class: org.sqlite.JDBC
  url: jdbc:sqlite:History
  query: |
    select
      datetime(t_v.visit_time/1000000-11644473600,'unixepoch','localtime') as visit_time,
      t_u.url,
      'chrome' as browser
    from
      visits as t_v
        left outer join urls as t_u
        on t_v.url = t_u.id
    order by
      t_v.id desc
  column_options:
    visit_time: {value_type: string}
    url: {value_type: string}
    browser: {value_type: string}
```

embulkのpreviewコマンドを実行すると、標準出力にsqlで引っこ抜いてきた結果が表示されます

```
$ embulk preview chrome_config.yml
```



### embulk-filter-eval

今回の分析ではurlのhostの部分だけ抜きだそうと思います。rubyの構文を書けるフィルタプラグインがあったので、それを使うことにしました。

```
$ embulk gem install embulk-filter-eval
```

eval_columnsの項目に、フィルタを書けたいカラム名、任意のruby構文を指定します。

```yml:chrome_config.yml
in:
（略）
filters:
  - type: eval
    eval_columns:
      - url: "require 'uri'; URI.parse(value).host"
```

```
$ embulk preview chrome_config.yml

+---------------------+-------------------------------+----------------+
|   visit_time:string |                    url:string | browser:string |
+---------------------+-------------------------------+----------------+
| 2015-12-23 05:32:12 |              www.google.co.jp |         chrome |
| 2015-12-23 05:31:12 |              www.google.co.jp |         chrome |
```

いいかんじにurlのhostの部分だけ抜き出せました

### embulk-output-elasticsearch

最後にelasticsearchにoutputする部分です

まずは、elasticsearch側にマッピングを作っておきます。インデックス名はembulk_browser_history、type名はhistoryとすることにします


```bash
$ curl -XPUT 'http://localhost:9200/embulk_browser_history' -d '{
  "mappings": {
    "history": {
      "properties": {
        "visit_time": {
          "type": "date",
          "format": "yyyy-MM-dd HH:mm:ss"
        },
        "url": {
          "type": "string",
          "index": "not_analyzed"
        }
      }
    }
  }
}'
```

マッピングが作られましたので、次にembulkのプラグイン、embulk-output-elasticsearchをインストールします

```
$ embulk gem install embulk-output-elasticsearch
```

index,index_typeには上記マッピングで作成したものを指定。cluster_nameには、さきほどelasticsearchを立ち上げた際のクラスタ名を指定します

```yml:chrome_config.yml
in:
（略）
filters:
（略）
out:
  type: elasticsearch
  nodes:
  - {host: localhost}
  cluster_name: embulk_sandbox
  index: embulk_browser_history
  index_type: history
```


では、いよいよ突っ込みます

```
$ embulk run chrome_config.yml
```

http://localhost:9200/_plugin/head/

![スクリーンショット 2015-12-23 6.10.43.png](https://qiita-image-store.s3.amazonaws.com/0/4506/578fcd2c-6545-ef3d-d10a-44809aabd024.png "スクリーンショット 2015-12-23 6.10.43.png")


突っ込めた！

### firefoxの閲覧履歴も突っ込んじゃいましょう

うまく動くことを確認できたので、firefox版もやってみます。

ちなみに、上記の手順により、chrome用の設定ファイルは以下のようになっているはずです。

#### chrome_config.yml

```yml:chrome_config.yml
in:
  type: jdbc
  driver_path: /Users/hidenorimaehara/sandbox/browser_history_search/sqlite-jdbc-3.8.11.2.jar
  driver_class: org.sqlite.JDBC
  url: jdbc:sqlite:History
  query: |
    select
      datetime(t_v.visit_time/1000000-11644473600,'unixepoch','localtime') as visit_time,
      t_u.url,
      'chrome' as browser
    from
      visits as t_v
        left outer join urls as t_u
        on t_v.url = t_u.id
    order by
      t_v.id desc
  column_options:
    visit_time: {value_type: string}
    url: {value_type: string}
    browser: {value_type: string}
filters:
  - type: eval
    eval_columns:
      - url: "require 'uri'; URI.parse(value).host"
out:
  type: elasticsearch
  nodes:
  - {host: localhost}
  cluster_name: embulk_sandbox
  index: embulk_browser_history
  index_type: history
```

#### firefox_config.yml

上記を元に、firefox用のも作ります。データベースとテーブル構造が違うので、chromeのを微修正する感じです。

```yml:firefox_config.yml
in:
  type: jdbc
  driver_path: /Users/hidenorimaehara/sandbox/browser_history_search/sqlite-jdbc-3.8.11.2.jar
  driver_class: org.sqlite.JDBC
  url: jdbc:sqlite:places.sqlite
  query: |
    select
      datetime(t_mh.visit_date/1000000,'unixepoch','localtime') as visit_time, 
      t_mp.url,
      'firefox' as browser
    from
      moz_historyvisits as t_mh
        left outer join moz_places as t_mp
        on t_mh.place_id = t_mp.id
    where
      visit_time >= '2015-09-22 00:00:00'
    order by
      t_mh.id desc
  column_options:
    visit_time: {value_type: string}
    url: {value_type: string}
    browser: {value_type: string}
filters:
  - type: eval
    eval_columns:
      - url: "require 'uri'; URI.parse(value).host"
out:
  type: elasticsearch
  nodes:
  - {host: localhost}
  cluster_name: embulk_sandbox
  index: embulk_browser_history
  index_type: history
```

実行！

```
$ embulk run firefox_config.yml
```

いい感じにelasticsearchに投入されました！

http://localhost:9200/_plugin/head/

![スクリーンショット 2015-12-23 0.21.02.png](https://qiita-image-store.s3.amazonaws.com/0/4506/d5139f06-9455-3270-8d5d-f6b0aef44092.png "スクリーンショット 2015-12-23 0.21.02.png")





## kibana

ここまでできれば、あとはkibanaを好きなようにいじって、可視化された結果を分析するだけです。kibanaでダッシュボード作るとこんな感じに


![スクリーンショット 2015-12-23 0.42.31.png](https://qiita-image-store.s3.amazonaws.com/0/4506/169bf87f-19d0-a6f7-4ee1-3ca3b4b7ab3c.png "スクリーンショット 2015-12-23 0.42.31.png")

カコイイ！

### 直近1週間のブラウザ別閲覧数

いよいよ過去の自分と向き合う時がきました。。直近1週間のブラウザ別閲覧数を見てみます。

![スクリーンショット 2015-12-23 0.12.47.png](https://qiita-image-store.s3.amazonaws.com/0/4506/e989168d-1274-783e-dc68-6d20b59265de.png "スクリーンショット 2015-12-23 0.12.47.png")

ムムッ！やたらfirefoxの閲覧数が多い日が。。。

### 直近1週間の閲覧サイトtop10

一体この日はどんなサイトにアクセスしているのでしょう。直近1週間の閲覧サイトtop10を見てみます

![スクリーンショット 2015-12-23 0.12.54.png](https://qiita-image-store.s3.amazonaws.com/0/4506/3b5ee99b-d90c-d830-ad25-f3d5d3a28e00.png "スクリーンショット 2015-12-23 0.12.54.png")

じ、自主規制！

## 来年の目標

ということで、私はブラウザの閲覧履歴を見ることで、人生の棚卸しをすることができました。来年の目標は

- Firefoxの閲覧比率を20%以下にする
- 閲覧サイトTOP10を、人に見せられるようにする

ということにします。

みなさんもご自身のブラウザの閲覧履歴を使って、人生の棚卸し、いかがですか？