データサイエンス

PrestoでUNIX時間の範囲指定をしたらハマった話

GRI
 

最近になってPrestoというSQLに近いクエリで操作できるデータ分散処理基盤を扱うようになりましたが、
今まで書いてきたSQLクエリと同じ感覚でデータ抽出しようとしていたら痛い目に遭いました。

本記事ではその時の模様をお話をします。

Take Home Message

  • UNIX時間のカラムで抽出範囲指定をするときは、TD_TIME_RANGE関数を使うのがよい
    • Presto側で設定したPartitionを有効利用できるため
    • TIMESTAMPをUNIX時間に変換してから数値比較に持ち込むにしても、型に注意が必要
    • 公式ドキュメントが参考になる

状況

目次

課題

とあるテーブルから、2019年7月（日本時間）の一か月間分のログの行数を数えたいと思いました。

しかしそのテーブルlog_tableでは、時刻カラムtimeにUNIX時間でデータが入っていました。

さて、どう抽出すればよいでしょう？

正解例

SELECT COUNT(*) FROM log_table
WHERE TD_TIME_RANGE("time", '2019-07-01 00:00:00', '2019-07-02 00:00:00', 'Asia/Tokyo')
)

PrestoにはTD_TIME_RANGE関数が用意されていて、これを使うとタイムゾーン指定も含めて簡単に書けます。

アンチパターン

なにをやってしもたみや？

ところがどっこい、とある港区のデータ分析会社で働くT君は、
以下のようなクエリを書いて処理を行っていました。

SELECT COUNT(*) FROM log_table
WHERE time BETWEEN TO_UNIXTIME(TIMESTAMP '2019-07-01 00:00:00 Asia/Tokyo')
AND TO_UNIXTIME(TIMESTAMP '2019-07-02 00:00:00 Asia/Tokyo')

いったんTO_UNIXTIME関数でTIMESTAMPをUNIX時間に直して、BETWEEN句により範囲指定を行おうとしたわけです。

しかし、こうしてしまったがゆえに、待てど暮らせど結果が返ってこず、とんだ待ちぼうけを食らってしまいました。

さて、何が悪かったのでしょう？

やってはいけない理由

まずは前提として、UNIX時間の範囲指定を行うときは、必ず整数型同士の比較を行わなくてはいけません。

さもないと、Presto側で用意したPartitionをうまく利用してくれず、必要以上にスキャンを走らせることになります。

しかし、ドキュメントでTO_UNIXTIMEの仕様を見てみると、以下のようにありました：

to_unixtime(timestamp) → double

Returns timestamp as a UNIX timestamp.

はい、もうわかりましたね。

気が付かないうちに、INTEGERとDOUBLEで比較を行っていたのです。

　　　　　・・・

一応、

CAST(TO_UNIXTIME(TIMESTAMP '2019-07-02 00:00:00 Asia/Tokyo' AS INTEGER)

のように整数型に変換すれば問題なく実行できます。

ただ、せっかくTD_TIME_RANGE関数という文明の利器（？）があるのですから、恩恵にすがりましょう。

おわりに

先ほどのアンチパターンクエリですが、実行してみると以下のようなWARNINGが出ていたので問題に気付き、リンク先のページを見て解決できました。

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

Prestoは時刻操作関連の関数が通常のSQLと比べて独自なものが多く、
知らないといろいろと余計な苦労するな、と感じました。

皆様もPrestoを使うときはお気を付けを！

関連の記事