クエリの作成が楽になるUDF

トレジャーデータサービス by IDCF 活用マニュアル 目次 (1) UDF の概要 概要 特長 P1 [ 日付を選択 ] (2) UDF の紹介 TIME 関連 UDF 1 TD_TIME_FORMAT P2 2 TD_TIME_RANGE 3 TD_SCHEDULED_TIME 4 TD_TIME_ADD 5 TD_TIME_PARSE 6 TD_DATE_TRUNC その他 UDF 7 TD_SESSIONIZE 8 TD_PARSE_AGENT 最終更新日 :2016/10/1

目次 UDF とは 特長 UDF とは UDF とは User Defined Functions の略で ユーザーが自分自身で定義する独自関数のことです トレジャーデータサービス by IDCF( 以降 TD) では クエリを作成するのに便利な UDF を多数用意しております これらを使用して Hive や Presto で使用できる標準 SQL コマンドでは表現することが難しいクエリを作成することができます 特長 クエリのパフォーマンスを向上できる :TD では 1 時間単位で区切られてデータが時系列で格納されています 時間の範囲を指定するクエリに対しては 必要なデータだけを読み込むことで クエリのパフォーマンスを向上できます 例えば 時間の範囲を指定する際に 時刻を指定する際に Time 関連の UDF が活用されます 取得することが難しいデータの取得 : 特定の UDF を使うことで 普通の SQL では取得することが難しいクエリの結果が取得できるようになります 例えば UDF を使って IP アドレスを元に 国のコードやセッション ID などのデータを取得できます 1

目次 TIME 関連 UDF 1 TD_TIME_FORMAT 2 TD_TIME_RANGE 3 TD_SCHEDULED_TIME 4 TD_TIME_ADD 5 TD_TIME_PARSE 6 TD_DATE_TRUNC その他 UDF 7 TD_SESSIONIZE 8 TD_PARSE_AGENT TIME 関連の UDF 1 TD_TIME_FORMAT UNIX タイムスタンプを string 型の日付フォーマットに変換します TD_TIME_FORMAT(Unix タイムスタンプ, ' 日付フォーマット ', ' タイムゾーン ') 第 1 引数には int と long 型の Unix タイムスタンプ を入力します 第 2 引数には string 型の 日付フォーマット を入力します 第 3 引数には タイムゾーン を入力します 未記入の場合 UTC になります # 利用可能な日付フォーマットに関してはこちらでご確認ください # 利用可能なタイムゾーンに関してはこちらでご確認ください Hive と Presto での例文 SELECT TD_TIME_FORMAT(time, 'yyyy-mm-dd HH:mm:ss', 'JST') FROM Unix タイムスタンプを持っている time というカラムを指定して 日本時間の yyyy-mm-dd HH:mm:ss 形式へ変換します 2

2 TD_TIME_RANGE 一定期間に該当するデータのみを抽出する際に使用します Hive での使い方 TD_TIME_RANGE(Unix タイムスタンプ, " 開始時刻 ", " 終了時刻 ", " タイムゾーン ") Presto での使い方 TD_TIME_RANGE(Unix タイムスタンプ, ' 開始時刻 ', ' 終了時刻 ', ' タイムゾーン ') 第 1 引数には int long 型の Unix タイムスタンプ を入力します 第 2 引数と第 3 引数の 開始時刻 と 終了時刻 は int long string 型に対応します 開始時刻 が省略あるいは Null の場合 UDF は 開始時刻 を 0 と解釈します 終了時刻 が省略あるいは Null の場合 終了時刻を設定せず 開始時刻以降を全ての期間として指定します 開始時刻 と 終了時刻 の形式が間違っている場合 UDF は Null として解釈します 第 4 引数に タイムゾーン を記入します 未記入の場合 UTC になります 開始時刻 と 終了時刻 内にタイムゾーンを表記する際には( 例 ) 2016-03-18 +0700 ) 第 4 引数 タイムゾーン を記入する必要はありません Hive の例文 SELECT WHERE TD_TIME_RANGE(time, "2016-03-18", "2016-03-19", "JST") Presto の例文 SELECT WHERE TD_TIME_RANGE(time, '2016-03-18', '2016-03-19', 'JST') Unix タイムスタンプを持っている time というカラムを指定して 日本時間で 2016 年 3 月 18 日 0 時 0 分 0 秒から 2016 年 3 月 19 日 0 時 0 分 0 秒に該当するデータを抽出します #HH:mm:ss の形式が省略された場合 0 時 0 分 0 秒として認識されます 3

