Tutorial: Criar, executar e testar modelos dbt localmente
Este site tutorial orienta o senhor sobre como criar, executar e testar localmente os modelos dbt. O senhor também pode executar dbt projetos como Databricks Job tarefa. Para obter mais informações, consulte Use dbt transformações in a Databricks Job.
Antes de começar
Para seguir este tutorial, você deve primeiro conectar seu workspace Databricks ao dbt Core. Para obter mais informações, consulte Conectar ao dbt Core.
passo 1: Criar e executar modelos
Neste passo, o senhor usa seu editor de texto favorito para criar modelos, que são instruções select
que criam um novo view (o default) ou uma nova tabela em um banco de dados, com base nos dados existentes nesse mesmo banco de dados. Esse procedimento cria um modelo com base na tabela de amostra diamonds
do conjunto de dados Sample.
Use o código a seguir para criar essa tabela.
DROP TABLE IF EXISTS diamonds;
CREATE TABLE diamonds USING CSV OPTIONS (path "/databricks-datasets/Rdatasets/data-001/csv/ggplot2/diamonds.csv", header "true")
No diretório
models
do projeto, crie um arquivo chamadodiamonds_four_cs.sql
com a seguinte instrução SQL. Esta declaração seleciona apenas os detalhes de quilate, corte, cor e clareza para cada diamante da tabeladiamonds
. O blococonfig
instrui o dbt a criar uma tabela no banco de dados com base nessa instrução.{{ config( materialized='table', file_format='delta' ) }}
select carat, cut, color, clarity from diamonds
Dica
Para opções
config
adicionais, como usar o formato de arquivo Delta e a estratégia incrementalmerge
, consulte as configurações do Databricks na documentação dbt.No diretório
models
do projeto, crie um segundo arquivo chamadodiamonds_list_colors.sql
com a seguinte instrução SQL. Esta instrução seleciona valores exclusivos da colunacolors
na tabeladiamonds_four_cs
, classificando os resultados em ordem alfabética do primeiro ao último. Como não há blococonfig
, esse modelo instrui o dbt a criar uma view no banco de dados com base nessa instrução.select distinct color from {{ ref('diamonds_four_cs') }} sort by color asc
No diretório
models
do projeto, crie um terceiro arquivo denominadodiamonds_prices.sql
com a seguinte instrução SQL. Esta declaração calcula a média dos preços dos diamantes por cor, classificando os resultados por preço médio do maior para o menor. Esse modelo instrui o dbt a criar uma view no banco de dados com base nessa instrução.select color, avg(price) as price from diamonds group by color order by price desc
Com o ambiente virtual ativado, execute o comando
dbt run
com os caminhos para os três arquivos anteriores. No banco de dadosdefault
(conforme especificado no arquivoprofiles.yml
), dbt cria uma tabela denominadadiamonds_four_cs
e duas view denominadasdiamonds_list_colors
ediamonds_prices
. dbt obtém esses nomes de view e tabela de seus nomes de arquivo.sql
relacionados.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
executar o seguinte código SQL para listar informações sobre a nova view e selecionar todas as linhas da tabela e view.
Se você estiver se conectando a clusters, poderá executar esse código SQL a partir de um Notebook conectado aos clusters, especificando SQL como o idioma default para o Notebook. Se você estiver se conectando a um SQL warehouse, poderá executar esse código SQL a partir de uma consulta.
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 | +-------+---------+ ...
passo 2: Criar e executar modelos mais complexos
Nesta passo, você cria modelos mais complexos para um conjunto de tabelas de dados relacionadas. Essas tabelas de dados contêm informações sobre uma liga esportiva fictícia de três times jogando uma temporada de seis jogos. Este procedimento cria as tabelas de dados, cria os modelos e executa os modelos.
execução do seguinte código SQL para criar as tabelas de dados necessárias.
Se você estiver se conectando a clusters, poderá executar esse código SQL a partir de um Notebook conectado aos clusters, especificando SQL como o idioma default para o Notebook. Se você estiver se conectando a um SQL warehouse, poderá executar esse código SQL a partir de uma consulta.
As tabelas e view nesta passo começam com
zzz_
para ajudar a identificá-las como parte deste exemplo. Você não precisa seguir esse padrão para suas próprias tabelas e view.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 | -- +---------+---------------+
No diretório
models
do projeto, crie um arquivo chamadozzz_game_details.sql
com a seguinte instrução SQL. Essa instrução cria uma tabela que fornece os detalhes de cada jogo, como nomes de times e pontuações. O blococonfig
instrui o dbt a criar uma tabela no banco de dados com base nessa instrução.-- 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
No diretório
models
do projeto, crie um arquivo chamadozzz_win_loss_records.sql
com a seguinte instrução SQL. Essa instrução cria uma view que lista os recordes de vitórias e derrotas da equipe na temporada.-- 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
Com o ambiente virtual ativado, execute o comando
dbt run
com os caminhos para os dois arquivos anteriores. No banco de dadosdefault
(conforme especificado no arquivoprofiles.yml
), dbt cria uma tabela denominadazzz_game_details
e uma view denominadazzz_win_loss_records
. dbt obtém esses nomes view e tabela de seus nomes de arquivo.sql
relacionados.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
executar o seguinte código SQL para listar informações sobre a nova view e selecionar todas as linhas da tabela e view.
Se você estiver se conectando a clusters, poderá executar esse código SQL a partir de um Notebook conectado aos clusters, especificando SQL como o idioma default para o Notebook. Se você estiver se conectando a um SQL warehouse, poderá executar esse código SQL a partir de uma consulta.
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 | +---------------+------+--------+
passo 3: Criar e executar testes
Nesta passo, você cria testes, que são asserções que você faz sobre seus modelos. Quando você executa esses testes, dbt informa se cada teste em seu projeto foi aprovado ou reprovado.
Existem dois tipos de testes. Os testes de esquema, aplicados em YAML, retornam a quantidade de registros que não passam em uma asserção. Quando esse número é zero, todos os registros passam, portanto os testes passam. Os testes de dados são query específicas que devem retornar zero registros para passar.
No diretório
models
do projeto, crie um arquivo chamadoschema.yml
com o seguinte conteúdo. Esse arquivo inclui testes de esquema que determinam se as colunas especificadas têm valores exclusivos, não são nulas, têm apenas os valores especificados ou uma combinação.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
No diretório
tests
do projeto, crie um arquivo chamadozzz_game_details_check_dates.sql
com a seguinte instrução SQL. Este arquivo inclui um teste de dados para determinar se algum jogo aconteceu fora da temporada regular.-- 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'
No diretório
tests
do projeto, crie um arquivo chamadozzz_game_details_check_scores.sql
com a seguinte instrução SQL. Este arquivo inclui um teste de dados para determinar se alguma pontuação foi negativa ou se algum jogo foi empatado.-- 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
No diretório
tests
do projeto, crie um arquivo chamadozzz_win_loss_records_check_records.sql
com a seguinte instrução SQL. Este arquivo inclui um teste de dados para determinar se alguma equipe teve registros negativos de vitórias ou derrotas, teve mais registros de vitórias ou derrotas do que jogos disputados ou jogou mais jogos do que o permitido.-- 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
Com o ambiente virtual ativado, execute o comando
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
passo 4: Limpar
Você pode excluir as tabelas e view criadas para este exemplo executando o código SQL a seguir.
Se você estiver se conectando a clusters, poderá executar esse código SQL a partir de um Notebook conectado aos clusters, especificando SQL como o idioma default para o Notebook. Se você estiver se conectando a um SQL warehouse, poderá executar esse código SQL a partir de uma consulta.
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;
Solução de problemas
Para obter informações sobre problemas comuns ao usar o dbt Core com Databricks e como resolvê-los, consulte Obtendo ajuda no site do dbt Labs.
Próximos passos
execução dbt Core projetos como Databricks Job tarefa. Consulte Usar dbt transformações em um trabalho Databricks .