プラットフォーム・ワン 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の処理への影響を避けるために、このようなクエリを無闇に実行することはできません。
システムへの負荷を軽減しつつ長期間のログを集計するために
- 集計対象のテーブルから必要なレコードのみを別テーブルに抜き出す
- 別テーブルに対して集計をかける
という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というテーブルに出力します。
td query -d mone -w -q extract_records.txt -P very-low -r td://@/p1_test/temp_table? -x
-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で長期間の集計をする方法についてプラットフォーム・ワンでの対応策を紹介しました。 分析対象レコードを一旦別テーブルに切り出すことにより、過大な負荷をかけることなく様々な集計クエリをアドホックに利用しています。
