Didacticiel : Créer, exécuter et tester des modèles dbt localement

Ce tutoriel vous explique comment créer, exécuter et tester des modèles dbt localement. Vous pouvez également exécuter des projets dbt en tant que tâches de Job Databricks. Pour plus d'informations, consultez Utiliser des transformations dbt dans les Lakeflow Jobs.

Avant de commencer

Pour suivre ce didacticiel, vous devez d'abord connecter votre workspace Databricks à dbt Core. Pour plus d'informations, consultez Connectez-vous à dbt Core.

Étape 1 : Créez et exécutez des modèles

Au cours de cette étape, vous utilisez votre éditeur de texte préféré pour créer des modèles , qui sont des instructions select qui créent soit une nouvelle vue (le default), soit une nouvelle table dans une base de données, à partir de données existantes dans cette même base de données. Cette procédure crée un modèle basé sur la table diamonds d’exemple des Exemples de datasets.

Utilisez le code suivant pour créer cette table.

SQL
DROP TABLE IF EXISTS diamonds;

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

Dans le répertoire models du projet, créez un fichier nommé diamonds_four_cs.sql avec l'instruction SQL suivante. Cette instruction sélectionne uniquement les détails de carat, taille, couleur et pureté pour chaque diamant de la table diamonds. Le bloc config demande à dbt de créer une table dans la base de données basée sur cette instruction.
```
{{ config(
  materialized='table',
  file_format='delta'
) }}
```
SQL
```
select carat, cut, color, clarity
from diamonds
```

astuce

Pour d'autres options config, telles que l'utilisation du format de fichier Delta et la stratégie incrémentielle merge, consultez les configurations Databricks dans la documentation dbt.

Dans le répertoire models du projet, créez un deuxième fichier nommé diamonds_list_colors.sql avec l'instruction SQL suivante. Cette instruction sélectionne les valeurs uniques de la colonne colors dans la table diamonds_four_cs, en triant les résultats par ordre alphabétique du premier au dernier. Puisqu'il n'y a pas de bloc config, ce modèle demande à dbt de créer une vue dans la base de données basée sur cette instruction.
SQL
```
select distinct color
from {{ ref('diamonds_four_cs') }}
sort by color asc
```
Dans le répertoire models du projet, créez un troisième fichier nommé diamonds_prices.sql avec l'instruction SQL suivante. Cette déclaration calcule la moyenne des prix des diamants par couleur, en triant les résultats par prix moyen du plus élevé au plus bas. Ce modèle indique à dbt de créer une vue dans la base de données basée sur cette instruction.
SQL
```
select color, avg(price) as price
from diamonds
group by color
order by price desc
```

Avec l’environnement virtuel activé, exécutez la commande dbt run avec les chemins d’accès aux trois fichiers précédents. Dans la base de données default (telle que spécifiée dans le fichier profiles.yml), dbt crée une table nommée diamonds_four_cs et deux vues nommées diamonds_list_colors et diamonds_prices. dbt obtient ces noms de vue et de table à partir des noms de fichiers .sql associés.

Bash
dbt run --model models/diamonds_four_cs.sql models/diamonds_list_colors.sql models/diamonds_prices.sql

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

Exécutez le code SQL suivant pour obtenir des informations sur les nouvelles vues et pour sélectionner toutes les lignes de la table et des vues.

Si vous vous connectez à un cluster, vous pouvez exécuter ce code SQL à partir d'un Notebook connecté au cluster, en spécifiant SQL comme langage default pour le Notebook. Si vous vous connectez à un SQL Warehouse, vous pouvez exécuter ce code SQL à partir d'une query.

SQL
SHOW views IN default;

Console
+-----------+----------------------+-------------+
| namespace | viewName             | isTemporary |
+===========+======================+=============+
| default   | diamonds_list_colors | false       |
+-----------+----------------------+-------------+
| default   | diamonds_prices      | false       |
+-----------+----------------------+-------------+

SQL
SELECT * FROM diamonds_four_cs;

Console
+-------+---------+-------+---------+
| carat | cut     | color | clarity |
+=======+=========+=======+=========+
| 0.23  | Ideal   | E     | SI2     |
+-------+---------+-------+---------+
| 0.21  | Premium | E     | SI1     |
+-------+---------+-------+---------+
...

SQL
SELECT * FROM diamonds_list_colors;

Console
+-------+
| color |
+=======+
| D     |
+-------+
| E     |
+-------+
...

SQL
SELECT * FROM diamonds_prices;

Console
+-------+---------+
| color | price   |
+=======+=========+
| J     | 5323.82 |
+-------+---------+
| I     | 5091.87 |
+-------+---------+
...

Étape 2 : Créer et exécuter des modèles plus complexes

Dans cette étape, vous créez des modèles plus complexes pour un ensemble de tables de données connexes. Ces tables de données contiennent des informations sur une ligue sportive fictive de trois équipes disputant une saison de six matchs. Cette procédure crée les tables de données, crée les modèles et exécute les modèles.

