Методы выгрузки данных из 1С для BI-аналитики

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

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

При анализе данных приходится работать с множеством источников: внешними базами данных, веб-сервисами, лидерами теневой IT-автоматизации (Excel, Google Таблицы), CRM-системами и 1С. Причем баз 1С может быть несколько — например, в холдинговой структуре или при раздельном учете затрат в «Бухгалтерии» и продаж в «Торговле». Сложность сбора этих данных — основная тема статьи.

Вызовы при работе с данными в 1С

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

Встроенные отчеты 1С, включая возможности Системы компоновки данных (СКД), мощны. Однако, по моему мнению, отчеты 1С зачастую созданы программистами для программистов — они не всегда интуитивно понятны и визуально ориентированы.

Существующие методы выгрузки данных из 1С и их ограничения

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

1. Формирование отчетов и выгрузка в Excel

Преимущество. Для работы в Excel не надо привлекать программистов, так как ее можно провести самостоятельно.

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

2. OData

Преимущество. Рекомендовано 1С. На сайте 1С указано, что стандартный метод для доступа к данным — это REST-сервис OData.

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

Подходит только для проверки гипотез с малыми объемами («песочница»).

3. 1С:Аналитика

Преимущество. Хорошая идея, можно «провалиться» вплоть до конкретного документа.

Недостатки. Работа только внутри экосистемы 1С, сложность консолидации данных из нескольких баз 1С и невозможность подключить внешние источники (Excel, базы данных и прочее).

4. COM-соединение

Преимущество. Если у вас есть навыки программирования, вы можете попробовать написать программный код с обращением к COM-компоненте 1С.

Недостатки. Такое решение подойдет далеко не всем, так как разработка специфична, плюс работает только из под Windows, а также занимает лицензию 1С Предприятия.

5. Прямой доступ к базе данных SQL в 1С

Преимущество. Самый быстрый способ извлечения данных.

Недостатки. Нарушение лицензионных соглашений («серая зона»), доступ к физической структуре таблиц 1С, требует глубокого знания Transact-SQL для построения сложных схем и значительные сложности в извлечении некоторых типов данных (остатки, срезы последних, реквизиты регистраторов, субконто).

Я не рекомендую использовать этот способ.

6. Привлечение программистов в 1С

Преимущество. Решение под конкретные требования.

Недостатки. Высокая загрузка IT-отделов, длительные сроки реализации. Во многих компаниях самым узким звеном является IT-отдел. Ну и написать задание для Отдела разработки непросто. Особенно — когда вы исследуете гипотезы по анализу данных и сформулировать четкие требования сразу не готовы.

Как интегрировать систему 1С с BI-аналитикой? Наш опыт

Имея опыт разработки под 1С и столкнувшись с этими проблемами, мы нашли решение. Ключевые требования к итоговому продукту:

1. Self-Service. Работа должна вестись с пользовательского интерфейса 1С. В ней должна быть предусмотрена возможность настройки выгрузки данных аналитиком без постоянного привлечения программистов.

2. Универсальность. Работа с любыми конфигурациями на поддерживаемых платформах 1С:Предприятие.

3. Производительность. Высокая скорость выгрузки больших объемов данных.

4. Инкрементальная выгрузка. Отслеживание изменений для выгрузки новых/измененных данных, а не всей истории.

5. Выгрузка во внешнюю аналитическую СУБД. Предпочтительно в колоночное хранилище (например, ClickHouse).

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

Пройдемся по каждому пункту подробнее.

Система работает без программиста 1С

Существует два подхода: работа с объектной моделью и произвольный запрос на языке 1С. Если вы задумываете собственную систему, необходимо реализовать оба подхода.

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

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

Схема работы системы

На входе подается объектная модель (документы, справочники, регистры) или произвольный запрос на языке 1С. Над полученным набором данных производятся дополнительные вычисления. Происходит автоматическое создание/модификация таблиц в целевой БД, маппинг полей и контроль типов данных (Numeric, Decimal и т.д.). Затем система ставится на отслеживание изменений и происходит передача данных через потоки в БД. И уже с данными из этой базы — системы BI-аналитики начинают свою работу.

Работа с BI-аналитикой

