События последних лет побудили eLama переориентироваться с зарубежных технологий на отечественные, но мы накопили достаточный опыт работы на стеке Google — и не хотелось бы, чтобы он потерялся.
Наш Data Office — единая точка доступа к подготовленным данным компании для всех сотрудников, нуждающихся в них. Разные отделы используют разные внешние инструменты и внутренние базы данных, разные сущности и бизнес-процессы. Всё это генерирует целые реки данных, которые мы собираем и превращаем в витрины.
eLama — динамичная IT-компания, которая постоянно ищет и реализует новые бизнес-идеи. Изменения в бизнесе, разумеется, ведут к появлению новой логики и новых данных. Именно поэтому у нас часто запрашивают создание новых витрин. Их общее количество давно перевалило за две с половиной сотни, а количество сложных BI-отчетов на их основе составляет несколько десятков. Сбор, хранение, описание, обработку и визуализацию всех этих данных мы обеспечиваем небольшой командой — нас всего 14 человек.
Чтобы всё получалось, мы стараемся автоматизировать и упрощать всё, что только можно. Далее я покажу, как с помощью Airflow и таблицы Google Sheets мы решили сразу как минимум две задачи и существенно упростили управление обновлением данных. Способ рассчитан на специалистов, которые понимают, как работает Airflow, и умеют обращаться к BigQuery из Python-скриптов.
Дано: Google BigQuery (BQ) в качестве аналитического хранилища; ETL/ELT-процессы управляются через Airflow. Большинство витрин обновляются ежедневно по утрам. Под витриной данных мы понимаем представление (view), сохраненное в BQ, и его кеш в виде статичной таблицы (base table). Чтобы работать с витринами легко и эффективно, нам нужно было решить задачи управления планом добавления витрин и метаданными в них.
Задача №1: добавление и обновление витрин
Можно, конечно, создать некий конфигурационный скрипт для дага Airflow и прописать в него массив витрин. Но здесь возникает две сложности.
Во-первых, работа с кодом — процесс непростой. Даже если нужно изменить всего один символ, потребуется выполнить множество действий, включая тестирование, мерж и деплой.
Во-вторых, программный код неудобно использовать как часть документации. Он лежит где-то в репозитории, имеет строгий синтаксис и вообще предназначен для выполнения определенной части программы, а не для наглядного отображения информации пользователю.
Задача №2: управление метаданными
Метаданные, то есть данные о данных: описания полей, происхождение данных, способы их преобразования и так далее, — по-хорошему должны быть неотъемлемой частью хранилища.
Их можно хранить в таблицах BQ, но при перезаписи таблиц-кешей их метаданные удаляются. Можно в сам SQL-запрос на пересоздание витрины вставлять все дескрипшены, но тогда мы возвращаемся к проблеме хранения информации о данных в программном коде.
Решение
Убить одним махом двух зайцев позволило ведение списка витрин в таблице Google Sheets. Здесь записи можно фильтровать, выделять цветом, применять формулы — в общем, пользоваться всеми возможностями электронных таблиц, а список расшаривать коллегам.
Вот как мы это организовали процесс.
Шаг №1. Создали список витрин для обновления
Список организовали в таблице со следующими полями:
- Целевая таблица (идентификатор проекта, имя датасета, имя таблицы).
- Источник (идентификатор проекта, имя датасета, имя таблицы) — представление или внешняя таблица, с которой делается кеш.
- Порядок выполнения.
- Частота обновления.
Далее мы поручили дагу Airflow проходиться по списку сверху вниз (или в указанном в столбце launch_order порядке) и с частотой, указанной в столбцах week_day (день недели) и month_day (число месяца), выполнять предельно простой запрос вида:
CREATE OR REPLACE TABLE `cache_project.cache_dataset.cache_table`
AS
SELECT * FROM `source_project.source_dataset.source_view`
Все датасеты, представления и внешние таблицы были заранее созданы в BQ.
Итог: при появлении в хранилище новой витрины мы можем просто добавить в список витрин еще одну строку и никак не менять даг.
Шаг №2. Добавили метаданные
Метаданные витрин у нас состоят из описания таблицы и ее полей, а также лейблов, в которых мы задаем создателя витрины, заказчика и способ обновления витрины.
Метаданные добавляет к уже перезаписанным кешам другой таск того же дага либо отдельный даг, вызываемый после обновления данных. Описания полей и самих таблиц хранятся на отдельных листах того же GS-документа: лист tables содержит метаданные таблиц, а лист fields — полей.
На листе tables, помимо полей с адресом таблицы, есть следующие столбцы:
- option принимает значение label для задания ярлыка таблицы и description для определения ее описания;
- option_name используется для задания имени ярлыка:
- creator — создатель витрины (инициалы);
- customer — ее заказчик (инициалы);
- generator_type — способ обновления витрины (airflow, manual, external_table и так далее);
- dag_name — имя дага Airflow, обновляющего витрину;
- generator_name — имя представления, с которого записан кеш в данную таблицу;
- option_value — значения соответствующих параметров.
Лист fields имеет еще более интуитивно понятную структуру: для каждого поля, имя которого указано в колонке field, прописывается описание в поле description.
Хранение метаданных всех витрин в едином списке позволяет:
- не прописывать все поля в коде представления, а сосредоточиваться при разработке на логике запроса;
- иметь все поля всех витрин в одном месте и легко находить при необходимости описание сущностей;
- легко менять описание полей, если логика их расчета изменилась.
Код тасков Airflow
Для запуска обновления витрин по списку мы использовали в таске Airflow такую функцию:
Вот код функции обновления и вспомогательных функций.
Функция берет список витрин из кеша внешней таблицы, которая смотрит на Google-таблицусо списком витрин. Это значит, что данный таск должен предваряться таском записи этого кеша. Либо можно обращаться сразу к созданной в BQ внешней таблице (external table) со списком.
Обновление метаданных можно вынести в отдельный таск. Вот код основных функций таска. Таск считывает список витрин с листов fields и tables списка в Google Sheets, а затем в цикле по всем найденным в этих списках витринам обновляет метаданные таблиц и их полей.
Даг обновления метаданных также должен включать в себя кеширование внешних таблиц с листов fields и tables, описанных выше. Даг может выглядеть примерно так.
В итоге получается изящный механизм, позволяющий:
- добавлять в план обновления или удалять из него витрины, просто вставив или стерев руками строки в электронной таблице;
- мгновенно понимать, в каком порядке и с какой частотой обновляются витрины и обновляются ли они вообще;
- видеть все описания таблиц и их полей в одном месте с возможностью их исправить, копировать, сортировать и использовать совместно с коллегами.
Надеюсь, это поможет вам сделать управление хранилищем данных более удобным и приятным.
