dbt Cloud に接続する

  • [アーティクル]

dbt (データ ビルド ツール) は、データ アナリストやデータ エンジニアが select ステートメントを記述するだけでデータを変換できる開発環境です。 dbt では、これらの select ステートメントをテーブルとビューに変換する処理を行います。 dbt では、コードを生の SQL にコンパイルし、そのコードを、Azure Databricks で指定されたデータベースに対して実行します。 dbt では、共同コーディング パターンと、バージョン管理、ドキュメント、モジュール性などのベスト プラクティスがサポートされています。

dbt は、データの抽出または読み込みには使用できません。 dbt は、"読み込み後の変換" アーキテクチャを使用して、変換ステップのみに焦点を合わせています。 dbt では、データベースにデータのコピーが既に存在すると想定しています。

この記事では、dbt Cloud について説明します。 dbt Cloud には、ジョブのスケジュール設定、CI/CD、ドキュメントの提供、監視とアラート、統合開発環境 (IDE) に対する設定不要のサポートが用意されています。

dbt Core と呼ばれる dbt のローカル バージョンも使用できます。 dbt Core を使用すると、ローカル開発マシン上の任意のテキスト エディターまたは IDE で dbt のコードを記述し、コマンド ラインから dbt を実行できます。 dbt Core には、dbt コマンド ライン インターフェイス (CLI) が含まれています。 dbt CLI は無料で使用できるオープンソースです。 詳細については、「dbt Core」を参照してください。

dbt Cloud と dbt Core では、ホストされた git リポジトリ (GitHub、GitLab、BitBucket など) を使用できるため、dbt Cloud を使用して dbt プロジェクトを作成し、それを dbt Cloud ユーザーと dbt Core ユーザーに提供することができます。 詳細については、dbt の Web サイトで「Creating a dbt project」 (dbt プロジェクトの作成) と「Using an existing project」 (既存のプロジェクトの作成) を参照してください。

dbt の概要については、次の YouTube ビデオ (26 分) をご覧ください。

Partner Connect を使用して dbt Cloud に接続する

このセクションでは、Partner Connect を使用して Databricks SQL ウェアハウスを dbt Cloud に接続し、dbt Cloud にデータへの読み取りアクセス権を付与する方法について説明します。

標準接続と dbt Cloud の違い

Partner Connect を使用して dbt Cloud に接続するには、「Partner Connect を使用してデータ準備パートナーに接続する」の手順に従います。 dbt Cloud 接続は、標準のデータ準備と変換の接続とは、次のように異なります。

  • サービス プリンシパルと個人用アクセス トークンに加え、Partner Connect によって、既定で DBT_CLOUD_ENDPOINT という名前の SQL ウェアハウス (以前の SQL エンドポイント) が作成されます。

接続する手順

Partner Connect を使用して dbt Cloud に接続するには、次の操作を行います。

  1. Partner Connect を使用してデータ準備パートナーに接続します。

  2. dbt Cloud に接続すると、dbt Cloud ダッシュボードが表示されます。 dbt Cloud プロジェクトを探索するには、メニューバーの dbt ロゴの横で、最初のドロップダウンから自身の dbt アカウント名を選択し (表示されていない場合)、2 番目のドロップダウン メニューから [Databricks Partner Connect Trial] プロジェクトを選択します (表示されていない場合)。

    ヒント

    自身のプロジェクトの設定を表示するには、"3 本線" ("ハンバーガー") メニューをクリックし、[アカウント設定] > [プロジェクト] の順にクリックし、プロジェクトの名前をクリックします。 接続設定を表示するには、[接続] の横にあるリンクをクリックします。 設定を変更するには、[編集] をクリックします。

    このプロジェクトの Azure Databricks 個人用アクセス トークン情報を表示するには、メニュー バーの "人物" のアイコンをクリックし、[プロファイル] > [資格情報] > [Databricks Partner Connect Trial] の順にクリックして、プロジェクトの名前をクリックします。 変更するには、[Edit] (編集) をクリックします。

