べにやまぶろぐ

技術寄りの話を書くつもり

Treasure Data でクエリを書く時に真っ先に頭に浮かべたい UDF、それが TD_TIME_RANGE()

Treasure Data SQL

Treasure Data での時間の範囲指定にはとにかく TD_TIME_RANGE() を使おう

この記事で言いたいことはこれにつきます。

Treasure Data での唯一のパーティションキーは time だけ

Treasure Data ではユーザがインデックスを作成したりパーティションキーを指定することはできず、あるのは time カラムによるパーティションだけです。

例えば、

SELECT 
  time, 
  td_client_id
FROM pageviews
ORDER BY time ASC

はもちろんフルスキャンですが、ここで 2017-03-01 以降のログが欲しい！というとき

SELECT 
time, 
td_client_id
FROM pageviews 
WHERE '2017-03-01' < to_iso8601(from_unixtime(time))
ORDER BY time ASC

とかすれば OK… ではありません。

このクエリを打つと TD の実行ログのところに

started at 2017-03-13T17:04:38Z
executing query: SELECT 
time, 
td_client_id
FROM pageviews 
WHERE '2017-03-01' < to_iso8601(from_unixtime(time))
ORDER BY time ASC
**
** WARNING: time index filtering is not set on pageviews
** This query could be very slow as a result.
** Please see https://docs.treasuredata.com/articles/presto-performance-tuning#leveraging-time-based-partitioning
**
Query plan:
- Stage-0
    Partitioning: SINGLE
    PartitionFunction: SINGLE
    -> Output[7]
        Columns: time = time:bigint, td_client_id = td_client_id:varchar
        -> Sort[3]
            OrderBy: time ASC NULLS LAST
            -> RemoteSource[13]
                Sources: Stage-1
- Stage-1
    Partitioning: SOURCE
    PartitionFunction: UNKNOWN
    -> Project[12]
        Assignments: 
        -> Filter[11]
            Condition: ('2017-03-01' < "to_iso8601"("from_unixtime"(CAST("time" AS DOUBLE))))
            -> TableScan[0]
                Table: pageviews
                Columns: td_client_id:varchar = td_client_id:"td_client_id",

などと出ると思います。

注目すべきは

WARNING: time index filtering is not set on pageviews This query could be very slow as a result. ** Please see https://docs.treasuredata.com/articles/presto-performance-tuning#leveraging-time-based-partitioning

のところで、お前のクエリ（フルスキャンで）くそ重くなるからこのURL見て出直してこいって親切にURLまで書いてあります。

ここでこのクエリを TD_TIME_RANGE() を使って

SELECT 
time, 
td_client_id
FROM pageviews 
WHERE TD_TIME_RANGE(time, '2017-03-01', NULL, 'JST')
ORDER BY time ASC

などと書くと

started at 2017-03-13T17:07:08Z
executing query: SELECT 
time, 
td_client_id
FROM pageviews 
WHERE TD_TIME_RANGE(time, '2017-03-01', NULL, 'JST')
ORDER BY time ASC
Query plan:
- Stage-0
    Partitioning: SINGLE
    PartitionFunction: SINGLE
    -> Output[7]
        Columns: time = time:bigint, td_client_id = td_client_id:varchar
        -> Sort[3]
            OrderBy: time ASC NULLS LAST
            -> RemoteSource[13]
                Sources: Stage-1
- Stage-1
    Partitioning: SOURCE
    PartitionFunction: UNKNOWN
    -> Project[12]
        Assignments: 
        -> Filter[11]
            Condition: ("time" BETWEEN BIGINT '1488294000' AND 9223372036854775806)
            -> TableScan[0]
                Table: pageviews
                Columns: td_client_id:varchar = td_client_id:"td_client_id", time:bigint = time:"time"
                ** Time indexes:
                    Time index: [2017-02-28 15:00:00 UTC, 9999-12-31 23:59:59 UTC]

となって警告が消え、

** Time indexes:
   Time index: [2017-02-28 15:00:00 UTC, 9999-12-31 23:59:59 UTC]

などと確かに time index が効いていそうな感じが醸し出されています。

手元の環境でこの実行速度を比べると 45sec だった実行時間が 28sec （およそ1.6倍の性能向上）になりました。

TD_TIME_RANGE() を使うべし。BETWEEN も要注意！

Treasure Data を利用する上で最も簡単でかつ有効なアドバイスは、「時間を扱うときは必ず UDF を使え」というものです。

上の警告メッセージにもありますが https://docs.treasuredata.com/articles/performance-tuning を読むといろんな注意点があることがわかります。

