トレジャーデータUDFで効率の良いクエリを実現する - Date Function 編 -

トレジャーデータはクラウドでデータマネージメントサービスを提供しています。

トレジャーデータサービスは，スタンダードプランとしてバッチクエリであるHive, Pigをサポートしています。また，プレミアムプランまたはオプション追加によってアドホッククエリであるTQA（Backend: Presto）をサポートしています。

Treasure Data Hive UDF

無尽蔵の列指向データベースであるトレジャーデータのストレージですが，特に時系列データに対しては強力なパフォーマンスを発揮します。今回はその理由と，パフォーマンスを出すためのクエリの記述方法をご紹介します。

TDS バックエンドアーキテクチャ概略

トレジャーデータサービスでは，Realtime Storage と Archive Storage の二種類のストレージを利用しています。Treasure Agent (Fluentd) などからのストリーミングログコレクタは，まず Realtime Storage に蓄積されていき，一定のタイミングで Archive Storage に移すためのMapReduce を実行しています。

この MapReduce 処理によって一定時間以内のデータは全て同じチャンクに入ることになります。

トレジャーデータのクエリは，この2種類両方のストレージに同時アクセスすることで以下の点でパフォーマンス向上を実現しています。

1. 最も使用されやすい直近のデータは Realtime Storage から効率良く取得できるようになっている
2. 過去のデータも1時間単位でデータがマージされているので，時間による制約文のあるクエリに対しては，必要なチャンクだけにアクセスすることが可能

以上の理由によって，パフォーマンスを上げるにはできるだけ time に対して制約文を入れることが肝要となります。ここではそれを「time index pushdown」と呼ぶことにします。

Hive Language にもいくつかの Date Function がありますが，これらはトレジャーデータサービスのストレージのメリットを享受できません（余計なチャンクを見に行ってしまいます）。

トレジャーデータサービスでは，これら Hive オリジナルの Date Function に替わる UDF を実装しておりますので，time index pushdown のためには以下のコマンドをご活用下さい。

それではいくつかを説明していきましょう。

TD_TIME_RANGE()

boolean TD_TIME_RANGE(int/long unix_timestamp,
                      int/long/string start_time,
                      int/long/string end_time
                      [, string default_timezone = 'UTC'])

-- 2013年のデータを取得
SELECT ... WHERE TD_TIME_RANGE(time, "2013-01-01 JST", "2014-12-31 JST")

特定の期間レンジをデータ処理対象とする場合は TD_TIME_RANGE コマンドを使用します。1つ目の引数には time ( unix timestamp ) カラムを設定し，始まりと終わりを時間の文字列を設定します。

トレジャーデータサービスのパフォーマンスを上げるためには，何よりもまず TD_TIME_RANGE での絞り込みが使えるかどうかを考えることが肝要です。

さて，TD_TIME_RANGE と組み合わせて使う他のUDFを見ていきましょう。

TD_TIME_ADD()

long TD_TIME_ADD(int/long/string time,
                 string duration
                 [, string default_timezone = 'UTC'])

TD_TIME_ADD は時間に対して足し算を行います。以下の例の様に，start_time に対して end_time を，start_time からの足し算にすることによって特定期間のレコード抽出がより柔軟に行えます。

SELECT ... WHERE TD_TIME_RANGE(time,
                               "2013-01-01",
                               TD_TIME_ADD("2013-01-01", "1d"))

2番目の引数に指定できるフォーマットは，"1d", "-1d"（N日），"1h","-1h"（N時間），"1m","-1m"（N分），...などです。

TD_SCEDULED_TIME()

さらに TD_SCHEDULED_TIME で動的に時間を取得することによって TD_TIME_RANGE をより柔軟に使います。

long TD_SCHEDULED_TIME()

このファンクションはスケジューリングによって定められたジョブの実行される時間のtimestampを取得します。例えばデイリーのバッチ処理を 6:00 に実行されるように設定しておくと，そのジョブに記述されたTD_SCHEDULED_TIMEは 6:00 の時間で timestamp を生成します。