Exécutez le code SQL suivant pour créer les tables de données nécessaires.

Les tables et les vues de cette étape start par zzz_ pour les aider à les identifier dans le cadre de cet exemple. Vous n'avez pas besoin de suivre ce modèle pour vos propres tables et vues.

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

Dans le répertoire models du projet, créez un fichier nommé zzz_game_details.sql avec l'instruction SQL suivante. Cette instruction crée une table qui fournit les détails de chaque jeu, tels que les noms d’équipe et les scores. Le bloc config indique à dbt de créer une table dans la base de données à partir de cette instruction.

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

Dans le répertoire models du projet, créez un fichier nommé zzz_win_loss_records.sql avec l'instruction SQL suivante. Cette instruction crée une vue qui répertorie les bilans victoires-défaites des équipes pour la saison.

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

Une fois l’environnement virtuel activé, exécutez la commande dbt run avec les chemins d’accès aux deux fichiers précédents. Dans la base de données default (telle que spécifiée dans le fichier profiles.yml), dbt crée une table nommée zzz_game_details et une vue nommée zzz_win_loss_records. dbt obtient ces noms de vues et de tables à partir des noms de fichiers .sql associés.

Bash
dbt run --model models/zzz_game_details.sql models/zzz_win_loss_records.sql

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

Exécutez le code SQL suivant pour lister les informations concernant la nouvelle vue et pour sélectionner toutes les lignes de la table et de la vue.

SQL
SHOW VIEWS FROM default LIKE 'zzz_win_loss_records';

Console
+-----------+----------------------+-------------+
| namespace | viewName             | isTemporary |
+===========+======================+=============+
| default   | zzz_win_loss_records | false       |
+-----------+----------------------+-------------+

SQL
SELECT * 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 |
+---------+---------------+---------------+------------+---------------+---------------+------------+

SQL
SELECT * FROM zzz_win_loss_records;

Console
+---------------+------+--------+
| team          | wins | losses |
+===============+======+========+
| Amsterdam     | 3    | 1      |
+---------------+------+--------+
| San Francisco | 2    | 2      |
+---------------+------+--------+
| Seattle       | 1    | 3      |
+---------------+------+--------+

Étape 3 : Créez et exécutez des tests

Dans cette étape, vous créez des tests , qui sont des assertions que vous faites concernant vos modèles. Lorsque vous exécutez ces tests, dbt vous indique si chaque test de votre projet réussit ou échoue.

Il existe deux types de tests. Tests de schéma , appliqués en YAML, renvoient le nombre d'enregistrements qui ne satisfont pas à une assertion. Lorsque ce nombre est nul, tous les enregistrements réussissent, par conséquent les tests réussissent. Les tests de données sont des requêtes spécifiques qui doivent retourner zéro enregistrement pour réussir.

Dans le répertoire models du projet, créez un fichier nommé schema.yml avec le contenu suivant. Ce fichier inclut des tests de schéma qui déterminent si les colonnes spécifiées ont des valeurs uniques, ne sont pas nulles, ont uniquement les valeurs spécifiées ou une combinaison.

YAML
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

Dans le répertoire tests du projet, créez un fichier nommé zzz_game_details_check_dates.sql avec l'instruction SQL suivante. Ce fichier contient un test de données pour déterminer si des parties ont eu lieu en dehors de la saison régulière.
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'
```

Dans le répertoire tests du projet, créez un fichier nommé zzz_game_details_check_scores.sql avec l'instruction SQL suivante. Ce fichier comprend un test de données pour déterminer si des scores étaient négatifs ou si des parties étaient à égalité.

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

Dans le répertoire tests du projet, créez un fichier nommé zzz_win_loss_records_check_records.sql avec l'instruction SQL suivante. Ce fichier inclut un test de données pour déterminer si des équipes avaient des bilans de victoires ou de défaites négatifs, avaient plus de victoires ou de défaites que de matchs joués, ou ont joué plus de matchs qu'il n'était autorisé.
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
```

Une fois l'environnement virtuel activé, exécutez la commande dbt test.

Bash
dbt test --models zzz_game_details zzz_win_loss_records

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

Étape 4 : Nettoyage

Vous pouvez supprimer les tables et les vues que vous avez créées pour cet exemple en exécutant le code SQL suivant.

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;

Dépannage

Pour des informations sur les problèmes courants lors de l’utilisation de dbt Core avec Databricks et comment les résoudre, consultez Getting help sur le site Web de dbt Labs.

Étapes suivantes

Exécutez les projets dbt Core en tant que tâches de Job Databricks. Voir Utiliser les transformations dbt dans les Lakeflow Jobs.

Ressources supplémentaires

Explorez les ressources suivantes sur le site web de dbt Labs :

Avant de commencer​

Étape 1 : Créez et exécutez des modèles​

Étape 2 : Créer et exécuter des modèles plus complexes​

Étape 3 : Créez et exécutez des tests​

Étape 4 : Nettoyage​

Dépannage​

Étapes suivantes​

Ressources supplémentaires​