Привет, Хабр! Сегодня мы поговорим о том, почему BI-платформам нужен движок, какие сложности создает ClickHouse для аналитиков, когда речь действительно заходит о больших данных, зачем нужно оптимизировать SQL и о многих других вещах, которые часто остаются «за горизонтом» в дискуссиях о BI и хранении данных. Говоря другими словами, я хочу рассказать о том, как мы разрабатывали ДанКо — новый движок, который лежит сегодня в основе Visiology 3, а главное — каким образом ДанКо позволяет достичь высокой производительности в аналитических задачах (в некоторых случаях показывая ускорение вплоть до х100)! Эта статья будет полезна тем, кто еще не сталкивался с задачей организации хранения аналитических данных компании, а также интересна тем, кто как раз, наоборот, уже делал это.

Зачем вообще нужен движок для BI?

В профильных сообществах уже несколько лет не утихают обсуждения и дискуссии о том, как лучше поступить — внедрить коммерческую BI-платформу или использовать что-то опенсорсное, например Apache SuperSet. 

И очень часто в подобных дискуссиях (пока дело не дойдет до конкретного проекта) упускается из виду тот факт, что SuperSet не ставит перед собой иных задач, кроме визуализации. И если у вас есть хороший опыт работы с инструментом, вы действительно сможете сделать на нем многое. Но для работы такой BI-платформы нужно организовать хранилище данных, которое будет предоставлять необходимую информацию для аналитических выборок. И даже при этом не все расчеты можно легко реализовать, для формирования модели на основе нескольких источников данных придется изрядно попотеть, а вопросы загрузки из Excel или других «не предназначенных» для этого источников вызывают еще больше трудностей. 

Вот типичные сложности, с которыми сталкиваются разработчики, когда данных становится все больше, а запросы — все сложнее:

1. Скорость работы хранилища — если вы просто возьмете и развернете ClickHouse и начнете загружать в него данные, очень скоро станет понятно, что далеко не все запросы обрабатываются быстро. Чтобы аналитическое хранилище на базе ClickHouse становилось ускорителем, а не тормозом BI-проекта, его надо оптимизировать. И об этом я уже подробно рассказывал в предыдущей статье на Хабре.. 

2. Права доступа к данным — тоже интересная тема, которая завязана на ролевые модели в компании. Если ваш BI просто берет информацию из хранилища, то как определить, кому из сотрудников какие строки можно показывать? В большинстве случаев этот вопрос решается дублированием таблиц и запуском отдельных инстансов BI-платформ для разных отделов и подразделений. Но мы все прекрасно понимаем, что это приводит к неприятностям с объемом хранимых данных и трудозатратами специалистов по поддержке всех версий  BI. Гораздо удобнее было бы интегрировать процесс доступа к данным на уровне дашбордов, но чтобы сделать это, необходимо сильнее «подружить» платформу и хранилище.

3. Написание сложных запросов — если над данными требуется какое-то преобразование, прежде чем вы захотите провести их анализ: нормализация, вычисление среднего, фильтрация, отсечение сильных отклонений и так далее, сделать это на уровне хранилища при помощи SQL-запросов, конечно, можно, но это не очень-то удобно и каждый раз будет требовать участия ИТ-специалистов. В общем, тут получается уже совсем не Self-Service.

4. Eventual Consistency — еще одна проблема заключается в загрузке больших объемов данных, которые, разумеется, не сразу оказываются на всех нодах одновременно. Собственные механизмы репликации ClickHouse не дают никаких гарантий скорости обновления информации, если не контролировать эти процессы вручную. А если мы начинаем воплощать в жизнь сценарии инкрементальной загрузки (которые не реализуются штатными средствами ClickHouse), тут вообще начинается кошмар, связанный с тем, чтобы понять — какие данные уже новые, а какие — нет.

