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

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

始める前に

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

ステップ 1: モデル を作成して実行する

このステップでは、任意のテキストエディタを使用して、同じデータベース内の既存のデータに基づいて、データベース内の新しいビュー (デフォルト) または新しいテーブルを作成するselectステートメントであるモデルを作成します。この手順では、「diamonds チュートリアル: ノートブックを使用したデータのクエリー 」の「テーブルの作成」セクションで説明されているように、サンプル データセットのサンプル テーブル に基づいてモデルを作成します。この手順では、このテーブルがワークスペースの default データベースに既に作成されていることを前提としています。

  1. プロジェクトの 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 構成 」を参照してください。

  2. プロジェクトの 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
    
  3. プロジェクトの models ディレクトリに、次の SQL ステートメントを使用して diamonds_prices.sql という名前の 3 番目のファイルを作成します。 このステートメントは、ダイヤモンドの価格を色で平均し、結果を平均価格で最高から最低に並べ替えます。 このモデルは、このステートメントに基づいてデータベースにビューを作成するように dbt に指示します。

    select color, avg(price) as price
    from diamonds
    group by color
    order by price desc
    
  4. 仮想環境をアクティブにした状態で、前の 3 つのファイルへのパスを指定して dbt run コマンドを実行します。 default データベース (profiles.yml ファイルで指定) に、dbt は diamonds_four_cs という名前のテーブルを 1 つ作成し、 diamonds_list_colorsdiamonds_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
    
  5. 次の 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: より複雑なモデル を作成して実行する

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

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

    クラスターに接続している場合は、クラスターに接続されている ノートブック からこの SQL コードを実行し、ノートブックのデフォルト言語として SQL を指定できます。 SQLウェアハウスに接続している場合は、この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     |
    -- +---------+---------------+
    
  2. プロジェクトの 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
    
  3. プロジェクトの 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
    
  4. 仮想環境をアクティブにした状態で、前の 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
    
  5. 次の SQL コードを実行して、新しいビューに関する情報を一覧表示し、テーブルとビューからすべての行を選択します。

    クラスターに接続している場合は、クラスターに接続されている ノートブック からこの SQL コードを実行し、ノートブックのデフォルト言語として SQL を指定できます。 SQLウェアハウスに接続している場合は、この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 レコードを返す必要がある特定のクエリです。

  1. プロジェクトの 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
    
  2. プロジェクトの 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'
    
  3. プロジェクトの 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
    
  4. プロジェクトの 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
    
  5. 仮想環境をアクティブにして、 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 コードを実行します。

クラスターに接続している場合は、クラスターに接続されている ノートブック からこの SQL コードを実行し、ノートブックのデフォルト言語として SQL を指定できます。 SQLウェアハウスに接続している場合は、この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;

トラブルシューティング

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

次のステップ

dbt コア プロジェクトを Databricks ジョブ タスクとして実行します。 「 Databricks ジョブでの dbt 変換の使用」を参照してください。