チュートリアル: dbt モデルをローカルで作成、実行、およびテストする
このチュートリアルでは、dbt モデルをローカルで作成、実行、およびテストする方法について説明します。 dbt プロジェクトを Databricks ジョブ タスクとして実行することもできます。 詳細については、「ジョブで dbt変換を使用するDatabricks 」を参照してください。
始める前に
このチュートリアルに従うには、まず 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'
) }}SQLselect carat, cut, color, clarity
from diamonds
Delta ファイル形式や merge incremental 戦略の使用など、追加の config オプションについては、dbt ドキュメントの Databricks 構成を参照してください。
-
プロジェクトの
modelsディレクトリに、次の SQL ステートメントを使用してdiamonds_list_colors.sqlという名前の 2 番目のファイルを作成します。 この文は、diamonds_four_csテーブルのcolorsカラムから一意の値を選択し、結果をアルファベット順にソートします。configブロックがないため、このモデルは dbt に、この文に基づいてデータベースにビューを作成するように指示します。SQLselect distinct color
from {{ ref('diamonds_four_cs') }}
sort by color asc -
プロジェクトの
modelsディレクトリに、次の SQL ステートメントを使用してdiamonds_prices.sqlという名前の 3 番目のファイルを作成します。 このステートメントは、ダイヤモンドの価格を色ごとに平均化し、結果を平均価格の高いものから低いものへと並べ替えます。 このモデルは、この文に基づいてデータベースにビューを作成するように dbt に指示します。SQLselect 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ファイル名から取得します。Bashdbt run --model models/diamonds_four_cs.sql models/diamonds_list_colors.sql models/diamonds_prices.sqlConsole...
... | 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クエリ からこの コードを実行できます。
SQLSHOW views IN default;Console+-----------+----------------------+-------------+
| namespace | viewName | isTemporary |
+===========+======================+=============+
| default | diamonds_list_colors | false |
+-----------+----------------------+-------------+
| default | diamonds_prices | false |
+-----------+----------------------+-------------+SQLSELECT * FROM diamonds_four_cs;Console+-------+---------+-------+---------+
| carat | cut | color | clarity |
+=======+=========+=======+=========+
| 0.23 | Ideal | E | SI2 |
+-------+---------+-------+---------+
| 0.21 | Premium | E | SI1 |
+-------+---------+-------+---------+
...SQLSELECT * FROM diamonds_list_colors;Console+-------+
| color |
+=======+
| D |
+-------+
| E |
+-------+
...SQLSELECT * FROM diamonds_prices;Console+-------+---------+
| color | price |
+=======+=========+
| J | 5323.82 |
+-------+---------+
| I | 5091.87 |
+-------+---------+
...
ステップ 2: より複雑なモデルを作成して実行する
この手順では、関連するデータ テーブルのセットに対して、より複雑なモデルを作成します。 これらのデータ テーブルには、3 つのチームが 6 試合のシーズンをプレイする架空のスポーツ リーグに関する情報が含まれています。 この手順では、データ・テーブルを作成し、モデルを作成し、モデルを実行します。
-
次の SQL コードを実行して、必要なデータ テーブルを作成します。
クラスターに接続している場合は、クラスターに接続されているSQL ノートブック からこのSQL コードを実行し、ノートブックのデフォルト言語として を指定できます。SQLウェアハウスに接続している場合は、SQLクエリ からこの コードを実行できます。
この手順のテーブルとビューは、この例の一部として識別するのに役立つ
zzz_で始まります。 独自のテーブルやビューでこのパターンに従う必要はありません。SQLDROP 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 に指示します。SQL-- 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'
) }}SQL-- 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という名前のファイルを作成します。 このステートメントは、シーズンのチームの勝敗レコードを一覧表示するビューを作成します。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という名前のテーブルとzzz_win_loss_recordsという名前のビューを 1 つずつ作成します。dbt は、これらのビュー名とテーブル名を、関連する.sqlファイル名から取得します。Bashdbt run --model models/zzz_game_details.sql models/zzz_win_loss_records.sqlConsole...
... | 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 コードを実行して、新しいビューに関する情報を一覧表示し、テーブルとビューからすべての行を選択します。
クラスターに接続している場合は、クラスターに接続されているSQL ノートブック からこのSQL コードを実行し、ノートブックのデフォルト言語として を指定できます。SQLウェアハウスに接続している場合は、SQLクエリ からこの コードを実行できます。
SQLSHOW VIEWS FROM default LIKE 'zzz_win_loss_records';Console+-----------+----------------------+-------------+
| namespace | viewName | isTemporary |
+===========+======================+=============+
| default | zzz_win_loss_records | false |
+-----------+----------------------+-------------+SQLSELECT * FROM zzz_game_details;Console+---------+---------------+---------------+------------+---------------+---------------+------------+
| 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 |
+---------+---------------+---------------+------------+---------------+---------------+------------+SQLSELECT * FROM zzz_win_loss_records;Console+---------------+------+--------+
| team | wins | losses |
+===============+======+========+
| Amsterdam | 3 | 1 |
+---------------+------+--------+
| San Francisco | 2 | 2 |
+---------------+------+--------+
| Seattle | 1 | 3 |
+---------------+------+--------+
ステップ 3: テストを作成して実行する
この手順では、モデルについて行うアサーションである テスト を作成します。 これらのテストを実行すると、dbt はプロジェクト内の各テストが成功したか失敗したかを示します。
テストには 2 つのタイプがあります。 YAML で適用された スキーマ テスト は、アサーションに合格しなかったレコードの数を返します。この数値が 0 の場合、すべてのレコードが合格するため、テストは合格します。 データ テスト は、合格するために 0 個のレコードを返す必要がある特定のクエリです。
-
プロジェクトの
modelsディレクトリに、次の内容でschema.ymlという名前のファイルを作成します。 このファイルには、指定した列が一意の値を持つか、null でないか、指定した値のみを持つか、またはその組み合わせがあるかを判断するスキーマ テストが含まれています。YAMLversion: 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という名前のファイルを作成します。 このファイルには、レギュラーシーズン以外で試合が行われたかどうかを判断するためのデータテストが含まれています。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という名前のファイルを作成します。 このファイルには、スコアが否定的であったか、または引き分けだったゲームがあるかを判断するためのデータテストが含まれています。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という名前のファイルを作成します。 このファイルには、勝敗の記録がマイナスだったチーム、プレイした試合数よりも勝敗の記録が多かったチーム、または許可された試合数よりも多くの試合をプレイしたチームがあるかどうかを判断するためのデータテストが含まれています。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コマンドを実行します。Bashdbt test --models zzz_game_details zzz_win_loss_recordsConsole...
... | 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;
トラブルシューティング
with を使用する際の一般的な問題とその解決方法については、 Labs Web サイトの 「ヘルプの表示dbt Core 」を参照してください。Databricksdbt
次のステップ
dbt Core プロジェクトを Databricks ジョブ タスクとして実行します。 「Databricks ジョブで dbt 変換を使用する」を参照してください。
追加のリソース
dbt Labs の Web サイトで次のリソースをご覧ください。