チュートリアル: dbt モデルをローカルで作成、実行、テストする

[アーティクル]
08/02/2024

このチュートリアルでは、dbt モデルをローカルで作成、実行、テストする方法について説明します。 dbt プロジェクトを Azure Databricks ジョブタスクとして実行することもできます。詳細については、「Azure Databricks ジョブでの dbt 変換の使用」を参照してください。

始める前に

このチュートリアルに従うには、まず Azure Databricks ワークスペースを dbt Core に接続する必要があります。詳細については、「dbt Core」を参照してください。

手順 1: モデルを作成して実行する

この手順では、任意のテキストエディターを使用して "モデル" を作成します。これは、同じデータベース内の既存のデータに基づいて、データベース内に新しいビュー (既定値) または新しいテーブルを作成する select ステートメントです。この手順では、サンプルデータセットからサンプルの diamonds テーブルに基づいてモデルを作成します。

このテーブルを作成するには、次のコードを使用します。

DROP TABLE IF EXISTS diamonds;

CREATE TABLE diamonds USING CSV OPTIONS (path "/databricks-datasets/Rdatasets/data-001/csv/ggplot2/diamonds.csv", header "true")

プロジェクトの models ディレクトリで、次の SQL ステートメントを使用して diamonds_four_cs.sql という名前のファイルを作成します。このステートメントでは、diamonds テーブルから各ダイヤモンドのカラット、カット、色、透明度の詳細のみを選択します。 config ブロックは、このステートメントに基づいてデータベースにテーブルを作成することを dbt に指示します。
```
{{ config(
  materialized='table',
  file_format='delta'
) }}
```
```
select carat, cut, color, clarity
from diamonds
```
ヒント

Delta ファイル形式の使用や merge 増分戦略などのその他の config オプションについては、dbt ドキュメントの「Databricks 構成」を参照してください。
プロジェクトの models ディレクトリで、次の SQL ステートメントを使用して diamonds_list_colors.sql という名前の 2 番目のファイルを作成します。このステートメントは、diamonds_four_cs テーブル内の colors 列から一意の値を選択し、結果をアルファベット順に並べ替えます。このモデルには config ブロックはないので、このステートメントに基づいてデータベースにビューを作成するように dbt に指示します。
```
select distinct color
from {{ ref('diamonds_four_cs') }}
sort by color asc
```
プロジェクトの models ディレクトリで、次の SQL ステートメントを使用して diamonds_prices.sql という名前の 3 番目のファイルを作成します。このステートメントでは、ダイヤモンドの色ごとに平均価格を求め、その結果を平均価格の高い方から順に並べます。このモデルでは、このステートメントに基づいてデータベースにビューを作成するように dbt に指示します。
```
select color, avg(price) as price
from diamonds
group by color
order by price desc
```

仮想環境をアクティブ化した状態で、前の 3 つのファイルへのパスを指定した dbt run コマンドを実行します。 default データベース (profiles.yml ファイルで指定されている) に、dbt によって diamonds_four_cs という名前の 1 つのテーブルと、diamonds_list_colors および diamonds_prices という名前の 2 つのビューが作成されます。 dbt では、これらのビューとテーブルの名前を、関連する .sql ファイル名から取得します。

dbt run --model models/diamonds_four_cs.sql models/diamonds_list_colors.sql models/diamonds_prices.sql

...
... | 1 of 3 START table model default.diamonds_four_cs.................... [RUN]
... | 1 of 3 OK created table model default.diamonds_four_cs............... [OK ...]
... | 2 of 3 START view model default.diamonds_list_colors................. [RUN]
... | 2 of 3 OK created view model default.diamonds_list_colors............ [OK ...]
... | 3 of 3 START view model default.diamonds_prices...................... [RUN]
... | 3 of 3 OK created view model default.diamonds_prices................. [OK ...]
... |
... | Finished running 1 table model, 2 view models ...

Completed successfully

Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3

次の SQL コードを実行し、新しいビューに関する情報を一覧表示して、テーブルとビューからすべての行を選択します。

クラスターに接続している場合は、クラスターに接続されたノートブックからこの SQL コードを実行できます。このとき、SQL をノートブックの既定の言語として指定します。 SQL ウェアハウスに接続している場合は、この SQL コードをクエリから実行できます。