dbt Cloud にデータへの読み取りアクセス権を付与する手順

Partner Connect は、既定のカタログに対してのみ、DBT_CLOUD_USER サービス プリンシパルに対して作成専用のアクセス許可を付与します。 Azure Databricks ワークスペースでこれらの手順に従って、DBT_CLOUD_USER サービスプリンシパルに、選択したデータへの読み取りアクセス権を付与します。

警告

ワークスペース内のカタログ、データベース、テーブル間で追加のアクセス権を dbt Cloud に付与するように、これらの手順を調整できます。 ただし、セキュリティのベスト プラクティスとして、Databricks では、DBT_CLOUD_USER サービス プリンシパルで使用する必要のある個々のテーブルへのアクセス権のみを付与し、これらのテーブルへの読み取りアクセス権のみを付与することを強くお勧めしています。

  1. サイド バーで、[カタログ] アイコン [カタログ] をクリックします。

  2. 右上にあるドロップダウン リストで、SQL ウェアハウス (DBT_CLOUD_ENDPOINT) を選択します。

    ウェアハウスを選択する

    1. Catalog Explorer の下で、テーブルのデータベースを含むカタログを選択します。
    2. テーブルを含むデータベースを選択します。
    3. テーブルを選択します。

    ヒント

    お使いのカタログ、データベース、またはテーブルが一覧に表示されていない場合は、[カタログの選択]、[データベースの選択]、または [フィルター テーブル] ボックスそれぞれに名前の一部を入力し、一覧を絞り込みます。

    フィルター テーブル

  3. [アクセス許可] をクリックします。

  4. [許可] をクリックします。

  5. [複数のユーザーまたはグループを追加する種類] には、[DBT_CLOUD_USER] を選択します。 これは、前のセクションで Partner Connect により作成された Azure Databricks サービス プリンシパルです。

    ヒント

    DBT_CLOUD_USER が表示されない場合は、一覧に表示されるまで [複数のユーザーまたはグループを追加する種類] ボックスに DBT_CLOUD_USER と入力し、それを選択します。

  6. SELECT と READ METADATA を選び、読み取りアクセスのみを付与します。

  7. OK をクリックします。

dbt Cloud に読み取りアクセス権を与える追加テーブルごとに、手順 4 から 9 を繰り返します。

dbt Cloud 接続のトラブルシューティング

誰かがこのアカウントの dbt Cloud のプロジェクトを削除し、[dbt] タイルをクリックすると、プロジェクトが見つからないことを示すエラー メッセージが表示されます。 これを解決するには、[接続の削除] をクリックし、この手順の最初から始めて、接続を作成し直します。

dbt Cloud に手動で接続する

このセクションでは、Azure Databricks クラスター、または Azure Databricks ワークスペース内の Databricks SQL ウェアハウスを dbt Cloud に接続します。

重要

Databricks では、SQL ウェアハウスに接続することをお勧めします。 Databricks SQL へのアクセス エンタイトルメントがない場合、または Python モデルを実行する場合は、代わりにクラスターに接続できます。

