На связи Никита Скирдин, программист 1С компании «Белый код». Для BI-аналитики необходимо собирать данные из различных систем в централизованное хранилище. Как правило, перед использованием данных для построения графиков, таблиц и т. д. их необходимо преобразовать. В этой статье расскажу, как решали задачу загрузки данных в централизованное хранилище ClickHouse и реализовали модель ELT (extract load transform), используя сначала 1С, а затем Apache Airflow.

Недавно мы работали над проектом по внедрению BI-аналитики. Нужны были данные, которые выгружались из системы заказчика в формате CSV-файлов. Эти CSV-файлы объединялись в ZIP-архив и выгружались на FTP-сервер, с которого наша система должна была забрать данные.

Все CSV-файлы имели определенный формат имени, который включал в себя название таблицы и, для периодических данных, дату, за которую в файле содержались данные. Например, файл «20241110_Продажи.csv» содержит все продажи за 10 ноября. Для таблиц с периодическими данными использовали механизм партиций в ClickHouse, чтобы быстро добавлять и заменять данные за определенную дату.

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

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

Наивное решение: HTTP-интерфейс ClickHouse и 1С

Изначально решили выполнить задачу в 1С, поскольку в компании уже много лет существует сервис, разработанный на 1С. Основной задачей сервиса является получение данных для BI, загрузка их в хранилище и запуск загрузки данных в BI. В этот же сервис решили дописать возможность загрузки данных в ClikHouse. Был написан код, который по регламентному заданию заходит на FTP-сервер, получает архив, разархивирует его и по HTTP-протоколу передает в ClickHouse содержимое CSV-файлов.

У данного решения было несколько особенностей.

Во-первых, содержимое файлов необходимо грузить в таблицы, а в базе данных их изначально нет. Соответственно, таблицы нужно было создать программно из 1С. Сначала для этой цели просто читалась первая строка CSV, чтобы получить имена колонок таблицы (ClickHouse позволяет называть таблицы и колонки и на кириллице, и на латинице), а все колонки имели тип String. Затем решили использовать штатные средства ClickHouse по определению типов колонок в CSV: теперь файл из архива помещался в директорию, указанную в опции user_files_path в ClickHouse, по описанию файла формировалась таблица, затем содержимое файла загружалось в таблицу, как раньше.

Во-вторых, у части таблиц данные необходимо было грузить с разбиением по партициям, т. е. к загружаемым из CSV строкам необходимо было дописать значение колонки, по которой выполняется разбиение. Это означало, что для загрузки по протоколу HTTP было недостаточно просто поместить содержимое CSV в тело HTTP-запроса: в каждую строку в 1С добавлялось значение для разбиения данных на партиции, например, 20241101 для всех данных за 1 ноября. Операция занимала до 10-15 минут для файлов размером примерно от нескольких миллионов строк. Медленная обработка файлов приводила к тому, что выгрузка за годовой период прогружалась от 3 до 6 часов. Поскольку нам периодически приходилось прогружать сразу большие объемы данных, потому что со стороны системы-источника выгрузку также исправляли по ходу проекта, то многочасовые ожидания были проблемой.

Полноценного мониторинга у нас не было. Загрузка файлов с FTP проверялась по регистру с текстовыми логами (он уже был в нашей информационной базе, в которой мы вели разработку) и состоянию регламентного задания, а если были какие-либо проблемы, то разработчику необходимо было смотреть журнал регистрации. Для проверки выполнения скриптов, которые запускались после загрузки, был создан отдельный регистр, в который писались состояния выполнения.

Плюсы данного решения на 1С: 

• используются только возможности платформы 1С, что означает, что любой 1С разработчик сможет разобраться в коде; 

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

Минусы: 

• медленная скорость обработки строк, когда их количество в файле превышает несколько миллионов; 

• неочевидное разделение ответственности, потому что описание таблиц выполнялось на стороне ClickHouse, но итоговую загрузку данных проводила база 1С; 

• отсутствие удобного интерфейса мониторинга.

В целом, мы могли исправить эти минусы, однако на это ушло бы много времени. Также появилось требование развернуть проект на серверах заказчика. Решили рассмотреть альтернативный вариант загрузки данных с использованием штатных возможностей ClickHouse и планировщика Apache Airflow.

Альтернативное решение: ClickHouse-driver и Apache Airflow

Новое решение было построено на Apache Airflow (дальше Airflow) — эффективном инструменте для управления обработкой данных. Airflow использует Python для планирования и описания графов и задач. Для нас это было как плюсом, поскольку открыло возможность использовать все множество библиотек, написанных для этого языка, например, pandas (часть данных начала приходить на FTP в формате xlsx-файла) и clickhouse-driver (использует нативный TCP протокол для соединения с clickhouse), так и минусом, т. к. знающих Python разработчиков у нас меньше, чем разработчиков 1С.

В рамках этой статьи не рассматриваю подробно то, как работает Airflow, для этого можно обратиться к документации или соответствующим статьям.

Процесс (DAG) загрузки данных выглядит следующим образом.

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

Для того чтобы связать Airflow и ClickHouse, использовался плагин с открытым исходным кодом: airflow-clickhouse-plugin. Чтение содержимого CSV-файла теперь происходит на стороне ClickHouse, что позволило снизить время на обработку файлов с нескольких минут до секунд. Загрузка данных за несколько лет в общей сложности заняла всего 3 минуты, 2 из которых ушло на распаковку архива, а 1 — на загрузку данных в ClickHouse.

Для таблиц с разбиением на партиции используется следующая конструкция:

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

Также большим плюсом стал богатый интерфейс Airflow.

На одном экране можно как визуально посмотреть историю исполнения или детали (логи, граф) какого-либо конкретного запуска, так и запустить выполнение по кнопке. С Airflow стало намного проще исследовать, на каком этапе загрузка встала или какой из sql-скриптов вызвал исключение. Также логи выполнения позволили более оперативно выяснять причину ошибки и, следовательно, исправлять ее.

Основные плюсы от внедрения Airflow: 

• увеличение скорости загрузки данных в Clickhouse с 3-6 часов до 1-5 минут за счет использования нативного драйвера,

• удобный интерфейс для мониторинга выполнения загрузки и скриптов,

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

Что дальше

Очевидно, решение не идеально и есть, куда двигаться дальше. Например, в рамках Airflow можно вместо периодического запуска загрузки данных настроить мониторинг FTP-сервера, чтобы данные загружались в ClickHouse после их поступления на сервер.

Для запуска скриптов можно использовать dbt. Это позволит автоматически выстраивать граф зависимостей таблиц и генерировать документацию к структуре данных, выполнять скрипты параллельно, а также упростит сопровождение sql-скриптов за счет использования шаблонизации и контроля версий.

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

Выводы

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

Кто-то скажет: «Спасибо, кэп, это же очевидно!». Очевидно, но далеко не для всех. Когда обсуждаем проекты по внедрению BI или ESB, часто приходится слышать от 1С-ников: «Зачем нужен ваш BI, ведь все данные вы берете из 1С, можно же там и отчеты написать» или «Зачем покупать ESB, мы на 1С создадим центральную базу и сделаем интеграцию по схеме звезда».

Лучше применить то, что проверено годами, чем пытаться написать очередной «свой фреймворк».