Esercitazione: Creare, eseguire e testare modelli dbt in locale

Articolo
08/02/2024

Questa esercitazione illustra come creare, eseguire e testare modelli dbt in locale. È anche possibile eseguire progetti dbt come attività di processo di Azure Databricks. Per altre informazioni, vedere Usare trasformazioni dbt in un processo di Azure Databricks.

Prima di iniziare

Per seguire questa esercitazione, è prima necessario connettere l'area di lavoro di Azure Databricks a dbt Core. Per altre informazioni, vedere Connettersi a dbt Core.

Passaggio 1: Creare ed eseguire modelli

In questo passaggio si usa l'editor di testo preferito per creare modelli, ovvero select istruzioni che creano una nuova vista (impostazione predefinita) o una nuova tabella in un database, in base ai dati esistenti nello stesso database. Questa procedura crea un modello basato sulla tabella di esempio dei set di dati di esempiodiamonds.

Usare il codice seguente per creare questa tabella.

DROP TABLE IF EXISTS diamonds;

CREATE TABLE diamonds USING CSV OPTIONS (path "/databricks-datasets/Rdatasets/data-001/csv/ggplot2/diamonds.csv", header "true")

Nella directory del models progetto creare un file denominato diamonds_four_cs.sql con l'istruzione SQL seguente. Questa istruzione seleziona solo i dettagli carat, cut, color e clarity per ogni diamante della diamonds tabella. Il config blocco indica a dbt di creare una tabella nel database in base a questa istruzione.
```
{{ config(
  materialized='table',
  file_format='delta'
) }}
```
```
select carat, cut, color, clarity
from diamonds
```
Suggerimento

Per altre config opzioni, ad esempio l'uso del formato di file Delta e la merge strategia incrementale, vedere Configurazioni di Databricks nella documentazione di dbt.
Nella directory del models progetto creare un secondo file denominato diamonds_list_colors.sql con l'istruzione SQL seguente. Questa istruzione seleziona valori univoci dalla colors colonna nella diamonds_four_cs tabella, ordinando i risultati in ordine alfabetico prima per ultimo. Poiché non è presente alcun config blocco, questo modello indica a dbt di creare una vista nel database in base a questa istruzione.
```
select distinct color
from {{ ref('diamonds_four_cs') }}
sort by color asc
```
Nella directory del models progetto creare un terzo file denominato diamonds_prices.sql con l'istruzione SQL seguente. Questa affermazione calcola i prezzi dei diamanti in base al colore, ordinando i risultati in base al prezzo medio dal più alto al più basso. Questo modello indica a dbt di creare una vista nel database in base a questa istruzione.
```
select color, avg(price) as price
from diamonds
group by color
order by price desc
```

Con l'ambiente virtuale attivato, eseguire il dbt run comando con i percorsi dei tre file precedenti. default Nel database (come specificato nel profiles.yml file), dbt crea una tabella denominata e due viste denominate diamonds_list_colors diamonds_four_cs e diamonds_prices. dbt ottiene questi nomi di vista e tabella dai nomi di file correlati .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

Eseguire il codice SQL seguente per elencare le informazioni sulle nuove viste e selezionare tutte le righe della tabella e delle viste.

Se ci si connette a un cluster, è possibile eseguire questo codice SQL da un notebook connesso al cluster, specificando SQL come linguaggio predefinito per il notebook. Se ci si connette a un'istanza di SQL Warehouse, è possibile eseguire questo codice SQL da una query.

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

Passaggio 2: Creare ed eseguire modelli più complessi

In questo passaggio vengono creati modelli più complessi per un set di tabelle dati correlate. Queste tabelle di dati contengono informazioni su una lega sportiva fittizia di tre squadre che giocano una stagione di sei partite. Questa procedura crea le tabelle dati, crea i modelli ed esegue i modelli.

Eseguire il codice SQL seguente per creare le tabelle di dati necessarie.

Le tabelle e le viste in questo passaggio iniziano con zzz_ per identificarle come parte di questo esempio. Non è necessario seguire questo modello per tabelle e viste personalizzate.

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

Nella directory del models progetto creare un file denominato zzz_game_details.sql con l'istruzione SQL seguente. Questa istruzione crea una tabella che fornisce i dettagli di ogni gioco, ad esempio i nomi e i punteggi della squadra. Il config blocco indica a dbt di creare una tabella nel database in base a questa istruzione.

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

Nella directory del models progetto creare un file denominato zzz_win_loss_records.sql con l'istruzione SQL seguente. Questa istruzione crea una visualizzazione che elenca i record di vittoria della squadra per la stagione.

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

Con l'ambiente virtuale attivato, eseguire il dbt run comando con i percorsi dei due file precedenti. default Nel database (come specificato nel profiles.yml file), dbt crea una tabella denominata e una vista denominata zzz_win_loss_recordszzz_game_details . dbt ottiene questi nomi di vista e tabella dai nomi di file correlati .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

Eseguire il codice SQL seguente per elencare le informazioni sulla nuova vista e selezionare tutte le righe dalla tabella e dalla vista.

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

Passaggio 3: Creare ed eseguire test

In questo passaggio vengono creati test, che sono asserzioni eseguite sui modelli. Quando si eseguono questi test, dbt indica se ogni test del progetto supera o non riesce.

Esistono due tipi di test. I test dello schema, applicati in YAML, restituiscono il numero di record che non superano un'asserzione. Quando questo numero è zero, tutti i record vengono superati, pertanto i test vengono superati. I test dei dati sono query specifiche che devono restituire zero record da passare.

Nella directory del models progetto creare un file denominato schema.yml con il contenuto seguente. Questo file include test dello schema che determinano se le colonne specificate hanno valori univoci, non sono Null, hanno solo i valori specificati o una combinazione.

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

Nella directory del tests progetto creare un file denominato zzz_game_details_check_dates.sql con l'istruzione SQL seguente. Questo file include un test di dati per determinare se eventuali partite si sono verificate al di fuori della stagione regolare.
```
-- 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'
```

Nella directory del tests progetto creare un file denominato zzz_game_details_check_scores.sql con l'istruzione SQL seguente. Questo file include un test di dati per determinare se i punteggi sono stati negativi o eventuali giochi sono stati legati.

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

Nella directory del tests progetto creare un file denominato zzz_win_loss_records_check_records.sql con l'istruzione SQL seguente. Questo file include un test dei dati per determinare se qualsiasi squadra ha avuto record negativi o di perdita, ha avuto più record di vittoria o perdita rispetto ai giochi o ha giocato più partite di quanto consentito.
```
-- 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
```

Con l'ambiente virtuale attivato, eseguire il 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

Passaggio 4: Pulire

È possibile eliminare le tabelle e le viste create per questo esempio eseguendo il codice SQL seguente.

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;

Risoluzione dei problemi

Per informazioni sui problemi comuni relativi all'uso di dbt Core con Azure Databricks e su come risolverli, vedere Ottenere assistenza nel sito Web dbt Labs.

Passaggi successivi

Eseguire progetti dbt Core come attività di processo di Azure Databricks. Vedere Usare le trasformazioni dbt in un processo di Azure Databricks.

Risorse aggiuntive

Esplorare le risorse seguenti nel sito Web dbt Labs:

Condividi tramite

Esercitazione: Creare, eseguire e testare modelli dbt in locale

Prima di iniziare

Passaggio 1: Creare ed eseguire modelli

Passaggio 2: Creare ed eseguire modelli più complessi

Passaggio 3: Creare ed eseguire test

Passaggio 4: Pulire

Risoluzione dei problemi

Passaggi successivi

Risorse aggiuntive

Commenti e suggerimenti

Risorse aggiuntive