クエリの作成が楽になる UDF
[日付を選択] 目次 (1) UDF の概要……… 概要 特長 (2) UDF の紹介……… TIME 関連 UDF ① TD_TIME_FORMAT ② TD_TIME_RANGE ③ TD_SCHEDULED_TIME ④ TD_TIME_ADD ⑤ TD_TIME_PARSE ⑥ TD_DATE_TRUNC その他 UDF ⑦ TD_SESSIONIZE ⑧ TD_PARSE_AGENT 最終更新日：2016/10/1 P1 P2
1 UDF とは
UDF とは、User Defined Functions の略で、ユーザーが自分自身で定義する独自関数 のことです。トレジャーデータサービス by IDCF(以降、TD) では、クエリを作成す るのに便利な UDF を多数用意しております。 これらを使用して、Hive や Presto で使 用できる標準 SQL コマンドでは表現すること が難しいクエリを作成することができ ます。 特長 クエリのパフォーマンスを向上できる ：TD では、1 時間単位で区切られてデータが時系列で格納されています。時間の 範囲を指定するクエリに対しては、必要なデータだけを読み込むことで、クエリの パフォーマンスを向上できます。例えば、時間の範囲を指定する際に、時刻を指定 する際に、Time 関連の UDF が活用されます。 取得することが難しいデータの取得 ：特定の UDF を使うことで、普通の SQL では取得することが難しいクエリの結果が 取得できるようになります。例えば、UDF を使って、IP アドレスを元に、国のコー ドやセッション ID などのデータを取得できます。 目次 UDF とは 特長
2 TIME 関連の UDF ① TD_TIME_FORMAT UNIX タイムスタンプを「string」型の日付フォーマットに変換します。 Hive と Presto での使い方 TD_TIME_FORMAT(Unix タイムスタンプ, '日付フォーマット', 'タイムゾーン') • 第１引数には「int」と「long」型の「Unix タイムスタンプ」を入力します。 • 第２引数には「string」型の「日付フォーマット」を入力します。 • 第３引数には「タイムゾーン」を入力します。未記入の場合、「UTC」になります。 ＃利用可能な日付フォーマットに関してはこちらでご確認ください。 #利用可能なタイムゾーンに関してはこちらでご確認ください。 Hive と Presto での例文
SELECT TD_TIME_FORMAT(time, 'yyyy-MM-dd HH:mm:ss', 'JST') …FROM… Unix タイムスタンプを持っている「time」というカラムを指定して、日本時間の 「yyyy-MM-dd HH:mm:ss」形式へ変換します。 目次 TIME 関連 UDF ① TD_TIME_FORMAT ② TD_TIME_RANGE ③ TD_SCHEDULED_TIME ④ TD_TIME_ADD ⑤ TD_TIME_PARSE ⑥ TD_DATE_TRUNC その他 UDF ⑦ TD_SESSIONIZE ⑧ TD_PARSE_AGENT
3 ② TD_TIME_RANGE 一定期間に該当するデータのみを抽出する際に使用します。 Hive での使い方 Presto での使い方 • 第 1 引数には「int」「long」型の「Unix タイムスタンプ」を入力します。 • 第 2 引数と第 3 引数の「開始時刻」と「終了時刻」は「int」「long」「string」 型に対応します。 • 「開始時刻」が省略あるいは「Null」の場合、UDF は「開始時刻」を「0」と解釈 します。 • 「終了時刻」が省略あるいは「Null」の場合、終了時刻を設定せず、開始時刻以降 を全ての期間として指定します。 • 「開始時刻」と「終了時刻」の形式が間違っている場合、UDF は「Null」として解 釈します。 • 第 4 引数に、「タイムゾーン」を記入します。未記入の場合、「UTC」になります。 • 「開始時刻」と「終了時刻」内にタイムゾーンを表記する際には(例)‘2016-03-18 +0700’)、第 4 引数「タイムゾーン」を記入する必要はありません。 Hive の例文 Presto の例文 Unix タイムスタンプを持っている「time」というカラムを指定して、日本時間で 2016 年 3 月 18 日 0 時 0 分 0 秒から 2016 年 3 月 19 日 0 時 0 分 0 秒に該当するデータ を抽出します。 #HH:mm:ss の形式が省略された場合、0 時 0 分 0 秒として認識されます。 TD_TIME_RANGE(Unix タイムスタンプ, "開始時刻", "終了時刻", "タイムゾーン") TD_TIME_RANGE(Unix タイムスタンプ, '開始時刻', '終了時刻', 'タイムゾーン') SELECT…WHERE TD_TIME_RANGE(time, "2016-03-18", "2016-03-19", "JST") SELECT…WHERE TD_TIME_RANGE(time, '2016-03-18', '2016-03-19', 'JST')
4 ③ TD_SCHEDULED_TIME スケジュールクエリによって、ジョブが実行されたときの正確な時刻を返します。 Hive と Presto での使い方 • クエリがスケジュール設定されて、ジョブがスケジュールより遅延された際の返す 値は、ジョブが実際走った時間とは異なる可能性があります。 • クエリがスケジュール設定されていないときには、ジョブが実行されたときの時刻 を返します。 ④ TD_TIME_ADD 指定したい時間から「分」「時間」「日」単位で差分を追加します。 Hive での使い方 Presto での使い方 • 第 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' • 「年」と「月」単位の差分はサポートされておりません。 TD_SCHEDULED_TIME() TD_TIME_ADD(“指定したい時刻”, “時間差分”, “タイムゾーン”) TD_TIME_ADD('指定したい時刻', '時間差分', 'タイムゾーン')
5 • 第 3 引数には「タイムゾーン」を記入します。未記入の場合、「UTC」になりま す。 • 第 1 引数にタイムゾーンを表記する際には(例)'2016-03-18 +0700')、第 3 引数 「タイムゾーン」を記入する必要はありません。 Hive での例文 Presto での例文 UTC 時間で、2016 年 03 月 18 日 0 時 0 分 0 秒から 2016 年 3 月 19 日 0 時 0 分 0 秒(1 日後)までのデータを抽出します。 ⑤ TD_TIME_PARSE 「string」型の時間を UNIX タイムスタンプに変換します。 Hive での使い方 Presto での使い方 • 第 1 引数には「string」型の「日付フォーマット」を記入します。 • 「日付フォーマット」の形式が間違っている場合、「Null」を返します。 • 第 2 引数には「タイムゾーン」を記入します。未記入の場合、「UTC」になりま す。 • 第 1 引数にタイムゾーンを表記する際には(例)‘2016-03-18+0700’)、第 2 引数 「タイムゾーン」を記入する必要はありません。 SELECT…WHERE TD_TIME_RANGE(time, "2016-03-18", TD_TIME_ADD("2016-03-18", "1d")) SELECT…WHERE TD_TIME_RANGE(time, '2016-03-18', TD_TIME_ADD('2016-03-18', '1d')) TD_TIME_PARSE("日付フォーマット", "タイムゾーン") TD_TIME_PARSE('日付フォーマット', 'タイムゾーン')
6 ＃利用可能な日付フォーマットに関してはこちらでご確認ください。 #利用可能なタイムゾーンに関してはこちらでご確認ください。 Hive での例文 Presto での例文 日本時間の 2016 年 3 月 18 日 0 時 0 分 0 秒の時刻を、Unix タイムスタンプの 「1426604400」に変換します。 ⑥ TD_DATE_TRUNC 「Hour」「minute」などのユニット単位で区切って UNIX タイムスタンプ値を返し ます。 Hive と Presto での使い方 • 第 1 引数には、「string」型のユニットである「minute」、「hour」、「day」、 「week」、「month」、「quarter」、「year」のいずれかを記入します。 • 第 2 引数には、「long」型の「時刻」を記入します。 • 第 3 引数には「タイムゾーン」を記入します。未記入の場合、「UTC」になりま す。 Hive と Presto での例文 1416787667(='2014-11-24 00:07:47 UTC')の場合、 1416787200(='2014-11-24 00:00:00 UTC')の値を返します。 TD_TIME_PARSE("2016-03-18", "JST") TD_TIME_PARSE('2016-03-18', 'JST') TD_DATE_TRUNC('ユニット', time, 'タイムゾーン') TD_DATE_TRUNC('ユニット', time, 'タイムゾーン')
7 その他の UDF ⑦ TD_SESSIONIZE 同一アクセスと解釈したいレコードに同一 UUID を付与します。 Hive と Presto での使い方 • 第 1 引数には、「int」「long」型の UNIX タイムスタンプを入力します。 • 第 2 引数には、「int」型のタイムアウト時間を入力します。 • 第 3 引数には、「string」型の「セッション見分け値」を入力します。同一な「セ ッション見分け値」に対して、同一 UUID を付与します。 Hive と Presto での例文 1 時間の間(3600 秒)でアクセスした同一「ip_address」に対して同一の UUID を振 っています。注意する点は、sub クエリの中に、第 3 引数である「セッション見分け 値」を「ORDER BY」する必要があります。 ⑧ TD_PARSE_AGENT 「string」型の「user_agent」のデータを分解して、「Map」型でキーと値を取得し ます。 Hive と Presto での使い方 TD_SESSIONIZE(Unix タイムスタンプ, タイムアウト時間, セッション見分け値)
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 )
8 • 第 1 引数には、「varchar」型の「user_agent」の情報を持っているカラムを指 定します。 • 「'キー'」という箇所には、分解される「Map」型のキーを入力することで、特定 のキーに該当する値を取得できます。 • 特定の「'キー'」を未記入の場合は、「user_agent」の全ての情報「Map」型の キーと値を取得します。 Hive と Presto での例文 「user_agent」情報内での「OS」という「キー」を特定して、「windows 7」「Mac OS」といった情報を取得します。 TD_PARSE_AGENT(user_agent)['OS']