要件

  • Azure Databricks ワークスペース内のクラスターまたは SQL ウェアハウス。

  • クラスターまたは SQL ウェアハウスの接続の詳細。具体的には、[サーバーのホスト名]、[ポート]、および [HTTP パス] の値です。

  • Azure Databricks 個人用アクセス トークンまたは Microsoft Entra ID (旧称 Azure Active Directory) トークン。 個人用アクセス トークンを作成するには、次の操作を行います。

    1. Azure Databricks ワークスペースの上部バーで、目的の Azure Databricks ユーザー名をクリックし、次にドロップダウンから [設定] を選択します。
    2. [開発者] をクリックします。
    3. [アクセス トークン] の横にある [管理] をクリックします。
    4. [新しいトークンの生成] をクリックします。
    5. (省略可能) 将来このトークンを識別するのに役立つコメントを入力し、トークンの既定の有効期間 90 日を変更します。 有効期間のないトークンを作成するには (推奨されません)、[有効期間 (日)] ボックスを空のままにします。
    6. [Generate](生成) をクリックします。
    7. 表示されたトークンを安全な場所にコピーし、[完了] をクリックします。

    Note

    コピーしたトークンは必ず安全な場所に保存してください。 コピーしたトークンは他人に見せないでください。 コピーしたトークンを失った場合、それとまったく同じトークンは再生成できません。 代わりに、この手順を繰り返して新しいトークンを作成する必要があります。 コピーしたトークンを紛失した場合や、トークンが侵害されていると思われる場合、Databricks では、[アクセス トークン] ページのトークンの横にあるごみ箱 ([取り消し]) アイコンをクリックして、ワークスペースからそのトークンをすぐに削除することを強くお勧めします。

    ワークスペースでトークンを作成することや使用することができない場合は、ワークスペース管理者によってトークンが無効にされているか、トークンを作成または使用する権限が作業者に付与されていない可能性があります。 ワークスペース管理者に連絡するか、以下の情報を参照してください。

    Note

    セキュリティのベスト プラクティスとして、自動化ツール、システム、スクリプト、アプリを使用して認証する場合、Databricks では、ワークスペース ユーザーではなくサービス プリンシパルに属する個人用アクセス トークンを使用することを推奨しています。 サービス プリンシパルのトークンを作成するには、「サービス プリンシパルのトークンを管理する」をご覧ください。

  • Dbt Cloud を Unity Catalog (dbt バージョン 1.1 以降) で管理されるデータに接続するには、次の手順に従います。

    この記事の手順では、最新の dbt バージョンを使用する新しい環境を作成します。 既存の環境での dbt バージョンのアップグレードについては、dbt ドキュメントでCloud での最新バージョンの dbt へのアップグレードに関するページを参照してください。

手順 1: dbt Cloud にサインアップする

dbt Cloud のサインアップ ページを開き、メール アドレス、氏名、会社の情報を入力します。 パスワードを作成し、[Create my account] (アカウントを作成) をクリックします。

手順 2: 新しい dbt プロジェクトを作成する

この手順では、Azure Databricks クラスターまたは SQL ウェアハウス、ソース コードを含むリポジトリ、1 つ以上の環境 (テストまたは運用環境など) を含む dbt プロジェクトを作成します。

  1. dbt Cloud にサインインします。

  2. 設定アイコンをクリックし、[アカウント設定] をクリックします。

  3. [新しいプロジェクト] をクリックします。

  4. [名前] にプロジェクトの一意の名前を入力し、[続行] をクリックします。

  5. [接続の選択] で [Databricks] をクリックし、[次へ] をクリックします。

  6. [Name] (名前) に、この接続の一意の名前を入力します。

  7. [アダプターの選択] で [Databricks (dbt-databricks)] をクリックします。

    Note

    Databricks では、dbt-spark の代わりに Unity Catalog をサポートする dbt-databricks を使用することをお勧めします。 既定では、新しいプロジェクトは dbt-databricks を使用します。 既存のプロジェクトを dbt-databricks に移行するには、dbt ドキュメントの「dbt-spark から dbt-databricks への移行」を参照してください。

  8. [設定] の [サーバー ホスト名] に、要件にあるサーバー ホスト名の値を入力します。

  9. [HTTP パス] には、要件の HTTP パス値を入力します。

  10. ワークスペースが Unity Catalog 対応である場合は、[オプション設定] に使用する dbt Cloud のカタログの名前を入力します。

  11. 開発資格情報のトークンに、要件から個人用アクセス トークンまたは Microsoft Entra ID トークンを入力します。

  12. [スキーマ] には、dbt Cloud でテーブルとビューを作成するスキーマの名前を入力します (例: default)。

  13. [接続テスト]をクリックします。

  14. テストが成功したら、[次へ] をクリックします。

