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

Size: px

Start display at page:

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

まなうるしはた
5 years ago
Views:

1 トレジャーデータサービス 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

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

3 目次 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

4 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 になります開始時刻と終了時刻内にタイムゾーンを表記する際には( 例 ) ) 第 4 引数タイムゾーンを記入する必要はありません Hive の例文 SELECT WHERE TD_TIME_RANGE(time, " ", " ", "JST") Presto の例文 SELECT WHERE TD_TIME_RANGE(time, ' ', ' ', 'JST') Unix タイムスタンプを持っている time というカラムを指定して日本時間で 2016 年 3 月 18 日 0 時 0 分 0 秒から 2016 年 3 月 19 日 0 時 0 分 0 秒に該当するデータを抽出します #HH:mm:ss の形式が省略された場合 0 時 0 分 0 秒として認識されます 3

5 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

6 第 3 引数にはタイムゾーンを記入します未記入の場合 UTC になります第 1 引数にタイムゾーンを表記する際には ( 例 )' ') 第 3 引数タイムゾーンを記入する必要はありません Hive での例文 SELECT WHERE TD_TIME_RANGE(time, " ", TD_TIME_ADD(" ", "1d")) Presto での例文 SELECT WHERE TD_TIME_RANGE(time, ' ', TD_TIME_ADD(' ', '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 引数にタイムゾーンを表記する際には ( 例 ) ) 第 2 引数タイムゾーンを記入する必要はありません 5

7 # 利用可能な日付フォーマットに関してはこちらでご確認ください # 利用可能なタイムゾーンに関してはこちらでご確認ください Hive での例文 TD_TIME_PARSE(" ", "JST") Presto での例文 TD_TIME_PARSE(' ', 'JST') 日本時間の 2016 年 3 月 18 日 0 時 0 分 0 秒の時刻を Unix タイムスタンプのに変換します 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, ' タイムゾーン ') (=' :07:47 UTC') の場合 (=' :00:00 UTC') の値を返します 6

8 その他の 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

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

関数サンプル1

関数サンプル1 SQLCompiler for LINQ(C#) のサンプル (LINQPad 用 LINQ to Entities 関数サンプル 1) 本サンプルで使用した Visual Studio プロジェクトの pubs データベースの概念モデルは以下のテーブル名とカラム名が直接 SQL Sever へクエリする場合と異なるのでが補正されていますテーブル名が異なるもの employee employees