Treasure Data & (一部Presto) の知っておいて便利だった関数 #TreasureData

結構ちゃんと調べて知っておくと無駄なクエリ書かなくて済むのがある。

このあたりのドキュメントが基本
- https://prestodb.io/docs/current/
- http://docs.treasuredata.com/articles/presto-udfs

Case 1. X日前からの日付指定したい時: TD_TIME_ADD ,TD_TIME_RANGE, TD_SCHEDULED_TIME

集計する時　X日前までほしい、とかいう要件多いけど、「今日が何日で何日前までで・・・」とかカレンダー見るのめんどい。

下記を覚えとけばいつでも実行タイミングからX日前とかで取得できるので何も考えずにコピペで使いまわせる。

TD_SCHEDULED_TIME でクエリの実行タイミングを起点とし、TD_TIME_ADDで1日前にして、それをTD_TIME_RANGEで区間としている

SELECT
  :
WHERE 
  -- 1日間のデータなら -1d。一ヶ月なら-30dとか。
  TD_TIME_RANGE(time,
    TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d', 'JST'), 
    null,
    'JST'
  )

覚えづらいのでだいたいどっかからコピペしてくる。

Case 2. URLのパースしたい(presto): URL function

prestoの標準関数
regexp_likeとかで頑張りたくなるけどurl_extract_***という関数が用意されている。
超便利。

SELECT *
FROM access
WHERE 
  url_extract_host(referer) = "google.com"
  -- 正規表現でやるとこんな感じ
  -- regexp_like(referer, '.*google.com/.*')

Case 3. UAからのブラウザ判定したい：TD_PARSE_AGENT

ブラウザをUAから簡単に判定してくれる関数。超便利。

SELECT
  TD_PARSE_AGENT(user_agent)['os_family']
  TD_PARSE_AGENT(user_agent)['os_major']
  TD_PARSE_AGENT(user_agent)['os_minor']
  TD_PARSE_AGENT(user_agent)['ua_family']
  TD_PARSE_AGENT(user_agent)['ua_major']
  TD_PARSE_AGENT(user_agent)['ua_minor']
  TD_PARSE_AGENT(user_agent)['device']
FROM access

os_family	os_major	os_minor	ua_family	ua_major	ua_minor	device
Windows 7	null	null	IE	11	0	Other
Android	5	0	Chrome Mobile	47	0	SC-04F

この関数使うときだいたいIEだけバージョン取りたいとかなるのでWITHと組み合わせてこんなふうにする

WITH a AS (
  SELECT 
    TD_PARSE_AGENT(user_agent)['ua_family'] AS family,
    TD_PARSE_AGENT(user_agent)['ua_major'] AS version
  FROM 
    access
),
b AS (
  SELECT
    if(family = 'IE', family || version, family) AS browser
  FROM a
)
SELECT * FROM b

Case 4. ボット判定したい TD_PARSE_AGENT(agent)['category']

TD_PARSE_AGENTは、ボット判定などにも便利
アクセスログ解析だとBotも厄介な問題。だがcategoryを利用すると、これが結構いい感じに解決出来る。

値としては "pc", "smartphone", "mobilephone", "appliance", "crawler", "misc", "unknown"のどれかが返ってくるようなので、これを判定すると良い

SELECT 
  *
  TD_PARSE_AGENT(user_agent)['ua_family'] AS family,
  TD_PARSE_AGENT(user_agent)['ua_major'] AS version
FROM
  access
WHERE TD_PARSE_AGENT(user_agent) IN ['pc', 'smartphone']

Case 5. X分間のアクセスを同一としてセッション数で取りたい: TD_SESSIONIZE

第三引数をキーとして、第二引数で指定した時間以内のアクセスであれば同一のセッションとしてIDを生成してくれる。
滞在時間とか知りたい時良さそう。
TD_SESSIONIZEする値は、ソートされている必要があるので注意（これちゃんと読んでなくて

SELECT TD_SESSIONIZE(time, 3600, ip_address) as session_id
FROM ...
ORDER BY ip_address, time 
-- ↑ソートしないと正しく出ない！

Case 6. arrayのunique化(presto)したい : `map_keys`, `histogram`

ハック的。array_aggで特定のカラムの値をarray<T>型にしてくれのだが、同値が大量に並んだ状態になる

SELECT 
  some_name,
  map_keys(
    histogram(some_type)
  )
FROM foo
GROUP BY some_name

Case 7. 大きいテーブルに対して試しで実行したい(presto) TABLESAMPLE

でかいテーブルに対して、そのままクエリ投げて遅くて試しづらい時はTABLESAMPLEが使える。

SELECT *
FROM access TABLESAMPLE BERNOULLI(1)

入れる値はパーセンテージらしいので、上記なら1%。

Treasure Data & (一部Presto) の知っておいて便利だった関数