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


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のパスを指定する。[ドキュメント](http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_LIBRARY.html)にはzipを指定するってあるけど`.whl`は中身はzipなので問題ないっぽい。

```sql
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を返しとく。

```sql
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をパースするできる関数があるのでそれを使えばよい。実際に使うときはこんな感じで使える。

```sql
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)
```

最高に便利だ。