redshift

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)

最高に便利だ。