3 TD_SCHEDULED_TIME スケジュールクエリによって ジョブが実行されたときの正確な時刻を返します TD_SCHEDULED_TIME() クエリがスケジュール設定されて ジョブがスケジュールより遅延された際の返す値は ジョブが実際走った時間とは異なる可能性があります クエリがスケジュール設定されていないときには ジョブが実行されたときの時刻を返します 4 TD_TIME_ADD 指定したい時間から 分 時間 日 単位で差分を追加します Hive での使い方 TD_TIME_ADD( 指定したい時刻, 時間差分, タイムゾーン ) Presto での使い方 TD_TIME_ADD(' 指定したい時刻 ', ' 時間差分 ', ' タイムゾーン ') 第 1 引数の 指定したい時間 は int long string 型に対応します 指定したい時刻の形式 が間違っている場合 Null を返します 第 2 引数の 時間差分 は下記のような形式に対応します 'Nd' N 日後 例 : '1d' '2d' '30d' '-Nd' N 日前 例 : '-1d' '-2d' '-30d' 'Nh' N 時間後 例 : '1h' '2h' '30h' '-Nh' N 時間前 例 : '-1h' '-2h' '-30h' 'Nm' N 分後 例 : '1m' '2m' '30m' '-Nm' N 分前 例 : '-1m' '-2m' '-30m' 'Ns' N 秒後 例 : '1s' '2s' '30s' '-Ns' N 秒前 例 : '-1s' '-2s' '-30s' 年 と 月 単位の差分はサポートされておりません 4

第 3 引数には タイムゾーン を記入します 未記入の場合 UTC になります 第 1 引数にタイムゾーンを表記する際には ( 例 )'2016-03-18 +0700') 第 3 引数 タイムゾーン を記入する必要はありません Hive での例文 SELECT WHERE TD_TIME_RANGE(time, "2016-03-18", TD_TIME_ADD("2016-03-18", "1d")) Presto での例文 SELECT WHERE TD_TIME_RANGE(time, '2016-03-18', TD_TIME_ADD('2016-03-18', '1d')) UTC 時間で 2016 年 03 月 18 日 0 時 0 分 0 秒から 2016 年 3 月 19 日 0 時 0 分 0 秒 (1 日後 ) までのデータを抽出します 5 TD_TIME_PARSE string 型の時間を UNIX タイムスタンプに変換します Hive での使い方 TD_TIME_PARSE(" 日付フォーマット ", " タイムゾーン ") Presto での使い方 TD_TIME_PARSE(' 日付フォーマット ', ' タイムゾーン ') 第 1 引数には string 型の 日付フォーマット を記入します 日付フォーマット の形式が間違っている場合 Null を返します 第 2 引数には タイムゾーン を記入します 未記入の場合 UTC になります 第 1 引数にタイムゾーンを表記する際には ( 例 ) 2016-03-18+0700 ) 第 2 引数 タイムゾーン を記入する必要はありません 5

# 利用可能な日付フォーマットに関してはこちらでご確認ください # 利用可能なタイムゾーンに関してはこちらでご確認ください Hive での例文 TD_TIME_PARSE("2016-03-18", "JST") Presto での例文 TD_TIME_PARSE('2016-03-18', 'JST') 日本時間の 2016 年 3 月 18 日 0 時 0 分 0 秒の時刻を Unix タイムスタンプの 1426604400 に変換します 6 TD_DATE_TRUNC Hour minute などのユニット単位で区切って UNIX タイムスタンプ値を返し ます TD_DATE_TRUNC(' ユニット ', time, ' タイムゾーン ') 第 1 引数には string 型のユニットである minute hour day week month quarter year のいずれかを記入します 第 2 引数には long 型の 時刻 を記入します 第 3 引数には タイムゾーン を記入します 未記入の場合 UTC になります Hive と Presto での例文 TD_DATE_TRUNC(' ユニット ', time, ' タイムゾーン ') 1416787667(='2014-11-24 00:07:47 UTC') の場合 1416787200(='2014-11-24 00:00:00 UTC') の値を返します 6

その他の UDF 7 TD_SESSIONIZE 同一アクセスと解釈したいレコードに同一 UUID を付与します TD_SESSIONIZE(Unix タイムスタンプ, タイムアウト時間, セッション見分け値 ) 第 1 引数には int long 型の UNIX タイムスタンプを入力します 第 2 引数には int 型のタイムアウト時間を入力します 第 3 引数には string 型の セッション見分け値 を入力します 同一な セッション見分け値 に対して 同一 UUID を付与します Hive と Presto での例文 SELECT TD_SESSIONIZE(time, 3600, ip_address) as session_id, time, ip_address, path FROM ( SELECT time, ip_address, path FROM web_logs ORDER BY ip_address, time ) 1 時間の間 (3600 秒 ) でアクセスした同一 ip_address に対して同一の UUID を振っています 注意する点は sub クエリの中に 第 3 引数である セッション見分け値 を ORDER BY する必要があります 8 TD_PARSE_AGENT string 型の user_agent のデータを分解して Map 型でキーと値を取得し ます TD_PARSE_AGENT(user_agent)[' キー '] 7

第 1 引数には varchar 型の user_agent の情報を持っているカラムを指定します ' キー ' という箇所には 分解される Map 型のキーを入力することで 特定のキーに該当する値を取得できます 特定の ' キー ' を未記入の場合は user_agent の全ての情報 Map 型のキーと値を取得します Hive と Presto での例文 TD_PARSE_AGENT(user_agent)['OS'] user_agent 情報内での OS という キー を特定して windows 7 Mac OS といった情報を取得します 8