dbt jinja 関数について挙動を確認してみた

よしなな

2024.06.25

こんにちは！よしななです。
前回の記事では、dbt jinja テンプレートの基礎構文についてまとめました。
今回は、dbt jinja テンプレートにある dbt jinja 関数について、実際に使用することの多い{{ref}}、{{source}}の2つの関数の挙動をまとめたので、備忘録として残します。

目次

  • 前提
    • 対象環境
    • コード実行のための準備
    • データとテーブルの準備
  • dbt jinja 関数とは
  • dbt jinja 関数一覧
    • {{ref}}
    • {{source}}
  • まとめ
  • 参考文献

前提

対象環境

本ブログのコードは以下の環境で実行しています。

  • OS
    • Windows 11
  • ターミナル
    • VSCode / PowerShell で実行

コード実行のための準備

本ブログのコードを実行するには、以下の準備が必要です。
今回は、以下の準備がすべて完了した前提で進めます。

データとテーブルの準備

.csv データの準備

下記の item_list.csv / transaction_data_raw.csv は ChatGPT を使用して生成しています。
https://chat.openai.com/

  • item_list.csv
アイテムID,大カテゴリ,小カテゴリ,アイテム名,アイテム価格
1001,家電,冷蔵庫,冷蔵庫A,50000
1002,家電,冷蔵庫,冷蔵庫B,60000
1003,家電,テレビ,テレビA,80000
1004,家電,テレビ,テレビB,90000
1005,家具,ソファ,ソファA,30000
1006,家具,ソファ,ソファB,40000
1007,家具,ベッド,ベッドA,70000
1008,家具,ベッド,ベッドB,80000
1009,食品,飲料,飲料A,500
1010,食品,飲料,飲料B,600
  • transaction_data_raw.csv
アイテムID,ユーザーID,タイムスタンプ
1001,u001,2023-04-01 10:00:00
1002,u002,2023-04-01 11:00:00
1003,u003,2023-04-01 12:00:00
1004,u004,2023-04-01 13:00:00
1005,u005,2023-04-01 14:00:00
1006,u006,2023-04-01 15:00:00
1007,u007,2023-04-01 16:00:00
1008,u008,2023-04-01 17:00:00
1009,u009,2023-04-01 18:00:00
1001,u001,2023-04-02 09:00:00
1002,u002,2023-04-02 10:00:00

テーブルの作成

上記の .csv データをもとに、以下のDDL文を実行し Amazon Athena 上にtransaction_raw_tableと、item_list_tableを作成します。

  • item_list_table を作成する DDL
CREATE EXTERNAL TABLE `item_list_table`(
  `アイテムid` int, 
  `大カテゴリ` string, 
  `小カテゴリ` string, 
  `アイテム名` string, 
  `アイテム価格` int)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  '{item_list.csv を格納したS3バケット名}'
TBLPROPERTIES (
  'classification'='csv', 
  'columnsOrdered'='false', 
  'compressionType'='none', 
  'delimiter'=',', 
  'skip.header.line.count'='1', 
  'transient_lastDdlTime'='1717037230')
  • transaction_raw_table を作成する DDL
CREATE EXTERNAL TABLE `transaction_raw_table`(
  `アイテムid` int , 
  `ユーザーid` string, 
  `タイムスタンプ` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  '{transaction_raw.csv を格納したS3バケット名}'
TBLPROPERTIES (
  'classification'='csv', 
  'columnsOrdered'='false', 
  'compressionType'='none', 
  'delimiter'=',', 
  'skip.header.line.count'='1')

dbt モデルの作成

dbt init実行で作成したtest_jinjaプロジェクト配下に dbt モデルを作成します。
作成した .sql ファイルに dbt モデルのロジックを書いていきます。

dbt jinja 関数とは

dbt には、標準の jinja テンプレートに加えて、dbt プロジェクトで作業する際に使用できる、dbt jinja 関数が用意されています。

dbt jinja 関数の一覧は以下になります。
https://docs.getdbt.com/reference/dbt-jinja-functions

本ブログでは、dbt jinja 関数の中でも、実際に使用することの多い{{ref}}、{{source}}について 実際に動かして挙動を確認してみたので次項にまとめます。

dbt jinja 関数一覧

{{ref}}

{{ref}}は、ある dbt モデルを別の dbt モデル内で参照するための関数です。
モデル間の依存関係を定義することが可能です。
本項で、{{ref}}を使用したdbtモデルの例をご紹介します。

参照元 dbt モデルの作成

まず、{{ref}}で参照する元となる dbt モデルを作成します。 ./test_jinja/models 配下にmaster.sqlを作成します。 master.sqlに、前提項目で作成したtransaction_raw_tableとitem_list_tableをitem_idで結合し、カラム名を日本語からアルファベットに修正するロジックを書きます。

  • master.sql
{{
    config(
        materialized='table'
    )
}}
with master as (
    select *
    from transaction_raw_table as t
    left join item_list_table as i
    using ("アイテムid")
)
select
  t."アイテムid" as item_id
  t."ユーザーid" as user_id
  t."タイムスタンプ" as transaction_date
  i."大カテゴリ" as category_l1
  i."小カテゴリ" as category_l2
  i."アイテム名" as item_name
  i."アイテム価格" as price
from master

master.sql を参照する dbt モデルの作成

次に、先ほど作成したmaster.sqlを参照する dbt モデルを作成します。 ./test_jinja/models 配下にmart.sqlを作成します。 mart.sqlに、master.sqlを{{ref}}で参照し、where句でuser_id = u001と条件を指定して抽出するロジックを書きます。

  • mart.sql
select *
from {{ref('master')}}
where user_id = 'u001'

実行結果：

２つのモデルが実行され、masterテーブルとmartテーブルが作成されていることを確認しました。

実行履歴：
master.sqlの実行履歴

mart.sqlの実行履歴

作成したテーブル：
masterテーブル

martテーブル

{{source}}

{{source}}は、./test_jinja/models 配下に格納された .yml ファイル（source.yml）に定義したデータベース名とテーブル名を参照してくれる関数です。

dbt のベストプラクティスとしては、source マクロを使用してソースデータを参照することが推奨されています。
sources.yml を使用する場合は、{{source}}を使用します。

source マクロを使用しない場合、dbt の挙動は、profiles.yml の prod部分で指定したdatabase、schemaを dbt モデルが参照します。

profiles.yml

test_jinja:
  outputs:
    prod:
      database: awsdatacatalog
      region_name: ap-northeast-1
      s3_data_dir: "{指定したS3バケット名}"
      s3_staging_dir: "{指定したS3バケット名}"
      schema: "{指定した Amazon Athena データベース名}" …この部分が dbt モデルで参照される
      threads: 1
      type: athena
  target: prod

./test_jinja/models 配下に source.yml ファイルを作成し、source.yml ファイルにデータベース名とテーブル名を定義することで、{{source}}が参照してくれるようになります。

本記事での検証

本記事では、作成した ./test_jinja/models ファイルの配下に source.yml を作成し定義をしています。

まずはじめに、profiles.yml で指定した データベースとは別に Athena でデータベース：test_jinja_dbとテーブル：transaction_raw_table、item_list_tableを作成します。

  • Athena 上でデータベース：test_jinja_dbを作成
CREATE DATABASE test_jinja_db;

作成したデータベースとテーブルを source.yml で指定します。

  • source.yml
sources:
  - name: test_jinja_db
    tables:
      - name: transaction_raw_table
      - name: item_list_table

最後に、dbt モデルを作成し{{source}}が想定通りに動くかを検証してみます。

  • test_jinja_source.sql
select *
from {{source('test_jinja_db', 'transaction_raw_table')}}
left join {{source('test_jinja_db', 'item_list_table')}}
using("アイテムid")

実行結果

source.yml と{{source}} で指定したデータベースとテーブルが参照されていることを確認しました。

まとめ

以上で、dbt jinja 関数についてのブログを終わります。
次回以降の記事で、dbt_project.yml の config について紹介できればと思います。
ここまで読んでいただきありがとうございました！

参考文献

https://docs.getdbt.com/reference/dbt-jinja-functions/source https://docs.getdbt.com/reference/dbt-jinja-functions/ref

customer-story-analytics

関連記事

dbt jinja テンプレートエンジンの基礎構文について調べてみた

よしなな

2024.06.17

CI強化、自動エクスポージャー(Automatic exposures)による最新データ反映…『dbt Cloud Launch Showcase』で発表されたその他有用なアップデートの紹介 #dbt

しんや

2024.05.16

注目の新機能＆GA(一般利用可能)アナウンスが盛り沢山！『dbt Cloud Launch Showcase』視聴レポート #dbt

しんや

2024.05.15

dbtで外部テーブルを参照するモデルを作成する

おざわ（じ）

2024.04.30