Treasure Dataで長期間の集計

     - ビッグデータ  


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

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

[sql]
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
[/sql]

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

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

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

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

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

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

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

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

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

extract_records_master.txt

[sql]
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
[/sql]

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

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

[bash]
START_DATE=date '+%Y-%m-%d' -d "1 days ago"
END_DATE=date '+%Y-%m-%d'
sed -e ‘s/START_TIME/’${START_DATE}’/g’ extract_records_master.txt | sed -e ‘s/END_TIME/’${END_DATE}’/g’ > extract_records.txt
[/bash]

外部ファイルを指定してコマンドラインから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日分の処理を回すには以下の様なスクリプトで上記の処理をループで回します。

[bash]
DAY_NUM=30
CURSOL_NUM=0
while [ ${CURSOL_NUM} -le ${DAY_NUM} ] ; do
NUM=${CURSOL_NUM}
END_DATE=date '+%Y-%m-%d' -d "${CURSOL_NUM} days ago"
CURSOL_NUM=expr ${CURSOL_NUM} + 1
START_DATE=date '+%Y-%m-%d' -d "${CURSOL_NUM} days ago"

sed -e ‘s/START_TIME/’${START_DATE}’/g’ extract_records_master.txt | sed -e ‘s/END_TIME/’${END_DATE}’/g’ > extract_records.txt
td query -d mone -w -q extract_records.txt -P very-low -r td://@/p1_test/temp_table? -x
done
[/bash]

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

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

[sql]
select useragent, count(1)
from temp_table
group by useragent
[/sql]

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

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


DACエンジニア採用情報

  関連記事

GoogleAppsScriptとTreasureData REST APIを使ってサーバレスにTwitterのデータを取得

またまたTreasureDataネタです。 ただ、今回はクエリ系のネタではなく、GoogleAppsScriptとTreasureDataのREST APIを使ってTwitterのデータをTreasureDataに入れてみたので、その方法を紹介したいと思います。 はじめに ログデータだけではなく、公 …

D3.jsとその活用事例について

D3.jsとは? D3とは「Data Driven Document」の略で、データに基づいてドキュメントを操作するための JavaScript ライブラリです。 ご存知の方も多いと思いますが、ちょっとだけD3.jsの基本的な使い方、そして弊社プラットフォームでの利用についてご紹介したいと思います。 …

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

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

Amazon ElastiCache/Redisのパフォーマンス確認

はじめに こんにちは、AudienceOne開発部です。AudienceOne開発部ではいわゆるビッグデータと呼ばれる大量のデータをアドホックあるいは定常的に日々ETLだの集合演算だのをする一方で、様々な大規模データ処理ソリューションを継続的に検証しております。 本記事は、その中でもユーザが保持して …

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

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

HivemallでMinhash!〜似てる記事を探し出そう。〜

こんにちは。俺やで。 前回の投稿に続き(間が空きましたが)、 ビッグデータに対応したHiveで使える機械学習ライブラリ、 「Hivemall」の使い方、第2弾となります。 今回はMinhashという手法について書きたいと思います。 ※前回 【超入門】Hivemallで機械学習 〜Treasure D …

fastavroとjqでAVRO形式のファイルからデータを取得しよう

AVRO形式のファイルを取り扱いたい AVROとはApacheプロジェクトのひとつとして開発されているデータ交換形式です。 コンパクトなバイナリで高速なシリアライズ・デシリアライズが行えるため、サーバーログなどに利用されています。 弊社内での一部システムのログデータにも利用されているのですが、専用の …

Tableau 9.2で郵便番号の特性を地図で可視化してみる

Tableau 9.2から郵便番号地図が表示可能に 弊社ではデータ分析ツールのTableauを利用しています。オーディエンスデータの重複を分析したり、デモグラフィック属性を表示したりするなどデータの可視化に役立ちますTableauでは9.2から日本の郵便番号を用いて地図を可視化できるようになりました …

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

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

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

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