Zelfstudie: Dbt-modellen lokaal maken, uitvoeren en testen

In deze zelfstudie leert u hoe u dbt-modellen lokaal kunt maken, uitvoeren en testen. U kunt ook dbt-projecten uitvoeren als Azure Databricks-taaktaken. Zie Dbt-transformaties gebruiken in een Azure Databricks-taak voor meer informatie.

Voordat u begint

Als u deze zelfstudie wilt volgen, moet u eerst uw Azure Databricks-werkruimte verbinden met dbt Core. Zie Verbinding maken naar dbt Core voor meer informatie.

Stap 1: Modellen maken en uitvoeren

In deze stap gebruikt u uw favoriete teksteditor om modellen te maken. Dit zijn select instructies voor het maken van een nieuwe weergave (de standaardinstelling) of een nieuwe tabel in een database, op basis van bestaande gegevens in diezelfde database. Met deze procedure maakt u een model op basis van de voorbeeldtabel op basis van de voorbeeldgegevenssets.diamonds

Gebruik de volgende code om deze tabel te maken.

DROP TABLE IF EXISTS diamonds;

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

1. Maak in de map van models het project een bestand met de naam met diamonds_four_cs.sql de volgende SQL-instructie. Met deze instructie selecteert u alleen de details van het carat, knippen, kleuren en helderheid voor elke ruit uit de diamonds tabel. Het config blok geeft dbt opdracht om een tabel in de database te maken op basis van deze instructie.

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

   select carat, cut, color, clarity
   from diamonds
   

   Tip

   Zie Databricks-configuraties in de dbt-documentatie voor aanvullende config opties, zoals het gebruik van de Delta-bestandsindeling en de merge incrementele strategie.

2. Maak in de map van models het project een tweede bestand met de naam diamonds_list_colors.sql met de volgende SQL-instructie. Met deze instructie worden unieke waarden uit de colors kolom in de diamonds_four_cs tabel geselecteerd, waarbij de resultaten eerst in alfabetische volgorde worden gesorteerd. Omdat er geen config blok is, geeft dit model dbt opdracht om een weergave in de database te maken op basis van deze instructie.

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

3. Maak in de map van models het project een derde bestand met de naam diamonds_prices.sql met de volgende SQL-instructie. Met deze verklaring worden de diamantprijzen gemiddelden per kleur, en worden de resultaten gesorteerd op gemiddelde prijs van hoog naar laag. Met dit model wordt dbt geïnstrueerd om een weergave in de database te maken op basis van deze instructie.

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

4. Als de virtuele omgeving is geactiveerd, voert u de dbt run opdracht uit met de paden naar de drie voorgaande bestanden. In de default database (zoals opgegeven in het profiles.yml bestand), maakt dbt één tabel met de naam diamonds_four_cs en twee weergaven met de naam diamonds_list_colors en diamonds_prices. dbt haalt deze weergave- en tabelnamen op uit hun gerelateerde .sql bestandsnamen.

   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. Voer de volgende SQL-code uit om informatie over de nieuwe weergaven weer te geven en om alle rijen in de tabel en weergaven te selecteren.

   Als u verbinding maakt met een cluster, kunt u deze SQL-code uitvoeren vanuit een notebook dat is verbonden met het cluster, waarbij SQL wordt opgegeven als de standaardtaal voor het notebook. Als u verbinding maakt met een SQL-warehouse, kunt u deze SQL-code uitvoeren vanuit een 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 |
   +-------+---------+
   ...
   

Stap 2: Complexere modellen maken en uitvoeren

In deze stap maakt u complexere modellen voor een set gerelateerde gegevenstabellen. Deze gegevenstabellen bevatten informatie over een fictieve sportcompetitie van drie teams die een seizoen van zes wedstrijden spelen. Met deze procedure worden de gegevenstabellen gemaakt, worden de modellen gemaakt en worden de modellen uitgevoerd.

