Freigeben über

Tutorial: Lokales Erstellen, Ausführen und Testen von dbt-Modellen

  • Artikel

In diesem Tutorial erfahren Sie, wie Sie dbt-Modelle lokal erstellen, ausführen und testen. Sie können dbt-Projekte auch als Azure Databricks-Auftragsaufgaben ausführen. Weitere Informationen finden Sie unter Verwenden von dbt-Transformationen in einem Azure Databricks-Auftrag.

Vorbemerkungen

Um dieses Tutorial bearbeiten zu können, müssen Sie zunächst Ihren Azure Databricks-Arbeitsbereich mit dbt Core verbinden. Weitere Informationen finden Sie unter Herstellen einer Verbindung mit dbt Core.

Schritt 1: Erstellen und Ausführen von Modellen

In diesem Schritt verwenden Sie Ihren bevorzugten Text-Editor, um Modelle zu erstellen. Dabei handelt es sich um select-Anweisungen, die entweder eine neue Sicht (Standardeinstellung) oder eine neue Tabelle in einer Datenbank erstellen, ausgehend von vorhandenen Daten in eben dieser Datenbank. Mit diesem Verfahren wird ein Modell erstellt, das auf der Beispieltabelle diamonds aus den Beispieldatasets basiert.

Verwenden Sie den folgenden Code, um diese Tabelle zu erstellen.

DROP TABLE IF EXISTS diamonds;

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

  1. Erstellen Sie im Verzeichnis des models-Projekts eine Datei mit dem Namen diamonds_four_cs.sql mit der folgenden SQL-Anweisung. Diese Anweisung wählt nur die Karat-, Schnitt-, Farb- und Reinheits-Details für jeden Diamanten in der diamonds-Tabelle aus. Der config-Block weist dbt an, auf der Grundlage dieser Anweisung eine Tabelle in der Datenbank zu erstellen.

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

    select carat, cut, color, clarity
from diamonds

    Tipp

    Weitere config-Optionen wie die Verwendung des Delta-Dateiformats und der inkrementellen merge-Strategie finden Sie unter Databricks-Konfigurationen in der dbt-Dokumentation.

  2. Erstellen Sie im Verzeichnis models des Projekts mit der folgenden SQL-Anweisung eine Datei mit dem Namen diamonds_list_colors.sql. Diese Anweisung wählt eindeutige Werte aus der Spalte colors in der Tabelle diamonds_four_cs aus und sortiert die Ergebnisse in aufsteigender alphabetischer Reihenfolge. Da kein config-Block vorhanden ist, weist dieses Modell dbt an, basierend auf dieser Anweisung eine Sicht in der Datenbank zu erstellen.

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

  3. Erstellen Sie im Verzeichnis models des Projekts mit der folgenden SQL-Anweisung eine Datei mit dem Namen diamonds_prices.sql. Mit dieser Anweisung werden die Preise der Diamanten nach Farbe gemittelt, und die Ergebnisse werden nach dem Durchschnittspreis vom höchsten zum niedrigsten Preis sortiert. Dieses Modell weist dbt an, auf der Grundlage dieser Anweisung eine Sicht in der Datenbank zu erstellen.

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

  4. Führen Sie bei aktivierter virtueller Umgebung den Befehl dbt run mit den Pfaden zu den drei vorherigen Dateien aus. Dbt erstellt in der default-Datenbank (wie in der Datei profiles.yml angegeben) eine Tabelle mit dem Namen diamonds_four_cs und zwei Sichten mit den Namen diamonds_list_colors und diamonds_prices. dbt ruft die Namen dieser Sichten und Tabellen aus den zugehörigen .sql-Dateinamen ab.

    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. Führen Sie den folgenden SQL-Code aus, um Informationen zu den neuen Sichten aufzulisten und alle Zeilen aus der Tabelle und den Sichten auszuwählen.

    Wenn Sie eine Verbindung mit einem Cluster herstellen, können Sie diesen SQL-Code aus einem Notebook ausführen, das mit dem Cluster verbunden ist, und dabei SQL als Standardsprache für das Notebook angeben. Wenn Sie eine Verbindung mit einem SQL-Warehouse herstellen, können Sie diesen SQL-Code über eine Abfrage ausführen.

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

Schritt 2: Erstellen und Ausführen komplexerer Modelle

In diesem Schritt erstellen Sie komplexere Modelle für einen Satz verknüpfter Datentabellen. Diese Datentabellen enthalten Informationen über eine fiktive Sportliga aus drei Teams, die eine Saison aus sechs Spielen spielen. Dieses Verfahren erstellt die Datentabellen, erstellt die Modelle und führt die Modelle aus.

  1. Führen Sie den folgenden SQL Code aus, um die erforderlichen Datentabellen zu erstellen.

    Wenn Sie eine Verbindung mit einem Cluster herstellen, können Sie diesen SQL-Code aus einem Notebook ausführen, das mit dem Cluster verbunden ist, und dabei SQL als Standardsprache für das Notebook angeben. Wenn Sie eine Verbindung mit einem SQL-Warehouse herstellen, können Sie diesen SQL-Code über eine Abfrage ausführen.

    Die Tabellen und Ansichten in diesem Schritt beginnen mit zzz_, um sie als Teil dieses Beispiels zu identifizieren. Sie brauchen dieses Muster für Ihre eigenen Tabellen und Sichten nicht zu befolgen.

    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. Erstellen Sie im models-Verzeichnis des Projekts mit der folgenden SQL-Anweisung eine Datei mit dem Namen zzz_game_details.sql. Mit dieser Anweisung wird eine Tabelle erstellt, die die Details der einzelnen Spiele enthält, z. B. Teamnamen und Bewertungen. Der config-Block weist dbt an, auf der Grundlage dieser Anweisung eine Tabelle in der Datenbank zu erstellen.

    -- 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. Erstellen Sie im models-Verzeichnis des Projekts mit der folgenden SQL-Anweisung eine Datei mit dem Namen zzz_win_loss_records.sql. Mit dieser Anweisung wird eine Sicht erstellt, in der Gewonnen-Verloren-Datensätze der Teams für die Saison aufgelistet werden.

    -- 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. Führen Sie bei aktivierter virtueller Umgebung den Befehl dbt run mit den Pfaden zu den zwei vorherigen Dateien aus. Dbt erstellt in der default-Datenbank (wie in der Datei profiles.yml angegeben) eine Tabelle mit dem Namen zzz_game_details und eine Sicht mit dem Namen zzz_win_loss_records. dbt ruft die Namen dieser Sichten und Tabellen aus den zugehörigen .sql-Dateinamen ab.

    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. Führen Sie den folgenden SQL-Code aus, um Informationen zu der neuen Sicht aufzulisten und alle Zeilen aus der Tabelle und der Sicht auszuwählen.

    Wenn Sie eine Verbindung mit einem Cluster herstellen, können Sie diesen SQL-Code aus einem Notebook ausführen, das mit dem Cluster verbunden ist, und dabei SQL als Standardsprache für das Notebook angeben. Wenn Sie eine Verbindung mit einem SQL-Warehouse herstellen, können Sie diesen SQL-Code über eine Abfrage ausführen.

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

Schritt 3: Erstellen und Ausführen von Tests

In diesem Schritt erstellen Sie Tests, bei denen es sich um Assertions zu Ihren Modellen handelt. Wenn Sie diese Tests ausführen, teilt dbt Ihnen mit, ob die einzelnen Tests in Ihrem Projekt erfolgreich waren.

Es gibt zwei Arten von Tests. Schematests, die in YAML angewendet werden, geben die Anzahl der Datensätze zurück, die keine Assertion bestehen. Wenn diese Zahl 0 (null) ist, bestehen alle Datensätze, daher sind die Tests erfolgreich. Datentests sind bestimmte Abfragen, die keine Datensätze zurückgeben dürfen, um erfolgreich zu sein.

  1. Erstellen Sie im models-Verzeichnis des Projekts eine Datei mit dem Namen schema.yml und folgendem Inhalt. Diese Datei enthält Schematests, die bestimmen, ob die angegebenen Spalten eindeutige Werte aufweisen, nicht NULL sind, nur die angegebenen Werte oder eine Kombination aufweisen.

    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. Erstellen Sie im tests-Verzeichnis des Projekts mit der folgenden SQL-Anweisung eine Datei mit dem Namen zzz_game_details_check_dates.sql. Diese Datei enthält einen Datentest, um zu bestimmen, ob Spiele außerhalb der regulären Saison stattgefunden haben.

    -- 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. Erstellen Sie im tests-Verzeichnis des Projekts mit der folgenden SQL-Anweisung eine Datei mit dem Namen zzz_game_details_check_scores.sql. Diese Datei enthält einen Datentest, um zu bestimmen, ob es negative Punkte gegeben hat oder Spiele unentschieden endeten.

    -- 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. Erstellen Sie im tests-Verzeichnis des Projekts mit der folgenden SQL-Anweisung eine Datei mit dem Namen zzz_win_loss_records_check_records.sql. Diese Datei enthält einen Datentest, um zu bestimmen, ob Teams negative Gewinn- oder Verlustdatensätze hatten, mehr Gewinn- oder Verlustdatensätze als Spiele hatten oder mehr Spiele als zugelassen spielten.

    -- 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. Wenn die virtuelle Umgebung aktiviert ist, führen Sie den dbt test-Befehl aus.

    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

Schritt 4: Bereinigung

Sie können die Tabellen und Sichten löschen, die Sie für dieses Beispiel erstellt haben, indem Sie den folgenden SQL-Code ausführen.

Wenn Sie eine Verbindung mit einem Cluster herstellen, können Sie diesen SQL-Code aus einem Notebook ausführen, das mit dem Cluster verbunden ist, und dabei SQL als Standardsprache für das Notebook angeben. Wenn Sie eine Verbindung mit einem SQL-Warehouse herstellen, können Sie diesen SQL-Code über eine Abfrage ausführen.

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;

Problembehandlung

Informationen zu häufig auftretenden Problemen bei der Verwendung von dbt Core mit Azure Databricks und deren Lösung finden Sie unter Hilfe auf der dbt Labs-Website.

Nächste Schritte

Ausführen von dbt Core-Projekten als Azure Databricks-Auftragsaufgaben. Weitere Informationen finden Sie unter Verwenden von dbt-Transformationen in einem Azure Databricks-Auftrag.

Zusätzliche Ressourcen

Erkunden Sie die folgenden Ressourcen auf der dbt Labs-Website: