【dbt】dbt ～ 基本編 / Model ～

■ はじめに

https://dk521123.hatenablog.com/entry/2023/06/30/000000
https://dk521123.hatenablog.com/entry/2023/05/30/151003
https://dk521123.hatenablog.com/entry/2023/11/20/230946

の続き。

dbt の モデル（Model）について、徐々に学んでみる

目次

【１】Model
【２】マテリアライゼーション
【３】Hello world

【１】Model

* モデルは SQL の SELECT 文で作る
* 1モデル : 1select文（with句等は除く）で構成
* モデルは.sqlファイルで定義
* 通常、modelsディレクトリに格納

【２】マテリアライゼーション

* モデルを DW 上で実体化すること

cf. Materialization = 物質化

https://zenn.dev/dbt_tokyo/books/537de43829f3a0/viewer/materializations_doc

#	マテリアライゼーション種類	説明
1	view	モデルがビューになる。（デフォルト）
2	table	モデルがテーブルになる。dbt run ごとにデータを入れ直す
3	incremental	モデルがテーブルになる。dbt run ごとにInsert/Update
4	view	モデルがCTE （共通テーブル）になる

WITH句 ～ 共通テーブル式 ～
https://dk521123.hatenablog.com/entry/2012/07/26/013620

変更の仕方

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

【３】Hello world

１）前準備

* 前提条件となるテーブルをSeedで作る

/seeds/raw_customers.csv

id,first_name,last_name
1,James,Smith
2,Maria,Garcia
3,David,Smith
4,Michael,Rodriguez
5,Mary,Smith

/seeds/raw_orders.csv

id,user_id,order_date,status
1,1,2023-01-01,returned
2,3,2023-01-02,completed
3,2,2023-06-04,completed
4,2,2023-10-26,placed
5,1,2023-11-23,shipped
6,4,2023-11-25,return_pending

/seeds/raw_payments.csv

id,order_id,payment_method,amount
1,1,credit_card,0
2,2,coupon,1000
3,3,bank_transfer,1700
4,4,credit_card,1990
5,5,gift_card,2300
6,6,bank_transfer,600

２）Model

/seeds/customers.sql

with customers as (
  select
    id as customer_id,
    first_name,
    last_name
  from raw_customers
),
orders as (
  select
    id as order_id,
    user_id as customer_id,
    order_date,
    status
  from raw_orders
),
customer_orders as (
  select
    customer_id,
    min(order_date) as first_order_date,
    max(order_date) as most_recent_order_date,
    count(order_id) as number_of_orders
  from orders
  group by 1
),
final as (
  select
    customers.customer_id,
    customers.first_name,
    customers.last_name,
    customer_orders.first_order_date,
    customer_orders.most_recent_order_date,
    coalesce(customer_orders.number_of_orders, 0) as number_of_orders
  from customers
  left join customer_orders using (customer_id)
)

-- 結局ここ
select * from final

３）動作確認

https://dk521123.hatenablog.com/entry/2023/05/30/151003

# で環境構築して、Windows だった場合
>dbt-env\Scripts\activate

>cd hello_world
>cd hello_world

# 使用するテーブルを作成
>dbt seed

# dbt run でモデルを作成
>dbt run

出力テーブル「customers」

customer_id	first_name	last_name	first_order_date	most_recent_order_date	number_of_orders
1	James	Smith	2023-01-01	2023-11-23	2
2	Maria	Garcia	2023-06-04	2023-10-26	2
3	David	Smith	2023-01-02	2023-01-02	1
4	Michael	Rodriguez	2023-11-25	2023-11-25	1
5	Mary	Smith	NULL	NULL	0

参考文献

https://zenn.dev/dbt_tokyo/books/537de43829f3a0/viewer/models_doc
https://zenn.dev/foursue/books/31456a86de5bb4/viewer/6037e5

関連記事

dbt ～ 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2023/06/30/000000
dbt ～ 入門編 ～
https://dk521123.hatenablog.com/entry/2023/05/30/151003
dbt ～ 基本編 / SEED ～
https://dk521123.hatenablog.com/entry/2023/11/20/230946
dbt ～ Model あれこれ ～
https://dk521123.hatenablog.com/entry/2023/11/26/224522
dbt ～ Macro ～
https://dk521123.hatenablog.com/entry/2023/11/29/003751
dbt ～ aliases ～
https://dk521123.hatenablog.com/entry/2023/12/13/031018