SQLを使ってURLをスキーム、ホスト名、パスに分ける

アクセスログなどのデータにおいてURLを使う際、最近はhttpとhttpsの両方のアクセスが存在する場合などがあり、URLスキーム部分は分離して集計したい場合がある。
例えば、以下のような場合である。(URLは一例)

• https://qiita.com/shiumachi
• http://qiita.com/shiumachi

そこで、ImpalaのSQLを使って、URLをスキーム、ホスト名、パスに分離する。

REGEXP_EXTRACT関数

REGEXP_EXTRACT(str, pattern, index)

文字列strを正規表現patternにかけ、マッチしたグループのうちindex番目の文字列を返す。
正規表現はGoogleのRE2ライブラリを使っている。
文法はこちら。

クエリ例

SELECT
    REGEXP_EXTRACT(url, '([[:alpha:]]*)://.*',1) AS scheme, 
    REGEXP_EXTRACT(url, '[[:alpha:]]*://(.*?)/.*',1) AS hostname,
    REGEXP_EXTRACT(url, '[[:alpha:]]*://.*?(/.*)',1) AS path
FROM
    access_log
LIMIT 100;

参考

• Clouderaドキュメント: Impala String Functions