Oktatóanyag: Dbt-modellek helyi létrehozása, futtatása és tesztelése

Ez az oktatóanyag bemutatja, hogyan hozhat létre, futtathat és tesztelhet dbt-modelleket helyileg. A dbt-projektek Azure Databricks-feladatfeladatként is futtathatók. További információ: Dbt-átalakítások használata a Lakeflow-feladatokban.

Előkészületek

Az oktatóanyag követéséhez először csatlakoztatnia kell az Azure Databricks-munkaterületet a DBT Core-hoz. További információ: Csatlakozás a dbt Core-hoz.

1. lépés: Modellek létrehozása és futtatása

Ebben a lépésben a kedvenc szövegszerkesztőjével hozhat létre modelleket, amelyek olyan utasítások, amelyek select új nézetet (alapértelmezett) vagy új táblát hoznak létre egy adatbázisban, ugyanazon adatbázis meglévő adatai alapján. Ez az eljárás létrehoz egy modellt a Sample datasets mintatáblája alapjándiamonds.

A tábla létrehozásához használja az alábbi kódot.

DROP TABLE IF EXISTS diamonds;

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

  1. A projekt models könyvtárában hozzon létre egy diamonds_four_cs.sql nevű fájlt a következő SQL-utasítással. Ez az utasítás a diamonds táblából minden egyes gyémánt esetében csak a karát-, csiszolás-, szín- és tisztasági adatokat választja ki. A config blokk arra utasítja a dbt-et, hogy hozzon létre egy táblát az adatbázisban ezen utasítás alapján.

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

    select carat, cut, color, clarity
from diamonds

    Tipp

    További config lehetőségek, például a Delta fájlformátum és a merge növekményes stratégia használata, lásd a Databricks konfigurációit a dbt dokumentációjában.

  2. A projekt models könyvtárában hozzon létre egy második fájlt diamonds_list_colors.sql néven az alábbi SQL-utasítással. Ez az utasítás egyedi értékeket választ ki a colorsdiamonds_four_cs táblázat oszlopából, és elsőként betűrendbe rendezi az eredményeket. Mivel nincs config blokk, ez a modell arra utasítja a DBT-t, hogy hozzon létre egy nézetet az adatbázisban ezen utasítás alapján.

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

  3. A projekt models könyvtárában hozzon létre egy harmadik fájlt diamonds_prices.sql névvel az alábbi SQL-utasítással. Ez az utasítás a gyémántárakat szín szerint átlagozza, és az eredményeket az átlagár alapján rendezi a legmagasabbtól a legalacsonyabbig. Ez a modell arra utasítja a dbt-t, hogy ezen utasítás alapján hozzon létre nézetet az adatbázisban.

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

  4. A virtuális környezet aktiválásával futtassa a dbt run parancsot az előző három fájl elérési útjával. Az default adatbázisban (a profiles.yml fájlban megadottak szerint) a dbt létrehoz egy diamonds_four_cs nevű táblát, valamint két, diamonds_list_colors és diamonds_prices nevű nézetet. A dbt ezeket a nézet- és táblaneveket a hozzájuk tartozó .sql fájlok nevéből nyeri.

    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. Futtassa az alábbi SQL-kódot az új nézetek adatainak listázásához, valamint a tábla és nézetek összes sorának kijelöléséhez.

    Ha fürthöz csatlakozik, ezt az SQL-kódot egy, a fürthöz csatlakoztatott jegyzetfüzetben futtathatja, és a jegyzetfüzet alapértelmezett nyelveként az SQL-t adhatja meg. Ha SQL-raktárhoz csatlakozik, egy lekérdezésből futtathatja ezt az SQL-kódot.

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

2. lépés: Összetettebb modellek létrehozása és futtatása

Ebben a lépésben összetettebb modelleket hoz létre a kapcsolódó adattáblák halmazához. Ezek az adattáblák egy fiktív, háromcsapatos sportligáról tartalmaznak információkat, amelyben a csapatok egy hat mérkőzésből álló szezont játszanak. Ez az eljárás létrehozza az adattáblákat, létrehozza a modelleket, és futtatja a modelleket.

  1. Futtassa a következő SQL-kódot a szükséges adattáblák létrehozásához.

    Ha egy fürthöz csatlakozik, ezt az SQL-kódot egy, a fürthöz csatlakozó jegyzetfüzetből futtathatja, ha az SQL-t állítja be a jegyzetfüzet alapértelmezett nyelveként. Ha SQL-raktárhoz csatlakozik, egy lekérdezésből futtathatja ezt az SQL-kódot.

    Az ebben a lépésben szereplő táblák és nézetek zzz_ kezdetűek, hogy a példa részeként azonosíthatók legyenek. Nem kell ezt a mintát követnie a saját táblái és nézetei esetében.

    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. A projekt models könyvtárában hozzon létre egy zzz_game_details.sql nevű fájlt a következő SQL-utasítással. Ez az utasítás létrehoz egy táblázatot, amely minden játék részleteit tartalmazza, például a csapatneveket és a pontszámokat. A config blokk arra utasítja a dbt-et, hogy hozzon létre egy táblát az adatbázisban ezen utasítás alapján.

    -- 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. A projekt models könyvtárában hozzon létre egy zzz_win_loss_records.sql nevű fájlt a következő SQL-utasítással. Ez az utasítás létrehoz egy nézetet, amely az adott szezon csapatainak győzelmi-vereségi mérlegét tartalmazza.

    -- 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. A virtuális környezet aktiválásával futtassa a dbt run parancsot az előző két fájl elérési útjával. A default adatbázisban (a profiles.yml fájlban megadottak szerint) a dbt létrehoz egy zzz_game_details nevű táblát és egy zzz_win_loss_records nevű nézetet. A dbt ezeket a nézet- és táblaneveket a hozzájuk tartozó .sql fájlnevekből veszi.

    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. Futtassa az alábbi SQL-kódot az új nézet adatainak listázásához, valamint a tábla és nézet összes sorának kijelöléséhez.

    Ha fürthöz csatlakozik, ezt az SQL-kódot egy, a fürthöz csatlakoztatott jegyzetfüzetből futtathatja, ha az SQL-t állítja be a jegyzetfüzet alapértelmezett nyelveként. Ha SQL-raktárhoz csatlakozik, egy lekérdezésből futtathatja ezt az SQL-kódot.

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

3. lépés: Tesztek létrehozása és futtatása

Ebben a lépésben teszteket hoz létre, vagyis a modellekről megfogalmazott állításokat. A tesztek futtatásakor a dbt jelzi, hogy a projekt egyes tesztjei sikeresek vagy sikertelenek-e.

Kétféle teszt létezik. A YAML-ben alkalmazott sématesztek az olyan rekordok számát adják vissza, amelyek nem adnak meg egy helyességi feltételt. Ha ez a szám nulla, minden rekord megfelel, ezért a tesztek sikeresen lefutnak. Az adattesztek olyan lekérdezések, amelyeknek nulla rekordot kell visszaadni.

  1. A projekt könyvtárában models hozzon létre egy fájlt schema.yml az alábbi tartalommal. Ez a fájl sémateszteket tartalmaz, amelyek meghatározzák, hogy a megadott oszlopok egyedi értékekkel rendelkeznek-e, nem null értékűek, csak a megadott értékekkel vagy kombinációkkal rendelkeznek- e.

    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. A projekt tests könyvtárában hozzon létre egy zzz_game_details_check_dates.sql nevű fájlt a következő SQL-utasítással. Ez a fájl egy adattesztet tartalmaz annak megállapítására, hogy az alapszakaszon kívül történtek-e játékok.

    -- 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. A projekt tests könyvtárában hozzon létre egy zzz_game_details_check_scores.sql nevű fájlt a következő SQL-utasítással. Ez a fájl tartalmaz egy adattesztet annak megállapítására, hogy a pontszámok negatívak voltak-e, vagy bármilyen játék meg volt-e kötve.

    -- 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. A projekt tests könyvtárában hozzon létre egy zzz_win_loss_records_check_records.sql nevű fájlt a következő SQL-utasítással. Ez a fájl egy adattesztet tartalmaz annak megállapítására, hogy bármely csapat negatív győzelmi vagy veszteségrekordokkal rendelkezik-e, több győzelmi vagy veszteségrekordot adott-e meg, mint a lejátszott játékok, vagy több játékot játszott, mint amennyit engedélyezett.

    -- 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. A virtuális környezet aktiválásával futtassa a dbt test parancsot.

    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

4. lépés: Tisztítás

A példához létrehozott táblákat és nézeteket az alábbi SQL-kód futtatásával törölheti.

Ha fürthöz csatlakozik, ezt az SQL-kódot egy, a fürthöz csatlakoztatott jegyzetfüzetből futtathatja, ha az SQL-t állítja be a jegyzetfüzet alapértelmezett nyelveként. Ha SQL-raktárhoz csatlakozik, egy lekérdezésből futtathatja ezt az SQL-kódot.

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;

Hibaelhárítás

A dbt Core Azure Databricks-lel való használatakor felmerülő gyakori problémákról és azok megoldásáról a Dbt Labs webhelyén talál segítséget .

Következő lépések

Dbt Core-projektek futtatása Azure Databricks-feladatfeladatként. Lásd: Adatbázis-átalakítások használata a Lakeflow-feladatokban.

További erőforrások

A dbt Labs webhelyén a következő erőforrásokat ismerheti meg:

  • Last updated on