LoginSignup
92
96

More than 5 years have passed since last update.

エムスリーAdvent Calendar 2015Day 22
@maeharin

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

Posted at

この記事は「エムスリー Advent Calendar 2015」の 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について調べてみました。

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

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

事前にさらっと調べたところ、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をインストールします

$ 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です

kibana 4.1.4

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

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

$ 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

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を指定することができます。便利ですね

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構文を指定します。

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とすることにします

$ 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を立ち上げた際のクラスタ名を指定します

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

スクリーンショット 2015-12-23 6.10.43.png

突っ込めた！

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

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

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

chrome_config.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のを微修正する感じです。

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に投入されました！

スクリーンショット 2015-12-23 0.21.02.png

kibana

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

スクリーンショット 2015-12-23 0.42.31.png

カコイイ！

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

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

スクリーンショット 2015-12-23 0.12.47.png

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

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

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

スクリーンショット 2015-12-23 0.12.54.png

じ、自主規制！

来年の目標

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

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

ということにします。

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

92
96
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
92
96