dbt入門
まえおき
ETL、否、ELTツールのdbtがどうやらデータ界隈ではポピュラーになっているようなのでいい加減そろそろ入門する。公式がチュートリアル？を公開してくれているようなのでこれをベースに見ていく。
余談？
ETLとELT
ETLとELTを別物として呼び分けるのが最近の世の中らしい。
extract, transform, load の3語からいずれもなる言葉だが、以下の違いがあるらしい
ETL
抽出し、加工したものをDBに取り込む
ELT
抽出し、一旦ロードしたものを加工する
ぶっちゃけどっちも「ETL」として今までETLという言葉を使ってきた。
あと、ELTの流れの処理のほうがより当たり前に今まで扱ってきた。
（ELTという言葉は知らなかったけど）
言葉は知らないけど実績はあるとか、改めて切り出した概念として理解はしていなかったけど「普通のこと」として認識していた、的な類のものに最近よく遭遇する。
なんとなく、自身のジジイ化を感じる。
上記は別によいとして、何かしらで今後老害ムーブをかまさないよう、自覚的でありたい。
ETLとELTの差の解説（AWS）
dbtのような外部ツールではないけど、今までもストアドのようなDB製品が保有する機能を使って、以下のようなことはやってきた。
所定の場所にファイルをおいてもらうか、DBから直接外部参照する形でデータ受領
ひとまず外部ファイル取り込み（一次テーブル）
扱いやすい形式に変換（二次加工テーブル化）
昔のアナログのやり方がより洗練されたのが今のELTであり、dbtってことか。
ユニバーサルセマンティックレイヤー、Headless BI
セマンティックレイヤーという言葉、意外と一般的ではないようだけど、SAP社のBIツール（BO）を扱っていた2010年ごろから知っていた言葉だ。
DBとビジネスの間に立って、その差を埋めるレイヤー、である。
最近では、更に一歩進んでユニバーサルセマンティックレイヤー、Headless BI という言葉があるらしい
特定のツールに限定しない形でのセマンティックレイヤーということだろうか。
SAP社のBOの文脈であれば、もちろんセマンティックレイヤーはBOというツール・サービスに閉じた形で扱われる。
ユニバーサルセマンティックレイヤー、Headless BI は👇️のような形で特定のツール・サービスに依存しないレイヤーを設けるスタイル、と理解した。
どうやらHeadless BI を導入すると下記が期待できるらしい。
データへの信頼性の向上
ロジックの定義をHeadless BIに寄せると、ビジネスロジックの分散を抑えることが期待できるため、組織がモニタリングするKPIに定義の揺れが発生するのを抑えることや、集計定義のバージョン管理や一括変更などが容易になります。
既存のETLツールなども、データウェアハウス内のテーブルやビューの定義を管理することはできますが、それらの完成されたテーブルやビューに対して、外部から投げられるSQLのロジックを管理するのは限界があります。Headless BIを活用することで、外部からデータウェアハウス内に構築済みのテーブル・ビューに対して投げられるクエリのロジックも含めて管理することが可能になります。
パフォーマンスの向上、DWコストの節約
例えば上記のCubeでは、インメモリキャッシュ(Request Cache)と、事前集計データ（Pre-aggregation Store）の２段階のクエリ高速化手段を提供しています。
事前集計済みデータ（Pre-aggregation Store）では、事前に頻繁に参照されるディメンションとメジャーを組み合わせた計算結果を、Cube内または利用しているデータウェアハウス内で事前集計した結果を保持出来るため、集計パフォーマンスを大幅に改善させることが期待できます。
こうした機能は、データウェアハウスへのクエリ本数の削減や、個別のクエリのパフォーマンス高速化に活用できるため、データウェアハウスのコスト節約手段としても期待できます。
データ民主化の加速
Headless BIの導入を行うと、データ利用者は事前構築済みのセマンティックレイヤーを利用することが可能なため、データ利用者はビジネスロジックのコピーに頭を悩ますことなく、慣れ親しんだ用語のみを使って集計したデータを、可視化や分析に利用できます。
そのため、データ活用にあたって必要な技術的なハードルが、従来よりも下げられた状態となり、より多くの人にデータを活用してもらえることも期待できます。
かなり疑問が残る…
信頼性の話…
Headless BI の概念やサービスを絡めなくても、データ管理者（DWHの主管）が統制を取って、利用者にデータ（テーブル）を公開するというスタイルの運用は一般的に存在していた。
各利用者が、管理者に依存することなくよしなにやれるということ…？
テーブル間のリレーションや各テーブルの定義などを、ユニバーサルセマンティックレイヤー用のツールで表現できていれば、様々なツールを利用するユーザにとって便利、信頼してデータを使える、という話？パフォーマンスの話…
結局データを管理するレイヤが増えてコストや管理が増えない…？データ民主化の話…
「信頼性」で疑問として述べた話はこっちか…
ひとまず、dbtが提供する dbt Semantic Layer を触ってみることで理解を深めたい。
ここからは冒頭で記載した公式チュートリアルを眺めていく。
dbt の嬉しいところ
「select文を書くだけでそれがパイプラインになる」
SQLで定義したモデルはrefと呼ばれるテーブル間の関係性を自動的に把握できるようになる仕組みで記述することで、DAG（有向非巡回グラフ）を考慮したデプロイを実行できるため、モデルを反映させるための細かなワークフローを組む必要がない
→View相当のSQLを記述をしておけば、テーブル間のリレーション管理や、データの伝達の自動化ができる、みたいは話？
dbtでできること
テンプレートエンジンのJinjaを使った高度なクエリ処理
SQL＋制御構文によってデータを処理できる
マクロ機能で他のdbtユーザーが作った処理を利用することも可能
テスト
モデルによって生成された結果にアサーションを行うことで、各モデルのSQLの整合性を向上させる方法を提供
ドキュメンテーション
dbtではdbtモデルのドキュメントを書き、バージョン管理し、共有する仕組みを提供
モデルやフィールドに対して、説明文（テキスト、またはマークダウン）を書くことができる
セットアップ
BigQuery環境を準備
以下の作業を実施
Googleクラウドコンソールでプロジェクト作成
サービスアカウントを作成（BigQuery管理者権限を付与）
キーの発行
dbt Cloudアカウント準備
アカウント作成
プロジェクト作成（BigQueryへの接続設定）
作成済みのBigQuery用のアクセスキー情報をアップロードして「Connection(接続)」を作成
Test Connection(接続確認)
この「接続確認」の段階で以下のエラーが出るかも。
作成したGCP上のプロジェクトのBigQueryに対してAPI接続許可を出していなければ、このエラーが出る。下記画面から「ENABLE」をクリックする。
再度接続確認
今度はdbtからBigQueryへの接続確認で成功
プロジェクト作成（リポジトリ作成）
今回はGithubを利用。Github上でプライベートリポジトリを一つ作成sいておく。
dbtの画面上でリポジトリをGithubと指定すると認証画面が表示され、Githubのアカウントとの連携（認証）画面が表示されるので認証する。
Github上のリポジトリから、dbt用のリポジトリを選択する。
プロジェクト作成完了
プロジェクト初期化
dbt Cloud のIDE画面で「Initialize dbt project」と表示されている。
まずは初期化が必要なので実施する。
初期化後
まずは初期コミットしたうえで、ブランチを作成する。
ここでは「add-customers-model」ブランチとする。
テーブル準備
チュートリアルでは「data」フォルダにCSVファイルを格納してそれをもとにテーブル定義をカジュアルに生成しているようである。
2024年10月現在、「data」フォルダは初期状態ではなくなっている。
最新では「seeds」フォルダがそれに相当するみたいなので、ここにサンプルデータのCSVを格納する。サンプルデータはこちらから。
IDEからのファイルアップロード方法が謎なので、Github上でファイルを追加・コミットしたうえで、IDE上でpullを実施。
コマンドを実行
dbt seed
成功。BigQuery側を見てみるとたしかにテーブルになっている。
customersテーブルを作成
/models/customers.sql を作成
with customers as (
select
id as customer_id,
first_name,
last_name
from dbt_xxxxxxx.raw_customers
),
orders as (
select
id as order_id,
user_id as customer_id,
order_date,
status
from dbt_xxxxxxx.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 finalPreviewボタンを押すとデータのプレビューを見れる。
下記コマンドを実行
dbt run成功。（初期化時に格納されていた examples が実行されて余計なテーブルが生成されたが、一旦無視）
モデルの構造化
with句でやっていたことを、別ファイルに切り出し、データやSQLを構造管理
-- stg_customers.sql
select
id as customer_id,
first_name,
last_name
from dbt_xxxxxxx.raw_customers
;
-- stg_orders.sql
select
id as order_id,
user_id as customer_id,
order_date,
status
from dbt_xxxxxxx.raw_orders
-- customers.sql (変更)
with customers as (
select * from {{ ref('stg_customers') }}
),
orders as (
select * from {{ ref('stg_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
ref('table_name')で参照設定することによって、リネージが可能となる。
依存関係も把握されるので、 dbt run 時の実行順序も良しなに制御される。
modelsで作成したものはBigQury上ではViewとして定義されている。
テスト,ビルド,ドキュメント
リレーションシップや各テーブルのカラム情報などをyaml で記載。
description でドキュメントとしての記載も可。
version: 2
models:
- name: customers
description: One record per customer
columns:
- name: customer_id
description: Primary key
tests:
- unique
- not_null
- name: first_order_date
description: NULL when a customer has not yet placed an order.
- name: stg_customers
description: This model cleans up customer data
columns:
- name: customer_id
description: Primary key
tests:
- unique
- not_null
- name: stg_orders
description: This model cleans up order data
columns:
- name: order_id
description: Primary key
tests:
- unique
- not_null
- name: status
tests:
- accepted_values:
values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']上記定義に従い、テストやドキュメント生成が行われる
-- テスト
dbt test
-- ドキュメント生成
dbt docs generatedbt build を実行すると、標準では下記コマンドがまとめて実行されるらしい
dbt run
dbt test
dbt snapshot
dbt seed注意
dbt run の直後に dbt test が実行されるわけだが、ここでしょっちゅうエラーが発生した。 table not found 系のエラーである。
おそらく、dbt run 実行の直後（つまりcreate or replaceが実行された直後）で test が行われた際に、生成されたはずのオブジェクト（tableやview）が存在しない、と言われるのである。
ChatGPT先生曰く、以下の通り。
dbt build は dbt test を含むため、モデルが作成された後にすぐにテストが実行され、データの整合性に関連する問題が発生している可能性があります。特に、ビューを作成する際にまだ参照できないテーブルやビューがテスト時に存在しないという問題が発生している可能性があります。
これに対し、同じくChatGPT先生が提示してくれた解決案は以下の通り
# テストなしでbuildを実行
dbt build --select models --exclude test
# build対象のオブジェクトを限定
dbt build --select stg_customers
dbt build --select stg_orders
dbt build --select customers
# 並列処理を行わない（シングルスレッドで処理する）
dbt build --threads 1これらの方法を実行すれば、build は通った。
ドキュメント生成については下記コマンドを実行するわけだが、
dbt docs generateこれをCloud IDE上で実行しても、dbtのExploreから参照できるドキュメントには更新はかからなかった。あくまでもローカル上でドキュメントを生成する際に有効なコマンドと思われる。
代わりに、Deploy > Jobs > 作成したビルド用ジョブ Run now を実行すると、ドキュメントが生成されたうえでdbt のExplore 上でも情報が反映された。
ジョブ定義
実行
ドキュメント
デプロイ
先行して、上記build時のエラーやドキュメント生成時の問題から、選考してデプロイ作業は実施してしまっていたが、チュートリアルに沿って、改めてデプロイについて記載する。
環境
Deply > Emvironment から、「環境」を作成できる。
開発環境かステージング環境か本番環境か、接続先はどこか、などの設定を行う
ジョブ
作成した環境上で実行するジョブの定義を行う。
（Deploy > Jobs から作成・確認が可能）
ジョブ作成時は、既定のパターンから選択してジョブ作成が可能
Deploy job の場合は以下の通り。デフォルトではコマンドは
dbt build だったが、前述のエラーの都合から --threads 1 を追加してある。
これで作成したジョブを実行すれば、ビルドジョブが走り、build や docs generate が行える。
Jinjaを使った高度なモデリング
基本SQL。select句に似たような記述がたくさんある。
select
order_id,
sum(amount) as total_amount,
sum(case when payment_method = 'bank_transfer' then amount end) as bank_transfer_amount,
sum(case when payment_method = 'credit_card' then amount end) as credit_card_amount,
sum(case when payment_method = 'gift_card' then amount end) as gift_card_amount
from {{ ref('raw_payments') }}
group by 1Jinjaを使ってDRYできる。
-- 変数管理
{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}
select
order_id,
sum(amount) as total_amount,
-- 変数の数だけループして select 句を記述
{% for payment_method in payment_methods %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount
-- ループのラストでなければカンマをつける、といった制御
{% if not loop.last %},{% endif %}
{% endfor %}
from
{{ ref('raw_payments') }}
group by
1マクロを定義して呼び出すことも可能。
-- マクロを定義（ここでは単純に、変数のリストを返すだけの関数）
-- /macros/get_payment_methods.sql
{% macro get_payment_methods() %}
{{ return(["bank_transfer", "credit_card", "gift_card"]) }}
{% endmacro %}
-- 本体
-- models/order_payment_method_amounts.sql
-- マクロ呼び出し
{%- set payment_methods = get_payment_methods() -%}
select
order_id,
sum(amount) as total_amount,
-- 変数の数だけループして select 句を記述
{% for payment_method in payment_methods %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount
-- ループのラストでなければカンマをつける、といった制御
{% if not loop.last %},{% endif %}
{% endfor %}
from
{{ ref('raw_payments') }}
group by
1dbtのモデルとは
概要
dbtで呼ばれるモデルとはselect文のこと
モデルは.sqlファイルで定義される
通常ではmodelsディレクトリに格納
ファイルの名前がモデル名として使用される
dbt runコマンドを実行するとdbtはこのモデルをCREATE VIEW asまたCREATE TABLE asのステートメントでラップしてDWHに構築
設定
dbt_project.ymlや各モデルのconfigブロック似て設定
materializationを適宜してい（viewとかtableとか）
スキーマの指定
モデルにタグを付与
例
# dbt_project.yml で指定
name: jaffle_shop
config-version: 2
...
models:
jaffle_shop: # これは `name:`` の設定で指定した値と同じものになる必要があります
+materialized: view # これは現在のプロジェクト全てのモデルに適用されます
marts:
+materialized: table # これは`marts/`以下の全てのモデルに適用されます
marketing:
+schema: marketing # これは`marts/marketing/`以下の全てのモデルに適用されます
-- sqlファイル内で指定
{{ config(
materialized="view",
schema="marketing"
) }}
with customer_orders as ...マテリアライゼーション
dbtのモデルをウェアハウスに保存するための戦略であり、dbt独自用語
マテリアライゼーションには以下がある
view（デフォルト）
table
incremental
テーブルとして実体を持ち、都度データの追加や更新を行う
（table より構築が高速になる）追加設定が必要になるので高度な使い方と言える
イベント形式データに最適
ephemeral
DBに直接組み込まれることはない
CTE(共通テーブル)表現として、依存するモデルに補完される
各DBオブジェクトに対して、上記のうちどれで保存する（作成する）かはdbt_project.ymlか、.sql内でconfigで設定する。
ソース
ymlにて、データソースの定義を指定可能
各種SQLからは、このソースとして宣言された情報をベースにしてデータ参照する
# models/souces.yml
version: 2
sources:
- name: my_bigquery ## 任意の名前
database: dbt-test-xxxxxxxx
schema: dbt_xxxxxxxx_2
tables:
- name: sample
# models/stg_sample.sql
SELECT
*
FROM {{ source('my_bigquery', 'sample') }}dbt画面上では、以下のようにリネージ情報が参照可能となる。
stg_sample モデルの内容は以下の通り
ライブラリ紹介
こちらを参照