Руководство по созданию, запуску и тестированию моделей dbt локально

Статья
08/02/2024

В этом руководстве описано, как создавать, запускать и тестировать модели субд локально. Вы также можете запускать проекты dbt в качестве задач задания Azure Databricks. Дополнительные сведения см. в разделе "Использование преобразований dbt" в задании Azure Databricks.

Перед началом работы

Чтобы следовать этому руководству, необходимо сначала подключить рабочую область Azure Databricks к dbt Core. Дополнительные сведения см. в разделе Подключение к dbt Core.

Шаг 1. Создание и выполнение моделей

На этом шаге вы с помощью любого текстового редактора будете создавать модели, которые представляют собой инструкции select, создающие новое представление (по умолчанию) или новую таблицу в базе данных на основе уже существующей в этой базе информации. Эта процедура создает модель на основе образца diamonds таблицы из наборов данных Sample.

Чтобы создать эту таблицу, используйте следующий код.

DROP TABLE IF EXISTS diamonds;

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

В каталоге проекта models создайте файл с именем diamonds_four_cs.sql, используя приведенную ниже инструкцию SQL. Эта инструкция выбирает только сведения о весе, огранке, цвете и прозрачности для каждого бриллианта из таблицы diamonds. Блок config указывает dbt создать таблицу в базе данных на основе этой инструкции.
```
{{ config(
  materialized='table',
  file_format='delta'
) }}
```
```
select carat, cut, color, clarity
from diamonds
```
Совет

Дополнительные config параметры, такие как использование формата разностного файла и merge добавочной стратегии, см . в документации по dbt конфигурации Databricks.
В каталоге проекта models создайте второй файл с именем diamonds_list_colors.sql, используя приведенную ниже инструкцию SQL. Эта инструкция выбирает уникальные значения из столбца colors в таблице diamonds_four_cs, упорядочивая результаты по алфавиту от первого к последнему. Поскольку блок config отсутствует, эта модель указывает dbt создать представление в базе данных на основе этой инструкции.
```
select distinct color
from {{ ref('diamonds_four_cs') }}
sort by color asc
```
В каталоге проекта models создайте третий файл с именем diamonds_prices.sql, используя приведенную ниже инструкцию SQL. Эта инструкция усредняет цену бриллиантов по цвету и сортирует результаты по среднему значению цены от большего к меньшему. Эта модель указывает dbt создать представление в базе данных на основе этой инструкции.
```
select color, avg(price) as price
from diamonds
group by color
order by price desc
```

Активируя виртуальную среду, выполните команду dbt run с путями к трем предыдущим файлам. В базе данных default (указанной в файле profiles.yml) dbt создает одну таблицу с именем diamonds_four_cs и два представления с именами diamonds_list_colors и diamonds_prices. dbt получает имена этих представлений и таблиц из связанных с ними имен файлов .sql.

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

Выполните следующий код SQL, чтобы вывести сведения о новых представлениях и выбрать все строки из таблицы и представлений.

При подключении к кластеру этот код SQL можно запустить из подключенной к кластеру записной книжки, указав для записной книжки SQL в качестве языка по умолчанию. При подключении к хранилищу SQL этот код SQL можно запустить из запроса.

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. Создание и выполнение более сложных моделей

На этом шаге вы создадите более сложные модели для набора связанных таблиц данных. Эти таблицы данных содержат сведения о вымышленной спортивной лиге из трех команд, проводящих сезон из шести матчей. Эта процедура создает таблицы данных, создает модели и запускает их.

Выполните приведенный ниже код SQL, чтобы создать необходимые таблицы данных.

Названия таблиц и представлений на этом шаге начинаются с символов zzz_, которые помогают идентифицировать их как часть этого примера. Использовать этот шаблон в собственных таблицах и представлениях нет необходимости.

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

В каталоге проекта models создайте файл с именем zzz_game_details.sql, используя приведенную ниже инструкцию SQL. Эта инструкция создает таблицу, которая содержит подробные сведения о каждом матче, например названия команд и итоговый счет. Блок config указывает dbt создать таблицу в базе данных на основе этой инструкции.

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

В каталоге проекта models создайте файл с именем zzz_win_loss_records.sql, используя приведенную ниже инструкцию SQL. Эта инструкция создает представление со списком побед и поражений команд в течение сезона.

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

Активируя виртуальную среду, выполните команду dbt run с путями к двум предыдущим файлам. В базе данных default (указанной в файле profiles.yml) dbt создает одну таблицу с именем zzz_game_details и одно представление с именем zzz_win_loss_records. dbt получает имена этих представлений и таблиц из связанных с ними имен файлов .sql.

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

Выполните приведенный ниже код SQL, чтобы вывести сведения о новом представлении и выбрать все строки из таблицы и представления.

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. Создание и запуск тестов

На этом шаге вы создадите тесты, которые являются утверждениями о моделях. При выполнении этих тестов в проекте dbt будет сообщать об их результатах.

Тесты бывают двух видов. Тесты схемы на языке YAML возвращают количество записей, которые не прошли утверждение. Если это число равно нулю, это означает, что все записи успешно утверждены, поэтому тесты считаются пройденными. Тесты данных — это конкретные запросы, которые должны возвращать нулевое количество записей (в этом случае считается, что тест пройден).

В каталоге проекта models создайте файл с именем schema.yml и приведенным ниже содержимым. Этот файл содержит тесты схемы, которые проверяют выполнение следующих условий: указанные столбцы содержат уникальные значения, не содержат значений null, содержат только указанные значения или выполняется сочетание этих условий.

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

В каталоге проекта tests создайте файл с именем zzz_game_details_check_dates.sql, используя приведенную ниже инструкцию SQL. Этот файл содержит тест данных, проверяющий наличие матчей вне рамок сезона.
```
-- 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'
```
В каталоге проекта tests создайте файл с именем zzz_game_details_check_scores.sql, используя приведенную ниже инструкцию SQL. Этот файл содержит тест данных, проверяющий наличие отрицательных значений счета или ничьих в матчах.
```
-- 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
```
В каталоге проекта tests создайте файл с именем zzz_win_loss_records_check_records.sql, используя приведенную ниже инструкцию SQL. Этот файл содержит тест данных, проверяющий, есть ли у команд отрицательные значения числа побед или поражений, не превышает ли число побед и поражений количество сыгранных матчей и не превышает ли число матчей допустимое количество.
```
-- 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
```

После активации виртуальной среды выполните 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

Шаг 4. Очистка

Чтобы удалить таблицы и представления, созданные для этого примера, выполните указанный ниже код SQL.

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;

Устранение неполадок

Сведения о распространенных проблемах при использовании dbt Core с Azure Databricks и их устранении см. в статье "Получение справки " на веб-сайте dbt Labs.

Следующие шаги

Запустите проекты dbt Core в качестве задач задания Azure Databricks. См. статью "Использование преобразований dbt" в задании Azure Databricks.

Дополнительные ресурсы

Ознакомьтесь со следующими ресурсами на веб-сайте dbt Labs:

Поделиться через

Руководство по созданию, запуску и тестированию моделей dbt локально

Перед началом работы

Шаг 1. Создание и выполнение моделей

Шаг 2. Создание и выполнение более сложных моделей

Шаг 3. Создание и запуск тестов

Шаг 4. Очистка

Устранение неполадок

Следующие шаги

Дополнительные ресурсы

Обратная связь

Дополнительные ресурсы