Модель данных: показываем взаимосвязь таблиц в Clickhouse

Время чтения 28 минут

Приветствую специалистов по данным!

Я Павел Беляев, тимлид группы обработки данных в дата-офисе сервиса автоматизации интернет-рекламы eLama и автор канала «Тимлидское об аналитике». Мы разрабатываем и поддерживаем сотни взаимосвязанных витрин данных в Clickhouse.

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

По-хорошему необходима разработка документации, но ведь это потребует ресурсов на ее актуализацию. В этой статье я расскажу, как мы организовали самообновляющуюся модель данных из подручных средств: Clickhouse (наше аналитическое хранилище) и любого инструмента визуализации.

Модель данных: показываем взаимосвязь таблиц в Clickhouse

Образ результата

Поскольку мы решили обойтись без специализированных средств построения модели данных, ограничимся следующими требованиями к нашему инструменту:

Есть возможность выбрать конкретную таблицу или представление.
Для выбранного объекта находятся его «родители», то есть объекты-источники, к которым он обращается, а также его «дети», то есть объекты-потребители, которые смотрят на него.
Пара представление + кеш-таблица, в которую сохраняется результат его выполнения (то есть витрина данных), рассматривается и выводится вместе.

В итоге имеем вот такой пользовательский интерфейс.

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

Мы видим, что исследуемая нами витрина — таблица test_data_model_selected и представление v_test_data_model_selected — имеет в качестве «родителей» таблицу test_data_model_source, а в качестве «детей» — представление v_test_data_model_consumer.

Такой «анализ» позволит быстро понять:

на какие объекты повлияет изменение в исследуемом объекте, например, если изучается вопрос о его удалении или модификации;
от каких объектов зависит исследуемый, например, при поиске причин некачественности данных в нем.

К информации об источниках и потребителях можно добавить описание объекта и его схемы, а также любые другие метаданные, которыми мы располагаем.

Решение

Увы, Clickhouse не позволяет задавать зависимости между таблицами с помощью DDL. Но зато в нем есть чудесная системная таблица system.tables. Она содержит все таблицы и представления всех баз данных вашего кластера. И помимо прочего, в этой таблице в поле create_table_query содержится SQL-запрос, создающий данный объект. Вот его-то мы и будем изучать, чтобы выявить источники объекта.

Для этого разделим этот DDL-запрос на отдельные слова, чтобы вытащить из него все конструкции вида database.object:


SELECT splitByRegexp('[\s()''"]+', create_table_query)
FROM system.`tables`

Теперь вытащим список всех объектов и создадим из него массив:


SELECT groupUniqArray(concat(database, '.', name))
FROM system.`tables`

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


WITH
( -- все объекты во всех базах
SELECT groupUniqArray(concat(database, '.', name))
FROM system.tables
) AS all_tables
 
-- список объектов и их "родителей"
SELECT st.database AS database_selected,
st.name AS object_selected,
parent -- "родитель"
FROM system.tables AS st
 
-- добавляем массив с "родителями", которых выфильтровываем массивом всех таблиц из DDL
LEFT ARRAY JOIN arrayIntersect(splitByRegexp('[\s()''"]+', create_table_query), all_tables) AS parent

Тем же способом, только «перевернутым», можно определить и «детей», то есть потребителей исследуемого объекта.


-- список объектов и их "родителей"
SELECT
-- меняем местами объекты: "родители" становятся исследуемым объектом, а исследуемые объекты — "детьми"
substring(parent, 1, position(parent, '.') - 1) AS database_selected,
substring(parent, position(parent, '.') + 1) AS object_selected,
st.database AS database_child,
st.name AS object_child
FROM system.tables AS st
 
-- добавляем отсекающим джойном массив с "родителями", которых выфильтровываем массивом всех таблиц из DDL
ARRAY JOIN arrayIntersect(splitByRegexp('[\s()''"]+', create_table_query), all_tables) AS parent

Далее объединим подзапросы с источниками и потребителями, чтобы получить полную картину. Но сначала еще хочется сопоставить представления и их кеш-таблицы. Такой связи нет в system.tables, но у нас это решается через нейминг: представления имеют такое же имя, как таблицы, но с префиксом v_.

Итоговый запрос

Соберем всё вышесказанное. Вот такой запрос у нас получился:


