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

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

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

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

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

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

Совет

Если в вашем рабочем пространстве используется Unity Catalog с бессерверным хранилищем SQL, Databricks рекомендует создавать производственные модели, которые должны поддерживаться в актуальном состоянии, как материализованные представления или потоковые таблицы, а не как таблицы или представления. Эти материализации обновляются постепенно, вместо того чтобы пересчитывать полный результат при каждом запуске. См. Шаг 4. Поддерживайте актуальность моделей с помощью материализованных представлений и потоковых таблиц.

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

DROP TABLE IF EXISTS diamonds;

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

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

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

    select carat, cut, color, clarity
from diamonds

    Совет

    Сведения о дополнительных config параметрах, таких как использование формата файлов Delta и merge инкрементной стратегии, см. в разделе Databricks configurations в документации dbt.

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

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

  3. В каталоге проекта models создайте третий файл с именем diamonds_prices.sql и следующим SQL-запросом. Эта инструкция усредняет цену бриллиантов по цвету и сортирует результаты по среднему значению цены от большего к меньшему. Эта модель указывает dbt создать представление в базе данных на основе этой инструкции.

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

  4. Активируя виртуальную среду, выполните команду 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

  5. Выполните следующий код 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. Создание и выполнение более сложных моделей

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

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

    При подключении к кластеру этот код SQL можно запустить из подключенной к кластеру записной книжки, указав для записной книжки SQL в качестве языка по умолчанию. При подключении к хранилищу SQL этот код 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     |
-- +---------+---------------+

  2. В директории проекта 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

  3. В директории проекта 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

  4. Активируя виртуальную среду, выполните команду 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

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

    При подключении к кластеру этот код SQL можно запустить из подключенной к кластеру записной книжки, указав для записной книжки SQL в качестве языка по умолчанию. При подключении к хранилищу SQL этот код 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, возвращают количество записей, которые не проходят утверждение. Если это число равно нулю, это означает, что все записи успешно утверждены, поэтому тесты считаются пройденными. Тесты данных — это конкретные запросы, которые должны возвращать нулевое количество записей (в этом случае считается, что тест пройден).

  1. В каталоге проекта 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

  2. В директории проекта 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'

  3. В директории проекта 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

  4. В директории проекта 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

  5. После активации виртуальной среды выполните 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: Поддерживайте актуальность моделей с помощью материализованных представлений и потоковых таблиц

Когда модель должна оставаться актуальной по мере поступления новых данных, материализуйте её как материализованный вид или потоковую таблицу вместо таблицы или представления. Материализованный вид постепенно обновляет результаты своего запроса, поэтому Azure Databricks обновляет только то, что изменилось, вместо того чтобы пересчитывать весь результат при каждом запуске, что делает материализованные представления хорошими вариантами для агрегирования и подачи данных в дашборды. Потоковая таблица инкрементально загружает исходные данные, в которые данные только добавляются, или потоковые исходные данные, например файлы, поступающие в облачное хранилище, без повторной обработки уже прочитанных строк. Оба проекта поддерживаются трубопроводами Lakeflow и являются рекомендуемыми материализациями для производственных преобразований, которые должны оставаться актуальными.

Note

Для выполнения этого шага требуется рабочее пространство с поддержкой Unity Catalog и бессерверным хранилищем SQL. Материализация materialized_view встроена в dbt, а streaming_table предоставляется адаптером dbt-databricks.

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

    {{ config(
  materialized='materialized_view'
) }}

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

  2. В директории проекта models создайте файл под именем diamonds_raw_stream.sql с следующим SQL-запросом. Блок config инструктирует dbt создать таблицу потоков, которая постепенно вводит исходный diamonds CSV-файл. read_files Функция с таблицным значением считывает файл, а ключевое STREAM слово обрабатывает только новые данные при каждом обновлении.

    {{ config(
  materialized='streaming_table'
) }}

    select * from stream read_files(
  '/databricks-datasets/Rdatasets/data-001/csv/ggplot2/diamonds.csv',
  format => 'csv',
  header => true
)

  3. Активируя виртуальную среду, выполните команду dbt run с путями к двум предыдущим файлам.

    dbt run --model models/diamonds_prices_mv.sql models/diamonds_raw_stream.sql

При обновлении этих моделей имейте в виду следующие особенности их поведения:

  • Изменение запроса в потоковой таблице не выполняет повторную обработку данных, которые уже были загружены. DBT использует CREATE OR REFRESH, который применяет обновлённый запрос только к будущим строкам. Чтобы повторно обработать доступные исходные данные, запустите команду с параметром --full-refresh.
  • Изменение конфигурации материализованного представления требует, чтобы dbt удалил и заново создал его, за исключением изменений расписания.
  • Теги, заданные с помощью databricks_tags, нельзя удалить через dbt после их применения. Чтобы удалить тег, используйте Azure Databricks напрямую или пост-хук.

Полный список поддерживаемых конфигураций, таких как запланированные обновления, разбиение на разделы и фильтры строк, см. раздел Materialized views and streaming table в документации dbt.

Шаг 5: Уборка

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

При подключении к кластеру этот код SQL можно запустить из подключенной к кластеру записной книжки, указав для записной книжки SQL в качестве языка по умолчанию. При подключении к хранилищу SQL этот код 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 MATERIALIZED VIEW diamonds_prices_mv;
DROP TABLE diamonds_raw_stream;
DROP TABLE diamonds;
DROP TABLE diamonds_four_cs;
DROP VIEW diamonds_list_colors;
DROP VIEW diamonds_prices;

Note

Если вы не выполнили Шаг 4, опустите операторы DROP MATERIALIZED VIEW diamonds_prices_mv и DROP TABLE diamonds_raw_stream.

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

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

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

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

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

  • Last updated on