詳細については、dbt の Web サイトの「Connecting to Databricks ODBC」 (Databricks ODBC への接続) を参照してください。

ヒント

このプロジェクトの設定を表示または変更するか、プロジェクトを完全に削除するには、設定アイコンをクリックし、[アカウント設定] > [プロジェクト] をクリックし、プロジェクトの名前をクリックします。 設定を変更するには、[Edit] (編集) をクリックします。 プロジェクトを削除するには、[Edit] (編集) > [Delete Project] (プロジェクトの削除) をクリックします。

このプロジェクトの Azure Databricks 個人用アクセス トークンの値を表示または変更するには、"人" アイコンをクリックし、[Profile] (プロファイル) > [Credentials] (資格情報) をクリックして、プロジェクトの名前をクリックします。 変更するには、[Edit] (編集) をクリックします。

Azure Databricks クラスターまたは Databricks SQL ウェアハウスに接続後、画面上の指示に従って [リポジトリを設定] を行い、[続行] をクリックします。

リポジトリを設定したら、画面の指示に従ってユーザーを招待し、[Complete] (完了) をクリック します。 または、[Skip & Complete] (スキップして完了) をクリックします。

チュートリアル

このセクションでは、dbt Cloud プロジェクトを使用していくつかのサンプル データを操作します。 このセクションでは、プロジェクトが既に作成されており、そのプロジェクトに対して dbt Cloud IDE が開かれていることを前提としています。

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

この手順では、dbt Cloud IDE を使用して "モデル" を作成して実行します。これは、同じデータベース内の既存のデータに基づいて、データベース内に新しいビュー (既定値) または新しいテーブルを作成する 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")

この手順は、このテーブルがワークスペースの default データベース内に既に作成済みであることを前提にしています。

  1. プロジェクトを開いた状態で、UI の上部にある [開発] をクリックします。

  2. [Dbt プロジェクトの初期化] をクリックします。

  3. [コミットと同期] をクリックし、コミット メッセージを入力して、[コミット] をクリックします。

  4. [ブランチの作成] をクリックし、ブランチの名前を入力して、[送信] をクリックします。

  5. 最初のモデルを作成します。[新しいファイルの作成] をクリックします。

  6. テキスト エディターで、次の SQL ステートメントを入力します。 このステートメントでは、diamonds テーブルから各ダイヤモンドのカラット、カット、色、透明度の詳細のみを選択します。 config ブロックは、このステートメントに基づいてデータベースにテーブルを作成することを dbt に指示します。

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

    select carat, cut, color, clarity
from diamonds

    ヒント

    merge 増分戦略などのその他の config オプションについては、dbt ドキュメントの「Databricks 構成」を参照してください。

  7. [名前を付けて保存] をクリックします。

  8. ファイル名に「models/diamonds_four_cs.sql」と入力し、[作成] をクリックします。

  9. 2 番目のモデルを作成します。右上隅にある [新しいファイルの作成] アイコン (「新しいファイルの作成」) をクリックします。

  10. テキスト エディターで、次の SQL ステートメントを入力します。 このステートメントは、diamonds_four_cs テーブル内の colors 列から一意の値を選択し、結果をアルファベット順に並べ替えます。 このモデルには config ブロックはないので、このステートメントに基づいてデータベースにビューを作成するように dbt に指示します。

    select distinct color
from diamonds_four_cs
sort by color asc

  11. [名前を付けて保存] をクリックします。

  12. ファイル名に「models/diamonds_list_colors.sql」と入力し、[作成] をクリックします。

  13. 3 番目のモデルを作成します。右上隅にある [新しいファイルの作成] アイコン (「新しいファイルの作成」) をクリックします。

  14. テキスト エディターで、次の SQL ステートメントを入力します。 このステートメントでは、ダイヤモンドの色ごとに平均価格を求め、その結果を平均価格の高い方から順に並べます。 このモデルでは、このステートメントに基づいてデータベースにビューを作成するように dbt に指示します。

    select color, avg(price) as price
