Рефакторинг устаревшего SQL в dbt

Обновлен

SQL

Advanced

Menu

Введение

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

Для этого нужно выполнить две части: миграцию и рефакторинг. В этом руководстве мы изучим процесс, который поможет нам преобразовать устаревший SQL-код в модульные модели dbt.

При миграции и рефакторинге кода, конечно, важно оставаться организованным. Мы сделаем это, следуя нескольким шагам:

1. Перенесите ваш код 1:1 в dbt
2. Используйте источники dbt вместо ссылок на сырые таблицы базы данных
3. Выберите стратегию рефакторинга
4. Реализуйте группировки CTE и косметическую очистку
5. Разделите преобразования данных на стандартизированные слои
6. Проведите аудит вывода моделей dbt по сравнению с устаревшим SQL

Давайте начнем!

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

Это руководство взято из нового курса dbt Learn On-demand "Рефакторинг SQL для модульности" - если вам интересно, возьмите бесплатный курс по рефакторингу здесь, который включает примеры и практические проекты по рефакторингу. Или для более глубокого изучения миграции DDL и DML из хранимых процедур, обратитесь к руководству Миграция из хранимых процедур.

Миграция вашего существующего SQL-кода

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

Во время рефакторинга вы будете перемещать много логики, но в идеале вы не будете изменять логику. Больше изменений = больше работы по аудиту, поэтому, если вы столкнетесь с чем-то, что хотите исправить, постарайтесь отложить это на другую задачу после рефакторинга! Мы сохраним основную часть нашего аудита на конец, когда завершим реструктуризацию модели от устаревшего к dbt.

Чтобы начать, скопируйте ваш устаревший SQL-запрос в ваш проект dbt, сохранив его в файле .sql в каталоге /models вашего проекта.

Структура папок вашего проекта dbt

После того как вы скопировали его, выполните dbt run, чтобы выполнить запрос и заполнить в вашем хранилище данных.

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

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

Это часто происходит, если вы мигрируете с рабочего процесса хранимых процедур на устаревшей базе данных в dbt + облачное .

Функции, которые вы использовали ранее, могут не существовать, или их синтаксис может немного измениться между SQL-диалектами.

Если вы не мигрируете хранилища данных в данный момент, то вы можете оставить ваш SQL-синтаксис таким же. У вас есть доступ к точно такому же SQL-диалекту внутри dbt, который вы используете при прямом запросе из вашего хранилища.

Создание источников из ссылок на таблицы

Для выполнения запросов из вашего хранилища данных мы рекомендуем создавать источники в dbt вместо прямого запроса к таблице базы данных.

Это позволяет вам вызывать одну и ту же таблицу в нескольких местах с помощью {{ src('my_source', 'my_table') }} вместо my_database.my_schema.my_table.

Мы начинаем здесь по нескольким причинам:

Отчетность о свежести источников

Использование источников разблокирует возможность запускать отчетность о свежести источников, чтобы убедиться, что ваши сырые данные не устарели.

Легкое отслеживание зависимостей

Если вы мигрируете несколько хранимых процедур в dbt, с источниками вы можете увидеть, какие запросы зависят от одних и тех же сырых таблиц.

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

Источники отображаются зеленым цветом в вашем DAG в документации dbt

Привычка аналитики как кода

Источники - это простой способ начать использовать конфигурационные файлы для определения аспектов вашего конвейера преобразования.

sources:
  - name: jaffle_shop
    tables:
      - name: orders
      - name: customers

С несколькими строками кода в файле .yml в подкаталоге /models вашего проекта dbt вы можете теперь контролировать версию того, как ваши источники данных (Snowplow, Shopify и т.д.) сопоставляются с реальными таблицами базы данных.

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

Выбор стратегии рефакторинга

Существует два способа, как вы можете выбрать рефакторинг: на месте или параллельно.

Рефакторинг на месте

Означает, что вы будете работать непосредственно с SQL-скриптом, который перенесли на первом этапе.

Вы переместите его в подкаталог /marts в папке /models вашего проекта и начнете работу.

Плюсы:

• У вас не будет старых моделей, которые нужно удалить после завершения рефакторинга.

Минусы:

• Больше давления, чтобы сделать все правильно с первого раза, особенно если вы ссылались на эту модель из любого BI-инструмента или процесса ниже по потоку.
• Сложнее проводить аудит, так как вы перезаписали вашу модель для сравнения аудита.
• Требуется навигация по коммитам Git, чтобы увидеть, какой код вы изменили.

Параллельный рефакторинг

Означает, что вы скопируете вашу модель в папку /marts и будете работать над изменениями в этой копии.

Плюсы:

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

Минусы:

• У вас будут старые файлы в вашем проекте, пока вы не сможете их устареть - запуск параллельно может казаться дублирующим, и может быть головной болью для управления, если вы мигрируете множество запросов в массовом порядке.

