SQL backend или как помочь BI работать с BigData
Привет, Хабр! В этом посте мы поговорим о том, как происходит подключение к внешней СУБД для BI-платформы Visiology, а также разберем ситуации, когда это действительно нужно. Под катом — подробный разбор возможностей SQL backend, которым может воспользоваться сегодня фактически каждый пользователь Visiology. Кроме этого мы остановимся на нюансах подключения различных СУБД, а также обсудим, в каких ситуациях можно «сесть на два стула» и получить как скорость расчетов, так и поддержку бигдаты для очень больших объемов данных.
В этом посте речь пойдет о том, как наша BI-платформа взаимодействует с внешним хранилищем (data warehouse — DWH), а также о преимуществах и ограничениях инструментария SQL Backend. Судя по количеству вопросов, которые задают по этому поводу наши пользователи и партнеры, тема сегодня не просто актуальна, а очень актуальна: данных становится все больше, количество источников растет и потребность обратиться к внешней базе возникает сегодня намного чаще, чем, скажем, в прошлом году.
Несколько слов про возможности ViQube
Одна из отличительных особенностей Visiology — использование собственного движка ViQube. По умолчанию он работает в режиме In-Memory, когда все данные, необходимые для анализа размещаются в оперативной памяти. Режим очень полезный и практичный, так как позволяет максимально быстро «дотянуться» до любого фрагмента данных, а также избежать затрат на создание DWH (временных и финансовых) для тех проектов, когда можно полностью уместиться в оперативную память. В принципе в современный сервер можно без проблем установить до 128 Гб памяти. И на некоторых проектах на базе Visiology (например, в корпорации МСП), возможности размещения In-Memory используются по максимуму.
Но бывают случаи, когда такая схема не удобна. Например, если данные в принципе не могут влезть в оперативную память, а также в случае постоянного обновления источника (когда процесс загрузки обновления данных в In-Memory движок создает дополнительные сложности и становится «узким бутылочным горлышком»), возникает желание подключиться к СУБД для анализа напрямую. Также это актуально и в тех случаях, когда в компании уже есть хорошо организованное DWH — оно может оставаться от другого BI-проекта или использоваться для других задач. Именно для этих случаев у нас долгое время просили сделать SQL-бэкенд.
Подключение SQL-бэкенда
В конце 2022 года Visiology уже поддерживала подключение через SQL напрямую к различным СУБД. Начиная с версии 2.29 мы поддерживаем следующие СУБД: ClickHouse Arenadata QuickMarts, PostgreSQL, Arenadata DB, Vertica, Greenplum, MySQL, Oracle, а в версии 2.30 мы добавили еще Microsoft SQL.
В случае с Visiology настройка SQL-бэкенда происходит на уровне таблиц данных, и работает через запросы API.
Для начала нужно создать в ViQube таблицы, полностью соответствующие тем, которые будут использованы через SQL Backend (Это необходимо для корректного маппинга при трансляции запросов в СУБД).
Дело в том, что при отработке запроса (с трансляцией в SQL) вы получаете данные, которые попадают в таблицу с результатом. Чтобы движок работал корректно и вы могли получить результирующую таблицу, в ViQube должна быть точно такая же структура с точно такими же типами полей. Наименования таблицы и столбцов могут отличаться, но типы данных должны быть одинаковыми. Также в ViQube могут отсутствовать те столбцы, которые не нужны для аналитических расчетов. ViQube просто будет игнорировать их при обращении к SQL Backend. Кстати, создать таблицы можно с помощью стандартного механизма загрузки данных. Например, вы можете загрузить первые 10 строк, чтобы получить аналогичную таблицу и настроить ее подключение к внешней СУБД (после этого данные из ViQube будут игнорироваться).
Далее нужно подготовить SQL-запрос для подключения в СУБД. Базовый вариант структуры таких настроек можно найти в документации.
В случае если вы пользуетесь облачной версией ClickHouse, дополнительно к прописыванию SQL-запроса необходимо также скачать сертификат яндекса и сохранить его в папку конфигурации ViQube. А при настройке SQL Backend также указать параметры JDBC (пункт jdbcOptions):
"jdbcOptions": {
2 "connection": "jdbc:clickhouse://{ADDRESS}:{PORT}/{DB_NAME}?user={USER};password={PASSWORD}",
3 "ssl": true,
4 "sslrootcert": "/mnt/volume/CA.pem",
5 "socket_timeout": 300000,
6 "isResetUpdateTime": false
Данные уже в таблицах
Гибридный режим
Когда я рассказываю про SQL-бэкенд, меня очень часто спрашивают «а можно ли как-то сохранить при этом скорость как при работе с In-Memory?». Ответ будет «Можно, но для тех запросов, которые не используют SQL-бэкенд». ViQube может работать в гибридном режиме, когда часть данных поступает из внешней СУБД, а другая часть обрабатывается в In-Memory хранилище движка ViQube.
Активировать гибридный режим очень просто. Достаточно при настройке SQL Backend пропустить те таблицы, которые должны получать данные из ViQube (In-memory). А в SQL Backend указать только те таблицы, которые должны обращаться напрямую во внешнюю СУБД. Благодаря этому появляется возможность настроить разные запросы на разные источники данных: ViQube (In-Memory) и/или ViQube (SQL Backend).
С точки зрения результата гибридный режим позволяет объединить на одном дашборде сразу несколько источников данных. Главное, чтобы каждый виджет (и, соответственно, каждый отдельно взятый запрос) опирался на данные из одного источника. Благодаря этому в Visiology можно получать максимально быструю аналитику для наиболее критически важных данных, но не отказываться при этом от глубокого анализа огромных массивов данных.
Например, такая схема может хорошо показывать себя при обработке событий в масштабах города (как раз Visiology используется в ряде регионов РФ и даже за пределами страны). Да, когда речь идет о сложных расчетах и возникают требования к высокой скорости и производительности имеет смысл подгружать и обрабатывать в режиме In-Memory, но при этом SQL Backend также позволяет анализировать данные из внешней СУБД одного из ведомств, например, ФНС или городского ситуационного центра.
Некоторые ограничения
При работе в режиме SQL backend необходимо учитывать некоторые нюансы при работе с данными. Они объясняются тем, что разные архитектуры хранилищ ведут себя по-разному, когда речь идет о логических переменных, пустых ячейка, операциях с датами и так далее.
В PostgreSQL не работает оператор sum (boolean). Поэтому могут быть отличия в результатах работы расчетных показателей, по сравнению с режимом In-Memory.
В Oracle вообще нет типа данных Boolean, и ViQube воспринимает такие поля как целочисленные (0/1). По этой причине не будут работать логические выражения, если включить их SQL-запрос.
В VERTICA при сортировке по полю нет возможности указывать характер сортировки NULL-значений (потому что там нет директив NULL FIRST/LAST). Нужно просто учитывать, что местоположение строк при сортировке полей с NULL-значениями зависит от типа сортируемых данных.
Для MySQL есть сразу несколько нюансов:
При настройке Backend нет возможности указать, каким образом сортировать NULL-значения, так как в MySQL не работают директивы NULLS FIRST и NULLS LAST.
Для получения разности дат, необходимо использовать функцию DATEDIFF вместо date1 — date2. Например: SELECT SUM (DATEDIFF (period2_id, period_id)) AS `0–0` FROM `fact_rur`
На запросы с указанием гранулярности Год за неделю приходят значения за каждый год и неделю в году.
Нужно быть аккуратнее при сравнении строковых значений, потому что в MySQL используется иная кодировка по сравнению с ViQube.
Из числа общих моментов:
При экспорте таблицы из ViQube (если источником данных таблицы является внешняя база данных), экспортируются только те данные, которые были внесены в базу данных ViQube ранее. Все, что осталось на бэкенде не попадет в экспорт.
На уровне SQL Backend не поддерживаются операции редактирования строк в таблицах, к которым, возможно, привыкли пользователи Visiology. В этом режиме возможно только чтение данных. При получении запроса на редактирование строка просто улетит в таблицу на ViQube (In-memory) и не будет видна при включенном SQL Backend.
Также имеются общие отличия при работе с NULL в выражениях (по сравнению с ViQube (In-memory)):
В результате сравнения с NULL в случае ViQube (In-memory) будет возвращено FALSE, а в случае ViQube (SQL Backend) будет возвращено NULL
При арифметических операциях NULL в ViQube (In-memory) интерпретируется как (1+NULL =1), а в SQL-базе как UNDEFINED (1+NULL =NULL)
Впрочем, если учесть эти моменты в остальном работа с SQL Backend происходит достаточно просто, особенно если использовать уже подготовленные скрипты и максимально автоматизировать процесс подключения источников данных.
Работа с внешним DWH
Также гибридный режим часто используется для того, чтобы подключить внешнее хранилище данных. За последний год это стал достаточно популярный сценарий, потому что когда компания выбирает замену ставшему недоступным инструменту аналитики и визуализации, каждый, кто участвует в этом процессе, хочет свести трудозатраты и время проекта к минимуму. В этих случаях можно пойти двумя путями: настроить регулярное зеркалирование всех актуальных данных в ViQube, чтобы обрабатывать их в памяти или, если данных много и обновляются они достаточно часто, использовать SQL backend.
Отчет системы о получении данных через SQL Backend
Особенно эффективна работа SQL backend в том случае, если над хранилищем данных уже работал опытный архитектор в нем произведены оптимизации, соответствующие тем типам данных и объемам, которые важны для аналитиков в конкретной компании.
Если же хранилище данных создается с нуля, потребуется немало усилий для того, чтобы оно действительно могло эффективно отрабатывать аналитические запросы. И чем больше объемы данных, размещенных в хранилище, тем заметнее будет эта разница. Взять, например, ClikHouse. Об эффективности доступной колоночной СУБД говорят уже давно (это, кстати, было одной из причин почему мы интегрировали ClickHouse в ядро Visiology 3).
Однако без должной оптимизации ClickHouse будет работать намного медленнее, чем мог бы. И мои коллеги обязательно поделятся своим опытом оптимизации ClickHouse в следующих постах. А сегодня я закончу тем, что SQL backend оказался очень полезной разработкой, которую используют сегодня все больше и больше клиентов. Но при этом все масштабные и успешные кейсы подразумевают дополнительную оптимизацию хранилища, каким бы оно ни было — ClickHouse или PostgreSQL, Oracle или Arenadata, Greenplum или что-то еще.
Использовать SQL Backend или остаться в режиме In-Memory — зависит от конкретного проекта. Но в среднем, если в таблице фактов насчитывается более миллионов строк, объем данных превышает 100 Гбайт, либо база обновляется так часто, что временные затраты на импорт данных портят картину real-time аналитики (или все сразу), то SQL Backend становится хорошим решением, хотя и более сложным с точки зрения настройки и оптимизации производительности.