from diamonds
group by color
order by price desc

  15. [名前を付けて保存] をクリックします。

  16. ファイル名に「models/diamonds_prices.sql」と入力し、[作成] をクリックします。

  17. モデルを実行します。コマンド ラインで、前述の 3 つのファイルへのパスを指定して dbt run コマンドを実行します。 default データベースでは、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

  18. 次の 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 試合を戦う架空のスポーツ リーグに関する情報が含まれます。 この手順では、データ テーブルを作成し、モデルを作成し、モデルを実行します。

  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. 最初のモデルを作成します。右上隅にある [新しいファイルの作成] アイコン (「新しいファイルの作成」) をクリックします。

  3. テキスト エディターで、次の 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

  4. [名前を付けて保存] をクリックします。

  5. ファイル名に「models/zzz_game_details.sql」と入力し、[作成] をクリックします。

  6. 2 番目のモデルを作成します。右上隅にある [新しいファイルの作成] アイコン (「新しいファイルの作成」) をクリックします。

  7. テキスト エディターで、次の 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 zzz_game_details
)
group by winner
order by wins desc

  8. [名前を付けて保存] をクリックします。

  9. ファイル名に「models/zzz_win_loss_records.sql」と入力し、[作成] をクリックします。

  10. モデルを実行します。コマンド ラインで、前述の 2 つのファイルへのパスを指定して dbt run コマンドを実行します。 default データベース (プロジェクト設定で指定されている) に、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

  11. 次の 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. スキーマ テストを作成します。右上隅にある [新しいファイルの作成] アイコン (「新しいファイルの作成」) をクリックします。

  2. テキスト エディターで、次の内容を入力します。 このファイルには、指定した列が一意の値を含むか、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

  3. [名前を付けて保存] をクリックします。

  4. ファイル名に「models/schema.yml」と入力し、[作成] をクリックします。

  5. 最初のデータ テストを作成します。右上隅にある [新しいファイルの作成] アイコン (「新しいファイルの作成」) をクリックします。

  6. テキスト エディターで、次の 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 zzz_game_details
where date < '2020-12-12'
or date > '2021-02-06'

  7. [名前を付けて保存] をクリックします。

  8. ファイル名に「tests/zzz_game_details_check_dates.sql」と入力し、[作成] をクリックします。

  9. 2 番目のデータ テストを作成します。右上隅にある [新しいファイルの作成] アイコン (「新しいファイルの作成」) をクリックします。

  10. テキスト エディターで、次の 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 zzz_game_details
where home_score < 0
or visitor_score < 0
or home_score = visitor_score

  11. [名前を付けて保存] をクリックします。

  12. ファイル名に「tests/zzz_game_details_check_scores.sql」と入力し、[作成] をクリックします。

  13. 3 番目のデータ テストを作成します。右上隅にある [新しいファイルの作成] アイコン (「新しいファイルの作成」) をクリックします。

  14. テキスト エディターで、次の SQL ステートメントを入力します。 このファイルには、勝敗記録がマイナスのチーム、試合数よりも勝敗記録が多いチーム、または認められている試合数以上に試合を行ったチームを調べるためのデータ テストが含まれています。

    -- Each team participated in 4 games this season.
-- For this test to pass, this query must return no results.

select wins, losses
from zzz_win_loss_records
where wins < 0 or wins > 4
or losses < 0 or losses > 4
or (wins + losses) > 4

  15. [名前を付けて保存] をクリックします。

  16. ファイル名に「tests/zzz_win_loss_records_check_records.sql」と入力し、[作成] をクリックします。

  17. テストを実行します。コマンド ラインで、dbt test コマンドを実行します。

手順 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;

次の手順

  • dbt のモデルの詳細情報を確認します。
  • dbt プロジェクトをテストする方法を確認します。
  • テンプレート言語である Jinja を使用して dbt プロジェクトで SQL をプログラミングする方法を確認します。
  • dbt のベスト プラクティスを確認します。

その他の技術情報