Pentaho Kettle вместо SSIS: ETL из 1С, Excel и MySQL в PostgreSQL за три недели

Задача пришла с формулировкой, которую мы слышим всё чаще: «хотим хранилище данных и отчёты, но платить за SQL Server Enterprise не готовы». Клиент - торгово-производственная компания, у которой на серверах стоит SQL Server 2012 Standard без лицензий BI-стека. SSAS, SSIS, SSRS - всё это требует редакции не ниже Enterprise или отдельных CAL, и разговор о лицензировании быстро превращается в разговор о бюджете.

Источников данных у клиента три: 1С:Управление торговлей (выгружает через COM-объекты или внешние обработки), Excel-таблицы от коммерческого отдела с планами продаж и бюджетами, MySQL с данными интернет-магазина. Всё это нужно сводить в единую витрину для отчётности. PostgreSQL как хранилище они были готовы рассмотреть - бесплатный, надёжный, под Unix-сервер лицензий не нужно.

Мы занимаемся хранилищами данных и BI и ситуацию с лицензиями видели не первый раз. Pentaho Data Integration (он же Kettle) лежал в списке кандидатов давно, но до живого проекта дело доходило редко. Выход пятой версии в этом году был хорошим поводом.

Первые два дня - знакомство со Spoon. Spoon - это GUI-среда разработки трансформаций в Kettle. Выглядит как нечто среднее между Informatica PowerCenter и старым BizTalk - холст с блоками, соединёнными стрелками, правая кнопка открывает список шагов. Учебная кривая мягче, чем казалось: базовый поток «читаем источник - трансформируем - пишем в цель» собирается за час даже без документации. Сложности начинаются там, где начинается реальная грязь данных.

1С через выгрузку, а не COM. Изначально думали подключиться к 1С через JDBC-драйвер или COM. JDBC для 1С существует, но требует настройки на стороне сервера 1С и рассчитан на конкретные версии. У клиента 1С:УТ 10.3, обновлять не планируют, доступ к серверу 1С ограничен. Выбрали более простой путь: настроили регламентное задание в 1С, которое выгружает нужные данные в CSV-файлы на общую папку. Kettle читает CSV-файл, парсит, грузит в staging-таблицу в PostgreSQL. Негламурно, зато работает без зависимости от версии драйвера и без риска нагрузить рабочую базу 1С лишними запросами.

Excel - главная головная боль. Это не метафора. Excel-файлы с планами продаж оказались образцовым примером того, как коммерческий отдел понимает «структурированные данные»: объединённые ячейки заголовков, суммарные строки в середине листа, разные форматы дат в разных файлах (кто-то пишет «январь 2013», кто-то «01.2013», кто-то просто «01»), пустые строки между блоками. Kettle читает Excel через Apache POI, и это само по себе работает хорошо. Но нормализовать такой формат пришлось через несколько шагов JavaScript-трансформаций и условных роутеров. Заняло дня три, включая разбор крайних случаев.

MySQL читается без проблем. JDBC-драйвер для MySQL в Kettle уже в комплекте, подключение настраивается за пять минут. Данные интернет-магазина - заказы, позиции, покупатели - легли в staging чисто. Единственный нюанс: MySQL хранил даты в local time без timezone, PostgreSQL с настройками по умолчанию хочет UTC. Явный CONVERT_TZ в SQL-запросе источника решил вопрос.

Структура хранилища. Витрину делали по классической схеме: staging-слой (сырые данные как есть), потом слой интеграции (единые справочники - контрагенты, номенклатура, менеджеры), потом витрина с денормализованными фактами для отчётности. PostgreSQL держит это без проблем, партиционирование на таких объёмах пока не нужно. Схему данных нарисовали в draw.io, согласовали с клиентом - оказалось полезным, потому что в процессе выяснились расхождения в понимании «что такое сделка» между коммерческим и финансовым отделом.

Три недели - реально, но без люфта. Первая неделя - анализ источников, настройка инфраструктуры, эксперименты с подключениями. Вторая - разработка основных трансформаций и отладка на реальных данных. Третья - витрина, базовые запросы для отчётов, передача на поддержку клиентской команде. Временной запас съели Excel-файлы и один инцидент: выяснилось, что часть SKU в 1С и MySQL называются по-разному из-за исторических переименований. Пришлось делать таблицу маппинга руками вместе с клиентом.

Что понравилось в Kettle. Логирование трансформаций настраивается легко - каждый шаг может писать статистику строк в таблицу в БД. Метаданные джобов хранятся в XML - можно держать в git, можно сравнивать версии. Планировщик встроенный есть, но мы запускаем через cron: проще, меньше движущихся частей.

Что насторожило. Community-версия без поддержки - это значит Stack Overflow и форумы Pentaho. Документация есть, но неровная: одни шаги описаны подробно, другие приходится изучать по исходникам или примерам. Kettle написан на Java, и на слабом сервере JVM-старт добавляет секунд десять к каждому джобу - некритично для ночных ETL, но раздражает при отладке. Ещё одна вещь: трансформации хранятся в XML, и при конфликте версий в git разобраться в диффе нетривиально - XML многословный.

В итоге клиент получил работающий ETL без доплат за лицензии. PostgreSQL с витриной уже подключён к Jasper Reports - это тоже из Pentaho-экосистемы, тоже open-source. Отчётность строится. Насколько это решение будет удобно в поддержке через полгода - посмотрим, но старт вышел рабочим.