🙆‍♀️

【dbt Docs】Building a dbt Project - Models - dbt Models

2022/03/10に公開

対象ページ

https://docs.getdbt.com/docs/building-a-dbt-project/building-models

dbt Models

関連するリファレンス

  • Model Configurations

    • Available configrations
      • Model-specific configurations
        dbt_project.yml
        models:
        <resource-path>:
          +materialized: <materialization_name>
          +sql_header: <string>
        
        models/properties.yml
        version: 2
        
        models:
          - name: [<model-name>]
            config:
              materialized: <materialization_name>
              sql_header: <string>
        
        models/<model_name>.sql Code Block
        {{ config(
            materialized="<materialization_name>",
            sql_header="<string>"
        ) }}
        
      • General configurations
      • Warehouse-spesific configrations
        • BigQuery configurations
        • Redshift configurations
        • Snowflake configurations
        • Spark configurations
    • Configuring models
      • modelファイル(.sql)の中にJinja macroで、config() を用いる方法
      • resouce propertyの .ymlファイルの中で config を用いる方法
      • dbt_project.ymlに記載する方法 ※全体への適用
    • Example
       name: dbt_labs
       models:
         dbt_labs:
           events:
             +enabled: true
             +materialized: view
    
             base:
               +materialized: ephemeral
           ...
    

    もし1つのモデルだけに適用したい場合の記載方法はこちら

    models/events/base/base_events.sql
     {{
       config(
           materialized = "table",
           sort = 'event_time',
           dist = 'event_id'
         )
       }}
       select * from ...
    
    models/events/base/properties.yml
    version: 2
    
    models:
     - name: base_events
       config:
         materialized: table
         sort: event_time
         dist: event_id
    
  • Model properties

    models/<filename>.yml
    version: 2
    
      models:
        - name: <model name>
          description: <markdown_string>
          docs:
            show: true | false
          config:
            <model_config>: <config_value>
          tests:
            - <test>
            - ... # declare additional tests
          columns:
            - name: <column_name> # required
      	description: <markdown_string>
      	meta: {<dictionary>}
      	quote: true | false
      	tests:
      	  - <test>
      	  - ... # declare additional tests
      	tags: [<string>]
      
            - name: ... # declare properties of additional columns
    
    
  • run command

    • --full-refresh : incremental modelをtable modelで作り直す
    $ dbt run --full-refresh
    

    フラグがしている際に、FULL_REFRESH を行う・・・といったことも可能

    models/example.sql
    select * from all_events
    
    -- if the table already exists and `--full-refresh` is
    -- not set, then only add new records. otherwise, select
    -- all records.
    {% if is_incremental() %}
       where collector_tstamp > (
         select coalesce(max(max_tstamp), '0001-01-01') from {{ this }}
       )
    {% endif %}
    
    • global configs
    • argument(s)
      • --select
      • --exclude
      • --selector
      • --defer
  • ref functon

    • dbtで最も重要な関数
    • これなしでは複雑なモデルの構築ができない
    • DAG(モデルの依存関係や構築順を決定する)を構築するのにとても重要
         ```sql:model_a.sql
      select *
      from public.raw_data
      
    ```sql:model_b.sql
    select *
    from {{ref('model_a')}}
    
    two_argment.sql
    select * from {{ ref('package_name', 'model_name') }}
    

Getting started

モデルは select ステートメントで。ファイルは、.sqlファイル(通常は、modelsディレクトリ内)で定義される。

  • .sqlファイルには、1モデル=selectステートメント
  • ファイル名はモデル名として使用
  • モデルは、ディレクトリ内のサブディレクトリにネストできる。(階層構造にできる)

dbt runコマンドを実行すると、create veiw ascreate table as が実行され、データウエアハウスにモデルが構築される。

models/customer.sql
with 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 jaffle_shop.orders
    group by 1
)

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 jaffle_shop.customers

left join customer_orders using (customer_id)

これを dbt runで実行すると、create view dbt_alice.customers as とラップされて実行される。

create view dbt_alice.customers as (
    with 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 jaffle_shop.orders

        group by 1
    )

    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 jaffle_shop.customers

    left join customer_orders using (customer_id)
)

dbtでは

  • view としてモデルを構築する(デフォルト、変更可能:table, ephemeral, increamental)
  • 定義したターゲットスキーマでモデルを構築する
  • ファイル名をデータベース内のビューまたはテーブル名として使う

