Partager via

Tutoriel : créer, exécuter et tester des modèles dbt localement

  • Article

Ce tutoriel vous guide tout au long de la création, de l’exécution et du test de modèles dbt localement. Vous pouvez également exécuter des projets dbt en tant que tâches de travail Azure Databricks. Consultez Utiliser des transformations dbt dans un travail Azure Databricks pour en savoir plus.

Avant de commencer

Pour suivre ce tutoriel, vous devez d’abord connecter votre espace de travail Azure Databricks à dbt Core. Pour plus d’informations, consultez Se connecter à dbt Core.

Étape 1 : créer et exécuter des modèles

Dans cette étape, vous allez utiliser votre éditeur de texte favori pour créer des modèles, à savoir des instructions select qui créent soit une nouvelle vue (par défaut), soit une nouvelle table dans une base de données, en fonction des données existantes de cette même base de données. Cette procédure crée un modèle basé sur l’exemple de table diamonds des Exemples de jeux de données.

Utilisez le code suivant pour créer cette table.

DROP TABLE IF EXISTS diamonds;

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

  1. 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 relatifs aux carats, à la taille, à la couleur et à la clarté de chaque diamant dans la table diamonds. Le bloc config indique à dbt de créer une table dans la base de données en fonction de cette instruction.

    {{ config(
  materialized='table',
  file_format='delta'
) }}

    select carat, cut, color, clarity
from diamonds

    Conseil

    Pour obtenir des options config supplémentaires telles que l’utilisation du format de fichier Delta et de la stratégie incrémentielle merge, consultez Configurations Databricks dans la documentation dbt.

  2. 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 des valeurs uniques dans la colonne colors de la table diamonds_four_cs, en triant les résultats par ordre alphabétique du premier au dernier. Étant donné qu’il n’y a aucun bloc config, ce modèle demande à dbt de créer une vue dans la base de données en fonction de cette instruction.

    select distinct color
from {{ ref('diamonds_four_cs') }}
sort by color asc

  3. Dans le répertoire models du projet, créez un troisième fichier nommé diamonds_prices.sql avec l’instruction SQL suivante. Cette instruction 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 demande à dbt de créer une vue dans la base de données en fonction de cette instruction.

    select color, avg(price) as price
from diamonds
group by color
order by price desc

  4. Une fois 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 (spécifiée dans le fichier profiles.yml), dbt crée une table nommée diamonds_four_cs et deux affichages nommés diamonds_list_colors et diamonds_prices. dbt obtient ces noms d’affichages et de table à partir des noms de fichiers .sql associés.

    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

  5. Exécutez le code SQL suivant pour répertorier les informations relatives aux nouvelles vues et 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 par défaut pour le notebook. Si vous vous connectez à un entrepôt SQL, vous pouvez exécuter ce code SQL à partir d’une requête.

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

É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 associées. Ces tables de données contiennent des informations sur une ligue sportive fictive composée de trois équipes jouant 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.

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

    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 par défaut pour le notebook. Si vous vous connectez à un entrepôt SQL, vous pouvez exécuter ce code SQL à partir d’une requête.

    Les tables et les vues de cette étape commencent par zzz_ afin de les identifier comme faisant partie de cet exemple. Vous n’avez pas besoin de suivre ce modèle pour vos propres tables et vues.

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

  2. 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 match, tels que les noms des équipes et les scores. Le bloc config indique à dbt de créer une table dans la base de données en fonction de cette instruction.

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

  3. 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 des victoires et des défaites des équipes pour la saison.

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

  4. 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 (spécifiée dans le fichier profiles.yml), dbt crée une table nommée zzz_game_details et un affichage nommé zzz_win_loss_records. dbt obtient ces noms d’affichages et de table à partir des noms de fichiers .sql associés.

    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

  5. Exécutez le code SQL suivant pour répertorier les informations relatives à la nouvelle vue et sélectionner toutes les lignes de la table et de la vue.

    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 par défaut pour le notebook. Si vous vous connectez à un entrepôt SQL, vous pouvez exécuter ce code SQL à partir d’une requête.

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

Étape 3 : créer et exécuter des tests

Dans cette étape, vous créez des tests, qui sont des instructions d’assertion que vous faites sur 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. Les tests de schéma, appliqués dans YAML, renvoient le nombre d’enregistrements qui ne réussissent pas l’instruction d’assertion. Lorsque ce nombre est égal à zéro, 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 renvoyer zéro enregistrement pour réussir.

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

    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

  2. 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 comprend un test de données permettant de déterminer si des matchs ont eu lieu en dehors de la saison normale.

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

  3. 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 permettant de déterminer si les scores sont négatifs ou si les matchs sont à égalité.

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

  4. 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 comprend un test de données permettant de déterminer si des équipes ont eu un nombre négatif de victoires ou de défaites, si elles ont eu plus de victoires ou de défaites que de matchs joués ou si elles ont joué plus de matchs qu’elles n’étaient autorisées.

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

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

    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

Étape 4 : nettoyer

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

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 par défaut pour le notebook. Si vous vous connectez à un entrepôt SQL, vous pouvez exécuter ce code SQL à partir d’une requête.

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;

Résolution des problèmes

Pour plus d’informations sur les problèmes courants liés à l’utilisation de dbt Core avec Azure Databricks et sur la façon de les résoudre, consultez Obtenir de l’aide sur le site web dbt Labs.

Étapes suivantes

Exécutez des projets dbt Core en tant que tâches de travail Azure Databricks. Consultez Utiliser des transformations dbt dans un travail Azure Databricks.

Ressources supplémentaires

Consultez les ressources suivantes sur le site web dbt Labs :