ClickHouse. DWH. Развиваем сетевую франшизу по разведению кроликов в колоночном Data Vault

Вступление

Современный подход к DWH пестрит различными шаблонами проектирования. Но увы, большинство людей, кто занимается непосредственной разработкой и внедрением этих подходов едут по рельсам и боятся сделать шаг в сторону. Не все - в конце концов в ИТ огромное число креативщиков - но многие.

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

Давайте перейдем к терминам программирования. Что такое Data Vault? Инструмент, технология или паттерн? Правильный ответ конечно же - паттерн. Ведь авторы данного термина не принесли на рынок готового продукта или инструментов для реализации подхода - они появились позже - а подарили миру свод правил, парадигм.

• до какой степени нужно нормализовать данные, чтобы вы могли считать свою модель Data Vault

• определили жесткие ограничения по построению модели

• выделили основные элементы модели и возможные связи между ними

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

Бизнес модель

Никогда не мечтали заняться экзотическим бизнесом? А мы вот сейчас и займемся.

Волей судьбы вам досталась в наследство успешная франшиза по разведению кроликов по всему миру. Сотни тысяч ферм разного масштаба. Миллиарды животных. Огромная реферальная сеть. Ваш дед был настолько продвинутым, что смог запустить цепную реакцию и теперь система растет сама по себе, привлекая все новых и новых участников. Но увы, дед, помимо любви к животным, был заядлым фронт-end разработчиком и БД для него была лишь средством сохранения данные о своей сети. И не более.

Но вы. Вы - другое дело. Посмотрев на все это богатство вы сразу поняли, что перед вами открываются новые горизонты. Как например - открытие маркетплейса для торговли мясом или живыми животными. Вы можете интегрировать рынок сбыта в свою модель и сделать еще много и много чего. А что для этого нужно? Правильно, для этого нужна аналитика. Мощная и точная.

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

Задача

Что нужно для начала. Выделить основную цель. Итак - первым делом мы хотим начать торговать животными или их мясом. Продукт специфичный и реализовать его можно только на внутреннем городском рынке. Дальше возить не хочется. Да и незачем, ведь наша сеть настолько масштабна, что есть практически везде. Зачем заморачиваться еще и с логистикой, которая сама по себе является крайне сложным вопросом.

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

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

• Первым этапом аналитики, мы должны иметь возможность быстро (~ 10 секунд) понять, какие животные и в каком количестве есть в каждом конкретном городе.

• Также быстро (~10 секунд) найти фермы, которые привлекли больше всего участников. Либо их рефералы наиболее эффективны - имеют наибольшее поголовье животных.

Построение модели

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

Немного поразмыслив мы выделили следующие Хабы:

• Животные. Пусть будут именно животные. А вдруг мы решим разводить ондатр, где то слышал, что их шкурки в цене. Добавим атрибут тип в будущем и будем радоваться гибкости системы.

• Фермы. Ну куда же без них.

• Города. Мы же решили строить районно-ориентированный бизнес. А регионы и прочие структуры выше по уровню, всегда можно будет добавить позже.

Теперь нужно связать данные Хабы и накинуть минимум атрибутов. Перед этим выясним ответы на ряд вопросов у бизнеса (внимание, далее - сферический конь в вакууме):

• Кролики в системе идентифицированы? - Конечно, нам с каждой фермы отправляют данные о рождении, смерти и продаже животного. Это условие франчайзинга.

• А как идентифицируются кролики в системе? - Ну это номер фермы + номер кролика на этой ферме. Мы присваиваем его каждому животному, когда пользователь вносит данные в личном кабинете.

• Имеются ли сведения о родстве? - Конечно. Всегда указывается мать - они живут отдельно до родов. А отец - ну фермеры не следят за половой жизнью своих животных.

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

Немого порисовав в первом подвернувшемся средстве, мы получили нечто похожее на точку роста для нашего Data Vault.

Из основных аспектов выделим следующие:

• рекурсивные родственные связи кроликов и реферальную зависимость ферм связали через Линки