SHOW views IN default;

+-----------+----------------------+-------------+
| namespace | viewName             | isTemporary |
+===========+======================+=============+
| default   | diamonds_list_colors | false       |
+-----------+----------------------+-------------+
| default   | diamonds_prices      | false       |
+-----------+----------------------+-------------+

SELECT * FROM diamonds_four_cs;

+-------+---------+-------+---------+
| carat | cut     | color | clarity |
+=======+=========+=======+=========+
| 0.23  | Ideal   | E     | SI2     |
+-------+---------+-------+---------+
| 0.21  | Premium | E     | SI1     |
+-------+---------+-------+---------+
...

SELECT * FROM diamonds_list_colors;

+-------+
| color |
+=======+
| D     |
+-------+
| E     |
+-------+
...

SELECT * FROM diamonds_prices;

+-------+---------+
| color | price   |
+=======+=========+
| J     | 5323.82 |
+-------+---------+
| I     | 5091.87 |
+-------+---------+
...

手順 2: より複雑なモデルを作成して実行する

この手順では、関連するデータテーブルのセットに対して、より複雑なモデルを作成します。これらのデータテーブルには、3 チームで 1 シーズン中 6 試合を戦う架空のスポーツリーグに関する情報が含まれます。この手順では、データテーブルを作成し、モデルを作成し、モデルを実行します。

次の SQL コードを実行し、必要なデータテーブルを作成します。

この手順のテーブルとビューでは、この例の一部として見分けるために、先頭に zzz_ が付いています。独自のテーブルとビューについては、このパターンに従う必要はありません。

DROP TABLE IF EXISTS zzz_game_opponents;
DROP TABLE IF EXISTS zzz_game_scores;
DROP TABLE IF EXISTS zzz_games;
DROP TABLE IF EXISTS zzz_teams;

CREATE TABLE zzz_game_opponents (
game_id INT,
home_team_id INT,
visitor_team_id INT
) USING DELTA;

INSERT INTO zzz_game_opponents VALUES (1, 1, 2);
INSERT INTO zzz_game_opponents VALUES (2, 1, 3);
INSERT INTO zzz_game_opponents VALUES (3, 2, 1);
INSERT INTO zzz_game_opponents VALUES (4, 2, 3);
INSERT INTO zzz_game_opponents VALUES (5, 3, 1);
INSERT INTO zzz_game_opponents VALUES (6, 3, 2);

-- Result:
-- +---------+--------------+-----------------+
-- | game_id | home_team_id | visitor_team_id |
-- +=========+==============+=================+
-- | 1       | 1            | 2               |
-- +---------+--------------+-----------------+
-- | 2       | 1            | 3               |
-- +---------+--------------+-----------------+
-- | 3       | 2            | 1               |
-- +---------+--------------+-----------------+
-- | 4       | 2            | 3               |
-- +---------+--------------+-----------------+
-- | 5       | 3            | 1               |
-- +---------+--------------+-----------------+
-- | 6       | 3            | 2               |
-- +---------+--------------+-----------------+

CREATE TABLE zzz_game_scores (
game_id INT,
home_team_score INT,
visitor_team_score INT
) USING DELTA;

INSERT INTO zzz_game_scores VALUES (1, 4, 2);
INSERT INTO zzz_game_scores VALUES (2, 0, 1);
INSERT INTO zzz_game_scores VALUES (3, 1, 2);
INSERT INTO zzz_game_scores VALUES (4, 3, 2);
INSERT INTO zzz_game_scores VALUES (5, 3, 0);
INSERT INTO zzz_game_scores VALUES (6, 3, 1);

-- Result:
-- +---------+-----------------+--------------------+
-- | game_id | home_team_score | visitor_team_score |
-- +=========+=================+====================+
-- | 1       | 4               | 2                  |
-- +---------+-----------------+--------------------+
-- | 2       | 0               | 1                  |
-- +---------+-----------------+--------------------+
-- | 3       | 1               | 2                  |
-- +---------+-----------------+--------------------+
-- | 4       | 3               | 2                  |
-- +---------+-----------------+--------------------+
-- | 5       | 3               | 0                  |
-- +---------+-----------------+--------------------+
-- | 6       | 3               | 1                  |
-- +---------+-----------------+--------------------+

CREATE TABLE zzz_games (
game_id INT,
game_date DATE
) USING DELTA;

INSERT INTO zzz_games VALUES (1, '2020-12-12');
INSERT INTO zzz_games VALUES (2, '2021-01-09');
INSERT INTO zzz_games VALUES (3, '2020-12-19');
INSERT INTO zzz_games VALUES (4, '2021-01-16');
INSERT INTO zzz_games VALUES (5, '2021-01-23');
INSERT INTO zzz_games VALUES (6, '2021-02-06');

-- Result:
-- +---------+------------+
-- | game_id | game_date  |
-- +=========+============+
-- | 1       | 2020-12-12 |
-- +---------+------------+
-- | 2       | 2021-01-09 |
-- +---------+------------+
-- | 3       | 2020-12-19 |
-- +---------+------------+
-- | 4       | 2021-01-16 |
-- +---------+------------+
-- | 5       | 2021-01-23 |
-- +---------+------------+
-- | 6       | 2021-02-06 |
-- +---------+------------+

CREATE TABLE zzz_teams (
team_id INT,
team_city VARCHAR(15)
) USING DELTA;

INSERT INTO zzz_teams VALUES (1, "San Francisco");
INSERT INTO zzz_teams VALUES (2, "Seattle");
INSERT INTO zzz_teams VALUES (3, "Amsterdam");

-- Result:
-- +---------+---------------+
-- | team_id | team_city     |
-- +=========+===============+
-- | 1       | San Francisco |
-- +---------+---------------+
-- | 2       | Seattle       |
-- +---------+---------------+
-- | 3       | Amsterdam     |
-- +---------+---------------+

プロジェクトの models ディレクトリで、次の SQL ステートメントを使用して zzz_game_details.sql という名前のファイルを作成します。このステートメントでは、チーム名やスコアなど、各試合の詳細を示すテーブルを作成します。 config ブロックは、このステートメントに基づいてデータベースにテーブルを作成することを dbt に指示します。

-- Create a table that provides full details for each game, including
-- the game ID, the home and visiting teams' city names and scores,
-- the game winner's city name, and the game date.

{{ config(
  materialized='table',
  file_format='delta'
) }}

-- Step 4 of 4: Replace the visitor team IDs with their city names.
select
  game_id,
  home,
  t.team_city as visitor,
  home_score,
  visitor_score,
  -- Step 3 of 4: Display the city name for each game's winner.
  case
    when
      home_score > visitor_score
        then
          home
    when
      visitor_score > home_score
        then
          t.team_city
  end as winner,
  game_date as date
from (
  -- Step 2 of 4: Replace the home team IDs with their actual city names.
  select
    game_id,
    t.team_city as home,
    home_score,
    visitor_team_id,
    visitor_score,
    game_date
  from (
    -- Step 1 of 4: Combine data from various tables (for example, game and team IDs, scores, dates).
    select
      g.game_id,
      go.home_team_id,
      gs.home_team_score as home_score,
      go.visitor_team_id,
      gs.visitor_team_score as visitor_score,
      g.game_date
    from
      zzz_games as g,
      zzz_game_opponents as go,
      zzz_game_scores as gs
    where
      g.game_id = go.game_id and
      g.game_id = gs.game_id
  ) as all_ids,
    zzz_teams as t
  where
    all_ids.home_team_id = t.team_id
) as visitor_ids,
  zzz_teams as t
where
  visitor_ids.visitor_team_id = t.team_id
order by game_date desc

プロジェクトの models ディレクトリで、次の SQL ステートメントを使用して zzz_win_loss_records.sql という名前のファイルを作成します。このステートメントは、今シーズンのチームの勝ち負けレコードを一覧表示するビューを作成します。

-- Create a view that summarizes the season's win and loss records by team.

-- Step 2 of 2: Calculate the number of wins and losses for each team.
select
  winner as team,
  count(winner) as wins,
  -- Each team played in 4 games.
  (4 - count(winner)) as losses
from (
  -- Step 1 of 2: Determine the winner and loser for each game.
  select
    game_id,
    winner,
    case
      when
        home = winner
          then
            visitor
      else
        home
    end as loser
  from {{ ref('zzz_game_details') }}
)
group by winner
order by wins desc

