ログってなんぼ

日々のメモです

TreasureData:昨日一日分のデータに対して投げるクエリ(完成版)

追記

delayを用いたほうがスマートなので

TreasureData:スケジュールにdelayを設定してcronを遅延実行する | ログってなんぼ はてなブックマーク - TreasureData:スケジュールにdelayを設定してcronを遅延実行する | ログってなんぼ

も参考にどぞ。


さてさて。TreasureData Advent Calender2013でも作ったほうが良かったなと思うくらいTreasureDataばっかりやってますが。

TD_TIME_RANGE()でTime indexを有効にする際、日付部分の引数は文字列で明示しましょうみたいなことを

TreasureData:TD_TIME_RANGE()の引数は明示しないとクエリの最適化がされない | ログってなんぼ はてなブックマーク - TreasureData:TD_TIME_RANGE()の引数は明示しないとクエリの最適化がされない | ログってなんぼ

で書いたわけですが、今日Facebook経由でTDのエンジニアの方から続報頂いて試してみたところ、うまくいきましたのでメモ。

cronとTD_SCHEDULED_TIMEを使う

TD_SCHEDULED_TIMEはTreasureDataにcron的スケジュールとして登録した時間を取得して動作するUDFですが、スケジュール設定とスクリプトを下記のように組み合わせることで、望む動作が実現しました。

cron(スケジュール)とWHERE句を工夫

下記のカタチが基本となるのかなと。

0 0 * * *

にcron設定して

WHERE
TD_TIME_RANGE(
    time,
    TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d'),
    TD_SCHEDULED_TIME()
)

こんなかんじで書くことで、「昨日一日分」のデータを取得することが出来ます。(つか出来ました)

TD_SCHEDULED_TIME()を用いる際にはTD_TIME_FORMAT()は必要ありません。

と言うより、TD_TIME_FORMAT()でTD_SCHEDULED_TIME()を囲ってしまうとTime indexが有効とならないようです。

データが揃ってから実行したい

当然ですが元となるデータはtd-agent(fluentd)で送っているため、00:00にクエリが動作すると前日の23:59:59とかのデータがまだTreasureDataに来ていない可能性もあります。

それに加え、TDの方でサマったデータを取得して更にゴニョゴニョする必要がある&もらったデータとガッチャンコしたいこちら側のデータが日が変わった直後だとまだ生成されていないという理由で、安全バッファも含めて朝4時に前日分のデータを取得するようにしました。

設定したスケジュール

$ td sched:list
+----------+-----------+----------+---------------------------+-------+----------+--------+----------+
| Name     | Cron      | Timezone | Next schedule             | Delay | Priority | Result | Database |
+----------+-----------+----------+---------------------------+-------+----------+--------+----------+
| my_sched | 0 4 * * * | Tokyo    | 2013-12-08 04:00:00 +0900 | 0     | NORMAL   |        | test     |

毎朝4時(JST)

実行されるクエリ

SELECT
    v[ 'hoge' ]
    ,COUNT( 1 ) AS cnt
  FROM
    accesslog
  WHERE
    TD_TIME_RANGE (
      TIME
      ,TD_TIME_ADD (
        TD_SCHEDULED_TIME (
        )
        ,'-28h'
      )
      ,TD_TIME_ADD (
        TD_SCHEDULED_TIME (
        )
        ,'-4h'
      )
      ,'JST'
    )
  GROUP BY
    v[ 'hoge' ] CLUSTER BY cnt

RANGE効いた!

started at 2013-12-07T14:03:30Z
Hive history file=/mnt/hive/tmp/2957/hive_job_log__1667882072.txt
**
** Time indices:
**    Time index: [2013-12-05 15:00:00 +0000, 2013-12-06 14:59:59 +0000)
**

うまくいきました。(個人的には、JSTで動かした時のログはJSTで出て欲しい・・・)

ログはUTCで表示されてますが、想定通り4時に動かして28時間前から4時間前までのデータに対して(JST 2013-12-06 00:00:00からJST 2013-12-06 23:59:59まで)のクエリが正常にスタートしました。Time indexもちゃんと動作してますね。

delayでもいいかも?(要検証)

Here, @hourly is the same as the cron 0 * * * *. The -D parameter allows you specify a delay time in seconds. In the example above, we set the delay to 1800 seconds or 30 minutes. The job will execute 30 minutes after each hour. 引用元:Scheduled Jobs | Treasure Data はてなブックマーク - Scheduled Jobs | Treasure Data

@hourlyの時についての説明に、delay指定のことが書かれていました。@dailyでも使えるのか試してみようと思いましたが、とりあえず上記方法でうまくいったので一旦コレで様子を見ることにします。