例えば先頭にある 1) WHERE time <=> Integer ですが

1) WHERE time <=> Integer

When the ‘time’ field within the WHERE clause is specified, the query parser will automatically detect which partition(s) should be processed.

とあり、time をWHERE句に入れると自動的にパーティションを絞ってくれるように書いてありますが

Please note that this auto detection will not work if you specify the time with float instead of int.

とあるように int 以外の型で指定すると無効になる、というのがわかります。

例えば

SELECT field1, field2, field3 FROM tbl WHERE time > 1349393020

SELECT field1, field2, field3 FROM tbl WHERE time > 1349393020 + 3600

SELECT field1, field2, field3 FROM tbl WHERE time > 1349393020 - 3600

は GOOD ですが

SELECT field1, field2, field3 FROM tbl WHERE time > 13493930200 / 10 

SELECT field1, field2, field3 FROM tbl WHERE time > 1349393020.00

は BAD です。直接 float で比較するケースは余りない気がしますが除算をしてしまうとダメだということです。当然前述の文字列比較もダメなわけです。

さらには BETWEEN、これも BAD でした。

SELECT field1, field2, field3 FROM tbl WHERE time BETWEEN 1349392000 AND 1349394000

普通にSQL書く感覚だとつい書いてしまうと思います。

これに対して TD_TIME_RANGE() は int/long/string を受け付けるので

TD_TIME_RANGE(time, '2017-03-01', '2017-03-10', 'JST')

みたいな書き方が可能です（BETWEEN とは異なり終端期間を含まないことに注意）。しかもタイムゾーンも簡単に指定できます。

つまり、何も考えずに TD_TIME_RANGE() を使うのが最も間違い無いのです。

TD には便利な時間系の UDF が揃っていますのでDATE系の標準SQLの関数を使う前にまず

https://docs.treasuredata.com/articles/udfsdocs.treasuredata.com

をご覧になることをお勧めします。

補足：TD_TIME_RANGE() と TD_TIME_FORMAT() を併用するとダメなのか？

上のドキュメントによると TD_TIME_RANGE() に TD_TIME_FORMAT() を突っ込むと time index が無効化されると書いてあります。

However, if you use TD_TIME_FORMAT UDF or division in TD_TIME_RANGE, time partition opimization doesn’t work. For instance, the following conditions disable optimization.

また、

http://blog-jp.treasuredata.com/entry/2014/11/29/165415blog-jp.treasuredata.com

にも

ここで注意すべきは「月初から現在まで」といった 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 が効かなくなる仕様になっています。

と書いてあります。

しかしこれを実行してみると

started at 2017-03-11T18:02:34Z
executing query: SELECT TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')
FROM pageviews 
WHERE TD_TIME_RANGE(time, TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'yyyy-MM-01'), TD_SCHEDULED_TIME(), 'JST')
ORDER BY time ASC
Query plan:
- Stage-0
    Partitioning: SINGLE
    PartitionFunction: SINGLE
    -> Output[7]
        Columns: _col0 = td_time_format:varchar
        -> Project[14]
            Assignments: 
            -> Sort[3]
                OrderBy: time ASC NULLS LAST
                -> RemoteSource[13]
                    Sources: Stage-1
- Stage-1
    Partitioning: SOURCE
    PartitionFunction: UNKNOWN
    -> Project[12]
        Assignments: td_time_format:varchar = "td_time_format"("time", CAST('yyyy-MM-dd' AS VARCHAR), CAST('JST' AS VARCHAR))
        -> Filter[11]
            Condition: ("time" BETWEEN BIGINT '1488294000' AND BIGINT '1489287599')
            -> TableScan[0]
                Table: pageviews
                Columns: time:bigint = time:"time"
                ** Time indexes:
                    Time index: [2017-02-28 15:00:00 UTC, 2017-03-12 02:59:59 UTC]

となりちゃんと指定されたパーティションを見に行っているように見えます。

Presto だからかな？と思って Hive で投げても

Hive history file=/mnt/hive/tmp/5397/hive_job_log_c1869776-51fb-4138-8a6f-141dfb872efd_955348095.txt
**
** Time indices:
**    Time index: [2017-02-28 15:00:00 +0000, 2017-03-12 02:59:59 +0000]
**

となっていてドキュメントが書かれた時分よりクエリ最適化が進化して施されている様子でした。

気になって Treasure Data に問い合わせたところ、TD_ 系の UDF はすでに time index pushdown に対応していて、ドキュメントも近く更新されるということでした。これで安心して TD_TIME/DATE_* が使えますね。