こちらの記事が Legacy SQL での方法を解説していました。諸事情で Standard SQL でやったのでその時の方法を書いておきます。
（準備）Google Cloud Storage に woothee.js をアップロードする
woothee.js は大きい & ある程度繰り返し使えるように Google Cloud Storage にアップロードします。ここでは仮にこのファイルを gs://example/woothee.js にアップロードしたとします。
UDF を定義する
CREATE TEMPORARY FUNCTION uaCategory(ua STRING)
RETURNS STRING
LANGUAGE js AS """
return woothee.parse(ua).category;
"""
OPTIONS (
library="gs://example/woothee.js"
);
これが UDF の本体です。 uaCategory が関数の名前で """ の中身が関数の本体になります。ここで woothee というオブジェクトにアクセスしていますが、これは gs://example/woothee.js で定義されているもので、ようするに woothee.js の実体です。
なんとなくわかると思いますがここではパース結果のうち category を STRING として return しています。 Legacy SQL では一気にテーブルを出力（？）できたみたいですが、 Standard SQL では一つの値を返すことしかできません。なのでもし os も必要ならその分 UDF を増やす必要があります。
UDF を使う
SELECT uaCategory(user_agent) AS category, count(*)
FROM `example.access_log_20170927`
GROUP BY uaCategory(user_agent);
このように SELECT 文で先程定義した uaCategory 関数を使うことができます。この結果は例えば
| Row | category | f0_ |
|---|---|---|
| 1 | crawler | 12345 |
| 2 | smartphone | 234 |
| 3 | pc | 678 |
とかそんな感じになります。
最終形態
#standardSQL
CREATE TEMPORARY FUNCTION uaCategory(ua STRING)
RETURNS STRING
LANGUAGE js AS """
return woothee.parse(ua).category;
"""
OPTIONS (
library="gs://example/woothee.js"
);
SELECT uaCategory(user_agent) AS category, count(*)
FROM `example.access_log_20170927`
GROUP BY uaCategory(user_agent);
全体をひとまとめにするとこんな感じになります。