はじめに
データ関連の業務に携わって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を使用するためにテーブルを作成します。
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を指しています。)
{% 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を作成します。
{% macro gu_summary_price(target_fruit) %}
`P****.B****.summary_price`({{ target_fruit }})
{% endmacro %}
これでmodels配下の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
