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

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

[ドキュメント：Supported Hive UDFs](http://docs.treasuredata.com/articles/udfs)

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

# TD_SESSIONIZE
これですが、Webのアクセスログとかで同一アクセスと思われるログに同一[UUID](http://ja.wikipedia.org/wiki/UUID)を振ってくれます。ただのログを情報の宝石箱や！に変えることができる関数ですね。

```sql:Signature
string TD_SESSIONIZE(int/long unix_timestamp, int timeout, string sessionize_by)
```

各引数は下記のものを指定します。
- 第一引数には時間
- 第二引数には同一アクセスがタイムアウトする時間
- 第三引数にはセッションを見分けるための値

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

```sql
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見つけました。便利みたいです。

<blockquote class="twitter-tweet" lang="ja"><p lang="ja" dir="ltr">Treasure Dataでアクセスログのsessionizeができるようになったので、皆さん試すべき。&quot;ページXにアクセスしたセッション内で最初にアクセスされたページのトップN件&quot;とか取れます： &quot;TD_SESSIONIZE&quot; <a href="http://t.co/juqg9Zij8N">http://t.co/juqg9Zij8N</a></p>&mdash; FURUHASHI Sadayuki (@frsyuki) <a href="https://twitter.com/frsyuki/status/373259541963497472">2013, 8月 30</a></blockquote>
<script async src="//platform.twitter.com/widgets.js" charset="utf-8"></script>

<blockquote class="twitter-tweet" lang="ja"><p lang="ja" dir="ltr">TD_SESSIONIZE 知らんかった。2年前からあるようだけど超絶便利じゃないか。<a href="http://t.co/r9lGZK8ZRv">http://t.co/r9lGZK8ZRv</a></p>&mdash; Dai Utsui (@utsuidai) <a href="https://twitter.com/utsuidai/status/598303706858196993">2015, 5月 13</a></blockquote>
<script async src="//platform.twitter.com/widgets.js" charset="utf-8"></script>

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

```sqlSignature
string TD_PARSE_USER_AGENT(user_agent string [, options string])
```

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

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

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

```sql
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|

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