5. Акцент на Self-Service — сегодня практически все пользователи BI-платформ хотят получить возможность самостоятельно работать как с готовыми дашбордами, так и с источниками данных и даже моделью данных. Но если мы позволяем пользователю формировать связи, подключать новые источники, делать выборки и готовить витрины без сопровождения ИТ-специалистов, вопрос оптимизации данных и их обработки становится еще острее — ведь в этом случае никто не будет думать, как именно разместить данные и каким образом правильно обращаться к ним. 

Все эти мысли говорят о том, что современной BI-платформе нужен собственный движок, который позволит максимально оптимизировать все эти процессы. Кстати, пользователи предыдущих версий Visiology знают, что у нашей платформы уже был свой неплохой движок, который мог переваривать достаточно большие объемы данных в режиме In-Memory. Это было быстро…но объем данных все-таки оказывался ограничен реальным количеством планок DDR, которые можно вставить на сервер. Поэтому вместе с Visiology 3 мы начали разрабатывать более низкоуровневый движок, который сегодня называется ДанКо.

А почему, собственно, не взять просто ClickHouse?

На реальных проектах Visiology 3 уже показала свои возможности производительности, масштабируемости и способность переваривать большие данные, например, реализованная специалистами ЛАНИТ система мониторинга ЕИС ГИС ЗАКУПКИ в Федеральном казначействе. И поскольку новейшая Visiology работает на базе ClickHouse, многие думают, что хорошие результаты в производительности появляются потому, что у нас под капотом правильная СУБД. Но это не совсем так…а может быть, даже совсем не так. 

Все дело в том, что кейсы использования ClickHouse в BI отличаются от наиболее распространенных сценариев работы с колоночной СУБД. В отличие от ML и аналитики реального времени, в BI имеются интересные особенности. Например, нам далеко не всегда нужно анализировать данные в реальном времени, и это создает возможности для дополнительных оптимизаций. Но при этом перед системой стоит задача обработки сложных аналитических запросов с высокой вариативностью, что делает задачу оптимизации более сложной. А учитывая, что спрогнозировать, какие ad-hoc запросы будут делать пользователи практически невозможно, для оптимизации ClickHouse под задачи BI требуется глубокая экспертиза. 

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

Доступность и отказоустойчивость

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

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

В ходе разработки мы сталкивались с задачами, когда нужно было обеспечить размещение набор данных, например не на все 10, а только на 3 ноды, учитывая характеристики серверов и текущую загруженность узлов кластера. А если какая-то нода упала, нужно сознательно не посылать на нее запросы, пока работают механизмы восстановления CH.

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

И это только несколько примеров, когда для решения реальных корпоративных задач необходимо «прикручивать» что-то поверх ClickHouse. Как минимум, еще семь аспектов оптимизации ClickHouse я рассмотрел в своей предыдущей статье на Хабре.

Итак, кто такой ДанКо?

Но вернемся к теме нашего разговора сегодня. Кто такой ДанКо, и что же он делает?

ДанКо — это отдельный слой автоматизации, который лежит над ClickHouse и является надежным фундаментом для дальнейшего развития аналитических практик. За счет того, что ДанКо контролирует размещение данных и оптимизирует их, у нас получается действительно умное хранилище. Мы проводим оптимизации на трех уровнях: загрузки данных, модели данных, запросов к данным.

Модель данных

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

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

Загрузка данных

На этапе загрузки данных также возможны различные оптимизации, которые помогают кратно увеличить скорость работы аналитического хранилища. В Visiology мы оптимизируем:

• семантическую модель, в том числе связи между таблицами и их направление,

• определение назначений и типов таблиц,

• добавление информации из Excel или в режиме ручного ввода через SmartForms,

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

• конфигурации инкрементальной загрузки,

• автоматический подбор движков для каждой таблицы,

• …

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

В ДанКо мы комбинируем эти способы в зависимости от объема таблицы, типов источников, ограничений доступа и способа загрузки (инкремент, полная загрузка и т.д.). Только за счет этого достигается ускорение работы аналитического хранилища в 2-3 раза.

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

