【入門編】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)

 

実際のテーブルは以下のようになっています。
td_demo_www_access

実行したクエリ

具体的には、WEBログからセッション単位のデータを作成しています。

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

結果

クエリの実行結果は以下のようなものになります。
resultdata

クエリ解説

それでは、実際のクエリで使っている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#tdlast
 

TD_FIRSTの逆です。
今回の場合はセッションの最後にアクセスしたページ、つまり離脱ページを取得することができます。

parse_url

公式ドキュメント: https://docs.treasuredata.com/articles/hive-functions
 

TDのUDFではなくhiveの関数です。
URLをパースしてホスト名やパス、指定したクエリパラメータの値を取得することができます。
これを使うことで、GoogleAnalyticsのパラメータを取得することができます。

最後にひとこと

実際の作業でよく使うHiveQLのクエリとUDFについて紹介させていただきました!

私自身、まだまだ知らないUDFもあり日々勉強しております。また次回、実例と一緒に学んだことを投稿できればと思います。

以上、TreasureDataでWEBログ分析をしてみたでした!


DACエンジニア採用情報

  関連記事

【超入門】Hivemallで機械学習_サムネイル
【超入門】Hivemallで機械学習 〜Treasure Dataでロジスティック回帰編〜

こんにちは。俺やで。 ビッグデータとかデータサイエンティストとかいう言葉が未だブームですね。 (「データサイエンティスト」は下火か。) ビッグデータ扱えるエンジニアも、 統計解析ができるアナリストも、 どっちもできるスーパーマンも世の中にはたくさんいますが、 ビッグデータも統計解析も扱えるインフラは …

6914441342_605f947885
Treasure Dataの新機能(Data Tank)をAudienceOneのレポート機能で利用した話

Data Tankとは? Treasure Dataの新機能でTreasure Dataのプラットフォーム上に構築されたデータマートです。 Tableau等のBIツールとの接続を想定されており、AWSでいうところのRedshift的なものだと考えるとわかりやすいかと。 Data TankはPostg …

bigdata
HyperLoglogでcount distinctを速くする

こんにちは。俺やで。 HyperLoglogについて書きます。おもしろいです。名前が。 ■1. HyperLoglogとは? count distinctを速くするアルゴリズム 以前、Minhashについて書きました。 (Treasure Dataさんのブログにも載せていただきました。ありがとうござ …

logomono-tableau-software-mono
Tableauを利用してMySQLとRedshiftのクロスDBジョインを実現する

はじめに RedshiftやTreasureDataなどのデータマート用のDBにはID単位の解析結果が格納され、ローカルのMySQLにはIDに紐づいた名称マスタが管理されている構成の場合、データマートのクロス集計結果に対してIDに紐づいた名称を付与したいことがあります。 データマート用に用意したDB …

heatmap
巨大データベースのスケールアップと引越作業

はじめに ビッグデータ解析部でオーディエンスデータ解析基盤の開発、運用を担当している Mike です。 弊社ではインターネット広告配信ログをはじめとする「ビッグデータ」と呼ぶにふさわしいデータボリュームを扱うオーディエンスデータ解析基盤を構築しています。今秋、そのうちの1構成要素である、データサイズ …

PPG_anteli-kunatokei_TP_V
Treasure Dataで大規模なマスタデータを扱う際にはtimeカラムインデックスを活用しよう

DACではTreasure Dataを利用して各種データの蓄積や集計を行っています。Treasure Dataは時系列のデータを扱うのに特にすぐれたアーキテクチャなのですが、セグメントIDとユーザーIDの組み合わせといった大量のマスタデータを利用した計算にも利用することもできます。そのような場合にt …

image1
トレジャーデータの新機能「Data Connector」でクライアントレスなビッグデータ連携を実現する

トレジャーデータは、スキーマレスな大量のデータ(ビッグデータ)をパブリッククラウド上に保管して集計や抽出をするためのサービスなのですが、他システムからの連携データをトレジャーデータのテーブルに格納するまでが一苦労でした。 他システムとの外部連携を行う場合、一般的にローカルサーバー内のストレージを外部 …

data-tenki
気象予報士とビッグデータ解析の意外な関係

DACから気象予報士が誕生しました ビッグデータ解析部のMikeです。 2015年1月の気象予報士試験に合格し、めでたく4月からアドテク業界ただ一人(本当?)の気象予報士となりました 。 そんなわけで、今回は気象予報士とビッグデータ解析の関係についてお話したいと思います。 なぜ気象予報士を目指したか …

11396380473_26f323b1e4_z
Google BigQuery / Tableauを使ってみた

TableauからGoogle BigQueryへ接続してみました。 弊社で利用しているTreasureDataからデータ出力してBigQueryへロード、Tableauから接続まで実際に行った手順について記載します。 TreasureDataからAmazonS3へデータ出力 まず、データが蓄積され …

tf
ディープラーニングで「顔が似ているAKB48のメンバーを教えてくれるbot」を構築

概要 こんにちは、システム開発部の中村です。 今回は、Facebook Messenger APIを利用して、 画像をアップロードすると、似ているAKB48のメンバーを教えてくれるbotを実装しました。 尚、ディープラーニングやTensorFlowそのものの解説というより、 「エンジンとしてディープ …