RedshiftはPythonでUDFを定義できるが、ライブラリを登録してUDFから呼べたりもする。試しにwootheeでUA文字列をパースする関数を作ってみた。
まずpip wheelでwootheeのファイルを手元にダウンロードする。
$ 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)
最高に便利だ。