Оптимальное использование оператора join также дает хороший эффект. Например, порядок таблиц в операторе JOIN в ClickHouse влияет на то какая таблица попадет в оперативную память в виде хэш-таблицы, а какая будет считываться с диска и ДанКо понимает, как оптимально расположить таблицы при вызове оператора в JOIN.

Если просчитывать эффект от всех 6 уровней оптимизации загрузки, которые включают в себя подбор order by-ключа, оптимизацию типов данных и join-ов, выбор движков и денормализацию, мы отметили ускорение загрузки вплоть до 100 раз! 

Конечно, достичь x100 можно только в идеальном случае, но по крайней мере х3 ДанКо демонстрирует при любых данных и в любой ситуации, где есть что оптимизировать.

Оптимизация запросов

Если вы уже знакомы с платформой Visiology 3, то прекрасно знаете, что одной из ее killer-фичей является поддержка синтаксиса DAX. Впервые он был внедрен корпорацией Microsoft и используется также в MS Power BI. Благодаря этому DAX хорошо знаком подавляющему большинству аналитиков, да и вообще за годы использования этот язык доказал, что он удобен и легко осваивается даже специалистами без ИТ-бэкграунда.

Но, разумеется, вы понимаете, что ClickHouse на DAX не разговаривает. Для обращения к СУБД используется SQL.

Но вопрос, насколько этот SQL будет работать быстро, остается открытым. Если вы писали запросы SQL к СУБД, то прекрасно знаете, что неоптимальные SQL-запросы могут обрабатываться в десятки и сотни раз дольше, чем выверенные запросы, учитывающие как контекст, так и особенности работы СУБД. 

Оптимизация запросов SQL в ДанКо включает в себя более 100 различных схем и приемов. За счет этого сокращается сложность и увеличивается скорость обработки запросов. 

Результаты

А благодаря тому, что ДанКо — это все-таки часть BI-платформы, Visiology Dashboards может эффективно использовать тюнинг хранилища, который был произведен для решения аналитических задач. Фактически с появлением ДанКо мы расширили опыт, который Visiology предоставляет своим пользователям. Многофункциональное хранилище, работающее внутри платформы, решает сразу целый спектр достаточно сложных задач. Вот основные из них:

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

2. Прямое подключение к данным при помощи сторонних приложений (например, прямо в Excel), что позволяет избежать переучивания пользователей, которые уже привыкли к работе с Excel как с системой визуализации в SAP BI.

3. Поддержка расчетов и трансформаций данных на базе синтаксиса DAX, который встроенными методами не только преобразуется в SQL, но и оптимизируется. 

4. Контроль прав доступа к данным на уровне строк (RLS) и создание рабочих пространств (Workspace), когда права доступа к данным на дашборде или витрине данных определяются изначальными корпоративными политиками, зафиксированными в каталоге пользователей.

Заключение

ДанКо представляет собой мощный слой управления данными, который решает за пользователя целый спектр задач, необходимых как в BI, так и для систематизации больших объемов данных. В отличие от кустарной доработки CH, ручного построения индексов, обвязки данных скриптами Python и копирования таблиц для повышения производительности, работа с ДанКо дает прозрачную схему взаимодействия с данными, а также готовый интерфейс с обратной совместимостью и преемственностью.

На сегодняшний день ДанКо уже можно использовать в контексте процессов разработки (Dev|Test|Prod), и это значит, что пользователи новейших версий Visiology имеют возможность не только полностью упорядочить свои аналитические процессы, но и глубоко погрузиться в структуру хранимых данных, чтобы получить максимальную отдачу от имеющейся у компании информации, с соблюдением всех требований к безопасности и разделения доступа.

Это был большой проект, который требовал много ресурсов, и работа над улучшениями ДанКо продолжается. Наверное, если бы в том же сегменте OpenSource было готовое предложение, способное обеспечить хотя бы часть этого функционала, мы бы использовали его. Но, увы, подобные задачи всегда приходилось решать вручную. И опыт проектов Visiology привел нас к разработке своего собственного подхода к автоматизации DWH.

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