Treasure Dataで長期間の集計

     - ビッグデータ  


プラットフォーム・ワン T氏です。プラットフォーム・ワンでは、DSPのMarketOneとSSPのYIELD ONE提供しています。

MarketOneやYIELD ONEのログを調査する場合にTreasure Dataを使うことがあります。Treasure Dataでは大量のデータに対してHiveやPrestoといったSQLライクなクエリで集計することができます。
Treasure Dataで保持しているログは3ヶ月分以上あるので、例えば1ヶ月のMarketOneにおけるスマホのインプレッション数をユーザーエージェント毎に集計するなら以下の様なHiveクエリで計算できます。

select useragent, count(1)
from imp
where TD_TIME_RANGE(time
                    ,'2014-12-01 00:00:00'
                    ,'2015-01-01 00:00:00'
                    ,'JST')
  and device_type = 'Phone'
  and useragent is not null
group by useragent

とはいえ集計期間が長く、データ量が膨大(MarketOneがリクエストを受けるインプレッションはPCとスマホを合わせて月間700億を超えます)なため処理時間がかかる上にCPUに負荷をかけてしまいます。並行して走っている他のTreasure Dataの処理への影響を避けるために、このようなクエリを無闇に実行することはできません。

システムへの負荷を軽減しつつ長期間のログを集計するために

  1. 集計対象のテーブルから必要なレコードのみを別テーブルに抜き出す
  2. 別テーブルに対して集計をかける

という2段階の処理をすることがあります。

1.集計対象のテーブルから必要なレコードのみを別テーブルに抜き出す

1ヶ月分のレコードを一度に抜き出すのはCPUの負荷が大きいので、1日分ずつ処理を回します。

そのためにレコード抽出のクエリを外部ファイルに記述し、スクリプトで外部ファイルのクエリの日時を1日ずつ書き換えてから、コマンドラインでTreasure Dataを実行します。

クエリを外部ファイルに記述

開始日と終了日は後で書き換えるため取り敢えずSTART_TIME、END_TIMEという文字列で記述します。

extract_records_master.txt
select useragent
from imp
where TD_TIME_RANGE(time
                   ,'START_TIME 00:00:00'
                   ,'END_TIME 00:00:00'
                   ,'JST')
  and device_type = 'Phone'
  and useragent is not null

外部ファイルの日時をスクリプトで書き換え

例えば昨日1日分の集計のためには以下のスクリプトで開始日を昨日、終了日を今日に置換してextract_records.txtというファイルに出力します。

START_DATE=<code>date '+%Y-%m-%d' -d &quot;1 days ago&quot;</code>
END_DATE=<code>date '+%Y-%m-%d'</code>
sed -e 's/START_TIME/'${START_DATE}'/g' extract_records_master.txt | sed -e 's/END_TIME/'${END_DATE}'/g' &gt; extract_records.txt

外部ファイルを指定してコマンドラインからTreasure Dataを実行

クエリの実行結果をp1_testというDBのtemp_tableというテーブルに出力します。


-d mone: moneというDBでクエリを実行します
-w: クエリが終わるまで待ちます
-q extract_records.txt: クエリに外部ファイルのextract_records.txtを指定します
-P very-low: クエリの優先度を最低にします
-r td://@/p1_test/temp_table?: 実行結果をp1_testというDBのtemp_tableというテーブルに出力します
-x: クエリの結果をコンソール画面に出力しません(結果のサイズが大きい場合画面出力に時間がかかるのを防ぎます)

1ヶ月分の処理を回す

直近30日分の処理を回すには以下の様なスクリプトで上記の処理をループで回します。

DAY_NUM=30
CURSOL_NUM=0
while [ ${CURSOL_NUM} -le ${DAY_NUM} ] ; do
  NUM=${CURSOL_NUM}
  END_DATE=<code>date '+%Y-%m-%d' -d &quot;${CURSOL_NUM} days ago&quot;</code>
  CURSOL_NUM=<code>expr ${CURSOL_NUM} + 1</code>
  START_DATE=<code>date '+%Y-%m-%d' -d &quot;${CURSOL_NUM} days ago&quot;</code>

  sed -e 's/START_TIME/'${START_DATE}'/g' extract_records_master.txt | sed -e 's/END_TIME/'${END_DATE}'/g' &gt; extract_records.txt
  td query -d mone -w -q extract_records.txt -P very-low -r td://@/p1_test/temp_table? -x
done

2.別テーブルに対して集計をかける

以下のクエリを実行します。

select useragent, count(1)
from temp_table
group by useragent

temp_tableにレコードを抽出する時点で条件を絞っているのでwhere句は不要です。

以上、Treasure Dataで長期間の集計をする方法についてプラットフォーム・ワンでの対応策を紹介しました。
分析対象レコードを一旦別テーブルに切り出すことにより、過大な負荷をかけることなく様々な集計クエリをアドホックに利用しています。


DACエンジニア採用情報

  関連記事

toadstool
【Hivemall入門】RandomForestで毒キノコ推定モデルを作る

こんにちは。俺やで。 今回も前回から間が空いてしましたが、ビッグデータに対応したHiveで使える機械学習ライブラリ、 Hivemallの使い方について、書かせていただければと思います。 なお今回はQiitaのTreasure Data / Advent Calender 2015の12/3日分として …

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

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

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

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

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

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

bigdata
HyperLoglogでcount distinctを速くする

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

sqlカクテル
【入門編】TreasureDataでサイトのアクセス解析をしてみた~第2弾!~

今回もやります、集計クエリ解説シリーズ第2弾!! 前回は、Webログからセッション単位のデータを作成するだけでした。 第2弾では作成したテーブルを元に、より実践的なアクセス解析、サイト分析で使えるHiveQLについて、実際に使用したクエリとともに解説していきたいと思います。 今回やったこと 利用した …

chain
PyStanによるはじめてのマルコフ連鎖モンテカルロ法

はじめに こんにちは。システム開発部の中村です。 社内で行っている『データ解析のための統計モデリング入門』(所謂緑本)の輪読会に参加した所、 大変わかりやすい本だったものの、Macユーザには悲しい事に実装サンプルがWinBUGSだったため、 9章の一般化線形モデルのベイズ推定によるアプローチをPyt …

4229031035_bc1650a045_o
【入門編】TreasureDataでWEBログ分析をしてみた

この記事は Treasure Data Advent Calendar 2015 – Qiita の24日目の記事です。 こんにちは。 今回はWEBログの集計や解析をする際によく使うHiveQLのクエリと、UDF(User Defined Functions)について実際の集計クエリを使 …

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

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

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

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