1. Voer de volgende SQL-code uit om de benodigde gegevenstabellen te maken.

   Als u verbinding maakt met een cluster, kunt u deze SQL-code uitvoeren vanuit een notebook dat is verbonden met het cluster, waarbij SQL wordt opgegeven als de standaardtaal voor het notebook. Als u verbinding maakt met een SQL-warehouse, kunt u deze SQL-code uitvoeren vanuit een query.

   De tabellen en weergaven in deze stap beginnen met zzz_ het identificeren ervan als onderdeel van dit voorbeeld. U hoeft dit patroon niet te volgen voor uw eigen tabellen en weergaven.

   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. Maak in de map van models het project een bestand met de naam met zzz_game_details.sql de volgende SQL-instructie. Met deze instructie maakt u een tabel met de details van elke wedstrijd, zoals teamnamen en scores. Het config blok geeft dbt opdracht om een tabel in de database te maken op basis van deze instructie.

   -- 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. Maak in de map van models het project een bestand met de naam met zzz_win_loss_records.sql de volgende SQL-instructie. Met deze instructie wordt een weergave gemaakt waarin records voor teamwinstverlies voor het seizoen worden vermeld.

   -- 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. Als de virtuele omgeving is geactiveerd, voert u de dbt run opdracht uit met de paden naar de twee voorgaande bestanden. In de default database (zoals opgegeven in het profiles.yml bestand), maakt dbt één tabel met de naam zzz_game_details en één weergave met de naam zzz_win_loss_records. dbt haalt deze weergave- en tabelnamen op uit hun gerelateerde .sql bestandsnamen.

   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. Voer de volgende SQL-code uit om informatie over de nieuwe weergave weer te geven en om alle rijen in de tabel en weergave te selecteren.

   Als u verbinding maakt met een cluster, kunt u deze SQL-code uitvoeren vanuit een notebook dat is verbonden met het cluster, waarbij SQL wordt opgegeven als de standaardtaal voor het notebook. Als u verbinding maakt met een SQL-warehouse, kunt u deze SQL-code uitvoeren vanuit een query.

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

Stap 3: Tests maken en uitvoeren

In deze stap maakt u tests. Dit zijn beweringen die u maakt over uw modellen. Wanneer u deze tests uitvoert, geeft dbt aan of elke test in uw project is geslaagd of mislukt.

Er zijn twee soorten tests. Schematests, toegepast in YAML, retourneren het aantal records dat geen assertie doorgeeft. Wanneer dit getal nul is, worden alle records doorgegeven, waardoor de tests slagen. Gegevenstests zijn specifieke query's die nul records moeten retourneren die moeten worden doorgegeven.

1. Maak in de map van models het project een bestand met de naam schema.yml met de volgende inhoud. Dit bestand bevat schematests die bepalen of de opgegeven kolommen unieke waarden hebben, niet null zijn, alleen de opgegeven waarden hebben of een combinatie.

   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. Maak in de map van tests het project een bestand met de naam met zzz_game_details_check_dates.sql de volgende SQL-instructie. Dit bestand bevat een gegevenstest om te bepalen of er games plaatsvinden buiten het reguliere seizoen.

   -- 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. Maak in de map van tests het project een bestand met de naam met zzz_game_details_check_scores.sql de volgende SQL-instructie. Dit bestand bevat een gegevenstest om te bepalen of scores negatief zijn of games zijn gekoppeld.

   -- 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. Maak in de map van tests het project een bestand met de naam met zzz_win_loss_records_check_records.sql de volgende SQL-instructie. Dit bestand bevat een gegevenstest om te bepalen of teams negatieve winst- of verliesrecords hadden, meer winst- of verliesrecords hadden dan gespeelde games of meer games speelden dan was toegestaan.

   -- 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. Als de virtuele omgeving is geactiveerd, voert u de dbt test opdracht uit.

   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
   

Stap 4: Opschonen

U kunt de tabellen en weergaven die u voor dit voorbeeld hebt gemaakt, verwijderen door de volgende SQL-code uit te voeren.

Als u verbinding maakt met een cluster, kunt u deze SQL-code uitvoeren vanuit een notebook dat is verbonden met het cluster, waarbij SQL wordt opgegeven als de standaardtaal voor het notebook. Als u verbinding maakt met een SQL-warehouse, kunt u deze SQL-code uitvoeren vanuit een query.

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;

Problemen oplossen

Zie Help op de dbt Labs-website voor informatie over veelvoorkomende problemen bij het gebruik van dbt Core met Azure Databricks en hoe u deze kunt oplossen.

Volgende stappen

Dbt Core-projecten uitvoeren als Azure Databricks-taaktaken. Zie Dbt-transformaties gebruiken in een Azure Databricks-taak.

Aanvullende bronnen

Verken de volgende bronnen op de dbt Labs-website:

• dbt-modellen
• Dbt-projecten testen
• Jinja, een sjabloontaal, gebruiken voor het programmeren van SQL in uw dbt-projecten
• Best practices voor dbt
• dbt Cloud, een gehoste versie van dbt