【入門編】TreasureDataでWEBログ分析をしてみた
この記事は 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ログ分析をしてみたでした!

関連記事
-
-
気象予報士とビッグデータ解析の意外な関係
DACから気象予報士が誕生しました ビッグデータ解析部のMikeです。 2015年1月の気象予報士試験に合格し、めでたく4月からアドテク業界ただ一人(本当?)の気象予報士となりました 。 そんなわけで、今回は気象予報士とビッグデータ解析の関係についてお話したいと思います。 なぜ気象予報士を目指したか …
-
-
Tableau 9.2で郵便番号の特性を地図で可視化してみる
Tableau 9.2から郵便番号地図が表示可能に 弊社ではデータ分析ツールのTableauを利用しています。オーディエンスデータの重複を分析したり、デモグラフィック属性を表示したりするなどデータの可視化に役立ちますTableauでは9.2から日本の郵便番号を用いて地図を可視化できるようになりました …
-
-
fastavroとjqでAVRO形式のファイルからデータを取得しよう
AVRO形式のファイルを取り扱いたい AVROとはApacheプロジェクトのひとつとして開発されているデータ交換形式です。 コンパクトなバイナリで高速なシリアライズ・デシリアライズが行えるため、サーバーログなどに利用されています。 弊社内での一部システムのログデータにも利用されているのですが、専用の …
-
-
Treasure Dataの新機能(Data Tank)をAudienceOneのレポート機能で利用した話
Data Tankとは? Treasure Dataの新機能でTreasure Dataのプラットフォーム上に構築されたデータマートです。 Tableau等のBIツールとの接続を想定されており、AWSでいうところのRedshift的なものだと考えるとわかりやすいかと。 Data TankはPostg …
-
-
トレジャーデータの新機能「Data Connector」でクライアントレスなビッグデータ連携を実現する
トレジャーデータは、スキーマレスな大量のデータ(ビッグデータ)をパブリッククラウド上に保管して集計や抽出をするためのサービスなのですが、他システムからの連携データをトレジャーデータのテーブルに格納するまでが一苦労でした。 他システムとの外部連携を行う場合、一般的にローカルサーバー内のストレージを外部 …
-
-
GoogleAppsScriptとTreasureData REST APIを使ってサーバレスにTwitterのデータを取得
またまたTreasureDataネタです。 ただ、今回はクエリ系のネタではなく、GoogleAppsScriptとTreasureDataのREST APIを使ってTwitterのデータをTreasureDataに入れてみたので、その方法を紹介したいと思います。 はじめに ログデータだけではなく、公 …
-
-
Tableauを利用してMySQLとRedshiftのクロスDBジョインを実現する
はじめに RedshiftやTreasureDataなどのデータマート用のDBにはID単位の解析結果が格納され、ローカルのMySQLにはIDに紐づいた名称マスタが管理されている構成の場合、データマートのクロス集計結果に対してIDに紐づいた名称を付与したいことがあります。 データマート用に用意したDB …
-
-
D3.jsとその活用事例について
D3.jsとは? D3とは「Data Driven Document」の略で、データに基づいてドキュメントを操作するための JavaScript ライブラリです。 ご存知の方も多いと思いますが、ちょっとだけD3.jsの基本的な使い方、そして弊社プラットフォームでの利用についてご紹介したいと思います。 …
-
-
Treasure Dataで大規模なマスタデータを扱う際にはtimeカラムインデックスを活用しよう
DACではTreasure Dataを利用して各種データの蓄積や集計を行っています。Treasure Dataは時系列のデータを扱うのに特にすぐれたアーキテクチャなのですが、セグメントIDとユーザーIDの組み合わせといった大量のマスタデータを利用した計算にも利用することもできます。そのような場合にt …
-
-
【入門編】TreasureDataでサイトのアクセス解析をしてみた~第2弾!~
今回もやります、集計クエリ解説シリーズ第2弾!! 前回は、Webログからセッション単位のデータを作成するだけでした。 第2弾では作成したテーブルを元に、より実践的なアクセス解析、サイト分析で使えるHiveQLについて、実際に使用したクエリとともに解説していきたいと思います。 今回やったこと 利用した …