ここで注意すべき点は実際にジョブが実行された時間が，例えば他の処理の終了待ちがあって 7:00 であったとしても，このファンクションは 6:00 での timestamp を取得することになる点です。

TD_TIME_RANGE() + TD_SCHEDULED_TIME() + TD_TIME_ADD()

さて，これらのファンクションを活用すれば time index pushdown を利用するのはそれほど難しいことではありません。

-- ジョブの実行される時間までのちょうど1日分のレコードを抽出する
SELECT ... WHERE TD_TIME_RANGE(time,
                               TD_TIME_ADD(TD_SCHEDULED_TIME(), "-1d"),
                               TD_SCHEDULED_TIME())

-- 現時点から過去30日分のレコードを抽出する
SELECT ... WHERE TD_TIME_RANGE(time,
                               TD_TIME_ADD(TD_SCHEDULED_TIME(), "-30d"),
                               TD_SCHEDULED_TIME())

ここで注意すべきは「月初から現在まで」といった start_time に 2014-09-01 などの文字列として設定したい場合です。TD_TIME_FORMAT については後述しますが，以下は time index pushdown が利用できない例です：

-- 月初から現時点までのレコードを抽出する（time index pushdown が効かない例）
SELECT ... WHERE TD_TIME_RANGE(time,
                               TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'yyyy-MM-01'),
                               TD_SCHEDULED_TIME())

現時点では TD_TIME_FORMAT で動的に文字列を生成すると pushdown が効かなくなる仕様になっています。

ではこの用途で pushdown を実現するにはどうしたらよいでしょうか？現状だと次のように二重の TD_TIME_RANGE を使う方法があります：

-- 過去 31 日分のデータに絞り込む
TD_TIME_RANGE(time,
  TD_TIME_ADD(TD_SCHEDULED_TIME(), '-31d'),
  TD_SCHEDULED_TIME())
AND
-- さらに月初から現在までの時間に絞り込む
TD_TIME_RANGE(time,
  TD_TIME_FORMAT(TD_SCHEDULED_TIME(),
  yyyy-MM-01'),
  TD_SCHEDULED_TIME())

その他のUDFも見ていきましょう。

TD_TIME_FORMAT()

string TD_TIME_FORMAT(long unix_timestamp,
                      string format
                      [, string timezone = 'UTC'])

timestamp を特定フォーマットの文字列に変換するUDFとして上記のファンクションがあります。

SELECT TD_TIME_FORMAT(time, "yyyy-MM-dd HH:mm:ss z") ... FROM ...

2つ目の引数に時間の format を指定する事で1つ目の引数の timestamp を文字列変換します。

-- 2014年10月20日のデータをのみを取得
-- ただしこの例では time index pushdown はきかない（前述）
SELECT ... WHERE TD_TIME_FORMAT(time, "yyyy-MM-dd" ,"JST") = "2014-10-20"

また，3つ目の引数に "JST" を指定すれば（デフォルトは UTC）unixtimestamp を日本時間の日付に変換してくれます。

TD_TIME_PARSE()

long TD_TIME_PARSE(string time
                   [, string default_timezone = 'UTC'])

TD_TIME_FORMAT とは逆に，時間を表す文字列を unixtimestamp に変換します。これが使用されるのは，time カラム以外の時間を表す文字列カラムから timestamp を取得する場合です。

TD_TIME_FORMAT および TD_TIME_PARSE で使用できる時間文字列のフォーマットは以下をご参照下さい。

他にも時間に関するUDFや，それ以外の機能を果たすUDFも用意されております。これらのファンクションはトレジャーデータサービスに最適化されたものですので積極的にご活用ください。

トレジャーデータに関するお問合せはこちら

お問い合わせ | Treasure Data

 まで宜しくお願い致します。