FAQs

  • dbtが実行されているSQLを確認するにはどうすればよいですか?
    • dbt Cloud:
      • 実行出力内で、モデル名をクリックし、[詳細]を選択します
    • dbt CLI:
      • target/compiledディレクトリにSelect句のSQL
      • target/run/ ディレクトリに Create区のSQL
      • logs/dbt.logに詳細ログがある
  • dbtを実行する前にターゲットスキーマを作成する必要がありますか?

    必要なし。もし存在しない場合は新規に作成されます。

  • dbtを再実行した場合、モデルが再構築されるときにダウンタイムは発生しますか?

    ダウンタイムは発生しない。dbtがバックグラウンドで生成するSQLにより

  • クエリのSQLが悪い場合はどうなりますか?

    Databaseから返すエラーを返し、モデルの下流にあるモデルもスキップされる

  • SQLの方言(? DWHそれぞれに特有の書き方)によった記述をしないといけない?

    対応するDWHごとにSQLを生成しているので気にしなくて良い

    models/test_model.sql
    select 1 as my_column
    

    は、Redshift、BigQuery、Snowflakeでは下記のように

    redshift
    -- you can't create or replace on redshift, so use a transaction to do this in an atomic way
    begin;
    create table "dbt_alice"."test_model__dbt_tmp" as (
        select 1 as my_column
    );
    alter table "dbt_alice"."test_model" rename to "test_model__dbt_backup";
    alter table "dbt_alice"."test_model__dbt_tmp" rename to "test_model"
    commit;
    begin;
    drop table if exists "dbt_alice"."test_model__dbt_backup" cascade;
    commit;
    
    bigquery
    create or replace table `dbt-dev-87681`.`dbt_alice`.`test_model` as (
      select 1 as my_column
    );
    
    snowflake
    create schema if not exists analytics.dbt_alice;
    
    create or replace table analytics.dbt_alice.test_model as (
        select 1 as my_column
    );
    

Configuring models

モデルの設定は、

  • dbt_project.yml
  • モデルのファイル(.sql)に  configブロックで記載する

設定例としては

  • モデルのマテリアライゼーションを変更する( view, table, ephemeral, increamental )
  • モデルを個別のスキーマに構築する(=生成先のスキーマを指定)
  • モデルに tag を適用する
    • tag : https://docs.getdbt.com/reference/resource-configs/tags
      • dbt run --select tag:my_tag
      • dbt seed --select tag:my_tag
      • dbt snapshot --select tag:my_tag
      dbt_project.yml
      models:
        jaffle_shop:
         +tags: "contains_pii"
         staging:
           +tags:
             - "hourly"
         marts:
           +tags:
             - "hourly"
             - "published"
         metrics:
           +tags:
             - "daily"
             - "published"
      

モデル構成の例は

dbt_project.yml
name: jaffle_shop
config-version: 2
...

models:
  jaffle_shop: # this matches the `name:`` config
    +materialized: view # this applies to all models in the current project
    marts:
      +materialized: table # this applies to all models in the `marts/` directory
      marketing:
        +schema: marketing # this applies to all models in the `marts/marketing/`` directory
models/customer.sql
{{ config(
    materialized="view",
    schema="marketing"
) }}

with customer_orders as ...

