Внедрение SQLMesh в команду аналитики

Содержание

1) Введение

2) В поиске решения: сравнение разных решений

3) SQLMesh как решение многих проблем

4) Логика построения БД по SQLMesh

5) Обновление таблиц вне расписания: ручные расчеты

6) Вывод lineage как один из способов документации базы данных

7) Выводы

Введение

Всем привет)

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

Однако помимо достоверности данных в БД хотелось бы также, чтобы результаты были:

1. Воспроизводимы;

2. Масштабируемы;

3. Задокументированы;

4. Версионируема.

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

В поиске решения у нас было 3 основных кандидатуры: dbt (все-таки как никак это стандарт индустрии), OpenMetaData и SQLMesh.

В поиске решения: сравнение разных решений

Первое, что нам довелось попробовать был dbt. Данное решение было достаточно развитое (первый релиз состоялся аж в 2016 году), позволяла хранить версионность документов в git, документация генерировалась автоматически, а также у данного инструмента достаточно большое community, которое его поддерживает (звезды не показатель, но также есть 12.8 тысяч звезд на Git).

Конечно, мы сразу захотели его развернуть, однако это оказалось не так и просто: множество ошибок интеграции с существующим ClickHouse (часть заключалось в проблемах с SSL сертификатом и его проверкой, часть проблем связано с реплицируемостью БД и ее шардированием, часть проблем возникло при интеграции с Airflow). Времени на эту задачу было не много, да и к тому же думать сильно не хотелось, поэтому от этого решения отказались.

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

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

Во-вторых, dbt не является полноценным оркестратором. Для запуска пайплайнов, контроля зависимостей между различными системами, мониторинга выполнения задач и обработки ошибок обычно приходится использовать сторонние инструменты вроде Airflow, Dagster или Prefect. В результате вместо единого решения получается целый стек технологий, который необходимо поддерживать.

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

Сейчас у нас в контуре также настроена OpenMetadata, однако она может только настраивать linage данных по таблицам. Все остальные функции для нее невозможны, поэтому ее мы используем только как справочник относительно базы данных.

SQLMesh как решение многих проблем

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

Когда я впервые увидел SQLMesh как механизм мне показалось удивительным, как много различных функций вбирает в себя этот проект. Он может как планировать определенные задачи в БД, строить план выполнения запросов, сохранять переменные среды и исполнять код в своих окружениях. В совокупности это дает отличный механизм для работы с базой данных, который позволяет заменить часть встроенных механизмов ClickHouse.

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

Отдельного внимания заслуживает декларация моделей в SQLMesh: это просто некоторая обертка относительно существующих SQL запросов. Следовательно, переписывать структуру базы данных полностью нет необходимости, следует только добавить некоторые блоки в запросы (которые уже хранились у нас на Git).

Одной из проблем для наших кейсов были RMV в ClickHouse. Их обновление строится следующим образом: раз в определенный интервал времени RMV обновлялись и данные в них пересчитывались. В целом, ничего сложного, достаточно простой процесс. Однако он вызывал некоторые сложности:

1. При перезагрузки БД все RMV запускались одновременно. Следовательно, если RMV было достаточно много, либо они были громоздкие, то это вызывало определенные проблемы при их обновлении;

2. RMV ничего не знают об обновлении других таблиц. Следовательно, для того, чтобы строить план обновления из нескольких таблиц (для больших аналитических историй) нужно было ставить RMV и разносить их по времени (или писать рукописный DAG, который заполнял нужные таблицы последовательно). Эту проблему также получилось элегантно решить через SQLMesh, о чем будет рассказано ниже.

Логика построения БД по SQLMesh

В начале для корректной работы SQLMesh нужно настроить модели, которые являются составной частью БД, построенной на SQLMesh. Декларировать их можно следующим образом:

MODEL(       name sqlmesh.table      , kind FULL      , cron '@daily'      , physical_properties (      order_by = '(columns)')      , description 'Описание таблицы'      , audits (audit_script)      , columns (       col_1 Datetime('Europe/Moscow')      , col_2 UInt64      , col_3 String)  );select *from table

• name — уникальное имя модели (по совместительству таблицы в базе данных)

• kind — тип модели и способ её обновления. В данном примере используется FULL, что означает полный пересчет таблицы при каждом запуске. Может быть как VIEW, INCREMENTAL_BY_TIME_RANGE, INCREMENTAL_BY_UNIQUE_KEY (для инкрементов) и другие

• cron — расписание обновления модели. В данном случае используется @daily, то есть пересчет будет происходить один раз в сутки. Также поддерживаются стандартные cron-выражения и сокращения вроде @hourly, @weekly и @monthly.

Важно: модель не будет пересчитываться раньше, чем это записано в cron. Т. е. если сделать restate model некоторой модели, то это не обновит данные в ней, даже если они были обновлены

• physical_properties — настройки физического хранения таблицы в конкретной СУБД. Этот блок зависит от используемого движка. Для ClickHouse здесь часто задаются параметры вроде order_by, partition_by, настроек хранения и других особенностей движка. SQLMesh просто передает эти параметры в целевую БД.

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

• audits — проверки качества данных, которые выполняются после построения модели. Например, можно проверить отсутствие дубликатов, наличие обязательных значений или выполнение бизнес-ограничений. Если аудит не проходит, SQLMesh сигнализирует об ошибке.

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

