Жизненный цикл витрины данных
Под витриной данных мы будем понимать таблицу, готовую для анализа, расположенную в аналитическом хранилище. Витрина обновляется через соответствующее представление, то есть сохраненный запрос, который формирует эту таблицу. Кстати, о варианте механизма такого обновления я уже писал.
Витрины данных, как и всё бренное в этом мире, имеют свой жизненный цикл. В общем виде он состоит из следующих шагов:
- Выявление потребности в информации и формирование технического задания на витрину.
- Сбор или досбор необходимых данных, если их еще нет в аналитическом хранилище.
- Разработка скрипта или запроса, формирующего витрину.
- Отладка/тестирование витрины.
- Принятие заказчиком, внедрение, использование.
- Поддержка и доработка: мониторинг качества данных, добавление столбцов, изменение логики при необходимости.
- Потеря актуальности витрины, снятие с поддержки, удаление.
Большинство шагов используется так или иначе везде. Но последний шаг часто обделен вниманием! А ведь в уничтожении ненужного кроется большой потенциал для оптимизации процессов. Зачем тратиться на обслуживание витрины, которая никому не нужна?
Но как узнать, нужна она или нет?
Проверка нужности витрин данных
Ну, можно прямо спросить потребителей. Можно, но заморочно. Представьте, что у вас множество витрин и толпа пользователей, которые еще и меняются то и дело (текучку кадров никто не отменял). Опрос превращается в длительную, слабо контролируемую процедуру, а его ведь к тому же надо проводить регулярно.
К счастью, процесс выявления потерявших актуальность витрин можно автоматизировать. Для этого достаточно изучить лог запросов и выбрать витрины, к которым никто не обращался уже давно.
Попробуем детализировать задачу.
Выберем витрины из тех, что стоят на поддержке и обновлении, для которых за последний месяц:
- Не было запросов типа SELECT.
- Был всего один запрос типа INSERT (если таблицы обновляются через INSERT INTO) в день, в котором использовалась эта витрина, то есть она только обновлялась сама, но не использовалась для обновления других витрин.
В качестве инструментов визуализации мы используем Data Lense, а аналитическое хранилище строим на Clickhouse, поэтому покажу соответствующий запрос для этой СУБД.
Запрос, выявляющий неиспользуемые витрины
Лог запросов Clickhouse находится в системной таблице system.query_log. Правда, он хранит не всю историю, поэтому имеет смысл создать свой архив лога и регулярно дополнять его из системного лога. Но если неинтересна давняя история, обойдемся и без этого.
Итак, мы возьмем из лога только завершенные запросы типа SELECT и INSERT, а также все таблицы, используемые в запросе (поле table, являющееся массивом).
Затем для каждой таблицы посчитаем количество запросов каждого типа и оставим только те витрины, которые упоминались где-то, кроме как в запросах для своего обновления. Для них мы выясним дату последнего такого использования — last_use.
Наконец, мы прикрепим полученный список к перечню регулярно обновляемых витрин, нужность которых следует проверить.
На выходе мы получим список проверяемых витрин и дату их последнего использования. Если вместо даты стоит NULL — витрина не использовалась в течение залогированного периода.
Ну а дальше мы идем изучать, кому нужны витрины, у которых нет last_use или он довольно старый. Если бы кто-то открывал отчеты, которые смотрят на эти витрины, то в логе были бы запросы типа SELECT, так как при просмотре отчета Data Lense делает соответствующий запрос.
Убедившись в ненужности подсвеченных витрин, снимаем их с поддержки и обновления.
А вот и запрос:
SELECT ql.t AS table_in_log,
rt.t AS table_to_update,
MAX(event_date) AS last_use -- дата последнего упоминания таблицы в запросе
FROM
(
SELECT t,
event_date,
CountIf(query_kind='Insert') AS ins, -- количество вставок
CountIf(query_kind='Select') AS sel -- количество запросов выбора
FROM
(
SELECT DISTINCT
arrayJoin("tables") AS t, -- использованные в запросе таблицы
query_kind,
event_date
FROM system.query_log -- системный лог запросов
WHERE type ='QueryFinish' -- только завершенные запросы
AND query_kind IN ('Select', 'Insert') -- только запросы выбора и вставки
)
GROUP BY ALL
HAVING (ins>1 OR sel>0) -- оставляем только таблицы, к которым были обращения, кроме как для обновления себя
) AS ql
RIGHT JOIN
(
SELECT CONCAT(trim(cache_dataset), '.', trim(cache_table)) AS t
FROM datamart.tables_to_update -- наш список витрин для проверки нужности
) AS rt ON rt.t=ql.t
GROUP BY ALL
ORDER BY last_use
Эту проверку легко поставить на расписание, чтобы она проводилась автоматически, например раз в месяц.
Резюме
Чтобы сократить время обновления витрин, а также расход ресурсов на их поддержку, важно выявлять ненужные витрины и снимать их с поддержки.
Для автоматизации этого процесса можно использовать обращение к логу запросов. Для каждой проверяемой витрины смотрим, когда в последний раз она использовалась в запросе типа SELECT или применялась для обновления чего-то, кроме себя самой (то есть больше одного запроса типа INSERT).
Если дата последнего применения давно прошла, следует рассмотреть возможность снятия такой витрины с поддержки.
