Edited at

RedshiftのUDFでwootheeを使ってUserAgentをパースする

More than 1 year has passed since last update.

RedshiftはPythonでUDFを定義できるが、ライブラリを登録してUDFから呼べたりもする。試しにwootheeでUA文字列をパースする関数を作ってみた。

まずpip wheelwootheeのファイルを手元にダウンロードする。

$ pip wheel --no-deps --wheel-dir . woothee

Collecting woothee
Downloading woothee-1.5.0-py2.py3-none-any.whl
Saved ./woothee-1.5.0-py2.py3-none-any.whl
Skipping woothee, due to already being wheel.

これをそのままS3の適当なバゲットに上げる。

$ aws s3 cp woothee-1.5.0-py2.py3-none-any.whl s3://bucketname/redshift/library/woothee-1.5.0-py2.py3-none-any.whl

Redshiftのcreate library文でUDFから呼べるライブラリを登録できるのでこれにさっきあげたS3のパスを指定する。ドキュメントにはzipを指定するってあるけど.whlは中身はzipなので問題ないっぽい。

create library woothee

language plpythonu
from 's3://bucketname/redshift/library/woothee-1.5.0-py2.py3-none-any.whl'
credentials 'aws_access_key_id=***;aws_secret_access_key=***'

これでwootheeがimportできるようになるのでUAをパースするUDFを作る。とりあえず文字列でJSONを返しとく。

create function f_ua_parse (ua varchar(max))

returns varchar(max)
stable
as $$
import woothee
import json

return json.dumps(woothee.parse(ua))
$$ language plpythonu

これでf_ua_parse関数がRedshiftから呼べるようになる。簡単だ。

> select f_ua_parse('Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/46.0.2486.0 Safari/537.36 Edge/13.10586');

f_ua_parse
-------------------------------------------------------------------------------------------------------------------------------
{"category": "pc", "vendor": "Microsoft", "name": "Edge", "os_version": "NT 10.0", "version": "13.10586", "os": "Windows 10"}
(1 row)

Time: 143.379 ms

RedshiftはJSON型には対応してないけどJSONをパースするできる関数があるのでそれを使えばよい。実際に使うときはこんな感じで使える。

select

*
from (
select
distinct
json_extract_path_text(ua, 'category') as category
, json_extract_path_text(ua, 'vendor') as vendor
, json_extract_path_text(ua, 'os') as os
, json_extract_path_text(ua, 'name') as name
, json_extract_path_text(ua, 'version') as version
, json_extract_path_text(ua, 'os_version') as os_version
from (select f_ua_parse(ua) as ua from access_logs)
)
where os != 'UNKNOWN'
limit 20
;

  category  |  vendor   |         os          |       name        |    version    |         os_version

------------+-----------+---------------------+-------------------+---------------+----------------------------
pc | Mozilla | Mac OSX | Firefox | 26.0 | 10.9
pc | Google | Linux | Chrome | 52.0.2743.116 | UNKNOWN
pc | Microsoft | Windows 7 | Internet Explorer | 9.0 | NT 6.1
pc | Mozilla | Mac OSX | Firefox | 24.0 | 10.8
pc | Mozilla | Windows 10 | Firefox | 47.0 | NT 10.0
smartphone | Microsoft | Windows Phone OS | Edge | 14.14393 | 10.0
smartphone | Apple | iPhone | Safari | 9.0 | 9.3
pc | Mozilla | Mac OSX | Firefox | 48.0 | 10.11
pc | Google | Windows 7 | Chrome | 52.0.2743.116 | NT 6.1
pc | Mozilla | Windows UNKNOWN Ver | Firefox | 9.0.1 | UNKNOWN
pc | Google | Linux | Chrome | 47.0.2526.106 | UNKNOWN
pc | Google | Mac OSX | Chrome | 51.0.2704.103 | 10.11.6
smartphone | Apple | iPad | Safari | 9.0 | 9.3.2
pc | Mozilla | Mac OSX | Firefox | 47.0 | 10.9
pc | Apple | Mac OSX | Safari | 5.1 | 10.7.1
pc | Google | Linux | Chrome | 49.0.2623.75 | UNKNOWN
pc | UNKNOWN | Windows UNKNOWN Ver | UNKNOWN | UNKNOWN | NT 6.1.7601 Service Pack 1
pc | Apple | Mac OSX | Safari | 8.0 | 10.10
pc | Google | Windows 10 | Chrome | 51.0.2704.84 | NT 10.0
pc | Google | Windows 7 | Chrome | 41.0.2228.0 | NT 6.1
(20 rows)

最高に便利だ。