Dela via

Självstudie: Skapa, köra och testa dbt-modeller lokalt

  • Artikel

Den här självstudien beskriver hur du skapar, kör och testar dbt-modeller lokalt. Du kan också köra dbt-projekt som Azure Databricks-jobbuppgifter. Mer information finns i Använda dbt-transformeringar i ett Azure Databricks-jobb.

Innan du börjar

Om du vill följa den här självstudien måste du först ansluta din Azure Databricks-arbetsyta till dbt Core. Mer information finns i Ansluta till dbt Core.

Steg 1: Skapa och köra modeller

I det här steget använder du din favorittextredigerare för att skapa modeller, som är select-instruktioner som skapar antingen en ny vy (standard) eller en ny tabell i en databas, baserat på befintliga data i samma databas. Den här proceduren skapar en modell baserat på exempeltabellen diamonds från Exempeldatauppsättningar.

Använd följande kod för att skapa den här tabellen.

DROP TABLE IF EXISTS diamonds;

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

  1. I projektets models katalog skapar du en fil med namnet diamonds_four_cs.sql med följande SQL-instruktion. Den här instruktionen väljer endast karats-, klipp-, färg- och tydlighetsinformation för varje romb från diamonds-tabellen. Blocket config instruerar dbt att skapa en tabell i databasen baserat på den här instruktionen.

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

    select carat, cut, color, clarity
from diamonds

    Dricks

    Ytterligare config alternativ som att använda Delta-filformatet och den merge inkrementella strategin finns i Databricks-konfigurationer i dbt-dokumentationen.

  2. I projektets models katalog skapar du en andra fil med namnet diamonds_list_colors.sql med följande SQL-instruktion. Den här instruktionen väljer unika värden från kolumnen colors i tabellen diamonds_four_cs och sorterar resultatet i alfabetisk ordning först till sist. Eftersom det inte finns något config block instruerar den här modellen dbt att skapa en vy i databasen baserat på den här instruktionen.

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

  3. I projektets models katalog skapar du en tredje fil med namnet diamonds_prices.sql med följande SQL-instruktion. Den här instruktionen beräknar rombpriserna efter färg och sorterar resultatet efter genomsnittligt pris från högsta till lägsta. Den här modellen instruerar dbt att skapa en vy i databasen baserat på den här instruktionen.

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

  4. När den virtuella miljön är aktiverad kör dbt run du kommandot med sökvägarna till de tre föregående filerna. I den default databasen (som anges i filen profiles.yml) skapar dbt en tabell med namnet diamonds_four_cs och två vyer med namnet diamonds_list_colors och diamonds_prices. dbt hämtar dessa vy- och tabellnamn från deras relaterade .sql filnamn.

    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. Kör följande SQL-kod för att visa information om de nya vyerna och för att välja alla rader från tabellen och vyerna.

    Om du ansluter till ett kluster kan du köra den här SQL-koden från en notebook-fil som är ansluten till klustret och ange SQL som standardspråk för notebook-filen. Om du ansluter till ett SQL-lager kan du köra den här SQL-koden från en fråga.

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

Steg 2: Skapa och köra mer komplexa modeller

I det här steget skapar du mer komplexa modeller för en uppsättning relaterade datatabeller. Dessa datatabeller innehåller information om en fiktiv sportliga med tre lag som spelar en säsong med sex matcher. Den här proceduren skapar datatabellerna, skapar modellerna och kör modellerna.

  1. Kör följande SQL-kod för att skapa nödvändiga datatabeller.

    Om du ansluter till ett kluster kan du köra den här SQL-koden från en notebook-fil som är ansluten till klustret och ange SQL som standardspråk för notebook-filen. Om du ansluter till ett SQL-lager kan du köra den här SQL-koden från en fråga.

    Tabellerna och vyerna i det här steget börjar med zzz_ för att identifiera dem som en del av det här exemplet. Du behöver inte följa det här mönstret för dina egna tabeller och vyer.

    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. I projektets models katalog skapar du en fil med namnet zzz_game_details.sql med följande SQL-instruktion. Den här instruktionen skapar en tabell som innehåller information om varje spel, till exempel lagnamn och poäng. Blocket config instruerar dbt att skapa en tabell i databasen baserat på den här instruktionen.

    -- 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. I projektets models katalog skapar du en fil med namnet zzz_win_loss_records.sql med följande SQL-instruktion. Den här instruktionen skapar en vy som listar lagets vinst-förlust-poster för säsongen.

    -- 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. När den virtuella miljön är aktiverad kör dbt run du kommandot med sökvägarna till de två föregående filerna. I den default databasen (som anges i profiles.yml-filen) skapar dbt en tabell med namnet zzz_game_details och en vy med namnet zzz_win_loss_records. dbt hämtar dessa vy- och tabellnamn från deras relaterade .sql filnamn.

    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. Kör följande SQL-kod för att visa information om den nya vyn och markera alla rader i tabellen och vyn.

    Om du ansluter till ett kluster kan du köra den här SQL-koden från en notebook-fil som är ansluten till klustret och ange SQL som standardspråk för notebook-filen. Om du ansluter till ett SQL-lager kan du köra den här SQL-koden från en fråga.

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

Steg 3: Skapa och köra tester

I det här steget skapar du tester, vilket är påståenden som du gör om dina modeller. När du kör de här testerna anger dbt om varje test i projektet godkänns eller misslyckas.

Det finns två typer av tester. Schema tester, som tillämpas i YAML, returnerar antalet poster som inte uppfyller ett villkor. När det här talet är noll godkänns alla poster, och därför godkänns testerna. Datatester är specifika frågor som måste returnera noll poster för att skickas.

  1. I projektets models katalog skapar du en fil med namnet schema.yml med följande innehåll. Den här filen innehåller schematester som avgör om de angivna kolumnerna har unika värden, inte är null, bara har de angivna värdena eller en kombination.

    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. I projektets tests katalog skapar du en fil med namnet zzz_game_details_check_dates.sql med följande SQL-instruktion. Den här filen innehåller ett datatest för att avgöra om några spel har inträffat utanför grundserien.

    -- 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. I projektets tests katalog skapar du en fil med namnet zzz_game_details_check_scores.sql med följande SQL-instruktion. Den här filen innehåller ett datatest för att avgöra om några poäng var negativa eller om några spel var bundna.

    -- 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. I projektets tests katalog skapar du en fil med namnet zzz_win_loss_records_check_records.sql med följande SQL-instruktion. Den här filen innehåller ett datatest för att avgöra om några lag hade negativa vinst- eller förlustposter, hade fler vinst- eller förlustrekord än matcher som spelades eller spelade fler matcher än vad som var tillåtet.

    -- 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. Kör kommandot när den virtuella miljön är aktiverad dbt test .

    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

Steg 4: Rensa

Du kan ta bort de tabeller och vyer som du skapade för det här exemplet genom att köra följande SQL-kod.

Om du ansluter till ett kluster kan du köra den här SQL-koden från en notebook-fil som är ansluten till klustret och ange SQL som standardspråk för notebook-filen. Om du ansluter till ett SQL-lager kan du köra den här SQL-koden från en fråga.

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;

Felsökning

Information om vanliga problem när du använder dbt Core med Azure Databricks och hur du löser dem finns i Få hjälp på dbt Labs webbplats.

Nästa steg

Kör dbt Core-projekt som Azure Databricks-jobbuppgifter. Se Använda dbt-transformeringar i ett Azure Databricks-jobb.

Ytterligare resurser

Utforska följande resurser på dbt Labs webbplats: