DBTでUDF作成からテーブル出力までやってみた

# はじめに
データ関連の業務に携わって2年目のエンジニアです。 ジャックダニエルのテネシーハニーって美味しいですよね。最近の一推しです。
今回は、DBTでBigQueryにUDFを作成し、そのUDFを参照してテーブルを出力する流れをやっていきたいと思います。

- 環境
  - MacOS(Intel)

DBTが初めての方は、こちらを参照ください。
- [dbt install](https://docs.getdbt.com/docs/core/installation)
- [BigQuery adapterのインストール](https://docs.getdbt.com/docs/core/connect-data-platform/bigquery-setup)
- `dbt init (project name)`でプロジェクトを作成
- $HOME/.dbt/profiles.ymlを設定
- `dbt debug`で接続を確認

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492719/86211bfb-4560-d4af-92a3-491454bc6090.png)
`brew install`では、dbt-coreまでしかサポートされていないようなのでadapterのインストールは`pip install`でやってください。

# 0. テーブルを作成する
UDFを使用するためにテーブルを作成します。
```sql:models/fruits.sql
SELECT
  1 AS id,
  'apple' AS fruit,
  100 AS price
UNION ALL
SELECT
  2 AS id,
  'banana' AS fruit,
  200 AS price
UNION ALL
SELECT
  3 AS id,
  'orange' AS fruit,
  150 AS price
UNION ALL
SELECT
  3 AS id,
  'pineapple' AS fruit,
  400 AS price
UNION ALL
SELECT
  4 AS id,
  'banana' AS fruit,
  120 AS price
UNION ALL
SELECT
  5 AS id,
  'apple' AS fruit,
  80 AS price
UNION ALL
SELECT
  6 AS id,
  'orange' AS fruit,
  130 AS price
UNION ALL
SELECT
  7 AS id,
  'pineapple' AS fruit,
  250 AS price
```
`dbt run --select fruits`を実行し以下のテーブルが作成されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492719/d5c0632b-6fe1-9981-7c2e-0db4fe94559d.png)
ちなみにこの時の`dbt_project.yml`はこのようにしています。
(以下の`P****`はproject ID, `A****`はテーブルを格納するdatasetを指しています。)
```yaml
models:
  dbt_hoge:
    +database: P****
    +schema: A****
    +materialzed: table
```



# 1. UDFを作成する
次にmacros配下に作成したいUDFを記述します。
今回は果物の合計金額を算出するクエリを作成しました。
(以下の`B****`はUDFを格納するdatasetを指しています。)
```sql:macros/create_udfs/summary_price.sql
{% macro summray_price(target_fruit) %}
  CREATE OR REPLACE FUNCTION P****(project ID).B****(dataset).summary_price(target_fruit STRING) AS (
    (
      SELECT
        SUM(price) AS summary
      FROM *****(project ID).A****(dataset).fruits
      WHERE fruit = target_fruit
    )
  )
{% endmacro %}
```
これを実行するには、`dbt_project.yml`で`on-run-start`を追加する必要があります。
```yaml
on-run-start:
  - '{{summary_price(target_fruit)}}'
```
これを加えることにより、`dbt run`した際に呼び出されUDFが作成・更新されます。(毎度呼び出されるので、更新時以外はコメントアウトする方が良いのかな？)
![スクリーンショット 2023-08-20 4.55.43.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492719/606381dc-b15a-d35e-b8e3-a6fb6bec5ed0.png)
ちなみに、on-run-startに記述する関数名は`{% macro summray_price(target_fruit) %}`に依存するため`{% macro sample(target_fruit) %}`としてから実行しても無事に実行されます。
```yaml
on-run-start:
  - '{{sample(target_fruit)}}'
```
以上によりUDFが以下のように作成されました。
![スクリーンショット 2023-08-20 5.06.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492719/0502026e-ac5c-d783-01cb-9cf7df46e9a2.png)

# 2. UDFを呼び出す
1では、UDFをBigQueryに作成しました。ここでは作成したUDFを呼び出して実際に使用したいと思います。
まず, UDFを呼び出すためのmacroを作成します。
```sql:macros/get_udfs/gu_summary_price.sql
{% macro gu_summary_price(target_fruit) %}
  `P****.B****.summary_price`({{ target_fruit }})
{% endmacro %}
```
これでmodels配下のSQLファイルで呼び出すことができます。

続いて果物の合計金額を算出するクエリを書いていきましょう。
```sql:models/summary_amount.sql
SELECT
  fruit,
  {{
    gu_summary_price(
      target_fruit="'banana'"
    )
  }} AS amount
FROM {{ ref('fruits') }}
WHERE fruit LIKE 'banana'
GROUP BY 1
```
呼び出すためのmacrosを作成したことにより、クエリの`{{ }}`で作成したUDFを使用することができます。
(※ jinja2では、引数をシングルクォーテーションまたはダブルクォーテーションで渡すためstringを渡したい時は`"' '"`で囲みます。UDFの方を`P****.B****.summary_price('{{ target_fruit }}')`と囲いmodelで`target_fruit='banana'`としても成功します)

BigQueryにも計算結果が反映されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492719/c4d5efad-6575-3bd5-5ff0-a769c3fd5083.png)

これで、DBTからBigQueryにUDFを作成し、利用するまでのプロセスは終わりです。
お疲れ様でした🙇‍♂️

# 補足: DBTで作成するメリット
DBTでは、 クエリの依存関係についてデータカタログを作成する機能がついています。チーム内のメンバーやチーム外のエンジニア、他部署のデータ利活用者にも簡単にリネージを見ることができるのでメリットが大きいのではと考えます。
```shell
dbt docs generate
```
↓
```shell
dbt docs serve
```
![スクリーンショット 2023-08-20 6.29.40.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492719/f10eef6d-736c-ea61-6df2-03362f7c2f88.png)
![スクリーンショット 2023-08-20 6.21.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/492719/47708380-99ab-1d11-1b09-c0a3e2460397.png)