219
0
0
Скопировать ссылку
Telegram
WhatsApp
Vkontakte
Одноклассники
Назад

Управление метаданными в BigQuery через Google Sheets

Время чтения 2 минуты
Нет времени читать?
Скопировать ссылку
Telegram
WhatsApp
Vkontakte
Одноклассники
219
0
0
Нет времени читать?
Скопировать ссылку
Telegram
WhatsApp
Vkontakte
Одноклассники

Привет, меня зовут Павел Беляев, я тимлид группы Data Processing отдела Data Office сервиса eLama. Расскажу о способе обновления витрин и их метаданных по списку, которым легко управлять через таблицу Google Sheets.

Управление метаданными в BigQuery через Google Sheets

События последних лет побудили 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, описанных выше. Даг может выглядеть примерно так.

В итоге получается изящный механизм, позволяющий:

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

Надеюсь, это поможет вам сделать управление хранилищем данных более удобным и приятным.

Комментарии0
Тоже интересно
Комментировать
Поделиться
Скопировать ссылку
Telegram
WhatsApp
Vkontakte
Одноклассники