WITH
( -- все объекты во всех базах
SELECT groupUniqArray(concat(database, '.', name))
FROM system.tables
) AS all_tables,
 
table_dependencies AS
(
SELECT *
FROM
(   -- "Дети"
     SELECT substring(parent, 1, position(parent, '.') - 1) AS database_selected,
         substring(parent, position(parent, '.') + 1) AS object_selected,
         NULL AS database_parent,
         NULL AS object_parent,
         if(parent = concat(database, '.', name), NULL, st.database) AS database_child,
         if(parent = concat(database, '.', name), NULL, st.name) AS object_child
     FROM system.tables AS st
    
     ARRAY JOIN arrayIntersect(splitByRegexp('[\s()''"]+', create_table_query), all_tables) AS parent
     UNION ALL
     -- "Родители"
     SELECT st.database AS database_selected,
         st.name AS object_selected,
         IF(
             parent = concat(st.database, '.', st.name) OR position(parent, '.') = 0,
             NULL,
             substring(parent, 1, position(parent, '.') - 1)
         ) AS database_parent,
         IF(
             parent = concat(st.database, '.', st.name) OR position(parent, '.') = 0,
             NULL,
             substring(parent, position(parent, '.') + 1)
         ) AS object_parent,
         NULL AS database_child,
         NULL AS object_child
     FROM system.tables AS st
     LEFT ARRAY JOIN arrayIntersect(splitByRegexp('[\s()''"]+', create_table_query), all_tables) AS parent
) AS unioned_results
),
 
view_to_base_table_mapping AS
(   -- Соответствие представлений и их кеш-таблиц (у представлений есть префикс v_)
SELECT database_selected,
     replace(object_selected, 'v_', '') AS table_selected,
     object_selected AS view_selected
FROM table_dependencies
WHERE object_selected LIKE 'v_%'
)
 
 
-- Итоговый список всех объектов, их источников и потребителей
SELECT DISTINCT * FROM
(
SELECT vtbtm.database_selected AS database_selected,
     vtbtm.table_selected AS table_selected,
     vtbtm.view_selected AS view_selected,
     td.database_parent,
     td.object_parent,
     td.database_child,
     td.object_child
FROM table_dependencies td
INNER JOIN view_to_base_table_mapping vtbtm
     ON td.database_selected = vtbtm.database_selected
     AND td.object_selected = vtbtm.view_selected
 
UNION ALL
 
SELECT td.database_selected,
     td.object_selected AS table_selected,
     vtbtm.view_selected AS view_selected,
     td.database_parent,
     td.object_parent,
     td.database_child,
     td.object_child
FROM table_dependencies td
LEFT JOIN view_to_base_table_mapping vtbtm
     ON td.database_selected = vtbtm.database_selected
     AND td.object_selected = vtbtm.table_selected
WHERE td.object_selected NOT LIKE 'v_%'
)
-- отфильтруем объекты, у которых нет ни "родителей", ни "детей"
WHERE NOT (
     database_parent IS NULL AND
     object_parent IS NULL AND
     database_child IS NULL AND
     object_child IS NULL
)
ORDER BY 1, 2, 3, 4, 5, 6, 7

Запрос выведет таблицу такого вида:

где:

database_selected — база данных исследуемого объекта;
table_selected — исследуемая витрина данных;
view_selected — представление исследуемой витрины данных;
database_parent — база данных источника исследуемой витрины;
object_parent — таблицы и представления, источники исследуемой витрины;
database_child — база данных потребителя витрины;
object_child — таблицы и представления, потребители витрины.

Мы можем видеть из этих данных, что витрина dm_selected1 имеет представление v_dm_selected1 (о том, как у нас устроено хранение и обновление витрин данных, я писал ранее), источником для витрины является таблица dm_source1, а потребителями — представления v_dm_consumer1 и v_dm_consumer2. Причем потребители могут обращаться как к кеш-таблице, так и к представлению.

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

Резюме

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

Создать SQL-запрос, который вытаскивает из системной таблицы Clickhouse system.tables все объекты, их «родителей» и «детей» из DDL в поле create_table_query.
Создать в BI-системе интерфейс пользователя как визуализацию с этим запросом или его кешем в качестве источника.

Можно также обогатить вывод любыми метаданными об объекте, которые у вас есть: описания таблиц, их полей, ответственные лица и др.