1С — это транзакционная система. Она заточена на то, чтобы быстро вводить и обрабатывать данные. Выбирать большие объемы данных, делать огромные агрегации, сводить множество таблиц между собой — можно, но ресурсоемко.

По классике для аналитиков строят отдельное хранилище. Желательно на Greenplum. Главное, чтобы можно было обрабатывать большие объемы данных. Между ними встраивается система отслеживания изменений (CDC) и система извлечения, трансформации и загрузки данных (ETL).

Именно ETL (Extract, Transform, Load — извлечение, преобразование, загрузка) играет важнейшую роль в подготовке данных для аналитики, поэтому давайте подробнее разберем, как она работает.

Немного о ETL-процессах

В качестве примера я приведу три варианта ETL-процессов. В реальности вариантов ETL значительно больше.

1. Классический ETL. Извлекаем данные, трансформируем их в необходимый для аналитики вид. В аналитических системах, как правило, требуются денормализованные данные: они представлены не в виде набора связей (звездочки), а в виде единой плоской таблицы, в которой может быть 100-200 полей. В конце производится загрузка.

2. ELT. В начале извлекаются данные, затем они загружаются в исходном формате в систему, а в конце трансформируются.

3. Модификации. Например, извлечь, предварительно преобразовать, загрузить и трансформировать. Вариантов много: Zero-ETL, Reverse ETL и так далее.

Существуют три метода извлечения данных ETL:

1. Batch ETL (пакетная). Излечение происходит по расписанию с дальнейшей передачей в базу данных.

2. Real-Time Event Streaming (потоковая). Каждое изменение базы данных уходит в базу назначения.

3. (Near) Real-Time Streaming (микропакетный поток). Схож с потоковой передачей данных. Например, Apache Airflow.

Механизмы сбора данных: Change Data Capture (CDC)

Сбор данных изменений может осуществляться на основе: данных (data-based), триггеров (trigger-based) и журнала (log-based).

На основе данных

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

Преимущество. Самый простой способ извлечения данных.

Недостаток. При удалении записи происходит рассинхронизация, так как поле ModifyDate отсутствует.

На основе триггера

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

Недостаток. Происходит нагрузка на базу, в связи с чем замедляется работа.

На основе лога (log-base)

Преимущество. Захватываются все типы изменений.

Недостаток. На данный момент такой способ нельзя использовать в 1С, так как в ней используется дата акселератор. При работе с Microsoft SQL Server и 1С, если поставить режим full для базы, можно очень легко «съесть» все место на диске.

Наш вариант CDC и ETL для 1С

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

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

В итоге получается, что, с одной стороны, у нас триггерная система, с другой — батчами мы выгружаем производные данные триггера.

При выгрузке набора данных их надо нарезать на партии и множеством фоновых заданий параллельно выгрузить. Для стандартной редакции 1С сервера можно использовать до 12 потоков.

Например, на одном из проектов у нас уходило 1,3 миллиона записей в час. Это серьезный запрос с обогащением данных.

Конфигурации и платформа 1С постоянно обновляются (релизы, обновления ядра, БСП). Решение не должно зависеть от конкретных версий или фич.

По описанным ранее требованиям разработана система, в которой:

1. Реализован Self Service;

2. Есть поддержка выгрузки больших данных;

3. Есть система отслеживания изменений;

4. Можно работать с любыми конфигурациями (универсальность);

5. Настроена автоматическая выгрузка.

Отображение в стандартном интерфейсе 1С.

Можно выгрузить произвольные запросы, объекты 1С конфигурации, обработчики и дополнительные вычисления. Загрузить данные можно в ClickHouse (самый популярный запрос заказчиков), PostgreSQL и так далее.

Дополнительные расчеты происходят достаточно быстро.

Создание и модификация таблиц в БД происходит автоматически. Дополнительные знания (CREATE TABLE, ALTER TABLE) не требуются.

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

По расписанию выполняется проект: выгрузка распараллеливается и данные загружаются в БД.

Результаты

Решение Экстрактор 1С успешно работает у более чем 200 заказчиков, преимущественно на производственных предприятиях с кастомными конфигурациями.

Направления развития:

• Интеграция с внешними системами оркестрации (управление пайплайнами);

• Развитие ролевой модели доступа.

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

Статья написана по итогам доклада (видео), прочитанного на конференции Анализ & Управление в ИТ-проектах.