Visualization referral domains using google BigQuery

Lorentzca -

re:dashとGoogle BigQueryでアクセスログを可視化して遊んでいます。

最近Google BigQueryのドキュメントを眺めていてDOMAIN関数の存在を知ったので、これを使ってリファラのドメインを集計してre:dashで可視化してみます。

h2oでリファラをログに出力する方法

ログフォーマットでリクエストヘッダを指定(%{Referer}i)することでリファラをアクセスログに出力することができます。

こんな感じでGoogle BigQueryに送られていっています。

$ bq query "SELECT referer FROM prod.h2o_access_log_20170118 where referer != \"-\" group by referer LIMIT 10"
Waiting on bqjob_r5e53ed2ee77cdc85_00000159b086ea8b_1 ... (0s) Current status: DONE
+-------------------------------------------------------------------------------------------------------+
|                                                referer                                                |
+-------------------------------------------------------------------------------------------------------+
| http://blog.lorentzca.me/                                                                             |
| https://ghost.ponpokopon.me/about-the-river-near-feedforce/                                           |
| https://blog.lorentzca.me/                                                                            |
| https://blog.lorentzca.me/serverspec-of-selinux/                                                      |
| https://blog.lorentzca.me/vs-catfish-weapon/                                                          |
| https://blog.lorentzca.me/tag/blog/                                                                   |
| http://alfamusics.com/xmlrpc.php                                                                      |
| https://blog.lorentzca.me/specify-the-status-code-by-h2o/                                             |
| https://hatenablog-parts.com/embed?url=https%3A%2F%2Fghost.ponpokopon.me%2Fenjoy-mackerel-metadata%2F |
| https://blog.lorentzca.me/tag/swiftype/                                                               |
+-------------------------------------------------------------------------------------------------------+

DOMAIN関数

DOMAIN関数は、URLを処理する関数の1つで、URLを渡すとそのドメインを返す関数です。例えばhttp://www.google.com:80/index.html を渡すとgoogle.comを返します。

実際に使ってみます。

  • 過去7日間のリファラのドメインを降順で取得する
SELECT
  DOMAIN(referer) AS referral_domain,
  COUNT(referer) AS count_referer
FROM
  (TABLE_DATE_RANGE(prod.h2o_access_log_, DATE_ADD(CURRENT_TIMESTAMP(), -6, 'DAY'), CURRENT_TIMESTAMP()))
  GROUP BY referral_domain
  ORDER BY count_referer DESC

DOMAIN関数を使った場合と使わなかった場合の出力を比較してみます。より細かくリファラの情報を得たい場合はDOMAIN関数を使わず、大体で良い場合はDOMAIN関数を使ったほうが綺麗にまとめられます。

使った場合

使わなかった場合

ほかにもHOST関数やTLD関数があります。

re:dashで可視化

こんな感じになりました。nullとlorentzca.meとponpokopon.meは値が大きすぎてほかのグラフが潰れてしまうので非表示にしています。nullと自ドメイン以外ではadventarとgoogleからのアクセスが多いみたいですね。

感想

上で試した内容はぶっちゃけGoogleアナリティクス見れば分かるしもっと詳しい情報は取れるのですが(死)今回は興味と練習のために使ってみました。Google BigQueryはほかにもIP関数など、普通のクエリにはない関数がいくつもあって面白いですね。