TreasureDataでHiveクエリを書くとき - TD_SESSIONIZE, TD_PARSE_USER_AGENT

はじめに

Treasure DataはHiveの関数以外に独自関数を。。。（略

ドキュメント：Supported Hive UDFs

ドキュメントを見れば、ここから下は不要だ。

TD_SESSIONIZE

これですが、Webのアクセスログとかで同一アクセスと思われるログに同一UUIDを振ってくれます。ただのログを情報の宝石箱や！に変えることができる関数ですね。

Signature

string TD_SESSIONIZE(int/long unix_timestamp, int timeout, string sessionize_by)

各引数は下記のものを指定します。

• 第一引数には時間
• 第二引数には同一アクセスがタイムアウトする時間
• 第三引数にはセッションを見分けるための値

使用例
同時にdistribute byとsort byを利用してます。
TDにログインすると最初からsample_datasetsというサンプルデータベースが用意されていますので、その中のアパッチログのサンプルwww_accessテーブルの値を利用してみましょう。同一hostからの２時間のアクセスに対して同一のUUIDを付加します。

SELECT TD_SESSIONIZE(time, 7200, host) as session_id, time, host, path
FROM (
  SELECT time, host, path
  from www_access
  distribute by host
  sort by host,time
) t

サンプルとはいえIPアドレスが表示されているので、ここでは結果例は表示しませんが、上のクエリを自分の環境で投げてみてください。

私の適当な説明ではありがたみがいまいち伝わってこないでが、下みたいなTweet見つけました。便利みたいです。

Treasure Dataでアクセスログのsessionizeができるようになったので、皆さん試すべき。"ページXにアクセスしたセッション内で最初にアクセスされたページのトップN件"とか取れます： "TD_SESSIONIZE" http://t.co/juqg9Zij8N

— FURUHASHI Sadayuki (@frsyuki) 2013, 8月 30

TD_SESSIONIZE 知らんかった。2年前からあるようだけど超絶便利じゃないか。http://t.co/r9lGZK8ZRv

— Dai Utsui (@utsuidai) 2015, 5月 13

TD_PARSE_USER_AGENT

前半で体力を使いすぎましたが、次はこれです。ログの中にuser_agent情報をJSON型で取得できるものです。（アクセスしてきたユーザのOSの種類とか、ブラウザの種類とかの情報。よくアクセス解析でXXOSからのアクセスXX%とかありますよね）

string TD_PARSE_USER_AGENT(user_agent string [, options string])

optionに下記のどれかを指定すると全体じゃなくて一部の情報だけを取り出すことができます。

os, os_family, os_major, os_minor, ua, ua_family, ua_major, ua_minor, device

使用例
先ほどのwww_accessテーブルがagent情報も持っているので、利用します。元はスペース区切りで並んでいた情報が利用しやすくなりましたね。

select agent,TD_PARSE_USER_AGENT(agent) as user_agent from www_access

結果の一部

agent	user_agent
Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv:9.0.1) Gecko/20100101 Firefox/9.0.1	{user_agent: {family: "9", major: "0", minor: "1", patch: null}, os: {family: "10", major: "6", minor: null, patch: null, patch_minor: null}, device: {family: "Other"}}
Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)	{user_agent: {family: "2", major: "1", minor: null, patch: null}, os: {family: "Other", major: null, minor: null, patch: null, patch_minor: null}, device: {family: "Spider"}}
Mozilla/5.0 (Windows NT 6.0) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11	{user_agent: {family: "17", major: "0", minor: "963", patch: null}, os: {family: "Windows Vista", major: null, minor: null, patch: null, patch_minor: null}, device: {family: "Other"}}
Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv:9.0.1) Gecko/20100101 Firefox/9.0.1	{user_agent: {family: "9", major: "0", minor: "1", patch: null}, os: {family: "10", major: "6", minor: null, patch: null, patch_minor: null}, device: {family: "Other"}}
Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)	{user_agent: {family: "IE", major: "0", minor: null, patch: null}, os: {family: "Windows 7", major: null, minor: null, patch: null, patch_minor: null}, device: {family: "Other"}}
Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11	{user_agent: {family: "17", major: "0", minor: "963", patch: null}, os: {family: "Windows 7", major: null, minor: null, patch: null, patch_minor: null}, device: {family: "Other"}}
Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv:9.0.1) Gecko/20100101 Firefox/9.0.1	{user_agent: {family: "9", major: "0", minor: "1", patch: null}, os: {family: "10", major: "6", min

まとめ

トレジャーデータはログを集めることにこだわっている会社さんなので、ログによくある形式などへの対応にも積極的で嬉しいですね。