• в атрибуты животных и городов добавили пару неизменяемых параметров (название города условно неизменяемое), чтобы можно было провести минимальный пилотный пуск

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

• статус животного - это его актуальное состояние, причем изменяемое - продан/продан живым/жив/умер естественной смертью. Поэтому выносим в отдельный сателлит. Разницу 'продан' и 'продан живым' пояснять не будем. Чтобы не выглядеть слишком жестокими. Статус - это тоже в идеале отдельный Хаб. Но мы упрощаем ради статьи.

• хэш-ключи, время загрузки и источник - исключительно для того, чтобы иметь возможность масштабироваться и менять техническую базу. Ведь модель это универсальная сущность и ее можно реализовать где угодно. Даже уехать в Data Lake при лавинообразном росте данных. У деда там столько всего интересного в его БД - годами разбираться. Даже фотографии с конкурса 'Кролик года' имеются.

Реализация модели

Засучив рукава вы стали думать на каких технологиях строить свою модель. Волею судьбы выбор пал на ClickHouse.

• Колоночная структура позволяет осуществлять молниеносную аналитику по огромным массивам данных

• Быстрое добавление данных с использованием различных форматов - от прямых вставок до json

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

Итак. Остается раздобыть себе машину на Ubuntu, выбить доступы к процессинговой базе и можно приступать. Сказано - сделано. Через пару часов беготни по офису и общения с админами, вы сидите перед терминалом и устанавливаете ClickHouse для своего пилота. Готово.

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

-- drop table h_animals
CREATE TABLE h_animals
(
  animal_num String,
  animal_hsh FixedString(64),
  timestamp_ DateTime,
  source_ String,
) ENGINE=MergeTree()
PARTITION BY (toYYYYMM(timestamp_))
ORDER BY (timestamp_);
-- drop table l_animal_tree
CREATE TABLE l_animal_tree
(
  animal_hsh FixedString(64),
  animal_mother_hsh FixedString(64),
  timestamp_ DateTime,
  source_ String,
) ENGINE=MergeTree()
PARTITION BY (toYYYYMM(timestamp_))
ORDER BY (timestamp_);
-- drop table s_animal_lifecycle
CREATE TABLE s_animal_lifecycle
(
  animal_hsh FixedString(64),
  timestamp_ DateTime,
  source_ String,
  status String,
) ENGINE=MergeTree()
PARTITION BY (toYYYYMM(timestamp_))
ORDER BY (timestamp_);
-- drop table s_animal_attrs
CREATE TABLE s_animal_attrs
(
  animal_hsh FixedString(64),
  timestamp_ DateTime,
  source_ String,
  sex String,
  color String,
  birthdate date,
) ENGINE=MergeTree()
PARTITION BY (toYYYYMM(timestamp_))
ORDER BY (timestamp_);
-- drop table h_farms
CREATE TABLE h_farms
(
  farm_num String,
  farm_hsh FixedString(64),
  timestamp_ DateTime,
  source_ String,
) ENGINE=MergeTree()
PARTITION BY (toYYYYMM(timestamp_))
ORDER BY (timestamp_);
-- drop table l_animal_farms
CREATE TABLE l_animal_farms
(
  animal_hsh FixedString(64),
  farm_hsh FixedString(64),
  timestamp_ DateTime,
  source_ String,
) ENGINE=MergeTree()
PARTITION BY (toYYYYMM(timestamp_))
ORDER BY (timestamp_);
-- drop table l_farm_referals
CREATE TABLE l_farm_referals
(
  attract_farm_hsh FixedString(64),
  ref_farm_hsh FixedString(64),
  timestamp_ DateTime,
  source_ String,
) ENGINE=MergeTree()
PARTITION BY (toYYYYMM(timestamp_))
ORDER BY (timestamp_);
-- drop table h_cities
CREATE TABLE h_cities
(
  city_code String,
  city_hsh FixedString(64),
  timestamp_ DateTime,
  source_ String,
) ENGINE=MergeTree()
PARTITION BY (toYYYYMM(timestamp_))
ORDER BY (timestamp_);
-- drop table l_city_farms
CREATE TABLE l_city_farms
(
  city_hsh FixedString(64),
  farm_hsh FixedString(64),
  timestamp_ DateTime,
  source_ String,
) ENGINE=MergeTree()
PARTITION BY (toYYYYMM(timestamp_))
ORDER BY (timestamp_);
-- drop table s_city_attrs
CREATE TABLE s_city_attrs
(
  city_hsh FixedString(64),
  timestamp_ DateTime,
  source_ String,
  name_ String,
) ENGINE=MergeTree()
PARTITION BY (toYYYYMM(timestamp_))
ORDER BY (timestamp_);

