【超基本】Treasure Data（Presto）初心者がSQLでデータ集計するときに陥りがちな罠

#想定読者

• 非エンジニア、もしくはSQLに詳しくないエンジニア
• 簡単なSQLは書けるが、Prestoは初めまして
• Treasure Dataを使い始めて、手元でデータ分析できるようになった（しなければならなくなった）マーケティング担当者とか

#３行で言うと

• Treasure Data初心者向けのPrestoのTips３つ
• ①メモリを食う処理は代替策を検討する
• 時間指定の際には、②時刻の省略　③タイムゾーンのつけ忘れ　に注意する

#Treasure Dataとは

• プライベートDMP製品の１つです。詳細はこちらの記事で。
• 競合は、Rtoasterが有名ですが、何が違うかというと、
• Treasure Data＝データレイク型プライベートDMP
• Rtoaster　　　＝アクション型プライベートDMP

らしいです。（こちらの受け売り）
要は、Treasure Dataはデータの蓄積・統合に強み、Rtoasterは統合後のマーケティングアクションに強み、ということでしょうか。

Treasure Dataには、コンソール上でSQLを書いて、簡単にビッグデータを集計できる機能がついているので、非エンジニアでも触る機会のある方が一定数いるのではと思っています。

が、ちょっとした仕様を理解していないと、そもそもクエリが回らなかったり、間違った集計値を出したりしてしまいます。。

この記事では、Treasure Data初心者がハマりがちな（というか私がハマった）罠をまとめてみました。

#Treasure Data初心者がSQLでデータ集計するときに陥りがちな罠
Treasure Dataでアドホック分析に使われる分析エンジンはPrestoです。
以下はPrestoでのSQLの書き方についての超基本的なTipsです。

##メモリオーバーの罠
これまでPrestoとかビッグデータでSQL叩いたことない人が陥りがちな罠ですが、億に近いレコード数に対して気軽にJOINやサブクエリを書くと、

Query exceeded max memory size of xx.xGB

```や
```Query exceeded maximum time limit of xx.xxm
```といったエラーが出て、結果が返ってきません。

（2019/12/26追記）
これを回避するためには、メモリを食う処理を減らせば良いのですが、
具体的に言うと、

- ```COUNT(DISTNCT x) ```
- ```UNION ```
- ```GROUP BY ```
- ```ORDER BY ```

あたりを使わないことが効果的らしいです。
　⇒["Exceeded Max Memory" に対処する](https://support.treasuredata.com/hc/ja/articles/115009729407--Exceeded-Max-Memory-%E3%81%AB%E5%AF%BE%E5%87%A6%E3%81%99%E3%82%8B)



- 特に、```COUNT(DISTINCT x) ```については、
代わりに```APPROX_DISTINCT(x) ```に置き換えるだけで手軽にメモリ消費を削減できるのでオススメです。
（厳密な数値集計が必要な場合を除く）

>ユニークユーザを使った集計をする際に，COUNT(DISTINCT)を利用して重複除去をすることがよくあります。（中略）
しかし Presto のようにメモリ上にデータを保持し，分散処理を得意とする分析エンジンにおいては，重複除去という処理が全データを1ノードのメモリ上に集約して処理しているために，リソースの大幅な利用及び処理に時間がかかることが知られています。
そこでPrestoでは，このユニーク数の算出を厳密に行うのではなく，推定値にすることで処理を効率的かつ高速に行うための関数の APPROX_DISTINCT が実装されています。
特に，ユニークユーザ数毎のKPIなど多くの集計ケースにおいてユニークユーザ数を厳密に求める必要がない場合が多く，こうしたケースにおいて有効に利用することができます。
[クエリのパフォーマンス改善のための Tips](https://support.treasuredata.com/hc/ja/articles/115009891468)



- また、サブクエリがあるクエリにおいて、「WITH句」を使うと、実行計画が変わり、メモリオーバーが回避できる場合もあります。
[分散SQLエンジン“Presto”のクエリチューニング](https://qiita.com/y-ken/items/31238bd71fbc04f77c60)



##時間指定の罠（TD_TIME_RANGE）
まず前提として、Treasure Dataで扱うようなビッグデータを効率的に検索するには、インデックスキーである「time」をWHERE句に入れることが常套手段です。
>（Tresure Dataでは）レコードの時間（time カラム）によってパーティショニングされるため，これ（Time Index）を利かせた（中略）クエリのパフォーマンスは，全スキャンのそれに比べて格段に向上します。

Treasure Dataでは、この「time」を条件に指定してデータを検索するための関数が多数用意されており、TD TIME 関数群と呼ばれます。
>TD TIME 関数群はこの Time Index の特徴を活かす事を目的としており，また，時間の計算や範囲指定といったやや複雑な時間演算を簡単にできる事を目的としたものと双対をなします。初級者と言えど，TD TIME 関数群は必ずマスターしてもらう必要のあるものですので，心して望んで下さい。
[初級ハンズオン [TD関数]（アクセスログ編）](https://support.treasuredata.com/hc/ja/articles/115011438008-%E5%88%9D%E7%B4%9A%E3%83%8F%E3%83%B3%E3%82%BA%E3%82%AA%E3%83%B3-TD%E9%96%A2%E6%95%B0-%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3%83%AD%E3%82%B0%E7%B7%A8-)


TD TIME関数群の中で主要な関数の１つにTD_TIME_RANGEがあります。
これは、開始時間を終了時間を指定して、一定期間のデータを取得する関数で、BETWEEN演算子に近い動きをします。

```sql
--TD_TIME_RANGE関数の例
SELECT *
FROM table
WHERE
  TD_TIME_RANGE(time,'開始時間','終了時間','JST')

　
