タグ: sql

前の記事が、若干TreasureData(正確にはPresto)への文句っぽくなったので、
今回はTreasureDataでよく使っている便利関数を一つ紹介します。

それが、TD_PARSE_AGENTです。
ドキュメント: 12. TD_PARSE_AGENT
(これはTreasureDataのUDFなので、他の環境のPrestoでは利用できません)

これは、アクセスログのuser-agentの文字列を解析する関数で、
アクセスデータを分析する時に、OSやブラウザ、PC/スマートフォンの区別、クローラーの除外などの目的で毎日のように使っています。

今、僕がこの記事を書いているブラウザのユーザーagentで試してみましょう。
Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.109 Safari/537.36

SELECT
    TD_PARSE_AGENT('Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.109 Safari/537.36')
-- 出力 
-- {"os":"Mac OSX","vendor":"Google","os_version":"10.14.3","name":"Chrome","category":"pc","version":"72.0.3626.109"}

欲しい情報が一通り揃っていますね。
ちなみに、戻り値はStringではなくMap型です。
そのため、この中から osだけ取りたい時は、下記のようにすると取得できます。
(access_logテーブルのagentという列に、例のUseragentの値が入っていると仮定します)

SELECT
    TD_PARSE_AGENT(agent)['os']
FROM
    access_log
-- 出力 
-- Mac OSX

また、ドキュメントの Exampleの中に、書いてある通り、
category はUserAgentによって7種類の値を取ります。

SELECT TD_PARSE_AGENT(agent)['category'] AS category FROM www_access
> pc // => "pc", "smartphone", "mobilephone", "appliance", "crawler", "misc", "unknown"

clawlerを除外するために使うことが多いです。