Ну и далее вы наполняете физическую модель данными. По легенде, их вы выгружаете с прода, преобразуете в любой удобный формат и разово импортируете в ClickHouse. ETL на базе Airflow или любой другой технологии можно будет реализовывать позже, когда модель пройдет первичные испытания и будет представлена бизнесу на оценку.

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

Ох и народилось же ушастых у деда.

SQL. Проверяем данные

select count(1) as cnt from h_farms;

select count(1) as cnt from h_animals;

select * from h_animals;

На самом деле, данные я конечно же генерировал сам. Причем ровно столько, сколько позволил мой маленький SSD на домашней машине. Процесс данный оказался настолько занимательным, что параллельно родилась еще одна статья - Golang-генератор TSV для загрузки в ClickHouse. Также данную стать будет интересно почитать тем, кто захочет понять, откуда взялись города Суровый Хряк и Томный Тапок (см. скриншоты ниже).

JOIN IT!

Я не просто так дал такое странное название данной главе. Уверен что многие, кто имел дело с Data Vault, ещё с первых строк думали - ну что за псих. Данный концепт в принципе создан для первичного слоя данных, а не для аналитики, так как выборки не очень быстрые и требуют огромного количества операций соединения. Так он ещё умудрился засунуть хранилище в колоночную СУБД. Ну бред же.

И тут мы вернёмся к началу статьи. Data Vault - это паттерн. И в нем не написано - пиши join чтобы делать выборки из хранилища.

Давайте на мгновенье подумаем в контексте DWH? Как часто вы видите - NESTED LOOP и прочие соединения с непрямым доступом к данным по индексам? Я думаю редко, вы же не OLTP-шник какой-нибудь (сарказм). Чаще вы все таки работаете с большими выборками, которые порождают хэш-таблицы для связки. Даже в классической звезде - вы всегда вынуждены думать о том, как бы не вязать много больших таблиц. Работаете так - много измерений и ограниченное число таблиц фактов. Иначе можем попасть.

А теперь перевернем все с ног на голову:

Остается теперь понять, а какая разница между двумя схемами? Разница в том, что во втором случае мы выберем обе таблицы, где можно отрежем по партициям, а далее объединим их через GROUP BY.

Также помним, что мы работаем в контексте колоночной БД, поэтому агрегации по колонкам и группировки нас вообще не пугают. Можете посмотреть скорости на сотнях миллионов записей. При условии, что запросы выполнялись на виртуальной машине с 2ГБ памяти - специально выделил как можно меньше.

В конструкции GROUP BY используем поля связок, агрегатными функциями выбираем значения, которых нет в одной из таблиц. Ведь если в таблицах h_cities и s_city_attr есть Хэш, то сгруппировав по нему и выбрав максимальное название города, мы получим название города для конкретного Хэша. Ну и развивая эту мысль и работая с подзапросами, вот так можно посчитать количество кроликов в каждом городе.

