この記事は Treasure Data Advent Calendar 2015 - Qiita の24日目の記事です。
こんにちは。
今回はWEBログの集計や解析をする際によく使うHiveQLのクエリと、UDF(User Defined Functions)について実際の集計クエリを使って解説していきたいと思います。 これからTreasureDataを使う人やTreasureDataを使い始めたばかりの人向けの内容になっています。
今回やったこと
利用したWEBログテーブル
以下のような一般的なWEBログを利用しました。 このログを元に標準的なアクセス解析用データを生成する集計クエリを書いてみます。
| カラム名 | 内容 |
|---|---|
| time | アクセス日時 |
| ip | アクセス元IPアドレス |
| url | アクセスしたページのURL |
| referer | リファラURL |
| useragent | ユーザーエージェント |
| user_id | ユーザID(Cookie) |
実際のテーブルは以下のようになっています。
実行したクエリ
具体的には、WEBログからセッション単位のデータを作成しています。
[sql] SELECT TD_TIME_FORMAT(min(time), 'yyyy-MM-dd HH:mm:ss', 'JST') as session_start_time, TD_TIME_FORMAT(max(time), 'yyyy-MM-dd HH:mm:ss', 'JST') as session_end_time, session_id, user_id, -- TD_PARSE_USER_AGENTを利用してUAからOSの文字列を取得し、PC/SDを判定 CASE WHEN TD_PARSE_USER_AGENT( TD_LAST( useragent, time ), 'os_family' ) IN( 'Android', 'iOS', 'Windows Phone', 'Windows RT' ) THEN 'sd' ELSE 'pc' END device, -- セッションの一番最初のリファラURL（流入元）を取得 TD_FIRST(referer, time) as referer, -- セッションの一番最初のURL（ランディングページ）を取得 TD_FIRST(url, time) as lp, -- セッションの一番最後のURL（離脱ページ）を取得 TD_LAST(url, time) as exit_page, COUNT(1) as views, -- ランディングページのGoogleAnalyticsのパラメータを取得 parse_url( TD_FIRST(url, time), 'QUERY', 'utm_source' ) as ga_utm_source, -- PV数が1のものに直帰フラグを設定 CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END bounce_flg, -- セッションの終了と開始時間から滞在時間を計算 MAX(time) - MIN(time) as duration FROM ( -- user_idをキーに30分でセッションIDを生成 SELECT time, ip, user_id, url, referer, useragent, TD_SESSIONIZE( time, 1800, user_id ) AS session_id FROM ( -- user_idが空の場合にUA+IPをキーに擬似user_idを生成。セッションIDを生成するため、user_id、timeでソート SELECT time, ip, COALESCE(user_id, TD_MD5(concat(useragent,ip))) as user_id, url, referer, useragent FROM demo_www_access DISTRIBUTE BY COALESCE(user_id, TD_MD5(concat(useragent,ip))) SORT BY COALESCE(user_id, TD_MD5(concat(useragent,ip))), time ) t0 ) t1 GROUP BY session_id, user_id [/sql]
結果
クエリの実行結果は以下のようなものになります。
クエリ解説
それでは、実際のクエリで使っているUDFについて解説していきたいと思います。
TD_MD5
公式ドキュメント: https://docs.treasuredata.com/articles/udfs#tdmd5
文字列から、md5のハッシュ値を生成する関数です。
今回何に使ったかというと、利用したWebログで利用しているユーザIDが3rdPartyCookieであるため、iOS Safariなどのブラウザではブランクになってしまっています。 ユーザIDがないとセッション単位の集計もできません。除外してしまってもいいのですが、ある程度の分析は行いたいので、擬似的にユーザIDを生成しました。
具体的にはユーザーエージェントとIPアドレスを結合した文字列からmd5でハッシュを生成し、擬似ユーザIDとしています。
TD_SESSIONIZE
公式ドキュメント: https://docs.treasuredata.com/articles/udfs#tdsessionize今回のWebログをセッション単位にデータにする上で、一番重要な関数です。
time値でソートし、タイムアウトを30分、セッションID生成の単位をユーザIDとしています。 ポイントとしては、事前に「distribute by」、「sort by 」を使って、ユーザID単位にtimeカラムでソートしておくことです。
TD_PARSE_USER_AGENT
公式ドキュメント: https://docs.treasuredata.com/articles/udfs#tdparseuseragentこれも非常に便利な関数で、Webログの解析ではよく利用します。ユーザーエージェントを元にOSやブラウザの判定をしてくれます。
TD_FIRST
公式ドキュメント: https://docs.treasuredata.com/articles/udfs#tdfirstアクセスログからセッション単位にサマリした際の一番最初（time値が一番小さい）のリファラとURLを取得しています。 つまり、流入元（リファラ）とランディングページ（進入ページ）が取得できます。
TD_LAST
公式ドキュメント: https://docs.treasuredata.com/articles/udfs#tdlastTD_FIRSTの逆です。 今回の場合はセッションの最後にアクセスしたページ、つまり離脱ページを取得することができます。
parse_url
公式ドキュメント: https://docs.treasuredata.com/articles/hive-functionsTDのUDFではなくhiveの関数です。 URLをパースしてホスト名やパス、指定したクエリパラメータの値を取得することができます。 これを使うことで、GoogleAnalyticsのパラメータを取得することができます。
最後にひとこと
実際の作業でよく使うHiveQLのクエリとUDFについて紹介させていただきました！
私自身、まだまだ知らないUDFもあり日々勉強しております。また次回、実例と一緒に学んだことを投稿できればと思います。
以上、TreasureDataでWEBログ分析をしてみたでした！
