この記事は、 BigQuery Advent Calendar 2022 の 9 日目です。

はじめに

こんにちは、 Zaim でデータエンジニアをしている、 @naoki85 です。
BigQuery 自体は半年前くらいから本格的に触り始め、やっと慣れてきた感じです。
さて、今回は Javascript UDF を試した話をします。

例えば、テーブル内のカラム文字列を名寄せ、正規化する際に、このようなクエリを書くこともあるかと思います。

SELECT 
REPLACE(normalize(original_name, NFKC),”削除したい文字”, ”削除したい文字”) as name
FROM `my_table`

一つ二つだけならいいのですが、複数あった場合は引数の中に引数を含めないといけません。
通常は、バッチ処理などであらかじめ前処理をしておくのですが、 BigQuery 内でなんとかできないかと思っていました。

そこで、 Remote UDF と Javascript UDF を試しました。
Remote UDF は別日の 記事で紹介があるようなので、今回は Javascript UDF の方を記載したいと思います。

作り方

コンソールから実行する場合は簡単です。
以下のクエリを実行すれば作成できます。

– 一時関数でよければ、 TEMP キーワードをつけます。 CREATE OR REPLACE TEMP FUNCTION

CREATE OR REPLACE FUNCTION `project.dataset.replace_name`(name STRING)
RETURNS STRING LANGUAGE js AS R"""
    var text = '' + name;
    text = text.replace(/削除したい文字/, "");
    text = text.replace(/置き換えたい文字/, "置き換え後の文字");
    // …
    return text;
""";

私はまだ試せていませんが、 GCS にライブラリを配置しておくことで、そのコードを使用することも可能なようです。

作成しておけば、他の関数と同じように呼び出すことができます。

SELECT 
`project.dataset.replace_name`(name) as name
FROM `my_table`

普通にクエリを書いた場合と UDF の場合で速度を比較してみた

Javascript UDF は、以下のように 2 回 replace を挟む関数を作成しました。

CREATE OR REPLACE FUNCTION `project.dataset.replace_name`(name STRING)
RETURNS STRING LANGUAGE js AS R"""
    var text = '' + name;
    text = text.replaceAll(/削除したい文字/, "");
    text = text.replaceAll(/置き換えたい文字/, "おきかえたいもじ");
    // …
    return text;
""";

比較に使用したクエリの方はこちらです。

SELECT
REPLACE(REPLACE(name,"削除したい文字", ""), "置き換えたい文字", "おきかえたいもじ") as name
FROM `my_table`;

こちらでサンプルテーブルを作成して、実行してみました。
実行タイミングによって差異はありますが、基本的に、 Javascript UDF の方が時間がかかる傾向が得られました。
おそらく、レコード数が増える、もしくは関数の処理が複雑になるほど、差が大きくなるのではないかと考えています。

terraform で UDF のコード管理

弊社では、汎用的に使用できる UDF などは terraform を使用してコード管理しております。
この UDF を terraform でインポートしたところ、このようになりました。

resource "google_bigquery_routine" "replace_name" {
   dataset_id         = “dataset_id”
   definition_body    = <<-EOT
       var text = '' + name;                                     
   　　　　　　　　text = text.replaceAll(/削除したい文字/, "")
       text = text.replaceAll(/置き換えたい文字/, "おきかえたいもじ");
    　　　　　　// …
       return text;
   EOT
   imported_libraries = []
   language           = "JAVASCRIPT"
   project            = "project_id"
   return_type        = jsonencode(
       {
           typeKind = "STRING"
       }
   )
   routine_id         = "replace_name"
   routine_type       = "SCALAR_FUNCTION"
 
   arguments {
       data_type = jsonencode(
           {
               typeKind = "STRING"
           }
       )
       name      = "name"
   }
 
   timeouts {}
}

先にレビューを通したい場合などは、これで PR を出してもいいかもしれません。

さいごに

BigQuery には他にもさまざまなサポート機能があり、直近でも機能拡充が頻繁に行われています。
より良いデータ基盤の整備のため、今後も弊社では情報をキャッチアップし、試していきたいと考えています。

BigQuery やその他最新の技術を検証し、 Zaim のデータ基盤を整備してくれる仲間も募集しています！