FAQs

  • dbtで利用できるマテリアライゼーションは何ですか?

    view, table, incremental, ephemeral
    必要に応じて独自のカスタムマテリアライゼーションを作ることもできるが、高度機能

  • 他にどのようなモデル構成がありますか?
    • 簡単な分類とグラフ選択をサポートするタグ
    • モデルを複数のスキーマに分割するためのカスタムスキーマ
    • ビュー/テーブル名がファイル名と異なる必要がある場合のエイリアス
    • モデルの開始時または終了時に実行されるSQLのスニペット(フックと呼ばれる)
    • パフォーマンスのためのウェアハウス固有の構成(例:Redshift、BigQuerysortのキー

Building dependencies between models

モデル間での依存関係

ref関数を使うことで、モデル間の依存関係を構築することができる。
また、ref関数の置き換えは、環境によってかえることもできる( devとproductionで違うDB、スキーマとか)

models/customer.sql
with customers as (

    select * from {{ ref('stg_customers') }}

),

orders as (

    select * from {{ ref('stg_orders') }}

),

...
```sql:開発環境でのSQL
create view dbt_alice.customers as (
  with customers as (

      select * from dbt_alice.stg_customers

  ),

  orders as (

      select * from dbt_alice.stg_orders

  ),

  ...
)

...
本番でのSQL
create view analytics.customers as (
  with customers as (

      select * from analytics.stg_customers

  ),

  orders as (

      select * from analytics.stg_orders

  ),

  ...
)

...

Testing and documenting models

https://docs.getdbt.com/docs/building-a-dbt-project/tests
https://docs.getdbt.com/docs/building-a-dbt-project/documentation
を参照すること

Additional FAQs

  • dbtモデルの例はありますか?
  • プロジェクトの `models` ディレクトリ以外のディレクトリにモデルを保存できますか?

    model-paths: ["transformations"] で変更可能

  • ターゲットスキーマ以外のスキーマでモデルを構築できますか?

    2つの方法がある。

    • dbt_project.ymlで指定する方法
      dbt_project.yml
      name: jaffle_shop
      ...
      models:
        jaffle_shop:
          marketing:
        schema: marketing # seeds in the `models/mapping/ subdirectory will use the marketing schema
      
    • .sqlconfigで記載する方法
      models/customers.sql
      {{
        config(
          schema='core'
        )
      }}
      
  • モデル名は一意である必要がありますか?

    一意である必要がある。(なので、Prefixをつけたりするのが推奨されているのだろう)
    モデル間の依存関係を構築するには、関数を使用する必要がありrefます。このref関数は、モデル名(つまりファイル名)という1つの引数のみを取ります。その結果、これらのモデル名は、別個のフォルダーにある場合でも、一意である必要があります。
    ユーザーがウェアハウス内で2つのモデルに同じ名前を付けて、別々のスキーマ(たとえばstripe.usersapp.users)に分割したいために発生します。これを実現するためには、カスタムエイリアス、カスタムスキーマを使う必要がある。

  • データウェアハウスから削除されたモデルを削除するにはどうすればよいですか?

    dbtプロジェクトからモデルを削除しても、dbtはスキーマからリレーションを自動的に削除しません。これは、 dbtが作成するスキーマに余分なオブジェクトが含まれる可能性があり、他のユーザーを混乱させる可能性があることを意味します。

    (これは、モデルをビューまたはテーブルからエフェメラルに切り替えるときにも発生する可能性があります)

    dbtプロジェクトからモデルを削除するときは、関連するリレーションをスキーマから手動で削除する必要があります。

  • より多くのモデルを作成するとき、プロジェクトをどのように整理しておく必要がありますか?モデルには何という名前を付ける必要がありますか?

    プロジェクトを構築するための最良の方法はありません!すべての組織はユニークです。

  • モデルが `select`ステートメントのみである場合、レコードを挿入するにはどうすればよいですか?

    ETL(Extract Transform Load)パラダイムから来ている人にとっては、変換insertupdateステートメントとして記述したいという要望がよくあります。それに比べて、dbtはselectクエリをcreate table asステートメントでラップするため、逆効果に感じる可能性があります。

    • パフォーマンス上の理由で処理量を減らす場合には、マテリアライザーション=incremental を使う
    • ソースデータが絶えず変化するためにステートメントを使用する場合insert(たとえば、「タイプ2の緩やかに変化するディメンション」を作成する場合)、ソースデータのスナップショットを作成し、スナップショットの上にモデルを構築することを検討してください。
  • トランスフォーメーションでDMLを記述できないのはなぜですか?

    selectだけのほうが色んな人が触ることができる。良いDML(CreateやDrop)を書くのは難しい

    • テーブルがすでに存在する場合はどうなりますか?または、このテーブルはすでにビューとして存在しますが、今はテーブルにしたいですか?
    • スキーマがすでに存在する場合はどうなりますか?または、スキーマがすでに存在するかどうかを確認する必要がありますか?
    • モデルをアトミックに置き換えるにはどうすればよいですか(誰かがテーブルをクエリするためのダウンタイムがないように)
    • 開発環境でこれらの変換を実行できるようにスキーマをパラメーター化する場合はどうなりますか?
    • これらのステートメントを実行するには、どのような順序が必要ですか?私が実行した場合、cascadeそれは他のものを壊しますか?

    あとは、ベンダーロックイン=SQLの方言 もあるのて、dbtでこの違いを吸収するのが良き

  • 列タイプを指定するにはどうすればよいですか?

    モデル内の正しいタイプに列をキャストするだけです。

    select
        id,
        created::timestamp as created
    from some_other_table
    

    追加の列オプションを定義するためには

    • 列に一意性とnull以外の制約を適用するのではなく、dbtのテスト機能を使用して、モデルに関するアサーションが真であることを確認します。
    • 列のデフォルト値を作成するのではなく、SQLを使用してデフォルトを表現します(例coalesce(updated_at, current_timestamp()) as updated_at)
    • 列を変更する必要があるエッジケース(Redshiftでの列レベルのエンコーディングなど)では、ポストフックを介してこれを実装することを検討してください。

Discussion

ログインするとコメントできます