ここからが本題ですが、例えば10月のデータを抽出したい場合、

TD_TIME_RANGE(time,'2019-10-01','2019-10-31','JST')

と書きたくなっちゃいませんか・・・？（僕はこう書いてました。。。）

しかしながら、この書き方で抽出されるのは、
2019年10月1日〜10月30日23:59:59までのデータです。31日のデータは含まれません。

'2019-10-31'のように時刻を省略して書くと、時刻は00:00:00だと見なされるため、上記のような抽出条件となります。

10月分のデータを抽出する正しいSQLは

TD_TIME_RANGE(time,'2019-10-01','2019-11-01','JST')

です。

ちなみに、TD_TIME_RANGEは「開始時間 <= time < 終了時間」で検索するという仕様ですので、上記のクエリ結果に11月1日00:00:00のデータは含まれず、キレイに10月分のデータのみが取得できます。（BETWEENとは異なる挙動なので、これも勘違いを生む一因です）

##デフォルトタイムゾーンの罠

Treasure Dataには、上記のTD_TIME_RANGEの他にも、時間を扱う関数が多数存在します。
しかし、初心者が陥りがちなのは、時間系の関数を使う際に、タイムゾーンの指定を忘れてしまうことです。

例えば、上記のTD_TIME_RANGEの例で言うと、

TD_TIME_RANGE(time,'2019-10-01','2019-11-01') --タイムゾーン指定無し

と書いてもSQL自体は問題なく動作します。

しかし、タイムゾーン（'JST'）を指定していないため、
デフォルトのタイムゾーンである'UTC'（世界標準時）が適用されてしまいます。

UTC（世界標準時）は、日本のタイムゾーンよりも９時間遅れているため、
10月1日 00:00:00〜10月31日 23:59:59で検索したつもりが、
9月30日 15:00:00〜10月31日 14:59:59で検索されてしまいます。

そのため、クエリの結果は本来の値とズレた集計値になってしまうのですが、ズレが微小な場合も多く、クエリのミスに気付かないこともあるのが厄介なところです。。

時間指定の際には、呪文のように'JST'をつける癖を付けましょう。

#まとめ

• メモリ足りないと言われたらDISTINCT, UNION, GROUP BY, ORDER BYあたりを無くせないか検討しよう（特にAPPROX_DISTINCTの検討）
• TD_TIME_RANGEの日付のみ指定は00:00:00と見なされるので注意
• 時間系の関数に'JST'を忘れずに

・・・今振り返ってみると、まとめるのも恥ずかしいくらい超基本事項ですが、少しでもこれからTreasure Data（Presto）を触る方の参考になれば・・・！

他にも押さえておいたほうがよいポイントがありましたら、ぜひコメントでお知らせください！