ClickHouse + 1С: архитектура, подводные камни, лучшие практики

Проблема медленных отчетов в 1С

В этой статье мы поговорим о том, как подружить 1С и ClickHouse: рассмотрим архитектуру, подводные камни, лучшие практики и в целом обсудим, как разгрузить учетную систему и сделать это хорошо.

Отчеты бывают непростые, бывают очень сложные. Например, себестоимость по 100 млн записей может формироваться достаточно долго. Долго работающие отчеты – частая ситуация для 1С, классическая проблема.

Причина проблемы: OLTP vs OLAP

Корень проблемы в том, что мы совмещаем два понятия: OLTP и OLAP.

OLTP – это родная нагрузка 1С и наших привычных СУБД, MS SQL, Postgres. Это множество коротких операций: провести документ, записать изменения, изменить статус. Здесь главное – скорость и надежность одной транзакции. Это операционная работа, к которой мы привыкли и которую постоянно используем.

Аналитические отчеты и сложные отчеты – это OLAP-нагрузка. Здесь сложные, тяжелые запросы к огромным массивам данных. Отчет по продажам за 5 лет с динамикой по месяцам и агрегацией – это OLAP-нагрузка. Здесь важна скорость обработки миллионов строк данных.

Проблема в том, что мы пытаемся заставить надежный транзакционный движок 1С, заточенный на OLTP-нагрузку, выполнять OLAP-нагрузку и делать аналитические отчеты. Это как пытаться на семейном мини-вэне выиграть Формулу-1. Особенно, когда база становится больше 1 ТБ.

О ClickHouse

Решение ClickHouse пришло из мира Big Data Многие уже слышали про этого представителя колоночных СУБД.

Если сравнивать его с привычной SQL-базой, то SQL – это семейный мини-вэн, который подходит для разных задач. ClickHouse – это болид Формулы-1, разработанный для конкретной цели: быстрой аналитики и быстрых аналитических отчетов.

ClickHouse – разработка компании «Яндекс». Он был создан при переходе на Метрику 2.0. У Метрики 2.0 огромное количество данных, и система должна работать с ними быстро. Так и есть – система работает очень быстро.

Колоночное хранение данных

Разберемся, за счет чего это работает. Есть три основных механизма, обеспечивающих высокую скорость. Один из столпов ClickHouse – колоночное хранение.

В отличие от стандартных СУБД, ClickHouse хранит все колонки отдельно и обрабатывает данные по колонкам.

Если нужно посчитать сумму за год, стандартной СУБД требуется прочитать всю таблицу. ClickHouse достаточно взять одну колонку и выполнить расчет по ней. Это в десятки и сотни раз меньше данных.

Однако здесь есть тонкий момент. 1С-ники привыкли жить строками: документы в строках, записи в строках, весь подход построен на строках. Когда мы начинаем разрабатывать сущности для ClickHouse, возникает нюанс.

В 1С выбрать «звездочку» из регистра – допустимая история.

Для ClickHouse это критично: он вытащит все колонки, и это будет крайне плохо с точки зрения производительности. Такой способ ему не подходит.

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

Сжатие данных

Второй секретный ингредиент – фантастическое сжатие. ClickHouse сжимает свои данные очень сильно.

ClickHouse обладает большим количеством типов данных. Подбирая тип данных, можно оптимально использовать занимаемое пространство. Ниже этот нюанс будет подробнее разобран.

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

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

При этом ClickHouse позволяет полностью отключить сжатие. Если нет задачи экономить пространство, можно отказаться от операций сжатия и разжатия и тем самым сократить накладные расходы.

Векторные вычисления

Третий секретный ингредиент – векторные вычисления. Это ключевая мощность ClickHouse: все операции выполняются векторами, наборами данных.

Если нужно посчитать сумму чеков по набору данных, стандартная СУБД будет брать один чек, прибавлять к сумме, затем второй, третий и так до миллиона записей.

ClickHouse берет колонку целиком и применяет одну функцию SUM ко всей колонке.

Это возможно благодаря современным серверным инструкциям SIMD. Они позволяют применять к набору данных целые методы. ClickHouse активно использует этот механизм. В результате не требуется последовательно выполнять операции для каждого элемента – функция применяется сразу ко всему набору данных.

Типы данных и оптимизация

Основные типы данных в ClickHouse:

• Целые: UInt8/16/32/64/128/256 и Int8/16/32/64/128/256.

• Вещественные: Float32, Float64.

• Десятичные: Decimal(P,S), включая Decimal32/64/128/256.

• Boolean (алиас UInt8).

