この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
大阪オフィスの玉井です。
dbtは、データ変換を行い、接続先のDWHにデータモデルを生成します。ただ、その「データモデル」ってそもそも何?と思う方がほとんどだと思います。
dbtは、変換した「データモデル」を色々な形でDWHに生成することができます。これをMaterializationsと呼びます。今回はdbtで設定できるMaterializationsのうち、基本となる3つをご紹介します。
基本のMaterializations3つの概要
今回紹介するMaterializationsの超概要について、先に紹介します。
- table
- DWHに、データモデルを「テーブル」として作成します。
- view
- DWHに、データモデルを「ビュー」として作成します。
- ephemeral
- DWHに、何らかの作成を行いません。
- データモデルのクエリにおける、共通テーブル式(CTE)として扱います。
おそらく、実際に実行してみた方が理解が進むと思います。
検証環境
- macOS Catalina 10.15.7
- dbt CLI 0.18.1
- Google BigQuery
やってみた
今回は、検証用として、下記のデータモデルを使います。
stg_orders.sql
select
id as order_id,
user_id as customer_id,
order_date,
status
from {{ source('jaffle','raw_order') }}
table
上記のデータモデルを「table」として生成したいと思います。このデータモデルのsqlファイルに、Materializationsの指定を追記します。
{{ config(
materialized='table'
)}}
select
id as order_id,
user_id as customer_id,
order_date,
status
from {{ source('jaffle','raw_order') }}
config
の部分で、このデータモデルのMaterializationsをtableに指定しました。
このデータモデルを実行します。CREATE TABLE
と表示されましたね。
$ dbt run --models stg_orders
Running with dbt=0.18.1
Found 6 models, 11 tests, 0 snapshots, 0 analyses, 326 macros, 0 operations, 0 seed files, 2 sources
12:22:54 | Concurrency: 4 threads (target='learn')
12:22:54 |
12:22:54 | 1 of 1 START table model jaffle.stg_orders........................... [RUN]
12:22:58 | 1 of 1 OK created table model jaffle.stg_orders...................... [CREATE TABLE (99.0 rows, 3.3 KB processed) in 4.26s]
12:22:58 |
12:22:58 | Finished running 1 table model in 8.97s.
Completed successfully
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
DWH側に生成されたデータモデルを確認します。確かにテーブルとして作られていますね。
view
先程「テーブル」として生成したデータモデルを、今度は「ビュー」として作り直してみます。
データモデルを下記のように書き換えます。
{{ config(
materialized='view'
)}}
select
id as order_id,
user_id as customer_id,
order_date,
status
from {{ source('jaffle','raw_order') }}
設定部分をview
に変更しただけです。
このデータモデルを実行します。今度はCREATE VIEW
と表示されましたね。ちなみに--full-refresh
というオプションをつけている理由ですが、先程の検証で、このデータモデルは既にテーブルとして存在しているため、そのまま実行してもエラーになります。同データモデルを「完全に作り直す」ために、このオプションを使用しています。
$ dbt run --models stg_orders --full-refresh
Running with dbt=0.18.1
Found 6 models, 11 tests, 0 snapshots, 0 analyses, 326 macros, 0 operations, 0 seed files, 2 sources
12:28:16 | Concurrency: 4 threads (target='learn')
12:28:16 |
12:28:16 | 1 of 1 START view model jaffle.stg_orders............................ [RUN]
12:28:21 | 1 of 1 OK created view model jaffle.stg_orders....................... [CREATE VIEW in 4.99s]
12:28:21 |
12:28:21 | Finished running 1 view model in 7.39s.
Completed successfully
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
DWH側に生成されたデータモデルを確認します。ビューとして生成されています。
ephemeral
テーブルやビューと違って、あまりしっくりこないephemeralを試してみます。記事の冒頭でも述べましたが、このMaterializationはDWHに何らかの形でデータモデルを残すことは行いません(何も生成されない)。
まず、事前に、先程生成したビューを(DWH側で)削除しておきます。
データモデルを下記のように書き換えます。
{{ config(
materialized='ephemeral'
)}}
select
id as order_id,
user_id as customer_id,
order_date,
status
from {{ source('jaffle','raw_order') }}
このデータモデルを実行します。何もCREATEしてない様子。
$ dbt run --models stg_orders --full-refresh
Running with dbt=0.18.1
Found 6 models, 11 tests, 0 snapshots, 0 analyses, 326 macros, 0 operations, 0 seed files, 2 sources
12:34:03 | Concurrency: 4 threads (target='learn')
12:34:03 |
12:34:03 |
12:34:03 | Finished running in 1.76s.
DWHを確認します。何も生成されていません。
ephemeralなデータモデルを参照しているデータモデルの検証
ephemeral
が指定されているデータモデルは、上記の検証の通り、DWHに何も生成しません。しかし、dbt上では、これもまた「データモデル」の一つとして定義されているわけです。
そこで、ephemeral
のデータモデルを参照している別のデータモデルはどのようになるのか、実際に見てみたいと思います。
さっきのephemeral
にしたデータモデルを参照するデータモデルを用意します。stg_orders
とstg_payments
のMaterializationsはephemeral
になっています。このデータモデル自体(fct_orders)はテーブルとして生成します。
fct_orders.sql
{{ config(
materialized='table'
)}}
with orders as (
select * from {{ ref('stg_orders' )}}
),
payments as (
select * from {{ ref('stg_payments') }}
),
order_payments as (
select
order_id,
sum(case when status = 'success' then amount end) as amount
from payments
group by 1
),
final as (
select
orders.order_id,
orders.customer_id,
orders.order_date,
coalesce(order_payments.amount, 0) as amount
from orders
left join order_payments using (order_id)
)
select * from final
このデータモデルを実行します。テーブルが1つだけ作成されました。
$ dbt run --models fct_orders --full-refresh
Running with dbt=0.18.1
Found 6 models, 11 tests, 0 snapshots, 0 analyses, 326 macros, 0 operations, 0 seed files, 2 sources
12:44:50 | Concurrency: 4 threads (target='learn')
12:44:50 |
12:44:50 | 1 of 1 START table model jaffle.fct_orders........................... [RUN]
12:44:56 | 1 of 1 OK created table model jaffle.fct_orders...................... [CREATE TABLE (99.0 rows, 5.2 KB processed) in 5.59s]
12:44:56 |
12:44:56 | Finished running 1 table model in 8.36s.
Completed successfully
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
DWH側を確認します。fct_orders
はテーブルとして生成されていますが、このテーブルの参照元となっているstg_orders
とstg_payments
はありません。
では、fct_orders
はどうやって生成されたのでしょうか。実際に実行されたクエリはどのようなものなのでしょうか。下記のようになっていました。
create or replace table `tamai-rei`.`jaffle`.`fct_orders`
OPTIONS()
as (
with __dbt__CTE__stg_orders as (
select
id as order_id,
user_id as customer_id,
order_date,
status
from `tamai-rei`.`jaffle`.`raw_order`
), __dbt__CTE__stg_payments as (
select
id as payment_id,
orderid as order_id,
paymentmethod as payment_method,
status,
amount / 100 as amount,
created as created_at
from dbt-tutorial.stripe.payment
),orders as (
select * from __dbt__CTE__stg_orders
),
payments as (
select * from __dbt__CTE__stg_payments
),
order_payments as (
select
order_id,
sum(case when status = 'success' then amount end) as amount
from payments
group by 1
),
final as (
select
orders.order_id,
orders.customer_id,
orders.order_date,
coalesce(order_payments.amount, 0) as amount
from orders
left join order_payments using (order_id)
)
select * from final
);
参照元のephemeral
のデータモデルの部分が、WITH句として挿入されています。記事の冒頭に書いた「データモデルのクエリにおける、共通テーブル式(CTE)として扱います。」というのは、こういう意味でした。それ自体がデータモデルとして生成されることはないですが、他のデータモデルからの参照としては、キッチリと認識されるということです。
検討事項(使い分け)
例えば、BIツールから頻繁に参照するようなデータモデルの場合は、パフォーマンスの観点から、実テーブルとして生成した方が良いと思いました。逆に、そこまで参照におけるパフォーマンスを重視しない場合は、ビューにしておいた方が、DWH側の容量の削減になるので、良いと思いました。
では、「ephemeral」はどういったときに使えばいいのでしょうか?
データ変換において「それ自体は必要ないけど、中間テーブルとして必要」というモデルは、結構頻繁に登場します。そういうデータモデルはephemeralにすれば良いと思います。DWH側にも、余分なテーブルやビューが増えなくなります。しかし、ephemeralは、参照先のデータモデルに、共通テーブル式として挿入される以上、実際に実行されるクエリが複雑になることに注意する必要があります(後で見直す時に、何をやっているか理解しづらくなる)。
Materializationsの指定方法
Materializationsを指定するにあたり、今までは、それぞれモデルファイルに、いちいちconfig
という記述を書いてきましたが、dbt_project.yml
で一括指定することができます。
...
models:
jaffle_shop:
staging:
materialized: view
marts:
materialized: table
上記のように書くと、例えば、modelsディレクトリ内のstagingディレクトリ下にあるデータモデルは全てビューとして生成されます。この場合、いちいち各モデルファイルにconfig
を指定する必要はありません(指定した場合、config
側の設定が優先される)。
おわりに
公式ドキュメントはこちら。
上記の見ればわかりますが、Materializationsには、もう一つincremental
というのがあります。ただ、こちらは少々複雑なため、別記事にして、ご紹介したいと思います。