[整形] URL文字列を整形する（★★★）

目的

アクセスログなどに含まれるURLを整形する

使用する関数

1. regexp_replace(string, pattern, replacement) → varchar
2. regexp_extract(string, pattern) → varchar

解説

Java の正規表現の基本

regexp_extract(page_id,'(js$|css$|png$|jpg$|jpeg$|woff$|gif$|eot$|ttf$|ico$|svg$|txt$)',1) は第二引数がパターン文字列で | で区切って拡張子を記述することで，それにマッチするレコードを発見することができる。egexp_extract ではパターン文字列には1つ以上の () が必要で，第三引数で指定した数字はマッチした場合に何番目の () の部分文字列を返すかを決める。 マッチしなければ Hive では '' を，Presto では NULL を返す。拡張子の最後の $ は行の末尾を意味する。つまり js$ は 'hoge.js' にはマッチするが，'hoge/jscript/' にはマッチしない。

Presto の regexp_replace(page_id,'http(.)*://|\?(.)*|/$','') は，| で区切って記述した正規表現にマッチした部分文字列を第3引数の文字列で置換する。パターン文字列内でメタ文字（ここでは ? ）を使いたい場合には Hive では \\?，Presto では /? とする。双方で与えるバックスラッシュの数が異なる事に注意。

サンプルデータ

access_log

クエリ 

Hive

Presto