BigQueryでdbt macrosを触ってみる

hanzawa.yuya

2024.07.23

Google Cloudデータエンジニアのはんざわです。
最近はdbtを触る機会が多く、前回と前々回に続きdbtのブログになります。
今回のブログでは、dbt macrosを触ってみたいと思います。

dbt macrosとは？

dbtでは、Pythonのテンプレートエンジンである Jinja とJinjaの機能である macros を使って、SQLを柔軟に記述することができます。

JinjaはSQLと組み合わせることでSQL単体では実現できないことができるようになります。
以前に投稿したブログで使用していた{{ source( ... ) }}もJinjaの表記になります。

macrosは他のプログラミング言語の「関数」のように再利用可能なクエリを定義し、呼び出すことができる機能です。
デフォルトでは、dbtプロジェクトのmacrosディレクトリで再利用したいクエリを定義します。

検証内容

今回の検証では、次の２つのdbt macrosを作成し、モデルで呼び出してみたいと思います。

文字列の接尾語に _Hello Worldを加える
指定したテーブルの特定のキーのカウントを取得する

前提

実行環境は以前の投稿したブログと同じ
dbt init dbt_macrosで新規のdbtプロジェクトを作成済み
BigQueryでdbt_macrosと名付けたデータセットをUSリージョンに作成し、dbtプロジェクトの出力先データセットに指定する

検証の準備

さっそく検証の準備を進めていきたいと思います。

ディレクトリ構成

最終的なディレクトリ構成は以下のようになりました。

% tree macros_test

macros_test
├── macros
│   ├── add_hello_world.sql
│   └── get_key_counts.sql
├── models
│   └── macros_test
│       ├── src_tables.yml
│       ├── test1.sql
│       └── test2.sql
// 省略

18 directories, 20 files

それぞれのファイルの詳細は次の通りです。

ソース（source）

以下のようにソースを定義しています。詳細は前回のブログを確認してください。

models/macros_test/src_tables.yml

version: 1

sources:
  - name: samples_tables
    database: bigquery-public-data
    schema: samples
    tables:
      - name: shakespeare

マクロ（macros）

dbt macrosは、以下のようなフォーマットで定義します。

{% macro function_name(*args, **kwargs) %}

{% endmacro %}

最初に{% macro function_name(*args, **kwargs) %}で関数名と引数を定義し、最後に{% endmacro %}で締めます。
関数で定義した引数は{{ args }}で呼び出すことができます。

詳細は以下のドキュメントを参考にしてください。

さっそく前述した２つのマクロを作成します。

文字列の接尾語に_Hello Worldを加える

関数名をadd_hello_worldとし、引数にinput_textを渡しています。
CONCATで引数のinput_textと_Hello Worldを連結するだけのシンプルなマクロです。

macros/add_hello_world.sql

{% macro add_hello_world(input_text) %}

    (CONCAT({{ input_text }}, '_Hello World') )

{% endmacro %}

指定したテーブルの特定のキーのカウントを取得する

関数名をget_key_countsとし、引数にinput_tableとinput_keyを渡しています。
input_tableでテーブルを指定し、input_keyでグループ化してそれぞれの要素をカウントするマクロです。

macros/get_key_counts.sql

{% macro get_key_counts(input_table, input_key) %}

    SELECT
        {{ input_key }},
        COUNT(*) AS cnt
    FROM
        {{ input_table }}
    GROUP BY {{ input_key }}

{% endmacro %}

最後にモデルでこれらのマクロを呼び出してみます。

モデル（models）

まずはadd_hello_worldのマクロを呼び出してみます。
ファイル名をtest1.sqlとして、引数のinput_textにソースで定義したテーブルのwordカラムを指定しています。
また、materializedをtableに指定しているため、モデルを実行すると事前に指定したデータセットにテーブルが作成されます。

このモデルを見て分かる通り、add_hello_worldは文字列を結合処理するだけのものであるため、SELECT句でマクロを呼び出しています。