--select sum(rabbit_cnt) from (
select max(t.city_name) as city_name,
       sum(t.rabbit_cnt) as rabbit_cnt
  from (
	select ca.city_hsh,
	       ca.name_ as city_name,
	       null as farm_hash,
	       0 as rabbit_cnt
	  from s_city_attrs ca
	 union all
	select max(t1.city_hsh) as city_hsh,
	       null as city_name,
	       t1.farm_hsh,
	       sum(t1.rabbit_cnt) as rabbit_cnt
	  from (
	    select cf.city_hsh,
	           cf.farm_hsh,
	           null as animal_hsh,
	           0 as rabbit_cnt
	      from l_city_farms cf
	     union all
	    select null as city_hsh,
	           max(t2.farm_hsh) as farm_hsh,
	           t2.animal_hsh,
	           sum(t2.rabbit_cnt) as rabbit_cnt
	      from (
		    select af.farm_hsh,
		           af.animal_hsh,
		           0 as rabbit_cnt
		      from l_animal_farms af
		     union all
		    select null as farm_hsh,
		           t3.animal_hsh,
		           sum(t3.rabbit_cnt) as rabbit_cnt
		      from (
		        select null as farm_hsh,
			           a.animal_hsh,
			           0 as rabbit_cnt,
			           a.timestamp_ as actual_timestamp,
			           a.timestamp_
			      from h_animals a
			    union all
			    select null as farm_hsh,
			           al.animal_hsh,
			           1 as rabbit_cnt,
			           null as actual_timestamp,
			           al.timestamp_
			      from s_animal_lifecycle al
			     where al.status = 'Жив'
			  ) t3
			  group by t3.animal_hsh,
                       t3.timestamp_
			  having max(t3.actual_timestamp) = t3.timestamp_
		 ) t2
		 group by t2.animal_hsh
	  ) t1
	group by t1.farm_hsh
  ) t
 group by t.city_hsh
--)

Что нужно помнить:

• поля группировки - это аналоги связок JOIN

• чем больше таблиц мы вяжем, тем больше колонок будет с null, так как общий набор колонок возрастает, а есть они не в каждой таблице

• чтобы проводить промежуточные связки с новыми Хабами придется писать подзапросы все глубже и глубже, группируя по полю связки. В примере выше - это подзапрос l_city_farms и l_animal_farms, который написан, чтобы прокинуть на уровень выше хэш города для каждой фермы

Также я не делал упора на один момент. Когда вязались таблица s_animal_lifecycle, с помощью конструкции having и двух полей timestamp мы выбрали запись, строго соответствующую Хабу. Тем самым обошлись без Point In Time таблиц, а использовали агрегации.

Вопрос с поиском самых эффективных рефералов вообще можно решить запросом из одной таблицы l_farm_referal. Но чтобы получить номер фермы пришлось привязать h_farms.

Самые эффективные фермы по привлечению

select max(t.farm_num) as farm_num,
       sum(t.cnt) as cnt
  from (
		select fr.attract_farm_hsh as farm_hsh,
		       count(1) as cnt,
		       null as farm_num
		  from l_farm_referals fr
		 group by fr.attract_farm_hsh
		 union all 
		select f.farm_hsh,
		       0 as cnt,
		       f.farm_num
		  from h_farms f
  ) t
group by t.farm_hsh
order by cnt desc

Заключение

Бизнес аналитики, после того как годами приходилось получать подобные данные по 10-20 минут из процессинговой базы, остались довольны и готовы дальше считать кроликов в вашем DWH.

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

Из плюсов подхода:

• можно проводить первичную аналитику на самом Data Vault - скорости позволяют. А витрины формировать по мере необходимости. Считаться в любом случае они будут быстрее чем в строковой БД

• очень быстрое получение фактов по одному Хабу и связанным с ним Линкам и Сателлитам

• мы почти не зависим от количества строк в БД. Так как аналитика проводится по колонкам. Главное не писать запросы и выборки с большим числом колонок

• Совместимость с организацией хранения данных СУБД - группировки и агрегации являются первоочередной задачей колоночных БД.

• Можно обойтись без Point in time таблиц. Так как мы все равно агрегируем данные в подзапросах

Из минусов:

• непривычные правила написания запросов

• большое число подзапросов, когда нужно вязать новые Хабы

• большое количество null-колонок в сложных запросах