チュートリアル: dbt モデルをローカルで作成、実行、およびテストする
このチュートリアルでは、dbt モデルをローカルで作成、実行、およびテストする方法について説明します。dbt プロジェクトを Databricks ジョブ タスクとして実行することもできます。詳細については、「dbtジョブで 変換を使用するLakeflow」を参照してください。
前提条件
このチュートリアルに従うには、まず Databricks ワークスペースを dbt Core に接続する必要があります。 詳細については、「dbt Coreへの接続」を参照してください。
ステップ 1: モデルを作成して実行する
この手順では、お気に入りのテキスト・エディタを使用して モデル を作成します。モデル。モデルは、同じデータベース内の既存のデータに基づいて、データベース内の新しいビュー (デフォルト) または新しいテーブルを作成する select ステートメントです。 この手順では、サンプル データセットのサンプル diamonds テーブルに基づいてモデルを作成します。
ワークスペースがサーバレス SQL ウェアハウスを使用した Unity Catalog に有効化されている場合、Databricks では、テーブルやビューではなく、最新の状態に保つ必要がある本番運用モデルをマテリアライズドビューまたはストリーミングテーブルとしてマテリアライズすることを推奨しています。これらのマテリアライズドビューは、ランのたびに完全な結果を再計算するのではなく、増分更新されます。「ステップ 4: マテリアライズドビューとストリーミングテーブルを使用してモデルを最新の状態に保つ」を参照してください。
次のコードを使用して、このテーブルを作成します。
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, clarityfrom diamonds
Delta ファイル形式や merge incremental 戦略の使用など、追加の config オプションについては、dbt ドキュメントの Databricks 構成を参照してください。
-
プロジェクトの
modelsディレクトリに、次の SQL ステートメントを使用してdiamonds_list_colors.sqlという名前の 2 番目のファイルを作成します。この文は、diamonds_four_csテーブルのcolorsカラムから一意の値を選択し、結果をアルファベット順にソートします。configブロックがないため、このモデルは dbt に、この文に基づいてデータベースにビューを作成するように指示します。SQLselect distinct colorfrom {{ ref('diamonds_four_cs') }}sort by color asc -
プロジェクトの
modelsディレクトリに、次の SQL ステートメントを使用してdiamonds_prices.sqlという名前の 3 番目のファイルを作成します。このステートメントは、ダイヤモンドの価格を色ごとに平均化し、結果を平均価格の高いものから低いものへと並べ替えます。このモデルは、この文に基づいてデータベースにビューを作成するように dbt に指示します。SQLselect color, avg(price) as pricefrom diamondsgroup by colororder 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 successfullyDone. 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.selectgame_id,home,t.team_city as visitor,home_score,visitor_score,-- Step 3 of 4: Display the city name for each game's winner.casewhenhome_score > visitor_scorethenhomewhenvisitor_score > home_scorethent.team_cityend as winner,game_date as datefrom (-- Step 2 of 4: Replace the home team IDs with their actual city names.selectgame_id,t.team_city as home,home_score,visitor_team_id,visitor_score,game_datefrom (-- Step 1 of 4: Combine data from various tables (for example, game and team IDs, scores, dates).selectg.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_datefromzzz_games as g,zzz_game_opponents as go,zzz_game_scores as gswhereg.game_id = go.game_id andg.game_id = gs.game_id) as all_ids,zzz_teams as twhereall_ids.home_team_id = t.team_id) as visitor_ids,zzz_teams as twherevisitor_ids.visitor_team_id = t.team_idorder 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.selectwinner as team,count(winner) as wins,-- Each team played in 4 games.(4 - count(winner)) as lossesfrom (-- Step 1 of 2: Determine the winner and loser for each game.selectgame_id,winner,casewhenhome = winnerthenvisitorelsehomeend as loserfrom {{ ref('zzz_game_details') }})group by winnerorder 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 successfullyDone. 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: 2models:- name: zzz_game_detailscolumns:- name: game_idtests:- unique- not_null- name: hometests:- not_null- accepted_values:values: ['Amsterdam', 'San Francisco', 'Seattle']- name: visitortests:- not_null- accepted_values:values: ['Amsterdam', 'San Francisco', 'Seattle']- name: home_scoretests:- not_null- name: visitor_scoretests:- not_null- name: winnertests:- not_null- accepted_values:values: ['Amsterdam', 'San Francisco', 'Seattle']- name: datetests:- not_null- name: zzz_win_loss_recordscolumns:- name: teamtests:- unique- not_null- relationships:to: ref('zzz_game_details')field: home- name: winstests:- not_null- name: lossestests:- 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 datefrom {{ 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_scorefrom {{ ref('zzz_game_details') }}where home_score < 0or visitor_score < 0or 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, lossesfrom {{ ref('zzz_win_loss_records') }}where wins < 0 or wins > 4or losses < 0 or losses > 4or (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 successfullyDone. PASS=19 WARN=0 ERROR=0 SKIP=0 TOTAL=19
ステップ 4: マテリアライズドビューとストリーミングテーブルを使用してモデルを常に最新の状態に保つ
新しいデータの到着に伴ってモデルを最新の状態に維持する必要がある場合は、テーブルやビューではなく、マテリアライズドビューまたはストリーミングテーブルとしてマテリアライズします。マテリアライズドビューはそのクエリー結果をインクリメンタルに更新するため、Databricksはランのたびにすべての結果を再計算するのではなく、変更された部分のみを更新します。これにより、マテリアライズドビューは集計やダッシュボードに提供するデータに適しています。ストリーミングテーブルは、クラウドストレージに到着するファイルなどの追加専用またはストリーミングのソースデータを、すでに読み取った行を再処理することなく、インクリメンタルに取り込みます。両方とも LakeFlow Pipelines によって支えられており、最新の状態を維持する必要がある本番運用の変換処理に推奨されるマテリアライゼーションです。
このステップでは、Serverless SQL Warehouse を備えた、Unity Catalog が有効化されたワークスペースが必要です。materialized_view のマテリアライゼーションは dbt に組み込まれており、streaming_table は dbt-databricks アダプターによって提供されます。
-
プロジェクトの
modelsディレクトリに、次のSQLステートメントを含むdiamonds_prices_mv.sqlという名前のファイルを作成します。これはdiamonds_pricesビューと同じ集計ですが、configブロックによってdbtにマテリアライズドビューを作成するように指示されます。クエリーごとに再計算を行うビューとは異なり、マテリアライズドビューは結果を事前計算し、増分更新します。{{ config(materialized='materialized_view') }}SQLselect color, avg(price) as pricefrom diamondsgroup by colororder by price desc -
プロジェクトの
modelsディレクトリに、次の SQL ステートメントを含むdiamonds_raw_stream.sqlという名前のファイルを作成します。configブロックは、sourcediamondsCSV ファイルをインクリメンタルに取り込むストリーミングテーブルを作成するように dbt に指示します。read_filesテーブル値関数はファイルを読み取り、STREAMキーワードは更新ごとに新しく到着したデータのみを処理します。{{ config(materialized='streaming_table') }}SQLselect * from stream read_files('/databricks-datasets/Rdatasets/data-001/csv/ggplot2/diamonds.csv',format => 'csv',header => true) -
仮想環境をアクティブ化した状態で、前の2つのファイルへのパスを指定して
dbt runコマンドを実行します。Bashdbt run --model models/diamonds_prices_mv.sql models/diamonds_raw_stream.sql
これらのモデルを更新する際は、次の動作に注意してください。
- ストリーミングテーブルのクエリーを変更しても、既に取り込まれたデータは再処理されません。dbtは
CREATE OR REFRESHを使用します。これにより、更新されたクエリーは将来の行にのみ適用されます。利用可能なソースデータを再処理するには、--full-refreshを使用して実行します。 - マテリアライズドビューの設定を変更する場合、スケジュール変更を除き、dbtによってマテリアライズドビューが削除されてから再作成されます。
databricks_tagsを使用して設定したタグは、適用後にdbtで削除することはできません。タグを削除するには、Databricksを直接使用するか、ポストフックを使用します。
スケジュール設定された更新、パーティション分割、行フィルターなど、サポートされている構成の完全な一覧については、dbt ドキュメントのマテリアライズドビューとストリーミングテーブルを参照してください。
ステップ 5: クリーンアップ
この例で作成したテーブルとビューを削除するには、次の 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 MATERIALIZED VIEW diamonds_prices_mv;
DROP TABLE diamonds_raw_stream;
DROP TABLE diamonds;
DROP TABLE diamonds_four_cs;
DROP VIEW diamonds_list_colors;
DROP VIEW diamonds_prices;
ステップ 4を完了していない場合は、DROP MATERIALIZED VIEW diamonds_prices_mvおよびDROP TABLE diamonds_raw_streamステートメントを省略します。
トラブルシューティング
with を使用する際の一般的な問題とその解決方法については、 Labs Web サイトの 「ヘルプの表示dbt Core 」を参照してください。Databricksdbt
次のステップ
- dbt Core プロジェクトを Databricks ジョブ タスクとして実行します。「dbtジョブでの 変換の使用 」を参照してください。Lakeflow
- データを最新の状態に保つマテリアライゼーションの詳細については、こちらをご覧ください。スタンドアロンのマテリアライズドビューの使用およびスタンドアロンのストリーミングテーブルの使用を参照してください。
追加のリソース
dbt Labs の Web サイトで次のリソースをご覧ください。