はじめに

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です。

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をパースしてみる

急にプログラミングの複雑度が上がります。

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"に対応する値を全部足すというややこしいことをやるためです。

入力データの1行目。"foo"の値の合計=1+3.14+10=14.14

{
  "s":{
    "foo":1,
    "bar":2,
    "baz":{
      "x":"foo",
      "foo":3.14
    }
  },
  "foo":10
}

１点目のSumFoo()はその中で、子の型がobjectだったら再度自分自身を呼ぶという再帰処理を行っています。これによって、深いところにあるfooも足し算できてます。これはSQLだけだと結構厳しい。

SumFooの一部

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に格納して、それを呼び出すことができます。

GCSにJavaScriptファイルを置いて呼び出す

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のパースに限らず、選択肢の１つとして持っておくと利用場面があると思います。

参考

標準 SQL ユーザー定義関数 | BigQuery | Google Cloud

BigQueryのユーザー定義関数（UDF）をJavaScriptで定義