はじめに
BigQueryでのユーザー定義関数の書き方と、利用場面を整理します。特にJavaScriptでできることについて。
Googleのこちらの記事からの抜粋と説明です。
1.基本
CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS ((x + 4) / y);
SELECT val, AddFourAndDivide(val, 2)
FROM UNNEST([2,3,5,8]) AS val;
結果
| val | f0_ |
|---|---|
| 2 | 3.0 |
| 3 | 3.5 |
| 5 | 4.5 |
| 8 | 6.0 |
valの値に4を足して2で割った値が返ってきます。どういう要件なのかわかりませんが、こういう特殊な処理を関数にしておけます。こんなの、SELECT (val+4)/2 FROM ～ とか書けばいいじゃないという話なんですが、この独特な計算が２か所以上にあっても、定義を１つにできるメリットがあります。
あと、処理の説明を関数名やコメントで残しやすく、後で他人が見たときに対応しやすくなります。
なので、この時点でもうある程度役に立ちます。
2. JavaScriptを使ったUDF
ここからJavaScriptです。
CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS r"""
return x*y;
""";
WITH numbers AS
(SELECT 1 AS x, 5 as y
UNION ALL
SELECT 2 AS x, 10 as y
UNION ALL
SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;
結果
| x | y | product |
|---|---|---|
| 1 | 5 | 5 |
| 2 | 10 | 20 |
| 3 | 15 | 45 |
LANGUAGE js AS ～ の後にJavaScriptのコードを書けます。最後にreturnした数値になりますね。これだけだと、全然ありがたみはないです。基本で書いたのと同じ状態。とりあえずJavaScriptで書く時のお作法のご紹介でした。次から本気出す。
3. JavaScriptでJSONをパースしてみる
急にプログラミングの複雑度が上がります。
CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
RETURNS FLOAT64
LANGUAGE js AS r"""
function SumFoo(obj) {
var sum = 0;
for (var field in obj) {
if (obj.hasOwnProperty(field) && obj[field] != null) {
if (typeof obj[field] == "object") {
sum += SumFoo(obj[field]);
} else if (field == "foo") {
sum += obj[field];
}
}
}
return sum;
}
var row = JSON.parse(json_row);
return SumFoo(row);
""";
WITH Input AS (
SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL
SELECT NULL, 4 AS foo UNION ALL
SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo
)
SELECT
TO_JSON_STRING(t) AS json_row,
SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;
結果
| json_raw | foo_sum |
|---|---|
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10} | 14.14 |
| {"s":null,"foo":4} | 4.0 |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59 |
まず入力のテストデータを作るとこですが、STRUCTでJSONの構造体を作ってます。ASで辞書型のキーを指定してます。結果のjson_rawの列の値を作り出してると思って見比べたら大体わかると思います。
今回の関数SumFoo()へは、そのjson情報をTO_JSON_STRING()して文字列として渡してます。
JavaScriptのところでは、主に３つのことをやってます。
- SQLから呼び出された
SumFieldsNamedFoo()の関数内で、内部関数としてSumFoo()を定義 - SQLから文字列として渡ってきた情報をJavaScript的に読みやすいJSONデータに変換
- SumFooにJSONデータを渡して計算した結果を返す
これは、入力データとして、↓こんな複雑なJSONが渡ってきても、"foo"に対応する値を全部足すというややこしいことをやるためです。
{
"s":{
"foo":1,
"bar":2,
"baz":{
"x":"foo",
"foo":3.14
}
},
"foo":10
}
１点目のSumFoo()はその中で、子の型がobjectだったら再度自分自身を呼ぶという再帰処理を行っています。これによって、深いところにあるfooも足し算できてます。これはSQLだけだと結構厳しい。
if (typeof obj[field] == "object") {
sum += SumFoo(obj[field]);
}
２点目のJSON.parse()も、JavaScriptらしい処理です。文字列のJSONをパースしてます。これは、BigQueryのFUNCTIONの引数として、JSONを任意の型のSTRUCTとして渡せないから、SQLでTO_JSON_STRING()して、JavaScriptでJSON.parse()してるんですね。ちょっとまどろっこしいですが、JavaScriptであればそういう回避策もやりやすい。
4. JavaScriptを外で定義する
これまで作ったJavaScriptをGCSに格納して、それを呼び出すことができます。
CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING)
RETURNS STRING
LANGUAGE js
OPTIONS (
library=["gs://my-bucket/path/to/lib1.js", "gs://my-bucket/path/to/lib2.js"]
)
AS
r"""
// 'doInterestingStuff'という関数が、
// 読み込んでいるlibraryのどれかのファイルに定義されている必要がある
return doInterestingStuff(a, b);
""";
SELECT myFunc(3.14, 'foo');
もう詳細はありませんが、JavaScriptを外に置くことができます。これによって、ほかの人との共有も簡単。
これを使いたい主なケースは、自分でない誰かが作ったJavaScriptのライブラリを使うことではないでしょうか。例えば私は、自然言語処理の分かち書きをしたいと思っていて、そのライブラリがあるので、library= の１つ目で分かち書きライブラリを読み込み、２つ目で自分が利用する処理を書く想定です。（別の記事にします）
おわりに
BigQueryのユーザー定義関数は、普通の関数定義はできることは当たり前で、それはそれで便利なんですが、外部のJavaScriptを読み込んで使用できるというところに大きな使用のメリットがあるように思いました。
今回のJSONのパースに限らず、選択肢の１つとして持っておくと利用場面があると思います。