• columns — явное описание структуры таблицы и типов данных (похоже на простое create table).

У нас в команде принято, чтобы каждое изменение исходило от ветки master в новой ветке, название которой совпадает с названием задачи, над которой мы работаем. В SQLMesh имеется возможность создавать виртуальное окружение, в котором он будет работать, тем же образом.

Так, например, если я работаю над задачей номер 857 я могу создать новое окружение следующим образом:

git checkout mastergit branch 857git checkout 857sqlmesh plan 857

После выполнения плана sqlmesh сам создаст необходимые таблицы, которые заполнит данными, рассчитанными на текущий момент и будет обращаться к ним в результате других моделей в данном окружении. Все изменения будут изолированы внутри окружения 857, поэтому их можно безопасно тестировать без влияния на другие окружения и production. При этом SQLMesh не копирует весь набор данных целиком: окружение представляет собой набор ссылок на версии моделей (snapshots), а вычисления и физические таблицы переиспользуются там, где это безопасно. Благодаря этому создание окружений происходит быстро и не требует полного пересчёта всего пайплайна.

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

Отдельного внимания заслуживает cron, ведь именно он позволяет реализовывать обновление таблиц по расписанию. Обновление не происходит, пока не прописать команду sqlmesh run, которая и запускает статус чек всех таблиц и времени их обновления. Если SQLMesh увидит, что какая-то таблица не была обновлена по расписанию, то он запустит ее обновление вместе с backfilling-ом (обратным заполнением по всему lineage). Удобство состоит в том, что SQLMesh знает, какие таблицы нужно обновлять, если обновление стоит хотя бы у последней из них (однако рекомендую ставить обновление на все таблицы, все равно они не будут обновляться чаще, а если пойдут ответвления от основной схемы таблицы, то можно что-то упустить).

Обновление таблиц вне расписания: ручные расчеты

Логика разработки таким образом понятна: мы создаем новую модель, записываем в нее необходимый SQL запрос, ставим scheduler, который обозначает, когда нужно обновлять эти данные, после запускаем sqlmesh run на некоторое расписание (чтобы пересчитывать сами значения в записанное в scheduler расписание, например через airflow) и все работает. Последовательность выполняется сама и не требует ручных правил.

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

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

Хочется, чтобы расчет запускался только в случае, если поменялись некоторые входные параметры, если же они остались неизменными, то отчет сохранял свой прежний формат. Сразу становится понятно, что управлять таким поведением через cron невозможно, т. к. мы не знаем, как часто будет использоваться тот или иной отчет.

Для таких вещей в SQLMesh можно использовать seed-ы. Seed — это специальный тип модели, источником данных для которой является не SQL-запрос, а статический CSV-файл, хранящийся непосредственно в репозитории проекта. При этом для SQLMesh seed ничем принципиально не отличается от остальных моделей: для него создается физическая таблица в хранилище, он участвует в lineage, может использоваться другими моделями и версионируется вместе с остальным кодом проекта.

Задается таблица с seed-ами следующим образом:

model (  name db.table  , kind SEED (      path '$root/путь_к_csv',  )  , columns (      col_1 String,      col_2 Date,      col_3 Date  )  );

Таким образом это позволяет выгрузить из csv необходимые данные и после обновить данные в таблицах, привязанных к данной через sqlmesh plan. Данная реализация позволяет обновлять все связанные таблицы, используя параметры из одной из них.

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

Вывод lineage как один из способов документации базы данных

В настоящий момент у нас используется Open Metadata как один из источников документации БД и он является наиболее полным: он позволяет ставить owner-ов для определенных таблиц и проектов, строит сложные зависимости, подключается к Superset и вытягивает информацию оттуда. Однако стоит отметить, что с этим также справляется и SQLMesh.

Так, например, выглядит lineage одного из наших проектов в SQLMesh:

В результате lineage позволяет определить:

1. Откуда пришло данное поле и какие таблицы зависят от него;

2. Какие поля могут сломаться при изменении определенной модели;

3. Какие таблицы зависят от текущей.

Скриншот был сделан из web интерфейса, который в настоящий момент, по словам разработчиков, уже не поддерживается. Это достаточно печально, т. к. не позволяет показывать lineage другим пользователям, которые не должны иметь доступ к редактированию БД.

Выводы

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

1. Документация таблиц ручного обновления;

2. Версионирование структуры БД с использованием Git и уверенностью в том, что структура БД именно такая, как записано в Git;

3. Воспроизводимость расчетов в SQL формате.

Данное решение, конечно, имеет свои недостатки. Как я уже отмечал ранее одним из них является создание избыточного количества таблиц для различных enviroment-ов. В случае, если с одной БД работает больше 5 веток одновременно, то это сразу превращается в нечитаемый фарш из таблиц. И даже при слиянии одних из веток с основной для быстрого удаление виртуального окружения следует ивалидировать ветку, запустить уборщика и сделать так, чтобы все зависимости данного окружения были удалены.

Также стоит отметить, что нередко встречаются и баги, которые иногда требуют вмешательства в исходный код самого проекта. Так, например, для того, чтобы было возможно использовать этот продукт на нашей БД нужно было протащить в коннектор одно поле, которое было потеряно. Мой коллега сделал pull request для того, чтобы было возможно его запустить в нашей инфраструктуре. Также для clickhouse есть некоторые другие ограничения в работе (проблема с парсингом дат и прочее).

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