仮想環境をアクティブ化した状態で、前の 2 つのファイルへのパスを指定した dbt run コマンドを実行します。 default データベース (profiles.yml ファイルで指定されている) に、dbt によって zzz_game_details という名前の 1 つのテーブルと、zzz_win_loss_records という名前の 1 つのビューが作成されます。 dbt では、これらのビューとテーブルの名前を、関連する .sql ファイル名から取得します。

dbt run --model models/zzz_game_details.sql models/zzz_win_loss_records.sql

...
... | 1 of 2 START table model default.zzz_game_details.................... [RUN]
... | 1 of 2 OK created table model default.zzz_game_details............... [OK ...]
... | 2 of 2 START view model default.zzz_win_loss_records................. [RUN]
... | 2 of 2 OK created view model default.zzz_win_loss_records............ [OK ...]
... |
... | Finished running 1 table model, 1 view model ...

Completed successfully

Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

次の SQL コードを実行し、新しいビューに関する情報を一覧表示して、テーブルとビューからすべての行を選択します。

SHOW VIEWS FROM default LIKE 'zzz_win_loss_records';

+-----------+----------------------+-------------+
| namespace | viewName             | isTemporary |
+===========+======================+=============+
| default   | zzz_win_loss_records | false       |
+-----------+----------------------+-------------+

SELECT * FROM zzz_game_details;

+---------+---------------+---------------+------------+---------------+---------------+------------+
| game_id | home          | visitor       | home_score | visitor_score | winner        | date       |
+=========+===============+===============+============+===============+===============+============+
| 1       | San Francisco | Seattle       | 4          | 2             | San Francisco | 2020-12-12 |
+---------+---------------+---------------+------------+---------------+---------------+------------+
| 2       | San Francisco | Amsterdam     | 0          | 1             | Amsterdam     | 2021-01-09 |
+---------+---------------+---------------+------------+---------------+---------------+------------+
| 3       | Seattle       | San Francisco | 1          | 2             | San Francisco | 2020-12-19 |
+---------+---------------+---------------+------------+---------------+---------------+------------+
| 4       | Seattle       | Amsterdam     | 3          | 2             | Seattle       | 2021-01-16 |
+---------+---------------+---------------+------------+---------------+---------------+------------+
| 5       | Amsterdam     | San Francisco | 3          | 0             | Amsterdam     | 2021-01-23 |
+---------+---------------+---------------+------------+---------------+---------------+------------+
| 6       | Amsterdam     | Seattle       | 3          | 1             | Amsterdam     | 2021-02-06 |
+---------+---------------+---------------+------------+---------------+---------------+------------+

SELECT * FROM zzz_win_loss_records;

+---------------+------+--------+
| team          | wins | losses |
+===============+======+========+
| Amsterdam     | 3    | 1      |
+---------------+------+--------+
| San Francisco | 2    | 2      |
+---------------+------+--------+
| Seattle       | 1    | 3      |
+---------------+------+--------+

手順 3: テストを作成して実行する

この手順では、モデルに関するアサーションである "テスト" を作成します。これらのテストを実行すると、プロジェクト内の各テストが成功したか失敗したかが dbt によって通知されます。

テストは 2 種類あります。 YAML で適用される "スキーマテスト" は、アサーションに合格しないレコードの数を返します。この数値が 0 の場合、すべてのレコードが合格するため、テストは合格します。 "データテスト" は、合格するには 0 件のレコードを返す必要がある特定のクエリです。

プロジェクトの models ディレクトリで、次の内容を持つ schema.yml という名前のファイルを作成します。このファイルには、指定した列が一意の値を含むか、null 値ではないか、指定された値のみを含むか、または組み合わせを含むかを判断する、スキーマテストが含まれています。

version: 2

models:
  - name: zzz_game_details
    columns:
      - name: game_id
        tests:
          - unique
          - not_null
      - name: home
        tests:
          - not_null
          - accepted_values:
              values: ['Amsterdam', 'San Francisco', 'Seattle']
      - name: visitor
        tests:
          - not_null
          - accepted_values:
              values: ['Amsterdam', 'San Francisco', 'Seattle']
      - name: home_score
        tests:
          - not_null
      - name: visitor_score
        tests:
          - not_null
      - name: winner
        tests:
          - not_null
          - accepted_values:
              values: ['Amsterdam', 'San Francisco', 'Seattle']
      - name: date
        tests:
          - not_null
  - name: zzz_win_loss_records
    columns:
      - name: team
        tests:
          - unique
          - not_null
          - relationships:
              to: ref('zzz_game_details')
              field: home
      - name: wins
        tests:
          - not_null
      - name: losses
        tests:
          - not_null

