教程:在本地创建、运行和测试 dbt 模型
本教程将引导你了解如何在本地创建、运行和测试 dbt 模型。
开始之前的准备工作
要遵循本教程,必须先将 Azure Databricks 工作区连接到 dbt Core。 有关详细信息,请参阅连接到 dbt Core。
步骤 1:创建并运行模型
在本步骤中,需要使用喜欢的文本编辑器创建模型,这些模型是 语句,会根据同一数据库中的现有数据在数据库中创建一个新视图(默认行为)或一个新表。 此过程基于示例数据集中的示例 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
提示
有关其他
config
选项(例如使用 Delta 文件格式和merge
增量策略),请参阅 dbt 文档中的 Databricks 配置。在项目的
models
目录中,创建第二个包含以下 SQL 语句的名为diamonds_list_colors.sql
的文件。 此语句从colors
表中的diamonds_four_cs
列中选择唯一值,按字母顺序依次对结果进行排序。 由于没有config
块,此模型将指示 dbt 根据此语句在数据库中创建视图。select distinct color from {{ ref('diamonds_four_cs') }} sort by color asc
在项目的
models
目录中,创建第三个包含以下 SQL 语句的名为diamonds_prices.sql
的文件。 此语句按颜色计算钻石的平均价格,并按平均价格从高到低对结果进行排序。 此模型指示 dbt 根据此语句在数据库中创建视图。select color, avg(price) as price from diamonds group by color order by price desc
激活虚拟环境后,使用上述三个文件的路径运行
dbt run
命令。 在default
数据库中(如profiles.yml
文件中指定),dbt 会创建一个名为diamonds_four_cs
的表和两个名为diamonds_list_colors
和diamonds_prices
的视图。 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:创建并运行更复杂的模型
在此步骤中,你将为一组相关数据表创建更复杂的模型。 这些数据表包含有关虚拟的体育联赛的信息,其中三支队伍需要在一个赛季中完成六场比赛。 此过程将创建数据表、创建模型并运行模型。
运行以下 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 | -- +---------+---------------+
在项目的
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
激活虚拟环境后,使用上述两个文件的路径运行
dbt run
命令。 在default
数据库中(如profiles.yml
文件中指定),dbt 会创建一个名为zzz_game_details
的表和一个名为zzz_win_loss_records
的视图。 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 代码,列出有关新视图的信息,并选择表和视图中所有行。
如果要连接到群集,可以从连接到群集的笔记本中运行此 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 会告诉你项目中的每个测试是通过还是失败。
有两种类型的测试。 架构测试,在 YAML 中应用,返回未通过断言的记录数。 如果此数字为零,则所有记录都通过,因此测试也通过。 数据测试是必须返回零条记录才算通过的特定查询。
在项目的
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 代码来删除为此示例创建的表和视图。
如果要连接到群集,可以从连接到群集的笔记本中运行此 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 Core 与 Azure Databricks 配合使用时的常见问题以及如何解决这些问题的信息,请参阅 dbt 实验室网站上的获取帮助。
其他资源
在 dbt 实验室网站上浏览以下资源: