Работа с dbt на базе Google BigQuery

На днях смотрел вебинар OWOX, где Андрей Осипов (веб-аналитик, автор блога web-analytics.me и лектор Школы веб-аналитики Андрея Осипова) рассказал о своем опыте использования dbt. Говорил о том, кому будет полезен инструмент и какие проблемы решает, а самое главное как не свихнуться со сложной иерархией таблиц и быть уверенным, что все данные считаются корректно. Я решил расшифровать вебинар в статью, потому что так удобнее возвращаться к информации, а она тут, поверьте, того стоит.

Зачем нужен dbt

Зачем нужен еще один инструмент для управления SQL-запросами? Ведь у нас есть Google BigQuery, и вообще в Google Cloud много различных механизмов, которые могут решать задачи по формированию таблиц, например scheduled queries.

Если у вас маленький проект, пара источников данных (таблица с событиями плюс расходы) и вам нужно построить 2-4 отчета, то структура расчета этих таблиц будет довольно простой.

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

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

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

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

При такой сложной структуре стандартные механизмы управления (типа scheduled queries) не подходят по нескольким причинам:

1. Зависимости. Каждый scheduled query нужно запускать в определенное время, то есть у вас должна быть четкая иерархия запуска расчета таблиц. Нельзя, чтобы результирующая таблица считалась раньше, чем исходная. К примеру, в 5 утра у вас формируется отчет. После этого происходит какой-то сбой, и только в 7 утра данные о нем попадают в исходную таблицу. В результате вы теряете данные за вчера и нужно пересчитывать весь проект. А если у вас десятка два-три запросов, то пересчет займет минут 40.

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

3. Тестирование. Данные, которые попадают в исходные таблицы, не всегда корректны, а в Google BigQuery нет встроенных инструментов для их тестирования. Например, у вас была таблица с заказами. Потом в нее добавили рейтинг, который пользователи могут менять. Из-за этого могут дублироваться транзакции. То есть в таблице будет две записи с одинаковой транзакцией, но с разными timestamp и рейтингами.

4. Много одинаковых кусков SQL. Когда у вас несколько финальных таблиц, которые генерируются из базовых, одни и те же агрегации происходят много раз. Это сильно увеличивает стоимость использования BigQuery. На нашем вебинаре Андрей Осипов поделился примером, как с помощью dbt ему удалось снизить стоимость использования GBQ в 20 раз для одного из проектов, с которым он работал. Только за счет того, что обращение к базовой таблице с событиями происходило всего один раз.

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

Что такое dbt (data build tool)

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

Как выглядит процесс обработки данных:

Схема из презентации dbt.

На схеме мы видим:

1. Источники данных.

2. Сервисы, которые достают данные из этих источников и складывают в хранилище. Это могут быть Cloud Functions, Cloud Run, OWOX BI Pipeline и др.

3. Хранилище, куда мы складываем данные и где потом их нужно преобразовать: объединить с другими данными, проверить, валидировать.

4. После обработки мы можем отправлять данные в инструменты визуализации, 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 под себя и формировать результат расчета на порядок проще и удобнее.

Пакеты макросов

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

Materializations

Еще одна полезная функция, которая значительно экономит ресурсы.

Допустим, вы не используете 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. Если вы его откроете, то фактически окажетесь внутри apenjin. То есть у вас есть возможность развернуть dbt не в доке, а прямо в apenjin.

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

Google Cloud Cloud Run

Схема работы с dbt, Google Cloud и Cloud Run выглядит примерно так:

1. В Atom или другом редакторе вносим правки, делаем для новых правок отдельные бренчи.

2. После этого коммитим изменения и пушим в Cloud репозиторий.

3. По факту пуша через Cloud Build формируется новая версия нашего Cloud Run и запускается через Cloud Scheduler по необходимому расписанию.

4. Как результат работы dbt, который находится в Cloud Run, все это рассчитывается в BigQuery, откуда идет уже в Data Studio.

5. Логи можно складывать через pop-up в Telegram по определенным правилам например, пушить не все, а только какие-то важные изменения.

Такая инфраструктура позволяет довольно легко переносить запросы с одного Cloud проекта на другой и контролировать все происходящее с расчетами в dbt. Благодаря использованию Git вы четко понимаете, кто в вашей команде что запушил, куда, зачем и почему.