通过浏览器的浏览历史进行人生的盘点（使用embulk + elasticsearch + kibana进行可视化）

这篇文章是来自「M3贴纸日历2015」的第22天文章。

首先

你好。现在是年末了。提到年末，这是回顾一年的好机会呢。我感觉有些有意识的人会说“这是评估人生的好时机呢★”。

回顧過去時光，唉，發現當初在網路上浪費了多少無謂時間啊…真是感到後悔萬分。但仔細一想，其實自己連當初在瀏覽器上看了些什麼都沒有好好回顧過。既然是個好機會，我在這篇文章中會好好深入探討一下，

可视化和分析个人浏览历史的基础设施

我想要尝试搭建起来。

只是，有一点令人担忧的是，我是

• chrome => メインブラウザ

 

firefox => ある特定の用途

我们以战略性方式使用两个浏览器。换句话说，浏览历史数据被分散了。必须满足的要求是将分散的Chrome和Firefox的浏览记录统合起来。

谷歌浏览器和火狐浏览器的浏览历史记录

当我稍微调查之后，我发现在Mac上，Chrome和Firefox的浏览历史都存储在SQLite 数据库中。

• chrome

/Users/自分のディレクトリ/Library/Application Support/Google/Chrome/Default/History

firefox

/Users/自分のディレクトリ/Library/Application Support/Firefox/Profiles/ユーザーごとに固有の値？.default/places.sqlite

谷歌浏览器

因为直接修改数据库有点可怕，所以我会将它复制到各自的工作目录中，然后查看其内容。

$ 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开始的微秒数，因此需要使用datetime函数进行适当处理，但可以确认可以通过sql获取浏览历史。

请参考这个链接：http://stackoverflow.com/questions/20458406/what-is-the-format-of-chromes-timestamps

火狐浏览器

我也试试看看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/

经确认，在SQL中可以获取到Firefox访问日期（visit_date），该日期表示自1970/1/1 UTC以来的微秒数。

用embulk + elasticsearch + kibana将浏览历史进行可视化展示。

嗯，说到可视化，那就一定是 elasticsearch + kibana 啦。kibana 那个黑色的仪表盘总让人感到很兴奋。不过，要往 elasticsearch 里面输入数据是需要一些过程的。

• sqlでjoinとかして必要なデータを抽出する

 

• urlはhostの部分だけ抜き出すよう加工処理

 

elasticsearchのAPIへhttpでPOSTする

这种事情需要一个一个地做，唉〜哎呀，看起来有点烦琐。

就用 Embulk 吧！

因此，我开始调查我一直对Embulk感兴趣的事情。

Embulk是一种类似于fluentd的批处理工具，在实时日志收集中被视为常识。它可以从文件和数据库中提取数据，并将其加载到另一个存储或数据库中。与fluentd一样，Embulk采用了插件化架构，因此可以通过使用Ruby或Java编写简单的代码来支持各种文件格式和存储。与fluentd不同的是，Embulk注重速度、事务控制和基于模式的数据验证，并专注于一次性执行或按日或每小时执行的批量处理。

听说是这样的。不知道为什么，感觉这次可以用在这个用途上。

仔细调查了一下embulk的插件，

• sqliteからの入力 => embulk-input-jdbcプラグイン

 

• urlからhostの部分抜き出し => embulk-filter-evalプラグイン

 

elasticsearchへの出力 => embulk-output-elasticsearchプラグイン

我已经发现已经存在了这样的东西。可以根据以下的方式，将Chrome和Firefox的浏览历史放入Elasticsearch中。

我们分别下载吧

事先只是简单地查了一下，发现Embulk的elasticsearch输出插件的最新版本似乎还不支持elasticsearch 2系，所以我们决定使用1系的最新版本。
– https://github.com/muga/embulk-output-elasticsearch

• elasticsearch 1.7.4

 

kibana 4.1.4

我决定采用这种方式。请注意，要运行以下内容需要Java（建议使用1.8或更高版本）。

embulk = 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

如果您在以下命令中看到帮助信息，则表示一切正常。

$ embulk -h

弹性搜索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

如果能够访问以下内容，就可以了。

基于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

如果可以访问以下内容，就可以了。

使用embulk将浏览历史推送到elasticsearch中

好，现在让我们终于开始使用embulk将数据导入到elasticsearch。首先，我们将尝试将Chrome的浏览历史导入到elasticsearch中。

embulk-input-jdbc 这个库是用来将数据库中的数据导入到 Embulk 数据流中的工具。

在从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。非常方便。

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

数据转换滤镜-评估

我打算在这次分析中仅提取URL的主机部分。因为有一个可以编写Ruby语法的过滤插件，所以我决定使用它。

$ embulk gem install embulk-filter-eval

在eval_columns的项目中，您可以指定要为筛选器编写的列名称以及任意的Ruby语法。

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中的主机部分。

Elasticsearch 输出插件 Embulk

最后是将结果输出到elasticsearch。

首先，在elasticsearch中创建映射。索引名称设置为embulk_browser_history，类型名称设置为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 时的集群名称。

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

好了，现在终于要深入讨论了。

$ embulk run chrome_config.yml

可以插一脚！

我们来查一下Firefox的浏览历史吧。

鑒於成功運作，我們也將嘗試火狐版本。

顺便提一下，根据上述步骤，Chrome的设置文件应该是以下这样的。

Chrome 配置文件.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

火狐配置文件.yml

根据上述内容，我将为Firefox创建一个新的版本。由于数据库和表格结构不同，我会对Chrome的版本进行微调。

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已经很好地投入使用了！

基本上，Kibana is a data visualization and exploration tool in the Elastic Stack that allows users to interact with and analyze data stored in Elasticsearch.

如果你能做到这一点，剩下的就是根据自己的喜好调整Kibana，仅需对可视化的结果进行分析。在Kibana上创建仪表板大致如下。

好酷！

最近一周的浏览器访问量（按照浏览器分类）

终于到了面对过去的时刻了。。让我们来看看最近一周每个浏览器的访问量。

嗯嗯！最近火狐浏览器的使用量好像特别高的一天。。。

过去一周内的前10个浏览网站

让我们来查看最近一周的访问网站前10名，看看今天到底访问了哪些网站。

自律管理！

明年的目标

通过查看浏览器的浏览历史，我能够对人生进行一次盘点。明年的目标是什么。

• Firefoxの閲覧比率を20%以下にする

 

閲覧サイトTOP10を、人に見せられるようにする

我决定这样做。

各位是否考虑利用自己的浏览器历史记录来进行人生的梳理呢？