Tutorial: Criar, executar e testar modelos dbt localmente

Artigo
08/02/2024

Este tutorial orienta você sobre como criar, executar e testar modelos dbt localmente. Você também pode executar projetos dbt como tarefas de trabalho do Azure Databricks. Para obter mais informações, consulte Usar transformações dbt em um trabalho do Azure Databricks.

Antes de começar

Para seguir este tutorial, você deve primeiro conectar seu espaço de trabalho do Azure Databricks ao dbt Core. Para obter mais informações, consulte Conectar-se ao dbt Core.

Etapa 1: Criar e executar modelos

Nesta etapa, você usa seu editor de texto favorito para criar modelos, que são select instruções que criam uma nova exibição (o padrão) ou uma nova tabela em um banco de dados, com base em dados existentes nesse mesmo banco de dados. Este procedimento cria um modelo baseado na tabela de exemplo diamonds dos conjuntos de dados de exemplo.

Use o código a seguir para criar esta 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 do models projeto, crie um arquivo nomeado diamonds_four_cs.sql com a seguinte instrução SQL. Esta instrução seleciona apenas os detalhes de quilate, corte, cor e clareza para cada diamante da diamonds tabela. O config bloco 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
```
Gorjeta

Para obter opções adicionais config , como usar o formato de arquivo Delta e a merge estratégia incremental, consulte Configurações do Databricks na documentação do dbt.
No diretório do models projeto, crie um segundo arquivo nomeado diamonds_list_colors.sql com a seguinte instrução SQL. Esta instrução seleciona valores exclusivos da colors coluna na diamonds_four_cs tabela, classificando os resultados em ordem alfabética do primeiro ao último. Como não há nenhum config bloco, esse modelo instrui o dbt a criar uma exibição no banco de dados com base nessa instrução.
```
select distinct color
from {{ ref('diamonds_four_cs') }}
sort by color asc
```
No diretório do models projeto, crie um terceiro arquivo nomeado diamonds_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 mais alto para o mais baixo. Este modelo instrui o dbt a criar uma exibição 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 dbt run comando com os caminhos para os três arquivos anteriores. default No banco de dados (conforme especificado no arquivo), dbt profiles.yml cria uma tabela nomeada diamonds_four_cs e duas exibições nomeadas diamonds_list_colors e diamonds_prices. O DBT obtém esses nomes de exibição e tabela de seus nomes de arquivo relacionados .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

Execute o seguinte código SQL para listar informações sobre os novos modos de exibição e selecionar todas as linhas da tabela e modos de exibição.

Se você estiver se conectando a um cluster, poderá executar esse código SQL a partir de um bloco de anotações conectado ao cluster, especificando SQL como o idioma padrão para o bloco de anotações. 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 |
+-------+---------+
...

Etapa 2: Criar e executar modelos mais complexos

Nesta etapa, você cria modelos mais complexos para um conjunto de tabelas de dados relacionadas. Estas tabelas de dados contêm informações sobre uma liga desportiva fictícia de três equipas que jogam uma época de seis jogos. Este procedimento cria as tabelas de dados, cria os modelos e executa os modelos.

Execute o seguinte código SQL para criar as tabelas de dados necessárias.

As tabelas e exibições nesta etapa 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 exibições.

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 do models projeto, crie um arquivo nomeado zzz_game_details.sql com a seguinte instrução SQL. Esta declaração cria uma tabela que fornece os detalhes de cada jogo, como nomes e pontuações das equipas. O config bloco 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 do models projeto, crie um arquivo nomeado zzz_win_loss_records.sql com a seguinte instrução SQL. Esta declaração cria uma visão 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 dbt run comando com os caminhos para os dois arquivos anteriores. default No banco de dados (conforme especificado no arquivo), o profiles.yml dbt cria uma tabela nomeada zzz_game_details e uma exibição chamada zzz_win_loss_records. O DBT obtém esses nomes de exibição e tabela de seus nomes de arquivo relacionados .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

Execute o seguinte código SQL para listar informações sobre o novo modo de exibição e selecionar todas as linhas da tabela e exibição.

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      |
+---------------+------+--------+

Etapa 3: Criar e executar testes

Nesta etapa, você cria testes, que são afirmações que você faz sobre seus modelos. Quando você executa esses testes, dbt informa se cada teste em seu projeto passa ou falha.

Existem dois tipos de testes. Os testes de esquema, aplicados em YAML, retornam o número 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 consultas específicas que devem retornar zero registros para serem aprovados.

No diretório do models projeto, crie um arquivo nomeado schema.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 do tests projeto, crie um arquivo nomeado zzz_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 do tests projeto, crie um arquivo nomeado zzz_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 do tests projeto, crie um arquivo nomeado zzz_win_loss_records_check_records.sql com a seguinte instrução SQL. Este ficheiro inclui um teste de dados para determinar se alguma equipa teve registos negativos de vitórias ou derrotas, se teve mais registos de vitórias ou derrotas do que os jogos disputados ou se 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 dbt test comando.

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 exibições criadas para este exemplo executando o seguinte código 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;

Resolução de Problemas

Para obter informações sobre problemas comuns ao usar o dbt Core com o Azure Databricks e como resolvê-los, consulte Obter ajuda no site do dbt Labs.

Próximos passos

Execute projetos dbt Core como tarefas de trabalho do Azure Databricks. Consulte Usar transformações dbt em um trabalho do Azure Databricks.

Recursos adicionais

Explore os seguintes recursos no site do dbt Labs:

Partilhar via

Tutorial: Criar, executar e testar modelos dbt localmente

Antes de começar

Etapa 1: Criar e executar modelos

Etapa 2: Criar e executar modelos mais complexos

Etapa 3: Criar e executar testes

Passo 4: Limpar

Resolução de Problemas

Próximos passos

Recursos adicionais

Comentários

Recursos adicionais