Мы обычно рекомендуем подход параллельного рефакторинга, который мы будем следовать в этом руководстве.

Реализация группировок CTE

После выбора стратегии рефакторинга, вам нужно будет провести косметическую очистку в соответствии с вашими лучшими практиками моделирования данных и начать перемещать код в группировки CTE. Это даст вам фору в переносе SQL-фрагментов из CTE в модульные модели данных dbt.

Что такое CTE?

CTE означает "Общее табличное выражение" (Common Table Expression), которое является временным набором результатов, доступным для использования до конца выполнения SQL-скрипта. Использование ключевого слова with в начале запроса позволяет нам использовать CTE в нашем коде.

Внутри модели, которую мы рефакторим, мы будем использовать 4-частную структуру:

1. 'Импортные' CTE
2. 'Логические' CTE
3. 'Финальное' CTE
4. Простой оператор SELECT

На практике это выглядит так:

with

import_orders as (

    -- запрос только не тестовых заказов
    select * from {{ source('jaffle_shop', 'orders') }}
    where amount > 0
),

import_customers as (
    select * from {{ source('jaffle_shop', 'customers') }}
),

logical_cte_1 as (

    -- выполнение некоторых математических операций на import_orders

),

logical_cte_2 as (

    -- выполнение некоторых математических операций на import_customers
),

final_cte as (

    -- объединение logical_cte_1 и logical_cte_2
)

select * from final_cte

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

1. Импортные CTE

Начнем с наших компонентов и определим сырые данные, которые используются в нашем анализе. Для этого упражнения компоненты - это три источника:

• jaffle_shop.customers
• jaffle_shop.orders
• stripe.payment

Давайте создадим CTE для каждого из них под комментарием Импортные CTE. Эти импортные CTE должны быть только простыми операторами select *, но могут иметь фильтры, если это необходимо.

Мы рассмотрим это позже - на данный момент просто используйте select * from {{ source('schema', 'table') }} для каждого, с соответствующей ссылкой. Затем мы заменим все жестко закодированные ссылки на имена наших импортных CTE.

2. Логические CTE

Логические CTE содержат уникальные преобразования, используемые для генерации конечного продукта, и мы хотим разделить их на логические блоки. Чтобы определить наши логические CTE, мы будем следовать за подзапросами по порядку.

Если имеет вложенные подзапросы, мы захотим продолжать двигаться вниз, пока не дойдем до первого уровня, затем извлечь подзапросы по порядку как CTE, возвращаясь к окончательному оператору select.

Назовите эти CTE так, как был назван псевдоним подзапроса - вы можете переименовать его позже, но на данный момент лучше вносить как можно меньше изменений.

Если скрипт особенно сложный, стоит пройтись по нему, когда вы закончите извлекать подзапросы, и следовать за CTE, чтобы убедиться, что они происходят в порядке, который имеет смысл для конечного результата.

3. Финальное CTE

Предыдущий процесс обычно приводит к оставшемуся оператору select в конце - этот оператор select можно переместить в собственное CTE, называемое финальным CTE, или назвать его так, чтобы это было понятно другим. Это CTE определяет конечный продукт модели.

4. Простой оператор SELECT

После того как вы переместили все в CTE, вам нужно будет написать select * from final (или что-то подобное, в зависимости от имени вашего финального CTE) в конце модели.

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

Для получения дополнительной информации о CTE, ознакомьтесь с стилевым руководством dbt Labs.

Перенос CTE в отдельные модели данных

Вместо того чтобы оставлять наш SQL-код в одном длинном SQL-файле, мы теперь начнем разделять его на модульные и повторно используемые модели данных dbt.

Внутри dbt Labs мы следуем примерно этой технике моделирования данных и структурируем наши проекты dbt соответственно.

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

Определение моделей стадий

Чтобы определить наши модели стадий, мы хотим взглянуть на вещи, которые мы импортировали в наших импортных CTE.

Для нас это клиенты, заказы и платежи. Мы хотим взглянуть на преобразования, которые могут происходить в каждом из этих источников без необходимости объединения друг с другом, и затем мы хотим сделать из них компоненты, чтобы они могли быть нашими строительными блоками для дальнейшей разработки.

CTE или промежуточные модели

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

Мы начнем с использования CTE, но когда модель становится сложной или может быть разделена на повторно используемые компоненты, вы можете рассмотреть промежуточную модель.

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

Финальная модель

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

Аудит модели данных

Мы захотим провести аудит наших результатов, используя пакет dbt audit_helper.

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

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

Готовы к практике рефакторинга?

Перейдите к бесплатному курсу по запросу, Рефакторинг из процедурного SQL в dbt для более глубокого примера рефакторинга + практической задачи по рефакторингу, чтобы проверить свои навыки.

Вопросы по этому руководству или курсу? Оставьте сообщение в #learn-on-demand в dbt Community Slack.