На днях смотрел вебинар OWOX, где Андрей Осипов (веб-аналитик, автор блога web-analytics.me и лектор Школы веб-аналитики Андрея Осипова) рассказал о своем опыте использования dbt. Говорил о том, кому будет полезен инструмент и какие проблемы решает, а самое главное — как не свихнуться со сложной иерархией таблиц и быть уверенным, что все данные считаются корректно. Я решил расшифровать вебинар в статью, потому что так удобнее возвращаться к информации, а она тут, поверьте, того стоит.
Зачем нужен dbt
Зачем нужен еще один инструмент для управления SQL-запросами? Ведь у нас есть Google BigQuery, и вообще в Google Cloud много различных механизмов, которые могут решать задачи по формированию таблиц, например scheduled queries.
Если у вас маленький проект, пара источников данных (таблица с событиями плюс расходы) и вам нужно построить 2-4 отчета, то структура расчета этих таблиц будет довольно простой.
Но если мы говорим о реальном проекте, то все может выглядеть как на картинке ниже.
Здесь мы видим большое количество таблиц с исходными данными. Это могут быть данные из рекламных систем, транзакции из CRM, информация о пользователях, расширенные данные о продуктах, о ваших мерчантах и другие данные, которые попадают в Google BigQuery разными путями.
Первая задача, которая возникает в таком случае — автоматизация отчетности. Нужно все данные собрать, посчитать, проверить, валидировать и построить отчеты, который отвечали бы на ваши вопросы.
Чтобы это сделать, необходимо связать исходные таблицы с данными между собой. То есть финальная таблица формируется из десятка других, которые, в свою очередь — из третьих.
При такой сложной структуре стандартные механизмы управления (типа scheduled queries) не подходят по нескольким причинам:
Зависимости. Каждый scheduled query нужно запускать в определенное время, то есть у вас должна быть четкая иерархия запуска расчета таблиц. Нельзя, чтобы результирующая таблица считалась раньше, чем исходная. К примеру, в 5 утра у вас формируется отчет. После этого происходит какой-то сбой, и только в 7 утра данные о нем попадают в исходную таблицу. В результате вы теряете данные за вчера и нужно пересчитывать весь проект. А если у вас десятка два-три запросов, то пересчет займет минут 40.
Отсутствие документации. В теории можно в том же Google Docs описывать каждую таблицу, правила ее формирования, поля и т.д. Но это занимает много времени, никак не автоматизировано и по факту мало кто это делает. В итоге у вас есть большая сложная структура и совершенно непонятно, как она работает. При возникновении ошибки будет сложно найти ее причину.
Тестирование. Данные, которые попадают в исходные таблицы, не всегда корректны, а в Google BigQuery нет встроенных инструментов для их тестирования. Например, у вас была таблица с заказами. Потом в нее добавили рейтинг, который пользователи могут менять. Из-за этого могут дублироваться транзакции. То есть в таблице будет две записи с одинаковой транзакцией, но с разными timestamp и рейтингами.
Много одинаковых кусков SQL. Когда у вас несколько финальных таблиц, которые генерируются из базовых, одни и те же агрегации происходят много раз. Это сильно увеличивает стоимость использования BigQuery. На нашем вебинаре Андрей Осипов поделился примером, как с помощью dbt ему удалось снизить стоимость использования GBQ в 20 раз для одного из проектов, с которым он работал. Только за счет того, что обращение к базовой таблице с событиями происходило всего один раз.
Для решения описанных проблем есть большое количество похожих инструментов. В этой статье мы рассмотрим, как с ними справляется dbt.
Что такое dbt (data build tool)
Инструментов для управления SQL довольно много, все они похожи. Почему стоит выбрать dbt? Во-первых, о нем больше всего информации, во-вторых, он очень активно развивается, постоянно добавляется новый функционал. На наш взгляд, из всех доступных инструментов он самый юзабельный и универсальный.
Как выглядит процесс обработки данных:
Схема из презентации dbt.
На схеме мы видим:
Источники данных.
Сервисы, которые достают данные из этих источников и складывают в хранилище. Это могут быть Cloud Functions, Cloud Run, OWOX BI Pipeline и др.
Хранилище, куда мы складываем данные и где потом их нужно преобразовать: объединить с другими данными, проверить, валидировать.
После обработки мы можем отправлять данные в инструменты визуализации, BI-системы или использовать как-либо еще.
dbt — это инструмент, который трансформирует сырые исходные данные, проверяет и валидирует. Он делает это на базе собственной логики, а не на базе scheduled queries.
Структура dbt
Фактически dbt состоит из двух сущностей: модель и файл конфигурации.
Модель — это сам запрос, то есть отдельный файл, на базе которого будут формироваться view или table.
Модель (.sql) — единица трансформации, выраженная SELECT-запросом.
Также есть отдельный конфигурационный файл, из которого потом генерируется документация. Все важные моменты, которые необходимо учесть при формировании таблиц (например, описание полей) вы можете прописать в descriptions в этом файле.
Вы можете настроить все таким образом, чтобы дескрипшн или лейблы таблицы считывались из этого конфигурационного файла и при формировании таблицы записывались прямо в BigQuery. Это удобно, так как вы получаете и документацию, и краткое описание таблицы внутри GBQ.
Файл конфигурации (.yml) — параметры, настройки, тесты, документация.
dbt CLI
dbt поставляется в двух версиях: консоль и cloud. То есть инструмент может быть как локальным, так и размещаться в Google Cloud и быть полноценным микросервисом.
Как в любой консоли здесь есть набор команд. Базовая — это dbt run, которая как раз просчитывает и формирует подряд с учетом зависимостей все ваши модели. Как результат она пишет количество обработанных байтов или строк. Кроме dbt run, есть dbt test, чтобы проверить, корректно ли все посчиталось, и другие команды.
Также у вас есть возможность привязать свой dbt проект к Cloud Source Repositories или GitHub. То есть фактически все будет вертеться вокруг вашего cloud-проекта, и сам dbt будет там работать.
Как работать с dbt консолью:
В текстовом редакторе, например Atom, вы формируете новые таблички, локально рассчитываете, проверяете, корректно ли все посчиталось. В случае каких-либо проблем можете все это дело поправить.
После этого используете команду git push для выгрузки содержимого локального репозитория в удаленный репозиторий. И сам dbt с вашими новыми моделями через Cloud Build билдятся в новый контейнер и запускаются.
Процесс работы довольно удобный, контролируемый и мы всегда можем увидеть, если что-то пошло не так.
dbt Cloud
Если использовать консоль для вас сложно или нецелесообразно, вы можете попробовать dbt Cloud. По сути это веб-интерфейс той же самой консоли. Простой сайт, на котором можно вносить правки в свои модели, добавлять макросы. Вы можете запускать как весь проект, так и отдельные модели. Здесь же хостится документация по проекту. Регистрация для первого пользователя бесплатна, для каждого следующего — $50 в месяц.
Функционал dbt
Зависимости (Refs…)
О них мы говорили в начале статьи — у вас должна быть четкая иерархия запуска расчета таблиц. dbt позволяет указывать эти зависимости прямо внутри каждого запроса. На базе этих зависимостей, во-первых, происходит расчет подряд необходимых табличек, а во-вторых, строится прямой ациклический граф (Directed Acyclic Graph).
Прямой ациклический граф (Directed Acyclic Graph)
Это част�� документации, которая также формируется самим dbt. Благодаря этому графу вы можете посмотреть, как именно формируются ваши таблицы. Этого, конечно, очень не хватает в Google BigQuery. Потому что, если в вашем проекте больше 2-3 датасетов с несколькими таблицами, довольно сложно разобраться, как они все формируются.
В dbt вы можете кликнуть на конкретную таблицу и посмотреть, из каких таблиц она собирается. На рисунке совсем простая схема. Но чем она больше, тем удобнее работать с нею благодаря графу. При каком-то дебаге можно идти сверху вниз и на каждом этапе проверять, где возникла проблема.
Кроме того, вы можете запускать не весь проект, а только какую-то часть. Например, можно пересчитать одну табличку в графе (и все, из которых она формируется), не изменяя вашу конечную таблицу. И это классно, потому что большой проект может считаться несколько минут.
Шаблоны (Loops)
Вторая полезная вещь — это использование Jinja. Это такой язык шаблонов. Шаблонизатор, в котором есть циклы, переменные и все остальное. Их можно указывать для похожих сущностей.
Например, в Google Analytics есть параметры событий, и у вас есть набор кастомных параметров, которые описываются в событии. Чтобы каждый раз не прописывать для каждого параметра одинаковые куски кода, в которых меняется одно-два значения, вы можете все э��о сделать в цикле. Список необходимых параметров можно указать в самом запросе или задать переменные в проекте.
Это удобно, потому что при создании нового проекта вы можете в переменных самого dbt прописать все нужные значения событий, параметров событий и т.д. Вы один раз прописываете в шаблонах все необходимые вычисления — и та же самая модель, которая работала на старом проекте, сработает и на новом. Это значительно экономит время, если у нас много повторяющихся задач. Сам запрос становится меньше, а значит, его легче читать и проверять.
Переменные (Variables)
Переменные в dbt двух типов: можно создать переменную в рамках всего проекта или в рамках конкретной модели.
Макросы (Macros)
Это кусочки запроса, которые можно прописать отдельно и сложить в папку «Macros». Они будут выполнять полезные преобразования данных. Например, в каждой модели вы можете прописать, в какой конкретно датасет будет складываться таблица. И не важно, в какой папке это датасет у вас лежит.
Благодаря макросам вы можете преобразовывать базовые вещи, подстраивать dbt под себя и формировать результат расчета на порядок проще и удобнее.
Пакеты макросов
Кроме того, есть библиотеки макросов, которые содержат массу полезных пакетов для привычных операций по работе с датами, геолокацией, логированием и т.д. То есть это готовые блоки, которые делают что-то полезное с вашими моделями.
Incremental
Еще одна полезная функция, которая значительно экономит ресурсы.
Допустим, вы не используете dbt. Когда вы обращаетесь в BigQuery к своей партиционной таблице, например делаете select * from [название вашей таблицы], то вы обращаетесь ко всему датасету. Если у вас немного трафика, GA 4 или стриминг OWOX BI настроены недавно, то вы обработаете небольшое количество данных. Если же у вас много данных и большие таблицы, то каждый такой запрос будет дорого стоить.
По факту инкрементальная модель dbt позволяет вам сначала удалять данные в формируемой таблице (например, по дате или по order_id), а потом записывать новые.
Например, в GA 4 данные за вчера, которые образовались в табличке events, еще не полностью готовы. Если вы посмотрите на логи, то вы увидите, что система перезатирает данные. Она формирует из таблицы intraday таблицу events и перезаписывает данные еще на два дня назад.
Используя инкрементальные модели, вы можете нивелировать этот фактор и на каждый день перезаписывать вчера, позавчера и позапозавчера. Вы не работаете со всем датасетом, а только с определенной частью. Соответственно данные, которые у вас после этого будут формироваться (какие-нибудь агрегированные таблицы и отчеты), также могут работать по этой логике. То есть у вас на каждый день будет просчет не только вчерашнего дня, не всего периода, а только последних трех дней.
Обычно объем данных, которые при этом формируются, не очень большой. Это позволяет вам работать с меньшим количеством данных и значительно экономит ресурсы и деньги.
Тесты
По умолчанию при формировании таблиц вы можете тестировать данные, которые в них появляются, а также настроить отправку уведомлений об обнаруженных ошибках.
Примеры тестов, которые можно проводить в dbt:
Not Null.
Unique.
Reference Integrity — ссылочная целостность (например, customer_id в таблице orders соответствует id в таблице customers).
Соответствие списку допустимых значений.
Custom data tests.
Тестирование — полезная штука, потому что данных все больше и больше, и контролировать их вручную все сложнее.
Документация
dbt позволяет формировать документацию по всем вашим моделям. Это descriptions, описания полей, тесты, как формируются таблицы, возможность посмотреть сам запрос и то, что получается после обработки, то есть вызывается непосредственно в Google BigQuery.
Такая документация очень упрощает ввод в курс дела нового сотрудника или поиск ошибок и их причин.
DEV — TEST — PROD
В dbt есть возможность разделения расчетов на разные среды. Если ваш проект на этапе тестирования, вы можете прямо в модели описать, что нужно работать не со всем массивом данных, а с выбранным куском, например, за последние три дня. В плане тестов и разработки это полезный функционал, который позволяет сэкономить ресурсы при процессинге данных в Google BigQuery.
Подключение Git
Вы можете подключить к своему dbt проекту любой Git, например GitHub или Google Cloud Source Repositories, и полноценно управлять всеми моделями, которые у вас есть.
Логгинг через вебхуки (Logging via webhooks)
Вы можете складывать результаты тестов и расчетов в какой-нибудь pop-up и потом отправлять через Google Cloud, куда вам надо. Или складывать их в отдельную таблицу GBQ и выводить визуализацию, как все посчиталось.
Как использовать dbt
dbt Сloud
В dbt cloud есть две опции: сама модель, конфигурационный файл, и что-то типа cron. Вы можете указать конкретную модель либо сделать для модели тег или лебл. Это позволит просчитывать, не определенную логически сгруппированную папку, а все папки по какому-то тегу. Например, все, что касается заказов или источников трафика.
Также здесь есть schedule, с помощью которого можно задать периодичность расчета данных.
Google Cloud — Cloud Shell
Еще один вариант использования dbt. В Google Cloud есть такой инструмент, как Cloud Shell. Если вы его откроете, то фактически окажетесь внутри AppEngine. То есть у вас есть возможность развернуть dbt не в доке, а прямо в AppEngine.
Это не очень удобно с точки зрения полноценной работы, в плане использования запросов, кронов и всего остального, но как вариант работы в облаке вполне сгодится. Все необходимые изменения можно делать внутри облака, не выгружая данные локально на компьютер.
Google Cloud — Cloud Run
Схема работы с dbt, Google Cloud и Cloud Run выглядит примерно так:
В Atom или другом редакторе вносим правки, делаем для новых правок отдельные бренчи.
После этого коммитим изменения и пушим в Cloud репозиторий.
По факту пуша через Cloud Build формируется новая версия нашего Cloud Run и запускается через Cloud Scheduler по необходимому расписанию.
Как результат работы dbt, который находится в Cloud Run, все это рассчитывается в BigQuery, откуда идет уже в Data Studio.
Логи можно складывать через pop-up в Telegram по определенным правилам — например, пушить не все, а только какие-то важные изменения.
Такая инфраструктура позволяет довольно легко переносить запросы с одного Cloud проекта на другой и контролировать все происходящее с расчетами в dbt. Благодаря использованию Git вы четко понимаете, кто в вашей команде что запушил, куда, зачем и почему.