プロジェクトの tests ディレクトリで、次の SQL ステートメントを使用して zzz_game_details_check_dates.sql という名前のファイルを作成します。このファイルには、レギュラーシーズン以外に試合が行われたかどうかを調べるためのデータテストが含まれています。
```
-- This season's games happened between 2020-12-12 and 2021-02-06.
-- For this test to pass, this query must return no results.

select date
from {{ ref('zzz_game_details') }}
where date < '2020-12-12'
or date > '2021-02-06'
```
プロジェクトの tests ディレクトリで、次の SQL ステートメントを使用して zzz_game_details_check_scores.sql という名前のファイルを作成します。このファイルには、負のスコアの有無や、引き分けで終わった試合を調べるためのデータテストが含まれます。
```
-- This sport allows no negative scores or tie games.
-- For this test to pass, this query must return no results.

select home_score, visitor_score
from {{ ref('zzz_game_details') }}
where home_score < 0
or visitor_score < 0
or home_score = visitor_score
```
プロジェクトの tests ディレクトリで、次の SQL ステートメントを使用して zzz_win_loss_records_check_records.sql という名前のファイルを作成します。このファイルには、勝敗記録がマイナスのチーム、試合数よりも勝敗記録が多いチーム、または認められている試合数以上に試合を行ったチームを調べるためのデータテストが含まれています。
```
-- Each team participated in 4 games this season.
-- For this test to pass, this query must return no results.

select wins, losses
from {{ ref('zzz_win_loss_records') }}
where wins < 0 or wins > 4
or losses < 0 or losses > 4
or (wins + losses) > 4
```

仮想環境がアクティブ化されたら、dbt test コマンドを実行します。

dbt test --models zzz_game_details zzz_win_loss_records

...
... | 1 of 19 START test accepted_values_zzz_game_details_home__Amsterdam__San_Francisco__Seattle [RUN]
... | 1 of 19 PASS accepted_values_zzz_game_details_home__Amsterdam__San_Francisco__Seattle [PASS ...]
...
... |
... | Finished running 19 tests ...

Completed successfully

Done. PASS=19 WARN=0 ERROR=0 SKIP=0 TOTAL=19

手順 4: クリーンアップする

この例で作成したテーブルとビューは、次の SQL コードを実行すると削除できます。

DROP TABLE zzz_game_opponents;
DROP TABLE zzz_game_scores;
DROP TABLE zzz_games;
DROP TABLE zzz_teams;
DROP TABLE zzz_game_details;
DROP VIEW zzz_win_loss_records;

DROP TABLE diamonds;
DROP TABLE diamonds_four_cs;
DROP VIEW diamonds_list_colors;
DROP VIEW diamonds_prices;

トラブルシューティング

Azure Databricks で dbt Core を使用する場合の一般的な問題とその解決方法については、dbt Labs の Web サイトの「ヘルプの表示」を参照してください。

次のステップ

dbt Core プロジェクトを Azure Databricks ジョブタスクとして実行します。 Azure Databricks ジョブでの dbt 変換の使用に関するぺージを参照してください。

その他のリソース

dbt Labs の Web サイトで次のリソースを調べてください。

次の方法で共有

チュートリアル: dbt モデルをローカルで作成、実行、テストする

始める前に

手順 1: モデルを作成して実行する

手順 2: より複雑なモデルを作成して実行する

手順 3: テストを作成して実行する

手順 4: クリーンアップする

トラブルシューティング

次のステップ

その他のリソース

フィードバック

その他のリソース

次の方法で共有

チュートリアル: dbt モデルをローカルで作成、実行、テストする

始める前に

手順 1: モデルを作成して実行する

手順 2: より複雑なモデルを作成して実行する

手順 3: テストを作成して実行する

手順 4: クリーン アップする

トラブルシューティング

次のステップ

その他のリソース

フィードバック

その他のリソース

手順 4: クリーンアップする