• String и FixedString(N).

• Даты/время: Date, Date32, DateTime, DateTime64.

• UUID, IPv4, IPv6.

• Коллекции и сложные: Array(T), Tuple(...), Map(K,V), Nested, LowCardinality(T), Nullable(T), Enum8/16.

Видно большое количество типов данных: UInt от 8 до 256, Int, Float и другие.

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

При входе в ClickHouse для GUID мы начали использовать фиксированные строки FixedString(36). Этот тип занимает больше места, чем UUID. После перевода таблиц с FixedString на UUID занимаемое пространство сократилось в два раза.

Сокращение объема данных в два раза означает в два раза меньше чтений и обращений к диску. Запросы начинают работать быстрее.

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

Архитектура интеграции 1С и ClickHouse

В итоге получается следующая связка: есть 1С, есть основная база – MS SQL или PostgreSQL – и есть ClickHouse.

Основная база не заменяется. ClickHouse дополняет ее и снимает аналитическую нагрузку с основной СУБД. Таким образом, инструменты разделяются по назначению.

Методы выгрузки данных из 1С в ClickHouse

Метод «В лоб»: выгрузка через файлы (CSV, JSON). Формируются они достаточно легко, но обработка происходит медленнее. Такой способ подходит для разовых операций. Можно выгрузить данные и загрузить их в ClickHouse.

Метод «ИЗНУТРИ» (HTTP API ClickHouse): использование встроенного веб-сервера ClickHouse. После установки ClickHouse из коробки доступен веб-сервер. Можно зайти по адресу сервера, выполнить любые запросы, использовать простой интерфейс. В 1С формируется HTTP-запрос и нужные данные отправляются в ClickHouse. Метод рабочий, не требует дополнительной инфраструктуры. Однако на больших объемах данных возможны задержки.

Метод «Швейцарские часы» (брокеры сообщений). Это надежный и асинхронный способ, который не нагружает 1С. Формируется пакет данных и отправляется в брокер сообщений. Данные доставляются в ClickHouse независимо от основной системы.

Метод «Проктолог» (CDC через Debezium). Решение работает с минимальной нагрузкой на 1С, но требует сложной настройки. Потребуются прямые руки либо хороший DevOps-инженер, который это настроит. Метод рабочий, но имеет нюансы при массовых изменениях данных.

Как получить данные из ClickHouse

ClickHouse имеет встроенный веб-сервер. Можно обращаться к нему через HTTP-запрос. Для простых запросов используется GET. Для сложных – POST, где SQL-запрос передается в теле запроса.

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

Альтернативный вариант – ODBC-драйвер и внешние источники данных. Это позволяет работать с таблицами ClickHouse почти как с таблицами 1С.

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

Моя личная рекомендация – использование брокеров сообщений. Связка: 1С + Apache Kafka + HTTP для чтения. Kafka справляется с большими потоками данных, работает асинхронно и она у нас уже есть.

Для настройки интеграции со стороны ClickHouse требуются три сущности:

Целевая таблица – таблица, в которую будут записываться данные. Используется необходимый движок хранения.

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

Читатель – материализованное представление, которое переносит данные из очереди в целевую таблицу. Материализованные представления работают как триггер на вставку данных. При появлении данных в очереди они автоматически переносятся в целевую таблицу.

Необходимо отслеживать изменения формата или типов данных. При их изменении может нарушиться работа очереди. Рост отставания в Kafka сигнализирует о проблеме синхронизации. В этом случае требуется корректировка Очереди или Читателя, то есть понадобится пересобрать наши таблицы.

Процесс обработки выполняется в фоне. При больших объемах данных возможна небольшая задержка.

Особенности денормализации в ClickHouse

Есть еще один подводный камень. В классических СУБД нас учили нормализовать данные: разделять их по таблицам, выносить сущности отдельно, выстраивать структуру аккуратно.

В ClickHouse все наоборот. ClickHouse любит денормализацию. Ему важно, чтобы данные были собраны сразу. Постоянные переходы по таблицам и сбор данных из разных источников ему не подходят.

ClickHouse плохо работает с JOIN. Для него JOIN – тяжелая операция. Ему лучше, когда данные собраны в одну большую, широкую таблицу. При правильно подобранных типах данных проблем с объемом не возникает. Поэтому данные нужно денормализовать и обогащать.

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

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

Итак,

• мы познакомились с миром аналитики,

• разобрали, почему ClickHouse такой быстрый,

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

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

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

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

*************

Статья написана по итогам доклада (видео), прочитанного на конференции INFOSTART TECH EVENT.