macros_test/models/test1.sql

{{
    config(
        materialized='table'
    )
}}

SELECT
    word,
    {{ add_hello_world('word') }} AS word_hello_world
FROM
    {{ source('samples_tables', 'shakespeare') }}

次にget_key_countsのマクロを呼び出してみます。
ファイル名をtest2.sqlとして、引数のinput_tableにソースで定義したテーブルを与え、input_keyにwordカラムを指定しています。

先ほどのモデルとは違い、get_key_countsはマクロの中で処理が完結しているため、引数を与えるだけのモデルとなっています。

このように一部の処理をマクロ化して再利用することも可能ですし、まとまった処理をマクロ化することも可能です。

macros_test/models/test2.sql

{{
    config(
        materialized='table'
    )
}}

{{ get_key_counts(source('samples_tables', 'shakespeare'), 'word') }}

動かしてみる

さっそくdbt runで動かしてみます。

% dbt run

16:41:43  Running with dbt=1.8.3
16:41:44  Registered adapter: bigquery=1.8.2
16:41:44  Found 2 models, 1 source, 473 macros
16:41:44  
16:41:46  Concurrency: 1 threads (target='dev')
16:41:46  
16:41:46  1 of 2 START sql table model dbt_macros.test1 .................................. [RUN]
16:41:50  1 of 2 OK created sql table model dbt_macros.test1 ............................. [CREATE TABLE (164.7k rows, 1.3 MiB processed) in 3.99s]
16:41:50  2 of 2 START sql table model dbt_macros.test2 .................................. [RUN]
16:41:54  2 of 2 OK created sql table model dbt_macros.test2 ............................. [CREATE TABLE (32.8k rows, 1.3 MiB processed) in 3.99s]
16:41:54  
16:41:54  Finished running 2 table models in 0 hours 0 minutes and 9.86 seconds (9.86s).
16:41:54  
16:41:54  Completed successfully
16:41:54  
16:41:54  Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

正常に成功しました！
BigQueryにテーブルが作成されているか確認してみます。

スクリーンショット 2024-07-23 0.24.57

上記のキャプチャのようにdbt_macrosのデータセット配下にtest1とtest2のテーブルが作成されていました。

スクリーンショット 2024-07-23 0.42.45

test1のテーブルを確認すると想定通りに接尾語に_Hello Worldが付いていました。

スクリーンショット 2024-07-23 0.46.13

test2のテーブルも想定通りに各要素がカウントされていました。

最後にBigQueryのジョブ履歴でdbtから発行されたクエリの履歴を確認してみます。

以下の２つのクエリがtest1とtest2のテーブルを作成したクエリになります。
これらを見て分かるようにマクロで定義したクエリがモデルで展開され、新しいクエリが生成されています。

/* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "macros_test", "target_name": "dev", "node_id": "model.macros_test.test1"} */

    create or replace table `PROJECT_ID`.`dbt_macros`.`test1`

    OPTIONS()
    as (

SELECT
    word,

    (CONCAT(word, '_Hello World') )

 AS word_hello_world
FROM
    `bigquery-public-data`.`samples`.`shakespeare`
    );

/* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "macros_test", "target_name": "dev", "node_id": "model.macros_test.test2"} */

    create or replace table `PROJECT_ID`.`dbt_macros`.`test2`

    OPTIONS()
    as (

    SELECT
        word,
        COUNT(*) AS cnt
    FROM
        `bigquery-public-data`.`samples`.`shakespeare`
    GROUP BY word

    );

まとめ

今回はdbt macrosを簡単に紹介しました。
dbt macrosを導入することでクエリの再利用性が向上し、それに伴って保守性の向上やテストの効率化など様々なメリットが生まれます。
また、本ブログでは紹介できませんでしたが、IF文を使った条件分岐やFOR文を使ったループ処理も可能ですので、是非活用してみてください。