はじめに

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

環境
- MacOS(Intel)

DBTが初めての方は、こちらを参照ください。

dbt install
BigQuery adapterのインストール
dbt init (project name)でプロジェクトを作成
$HOME/.dbt/profiles.ymlを設定
dbt debugで接続を確認

brew installでは、dbt-coreまでしかサポートされていないようなのでadapterのインストールはpip installでやってください。

0. テーブルを作成する

UDFを使用するためにテーブルを作成します。

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を実行し以下のテーブルが作成されました。

ちなみにこの時のdbt_project.ymlはこのようにしています。
(以下のP****はproject ID, A****はテーブルを格納するdatasetを指しています。)

models:
  dbt_hoge:
    +database: P****
    +schema: A****
    +materialzed: table

1. UDFを作成する

次にmacros配下に作成したいUDFを記述します。
今回は果物の合計金額を算出するクエリを作成しました。
(以下のB****はUDFを格納するdatasetを指しています。)

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を追加する必要があります。

on-run-start:
  - '{{summary_price(target_fruit)}}'

これを加えることにより、dbt runした際に呼び出されUDFが作成・更新されます。(毎度呼び出されるので、更新時以外はコメントアウトする方が良いのかな？)

ちなみに、on-run-startに記述する関数名は{% macro summray_price(target_fruit) %}に依存するため{% macro sample(target_fruit) %}としてから実行しても無事に実行されます。

on-run-start:
  - '{{sample(target_fruit)}}'

以上によりUDFが以下のように作成されました。

2. UDFを呼び出す

1では、UDFをBigQueryに作成しました。ここでは作成したUDFを呼び出して実際に使用したいと思います。
まず, UDFを呼び出すためのmacroを作成します。

macros/get_udfs/gu_summary_price.sql

{% macro gu_summary_price(target_fruit) %}
  `P****.B****.summary_price`({{ target_fruit }})
{% endmacro %}

これでmodels配下の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にも計算結果が反映されました。

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

補足: DBTで作成するメリット

DBTでは、クエリの依存関係についてデータカタログを作成する機能がついています。チーム内のメンバーやチーム外のエンジニア、他部署のデータ利活用者にも簡単にリネージを見ることができるのでメリットが大きいのではと考えます。

dbt docs generate

↓

dbt docs serve