クエリの作成が楽になるUDF（PDF：171KB）

トレジャーデータサービス by IDCF 活用マニュアル
クエリの作成が楽になる UDF
目次
(1) UDF の概要…………………………………………………………………………
 概要
P1
 特長
[日付を選択]
(2) UDF の紹介………………………………………………………………………
 TIME 関連 UDF
P2
① 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
[トレジャーデータサービス by IDCF 活用マニュアル]
クエリの作成が楽になる UDF
目次



UDF とは
特長
UDF とは
UDF とは、User Defined Functions の略で、ユーザーが自分自身で定義する独自関数
のことです。トレジャーデータサービス by IDCF(以降、TD) では、クエリを作成す
るのに便利な UDF を多数用意しております。これらを使用して、Hive や Presto で使
用できる標準 SQL コマンドでは表現することが難しいクエリを作成することができ
ます。

特長

クエリのパフォーマンスを向上できる
：TD では、1 時間単位で区切られてデータが時系列で格納されています。時間の
範囲を指定するクエリに対しては、必要なデータだけを読み込むことで、クエリの
パフォーマンスを向上できます。例えば、時間の範囲を指定する際に、時刻を指定
する際に、Time 関連の UDF が活用されます。

取得することが難しいデータの取得
：特定の UDF を使うことで、普通の SQL では取得することが難しいクエリの結果が
取得できるようになります。例えば、UDF を使って、IP アドレスを元に、国のコー
ドやセッション ID などのデータを取得できます。
1
[トレジャーデータサービス by IDCF 活用マニュアル]
クエリの作成が楽になる 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


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」形式へ変換します。
2
[トレジャーデータサービス by IDCF 活用マニュアル]
クエリの作成が楽になる UDF
② 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
[トレジャーデータサービス by IDCF 活用マニュアル]
クエリの作成が楽になる UDF
③ TD_SCHEDULED_TIME
スケジュールクエリによって、ジョブが実行されたときの正確な時刻を返します。
 Hive と Presto での使い方
TD_SCHEDULED_TIME()
• クエリがスケジュール設定されて、ジョブがスケジュールより遅延された際の返す
値は、ジョブが実際走った時間とは異なる可能性があります。
• クエリがスケジュール設定されていないときには、ジョブが実行されたときの時刻
を返します。
④ 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'
'-Nh'
N 時間前
例: '-1h' '-2h' '-30h'
'Nm'
N 分後
例: '1m' '2m' '30m'
'-Nm'
N 分前
例: '-1m' '-2m'
'Ns'
N 秒後
例: '1s'
'-Ns'
N 秒前
例: '-1s' '-2s' '-30s'
'2h' '30h'
'-30m'
'2s' '30s'
「年」と「月」単位の差分はサポートされておりません。
4
[トレジャーデータサービス by IDCF 活用マニュアル]
クエリの作成が楽になる UDF
•
第 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 日後)までのデータを抽出します。
⑤ 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
[トレジャーデータサービス by IDCF 活用マニュアル]
クエリの作成が楽になる UDF
＃利用可能な日付フォーマットに関してはこちらでご確認ください。
#利用可能なタイムゾーンに関してはこちらでご確認ください。
 Hive での例文
TD_TIME_PARSE("2016-03-18", "JST")
 Presto での例文
TD_TIME_PARSE('2016-03-18', 'JST')
日本時間の 2016 年 3 月 18 日 0 時 0 分 0 秒の時刻を、Unix タイムスタンプの
「1426604400」に変換します。
⑥ TD_DATE_TRUNC
「Hour」「minute」などのユニット単位で区切って UNIX タイムスタンプ値を返し
ます。
 Hive と Presto での使い方
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
[トレジャーデータサービス by IDCF 活用マニュアル]
クエリの作成が楽になる UDF
その他の UDF

⑦ TD_SESSIONIZE
同一アクセスと解釈したいレコードに同一 UUID を付与します。
 Hive と Presto での使い方
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」する必要があります。
⑧ TD_PARSE_AGENT
「string」型の「user_agent」のデータを分解して、「Map」型でキーと値を取得し
ます。
 Hive と Presto での使い方
TD_PARSE_AGENT(user_agent)['キー']
7
[トレジャーデータサービス by IDCF 活用マニュアル]
クエリの作成が楽になる UDF
•
第 1 引数には、「varchar」型の「user_agent」の情報を持っているカラムを指
定します。
•
「'キー'」という箇所には、分解される「Map」型のキーを入力することで、特定
のキーに該当する値を取得できます。
•
特定の「'キー'」を未記入の場合は、「user_agent」の全ての情報「Map」型の
キーと値を取得します。
 Hive と Presto での例文
TD_PARSE_AGENT(user_agent)['OS']
「user_agent」情報内での「OS」という「キー」を特定して、「windows 7」「Mac
OS」といった情報を取得します。
8

Download Report