dbtを使って、BigQueryにJavaScriptのUDFを作成する方法

Rettyのデータ分析チーム アナリティクスエンジニアの井下田(@Hiroki Igeta)です。
この記事はRetty Advent Calendar 2022 の24日目の記事です。 ※Part1 と Part2 の2つがあります！！

はじめに

本記事ではdbtを用いて、JavaScriptのユーザー定義関数（以下、UDF）を永続的な関数としてBigQueryに作成する方法を記載します。
内容としては、dbt のCommunity Forum に記載のものを参考にしています。 discourse.getdbt.com

dbtを使ってUDFを作成するメリット

BigQueryではdbtを用いずとも、SQL式またはJavaScriptのコードを使用してUDFを作成することができます。（公式ドキュメント）
そこであえてdbtを用いてUDFを作成するメリットとしては、dbtのコード管理下におくことでUDFもまたバージョン管理できる点、UDFの参照関係をdbtを通して確認できる点にあります。
なおBigQuery上でのJavaScript処理はクエリあたりの使用可能メモリが制限されているため、むやみやたらとJavaScriptでUDFを作るのではなく、条件分岐が複雑になる場合や既存のJavaScriptライブラリと同様の出力結果が欲しい場合に限定して用いるようにしています。

1. macrosディレクトリ配下に作成したいUDFを定義する

具体例として、user agentの分類に使えるライブラリwoothee-js をBigQueryに作成します。

まずは、macrosディレクトリ配下にファイルを作成し、BigQuery上に作成したいUDFを定義します。
macroとして他のファイルから呼び出せるように、最初に {% macro xxxx %}、最後に {% endmacro %} を記載しますが、それ以外はBigQueryのGUI上でUDFを作成するコードと同じです。
ここまででmacro（のコード）を実行すれば、定義したUDFがBigQueryに作成される状態になります。

macros/create_udfs/f__parse_user_agent.sql

{% macro f__parse_user_agent(ua) %}

-- 任意のデータセット名・関数名を設定すること（BigQuery上でUDFを設定する方法と同じ)
CREATE OR REPLACE FUNCTION UDFs.gen__struct_from_user_agent(ua STRING)
RETURNS STRUCT<category STRING, name STRING, os STRING, version STRING, vendor STRING, os_version STRING>
LANGUAGE js

-- ※ライブラリを使わない場合はOPTIONS設定は不要
OPTIONS (
  library=["gs://my-bucket/udf/externals/woothee.js"] -- アップロード先のパスを指定
)
AS
r"""
  const out = woothee.parse(ua);
  return {
        category: out.category,
        vendor: out.vendor,
        name:  out.name,
        version: out.version,
        os: out.os,
        os_version: out.os_version,
  };
""";
{% endmacro %}

[補足]
・ライブラリの関数をUDF化したい場合は、事前にライブラリを単一ファイルに変換し、GCSのバケットにアップロードしておく必要があります。 （詳細はクラスメソッドさんの記事が参考になります。）
・f__parse_user_agent：任意で設定可能です。Rettyでは f__ のprefix をつけることにしています。
・下記の名称でBigQueryのプロジェクト上に作成されます。
　 L データセット名：UDFs（任意で設定可能です。）
　 L 関数名：gen__struct_from_user_agent（任意で設定可能です。）
・うまくいかない場合は `プロジェクト名.データセット名.関数名(引数)`としてください。

2. BigQueryにUDFを作成する

次に1で作成したmacro を実行するための設定と、macro の実行をします。
ここまででタイトルにある、dbtを使って、BigQueryにJavaScriptのUDFを作成することは達成できます。

2-1. macro を実行するための設定

dbt run を実行した際にmacro を動かすように設定します。
作成したmacroが動くことで、macro内に定義したコードが走りBigQueryにUDFが作成されます。

dbt_project.yml

on-run-start:
    - '{{f__parse_user_agent(ua)}}' -- 1で定義した macro 名を指定

[補足]
・参考： Hooks and operations | dbt Developer Hub
・dbt run するたびに、macroが動くことになるので一度UDFを作成した以降の運用は改善余地がありそうです。

2-2. macroを実行する

設定完了後にdbt runを実行します。
これによって macroで定義したコードが走って、BigQueryにUDFが作成されます。
↓こんな感じです！

↓ dbt docs serve した結果は下記になります。Refereced Byで on-run-start と依存関係にあることが確認できます。

3. macrosディレクトリ配下に作成後のUDFを呼び出すファイルを作成する

せっかくdbtを使用しているので、作成されたUDFの参照関係を確認できるように工夫を加えます。
ここでは、macrosディレクトリ配下にBigQueryに作成済みの関数を呼び出すためのファイルを新規作成します。

macros/gen__struct_from_user_agent.sql

{% macro gen__struct_from_user_agent(ua) %} -- modelで呼び出すときに用いる名称
`{{target.project}}.UDFs.gen__struct_from_user_agent`({{ ua }})
{% endmacro %}

[補足]
・ファイル名が1で作成したものと重複するとエラーが起きるので注意
・gen__struct_from_user_agent(ua)：任意で設定可能です。Rettyでは gen__ のprefix をつけることにしています。
・{{target.project}}.UDFs.gen__struct_from_user_agent
L プロジェクト名.データセット名.関数名({{ 引数 }}) から構成されています。
L 1のmacro 内で記載した xxx.yyyを指定する必要があります。（CREATE OR REPLACE FUNCTION xxx.yyy の部分）

4. modelディレクトリ配下のファイルで作成したUDFを呼び出す

他のmacrosを使う時と同様の記述で、クエリを記載したファイル内でJavaScriptを利用したUDFを呼び出すことができます。

model/xxxxx.sql

--- 略 ----
with base as(
    select
        user_agent
    from user
)
select
    {{generate_struct_from_user_agent('user_agent')}} as user_agent_struct -- UDFの関数を利用した結果が戻り値となる
from base

[補足]
・ 'user_agent'：引数

↓ dbt docs serve した結果は下記になります。Referenced By で参照関係が確認できるのでいい感じです！

以上です。
もっといい方法ある、情報交換したいなど何かありましたらカジュアルに話しましょう！！

↓meety・Twitterのリンク貼っておきます！
- meety
- Twitter(@hiroki_igeta)

クリスマス感皆無での更新となりましたが、ここまで読んでいただきありがとうございましたmm