Из боли клиентов — в новый продукт: как мы пересобрали аналитику на Clickhouse
Статья рассказывает о кейсе перехода с OLTP-системы (сфокусированной на быстрых транзакциях) к OLAP-системе (оптимизированной для аналитики и работы с большими объемами многомерных данных) на примере LMS-платформы Teachbase.
Чем лучше разработчик, тем хуже он делает аналитику. Просто потому, что для этого требуется разный технологический стек.
Системы, которые пишут разрабы, шикарно записывают и хранят данные. Но попытка прочесть большой объём данных сразу роняет всю систему, так как она плохо для этого предназначена.
В этой статье я расскажу про 2 ключевых подхода к хранению и обработке данных, какой мы выбрали для аналитики в Saas-платформе и к чему это привело.
Системы, которые пишут разрабы, шикарно записывают и хранят данные. Но попытка прочесть большой объём данных сразу роняет всю систему, так как она плохо для этого предназначена.
В этой статье я расскажу про 2 ключевых подхода к хранению и обработке данных, какой мы выбрали для аналитики в Saas-платформе и к чему это привело.
Какие бывают подходы к хранению и обработке данных
Существует 2 разных подхода к хранению и обработке данных:
OLTP прекрасно предназначена под быструю запись большого количества коротких транзакций. Например, для банковских операций – перевод денег со счёта на другой, проверка остатка на счёте. Для интернет-магазина – оформление заказа, добавление товара в корзину и т.д.
Но когда необходимо прочитать больший по размеру объём данных, то лучше работает Olap подход. Например, определить, какие товары чаще всего заказывают по регионам или посчитать, в какие дни пользователи чаще пополняют счёт через банкоматы.
Существует 2 разных подхода к хранению и обработке данных:
- Oltp – online transaction processing, быстрый сбор данных. На нём лучше работают транзакционные системы
- Olap – получать большой объём многомерных данных за какой-то период, быстрое чтение базы. Лучше подходит для аналитических систем
OLTP прекрасно предназначена под быструю запись большого количества коротких транзакций. Например, для банковских операций – перевод денег со счёта на другой, проверка остатка на счёте. Для интернет-магазина – оформление заказа, добавление товара в корзину и т.д.
Но когда необходимо прочитать больший по размеру объём данных, то лучше работает Olap подход. Например, определить, какие товары чаще всего заказывают по регионам или посчитать, в какие дни пользователи чаще пополняют счёт через банкоматы.
Если OLTP оптимизирован под работу с отдельными записями (например, оформление заказа), то OLAP — под массовую агрегацию и разрезы (например, сравнение продаж по регионам за квартал).
Потому дата-инженеры придерживаются olap технологии, так как в oltp подходе попытка сделать какой-то массивный отчёт может заблокировать всю систему, как периодически происходит с 1С.
Я расскажу кейс, как можно обогатить oltp систему, если вам необходимо сделать более продвинутую аналитику, чем Excel таблички.
Потому дата-инженеры придерживаются olap технологии, так как в oltp подходе попытка сделать какой-то массивный отчёт может заблокировать всю систему, как периодически происходит с 1С.
Я расскажу кейс, как можно обогатить oltp систему, если вам необходимо сделать более продвинутую аналитику, чем Excel таблички.
Почему потребовался переход от oltp к olap системе
Teachbase – это LMS-платформа, работающая как со внутренним обучением, так и с ДПО, обучением заказчика, проведением олимпиад и т.д.
У их клиентов уже был запрос на аналитику. Платформа выдавала её в формате csv таблиц в Экселе. Клиенты скачивали таблицу и пытались её прочесть, но это было неудобно. Из-за этого компания недозакрывала часть сделок.
Teachbase – это LMS-платформа, работающая как со внутренним обучением, так и с ДПО, обучением заказчика, проведением олимпиад и т.д.
У их клиентов уже был запрос на аналитику. Платформа выдавала её в формате csv таблиц в Экселе. Клиенты скачивали таблицу и пытались её прочесть, но это было неудобно. Из-за этого компания недозакрывала часть сделок.
Их база данных насчитывала более 10 миллионов строк. Если применить oltp подход и собрать графику на таком объёме данных, база упадёт в out of memory.
Teachbase накопили большой объём данных, который оставалось лишь правильно прочитать и визуализировать. Для этого olap подход подходит куда лучше, чем oltp, поэтому потребовалось сделать переход от одной системы работы с данными к другой.
Расскажу, как мы подошли к решению этой задачи.
Teachbase накопили большой объём данных, который оставалось лишь правильно прочитать и визуализировать. Для этого olap подход подходит куда лучше, чем oltp, поэтому потребовалось сделать переход от одной системы работы с данными к другой.
Расскажу, как мы подошли к решению этой задачи.
Ход проекта
Определили ключевые термины/метрики и способы их расчёта
Самая первая часть была не техническая, а методологическая. Важно было определить ключевые понятия и считать их единым образом в разных таблицах.
Например, возьмём понятие «завершённый курс». Какой курс считать завершённым? Если дедлайн истёк – то курс завершился? А если человек сделал все задания, но не прошёл проходной балл – то как в этом случае?
В итоге определили, что завершённый курс – это тот, который начался и завершился в рамках конкретного периода, и пользователь по нему набрал определённое количество баллов.
Пока у каждого ключевого понятия в системе не появится чёткого описания в конкретных полях таблиц из базы данных, нет смысла переходить к разработке системы. Потому что в разных таблицах одна и та же сущность может иметь разный смысл. Например, id может стать order_id, а session_id - просто id.
Только после того, как определились с ключевыми терминами и метриками, можно переходить к техническому переносу.
Например, возьмём понятие «завершённый курс». Какой курс считать завершённым? Если дедлайн истёк – то курс завершился? А если человек сделал все задания, но не прошёл проходной балл – то как в этом случае?
В итоге определили, что завершённый курс – это тот, который начался и завершился в рамках конкретного периода, и пользователь по нему набрал определённое количество баллов.
Пока у каждого ключевого понятия в системе не появится чёткого описания в конкретных полях таблиц из базы данных, нет смысла переходить к разработке системы. Потому что в разных таблицах одна и та же сущность может иметь разный смысл. Например, id может стать order_id, а session_id - просто id.
Только после того, как определились с ключевыми терминами и метриками, можно переходить к техническому переносу.
Какой подход мы выбрали для сбора и чтения данных
Аналитика может забирать и читать данные двумя способами:
Батчи же используются в случаях, когда данные не нужно собирать в реальном времени, достаточно раз в период. Например, в продажах.
Аналитика может забирать и читать данные двумя способами:
- стриминг (данные нужно получать секунда в секунду)
- батчи (кусочки)
Батчи же используются в случаях, когда данные не нужно собирать в реальном времени, достаточно раз в период. Например, в продажах.
Почти в любой BI-аналитике достаточно собирать данные раз в день. Потому мы и остановились на батчах вместо стриминга.
Почему важно определиться с терминами
Представьте систему, в которой существует 300 таблиц, они собирались и дополнялись в течение 5 лет разными людьми. Это большой и разрозненный массив данных, который нужно как-то обрабатывать.
Важная разница между oltp и olap – нормирование данных. В oltp данные нормализуются и собираются под любой чих, появляется куча таблиц с кучей сущностей.
Например, чтобы сохранить действия большого количества людей, проходящих курсы, в oltp методе это описывается через 25 таблиц, в каждой из них 10-15 столбцов с данными.
Затем эти данные нужно проанализировать и понять, а что из всего этого массива означает, что студент прошёл курс. Для этого нужно сделать ряд объединений таблиц.
В итоге мы понимаем, что человек прошёл курс, когда собрали следующие данные: какой учебный поток, когда дата старта, какие итоговые баллы за тест, являются ли 3 нужные столбцы ненулевыми и т.д.
Аналогичный процесс происходит, когда аналитик задаёт вопрос «а сколько у нас пользователей начали, но не закончили обучение».
Даже одно слово «обучение» может пониматься по-разному, и приходилось задавать уточняющие дополнительные вопросы:
Сущность «прогресс» может быть отражена в 4-5 разных таблицах в разных полях, которые могут неочевидно называться. И нужно все эти таблицы отыскать, вычленить оттуда данные, и правильным образом соединить в единую таблицу.
Следующий шаг – перенести все данные из базы данных LMS в аналитическое хранилище. Потому что делать аналитические запросы напрямую на oltp систему значит уронить всю систему из-за высокой нагрузки.
Представьте систему, в которой существует 300 таблиц, они собирались и дополнялись в течение 5 лет разными людьми. Это большой и разрозненный массив данных, который нужно как-то обрабатывать.
Важная разница между oltp и olap – нормирование данных. В oltp данные нормализуются и собираются под любой чих, появляется куча таблиц с кучей сущностей.
Например, чтобы сохранить действия большого количества людей, проходящих курсы, в oltp методе это описывается через 25 таблиц, в каждой из них 10-15 столбцов с данными.
Затем эти данные нужно проанализировать и понять, а что из всего этого массива означает, что студент прошёл курс. Для этого нужно сделать ряд объединений таблиц.
В итоге мы понимаем, что человек прошёл курс, когда собрали следующие данные: какой учебный поток, когда дата старта, какие итоговые баллы за тест, являются ли 3 нужные столбцы ненулевыми и т.д.
Аналогичный процесс происходит, когда аналитик задаёт вопрос «а сколько у нас пользователей начали, но не закончили обучение».
Даже одно слово «обучение» может пониматься по-разному, и приходилось задавать уточняющие дополнительные вопросы:
- а какое обучение?
- в какую программу оно включено?
- курс/не курс?
- отдельный вебинар или задание?
Сущность «прогресс» может быть отражена в 4-5 разных таблицах в разных полях, которые могут неочевидно называться. И нужно все эти таблицы отыскать, вычленить оттуда данные, и правильным образом соединить в единую таблицу.
Следующий шаг – перенести все данные из базы данных LMS в аналитическое хранилище. Потому что делать аналитические запросы напрямую на oltp систему значит уронить всю систему из-за высокой нагрузки.
При анализе таблиц важно было не потерять иерархию: внутри каждой программы есть отдельные курсы, а внутри них – несколько заданий. Под каждую сущность были отдельные таблицы, которые должны были быть логически соединены между собой.
Нужно было сделать так, чтобы при соединении таблиц все нужные строки и столбцы оказались заполнены. Это не всегда получалось, потому что ученикам могли быть назначены разные сущности. Например, одному ученику назначили программу целиком, а другому – только конкретные задания.
Нужно было сделать так, чтобы при соединении таблиц все нужные строки и столбцы оказались заполнены. Это не всегда получалось, потому что ученикам могли быть назначены разные сущности. Например, одному ученику назначили программу целиком, а другому – только конкретные задания.
Технические сложности в реализации
Мы предсобрали view на стороне системы-источнике, превратив 50 старых таблиц в 8 новых, которые не будут перегружать систему. Раз в сутки мы собираем эти данные в хранилище через инструмент Yandex Datatransfer. (Extract)
Клиент использует Management service Яндекса, чтобы сократить расходы на поддержку сложных решений. Потому создавая MVP, мы сокращали стоимость инфраструктуры и использовали продукты Яндекса.
Изначально мы тащили все нужные таблицы со всеми данными из базы источника, а view делали уже в промежуточной базе. Это и был самый большой косяк.
Мы предсобрали view на стороне системы-источнике, превратив 50 старых таблиц в 8 новых, которые не будут перегружать систему. Раз в сутки мы собираем эти данные в хранилище через инструмент Yandex Datatransfer. (Extract)
Клиент использует Management service Яндекса, чтобы сократить расходы на поддержку сложных решений. Потому создавая MVP, мы сокращали стоимость инфраструктуры и использовали продукты Яндекса.
Изначально мы тащили все нужные таблицы со всеми данными из базы источника, а view делали уже в промежуточной базе. Это и был самый большой косяк.
Во второй версии мы решили оставить промежуточную postgress базу, но не делать на ней таблички, готовые для дашборда. Вместо этого мы сделали промежуточные таблички, которые потом можно использовать для нескольких дашбордов сразу. Следующим шагом понесли таблички в Clickhouse и сверху с помощью serverless функций делали таблицы для дашбордов.
Это решение сработало, но тратило дополнительные деньги на промежуточную базу.
Все эти сложности возникли потому, что изначально мы не хотели нагружать базу первоисточника, чтобы её не шатало. Создание промежуточного слоя данных – сложная операция, которая нагружает базу по ЦПУ и оперативке.
Clickhouse в принципе не предназначен для большого количества объединений. На большом объёме данных он плохо преобразует 20 таблиц в 3, потому туда нужно переносить только уже готовые таблицы.
Потому мы и пытались сделать промежуточную postgress базу, которая хорошо умеет соединять много таблиц в одну. Но в итоге получилась одна огромная таблица, которую было сложно читать на два раза на выходе.
Ещё это приводило к двум трансферам – из одной postgress базы в другую промежуточную, а затем в Clickhouse.
Весь этот процесс был дорогим, потому решили пойти другим путём. Протестировали, какая нагрузка будет на продовой базе, посмотрели, не перегружаем ли мы её запросами. Сделали фильтры, чтобы показывались не все данные, а только те, которые нужны клиентам, купившим аналитику.
В итоге мы решили собирать промежуточные слои данных сразу из продовой базы, нести их сразу в clickhouse и там уже делаем витрины для дашбордов. Сами витрины делаем физическими таблицами, прописываем для них индексацию для более быстрой работы.
Как реализовали итоговое решение
Данные мы складывали в Clickhouse (Load), где уже раскладывали на витрины данных от клиентов (Transformed).
Вычисление метрик происходит в базе данных, и уже готовые таблицы с метриками визуализируются дашбордами.
ELT процесс прокручивали с помощью Яндекс функций с ограниченным временем исполнения, для MVP времени выполнения хватало.
Под каждого клиента был отдельный дашборд и витрина, чтобы физически изолировать данные друг от друга.
https://datalens.yandex/938ncx75hvaqv тут можно посмотреть демодашборд
Первую итерацию дашбордов мы сделали на postgress, который уже использовался в oltp базе. Тестовую базу сделали так, чтобы не шатать прод. Да, витрины были кривые, источники косые, но зато можно было увидеть конечный результат, как он будет выглядеть.
Дальше мы показали первую итерацию клиентам Teachbase, получили обратную связь и полностью пересобрали систему. Оказалось, что некоторые данные клиентам были не нужны, зато другие – в приоритете.
Например, внутри программы или курса обучения есть много видов данных: тесты, задания, документы, скормы и т.д. Мы оставили только данные по тестам и заданиям, а все остальные не собирали. Тем самым мы снизили нагрузку на систему.
Даже когда мы брали данные из системы только за последний месяц, то обработка была супер медленная, потому перенесли данные на кликхаус и вторую итерацию по разработке дашборда делали уже там.
Вторая итерация оказалась финальной.
Итог
У Teachbase возник продукт, который компания может продавать своим клиентам. Бизнесу это даёт повышение конверсии/среднего чека, а заодно и конкурентное преимущество.
Для разработки этой системы мы не привлекали инженеров и обошлись силами двух дата аналитиков. Дата инженеров привлекали только в том случае, когда упирались в хардовый SQL.
Это решение сработало, но тратило дополнительные деньги на промежуточную базу.
Все эти сложности возникли потому, что изначально мы не хотели нагружать базу первоисточника, чтобы её не шатало. Создание промежуточного слоя данных – сложная операция, которая нагружает базу по ЦПУ и оперативке.
Clickhouse в принципе не предназначен для большого количества объединений. На большом объёме данных он плохо преобразует 20 таблиц в 3, потому туда нужно переносить только уже готовые таблицы.
Потому мы и пытались сделать промежуточную postgress базу, которая хорошо умеет соединять много таблиц в одну. Но в итоге получилась одна огромная таблица, которую было сложно читать на два раза на выходе.
Ещё это приводило к двум трансферам – из одной postgress базы в другую промежуточную, а затем в Clickhouse.
Весь этот процесс был дорогим, потому решили пойти другим путём. Протестировали, какая нагрузка будет на продовой базе, посмотрели, не перегружаем ли мы её запросами. Сделали фильтры, чтобы показывались не все данные, а только те, которые нужны клиентам, купившим аналитику.
В итоге мы решили собирать промежуточные слои данных сразу из продовой базы, нести их сразу в clickhouse и там уже делаем витрины для дашбордов. Сами витрины делаем физическими таблицами, прописываем для них индексацию для более быстрой работы.
Как реализовали итоговое решение
Данные мы складывали в Clickhouse (Load), где уже раскладывали на витрины данных от клиентов (Transformed).
Вычисление метрик происходит в базе данных, и уже готовые таблицы с метриками визуализируются дашбордами.
ELT процесс прокручивали с помощью Яндекс функций с ограниченным временем исполнения, для MVP времени выполнения хватало.
Под каждого клиента был отдельный дашборд и витрина, чтобы физически изолировать данные друг от друга.
https://datalens.yandex/938ncx75hvaqv тут можно посмотреть демодашборд
Первую итерацию дашбордов мы сделали на postgress, который уже использовался в oltp базе. Тестовую базу сделали так, чтобы не шатать прод. Да, витрины были кривые, источники косые, но зато можно было увидеть конечный результат, как он будет выглядеть.
Дальше мы показали первую итерацию клиентам Teachbase, получили обратную связь и полностью пересобрали систему. Оказалось, что некоторые данные клиентам были не нужны, зато другие – в приоритете.
Например, внутри программы или курса обучения есть много видов данных: тесты, задания, документы, скормы и т.д. Мы оставили только данные по тестам и заданиям, а все остальные не собирали. Тем самым мы снизили нагрузку на систему.
Даже когда мы брали данные из системы только за последний месяц, то обработка была супер медленная, потому перенесли данные на кликхаус и вторую итерацию по разработке дашборда делали уже там.
Вторая итерация оказалась финальной.
Итог
У Teachbase возник продукт, который компания может продавать своим клиентам. Бизнесу это даёт повышение конверсии/среднего чека, а заодно и конкурентное преимущество.
Для разработки этой системы мы не привлекали инженеров и обошлись силами двух дата аналитиков. Дата инженеров привлекали только в том случае, когда упирались в хардовый SQL.