В последние годы использование высокопроизводительных аналитических баз данных стало критически важным для бизнеса, стремящегося эффективно анализировать большие объёмы данных и принимать обоснованные решения. Одним из ведущих решений в этой области является ClickHouse — колоночная система управления базами данных, известная своей скоростью и масштабируемостью. Однако, для многих компаний, использующих 1С:Предприятие в качестве основной системы автоматизации бизнеса, интеграция с такими современными инструментами, как ClickHouse, может представляться сложной задачей.
Радует, что на портале Infostart.ru начали появляться статьи, посвящённые применению ClickHouse в связке с 1С. Их пока не так много, можно найти в поиске по ключевому слову "clickhouse". Эти материалы стали отличным ресурсом для специалистов, стремящихся освоить новые инструменты и повысить производительность своих решений.
В этой статье я хочу рассмотреть еще один кейс применения это базы данных в связке с 1С - оптимизацию поиска номенклатуры по ключевым словам с применением Clickhouse. Здесь не будет готового решения, но будут описаны важные моменты, которые позволят легко применить данный кейс в реальности.
Итак, концепт задачи
Так как 1С при использовании стандартного поиска платформа строит не оптимальные запросы с использованием LIKE, это приводит к замедлению работы, особенно в случае, когда поиск осуществляется по нескольким ключевым словам(при интенсивной параллельной работе клиент даже может входить в ступор на минуты). Наиболее заметно это проявляется при поиске номенклатуры, когда в справочнике от 500к элементов. Поэтому есть смысл вынести непосредственно поиск кодов номенклатуры по наименованию, полному наименованию, артикулу и прочим полям в отдельную базу данных, которая умеет это делать достаточно быстро, чтобы она возвращала список найденных кодов, а 1С по этим кодам выбирала номенклатуру используя индекс, что тоже происходит быстро. Есть уверенность, что прирост производительности будет заметен невооруженным взглядом, поэтому синхронизацию на постоянной основе делаем сразу.
Подготовим базу данных
На просторах интернета куча информации, как поднять кликхаус. Для академических целей можно поднять самому в докере локально, а для прода - поручить системному администратору. Считаем, что развернутый экземпляр кликхауза у нас есть. Хочу только сказать, что несмотря на то, что в системных требования кликхауза упоминается минимум 32 гб оперативной памяти, для данной задачи достаточно будет виртуалочки с 4 гб. Должен быть опубликован порт 8123(http), мы в дальнейшем будем обращаться именно по http. Также будет удобно поднять tabix, это браузерный клиент для clickhouse, очень удобно для подготовки таблиц и отладки запросов.
Также не будем здесь останавливаться на сжатии и прочих тонких настройках таблиц.
Создадим базу данных и таблицу:
CREATE DATABASE ones_fastsearch
CREATE TABLE ones_fastsearch.sku (
`code` String,
`search` String,
`name` String,
`working` Bool
) ENGINE = ReplacingMergeTree
ORDER BY code
Обратите внимание, используем RepacingMergeTree, которая отличается от MergeTree тем, что выполняет удаление дублирующихся записей с одинаковым значением ключа сортировки (секция ORDER BY)
Синхронизируем номенклатуру
Теперь нужно научить 1С пополнять эту нашу таблицу на постоянной основе.
Это удобно делать асинхронно. Так все будет происходить в фоне и никому не будет мешать. Создадим очередь обработки измененной номенклатуры, которую будем наполнять при записи номенклатуры, а разбирать регламентным заданием, которое будет синхронизировать справочник.
РС ОчередьИнтеграцииНоменклатурыДляПодбора.
Модуль менеджера регистра
Процедура ДобавитьНоменклатуруВОчередь(СсылкаМассивСсылок) Экспорт
УстановитьПривилегированныйРежим(Истина);
Если ТипЗнч(СсылкаМассивСсылок) = Тип("СправочникСсылка.Номенклатура") Тогда
МассивНоменалатуры = Новый Массив;
МассивНоменалатуры.Добавить(СсылкаМассивСсылок);
ДобавитьНоменклатуруВОчередь(МассивНоменалатуры);
Иначе
НаборЗаписей = РегистрыСведений.ОчередьИнтеграцииНоменклатурыДляПодбора.СоздатьНаборЗаписей();
ДатаДобавленияМС = ТекущаяУниверсальнаяДатаВМиллисекундах();
Для Каждого Номенклатура Из СсылкаМассивСсылок Цикл
НоваяСтрокаНаборЗаписей = НаборЗаписей.Добавить();
НоваяСтрокаНаборЗаписей.УникальныйИдентификатор = Новый УникальныйИдентификатор;
НоваяСтрокаНаборЗаписей.Номенклатура = Номенклатура;
НоваяСтрокаНаборЗаписей.ДатаДобавленияМС = ДатаДобавленияМС;
НоваяСтрокаНаборЗаписей.ДатаДобавления = '00010101' + НоваяСтрокаНаборЗаписей.ДатаДобавленияМС / 1000;
КонецЦикла;
НаборЗаписей.Записать(Ложь);
КонецЕсли;
КонецПроцедуры
Процедура ПометитьОбработанным(УникальныйИдентификатор) Экспорт
УстановитьПривилегированныйРежим(Истина);
МенеджерЗаписи = РегистрыСведений.ОчередьИнтеграцииНоменклатурыДляПодбора.СоздатьМенеджерЗаписи();
МенеджерЗаписи.УникальныйИдентификатор = УникальныйИдентификатор;
МенеджерЗаписи.Удалить();
УстановитьПривилегированныйРежим(Ложь);
КонецПроцедуры
Общий модуль ИнтеграцияНоменклатураДляПодбораПовтИсп (как видно из именования, он серверный). Предполагаем, что у нас уже есть КоннекторHTTP (https://github.com/vbondarevsky/Connector). Если захочется повторить, придется самостоятельно организовать получение параметров для подключения к кликхаузу (у вас не будет модуля ИнтеграцииВызовСервераПовтИсп). Для академических целей можно просто захардкодить.
Функция ПараметрыДляЗапросовКХ() Экспорт
ПараметрыИнтеграции = ИнтеграцииВызовСервераПовтИсп.ПараметрыИнтеграции(Справочники.Интеграции.ИнтеграцияНоменклатураДляПодбора);
Результат = Новый Структура;
Результат.Вставить("Адрес", ПараметрыИнтеграции.СоответствиеПараметров["Адрес"]);
Результат.Вставить("БазаданныхТаблица", ПараметрыИнтеграции.СоответствиеПараметров["БазаданныхТаблица"]);
Результат.Вставить("ПараметрыHTTP", КоннекторHTTPВызовСервера.НовыеПараметры());
Результат.ПараметрыHTTP.Вставить("Аутентификация", Новый Структура);
Результат.ПараметрыHTTP.Аутентификация.Вставить("Пользователь", ПараметрыИнтеграции.СоответствиеПараметров["Логин"]);
Результат.ПараметрыHTTP.Аутентификация.Вставить("Пароль", ПараметрыИнтеграции.СоответствиеПараметров["Пароль"]);
Результат.ПараметрыHTTP.Заголовки.Вставить("Content-Type", "application/text;encoding=utf-8");
Результат.ПараметрыHTTP.Вставить("Таймаут", 5);
Результат.ПараметрыHTTP.Вставить("РазрешитьПеренаправление", Ложь);
Результат.ПараметрыHTTP.Вставить("МаксимальноеКоличествоПовторов", 0);
Результат.ПараметрыHTTP.Вставить("ПараметрыЗапроса", Новый Соответствие);
Возврат Результат;
КонецФункции
Функция ПараметрыКликхауза() Экспорт
ПараметрыИнтеграции = ИнтеграцииВызовСервераПовтИсп.ПараметрыИнтеграции(Справочники.Интеграции.ИнтеграцияНоменклатураДляПодбора);
Результат = Новый Структура;
Результат.Вставить("АдресКликхаус", ПараметрыИнтеграции.СоответствиеПараметров["Адрес"]);
Результат.Вставить("Логин", ПараметрыИнтеграции.СоответствиеПараметров["Логин"]);
Результат.Вставить("Пароль", ПараметрыИнтеграции.СоответствиеПараметров["Пароль"]);
Результат.Вставить("БазаданныхТаблица", ПараметрыИнтеграции.СоответствиеПараметров["БазаданныхТаблица"]);
Возврат Результат;
КонецФункции
Общий модуль ИнтеграцияНоменклатураДляПодбора. Обратите внимание, все в привилегированном режиме, чтобы не париться с ролями. Процедура ТочкаВходаРегламентноеЗаданиеИнтеграцияНоменклатурыДляПодбора() отправляет изменения в кликхаус добавлением. А так как мы для таблицы используем ReplacingMergeTree, кликхаус самостоятельно дедуплицирует записи тогда, когда посчитает нужным. Но не следует думать, что у нас будут дубли, поскольку в КодыНоменклатурыПоСтрокеПоиска() мы используем ключевое слово FINAL, которое дедуплицирует недодуплицированные записи непосредственно в момент выборки.
Процедура ТочкаВходаРегламентноеЗаданиеИнтеграцияНоменклатурыДляПодбора()
УстановитьПривилегированныйРежим(Истина);
ИнтеграцияНоменклатураДляПодбора.ПроверитьЧтоВсеПараметрыЗаполнены();
ПараметрыКХ = ИнтеграцияНоменклатураДляПодбораПовтИсп.ПараметрыДляЗапросовКХ();
ТекущаяДатаМС = ТекущаяУниверсальнаяДатаВМиллисекундах();
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ТекущаяДатаМС", ТекущаяДатаМС);
Запрос.Текст = "ВЫБРАТЬ
| Очередь.УникальныйИдентификатор КАК УникальныйИдентификатор,
| Очередь.Номенклатура КАК Ссылка,
| Очередь.ДатаДобавленияМС КАК Порядок
|ПОМЕСТИТЬ ВТТаблицаСущностей
|ИЗ
| РегистрСведений.ОчередьИнтеграцииНоменклатурыДляПодбора КАК Очередь
|ГДЕ
| Очередь.ДатаДобавленияМС <= &ТекущаяДатаМС
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТТаблицаСущностей.Ссылка КАК Ссылка
|ПОМЕСТИТЬ ВТСсылки
|ИЗ
| ВТТаблицаСущностей КАК ВТТаблицаСущностей
|
|СГРУППИРОВАТЬ ПО
| ВТТаблицаСущностей.Ссылка
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Номенклатура.Код КАК Код,
| Номенклатура.Наименование КАК Наименование,
| Номенклатура.АльтернативноеНаименование КАК АльтернативноеНаименование,
| Номенклатура.Артикул КАК Артикул,
| Номенклатура.Бренд КАК Бренд,
| Номенклатура.ОЕМ КАК ОЕМ,
| Номенклатура.Рабочая КАК Рабочая
|ИЗ
| ВТСсылки КАК ВТСсылки
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Номенклатура
| ПО ВТСсылки.Ссылка = Номенклатура.Ссылка
|
|УПОРЯДОЧИТЬ ПО
| Номенклатура.Код
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТТаблицаСущностей.УникальныйИдентификатор КАК УникальныйИдентификатор,
| ВТТаблицаСущностей.Ссылка КАК Ссылка
|ИЗ
| ВТТаблицаСущностей КАК ВТТаблицаСущностей
|
|УПОРЯДОЧИТЬ ПО
| УникальныйИдентификатор";
РезультатыЗапросов = Запрос.ВыполнитьПакет();
ВыборкаИдентификаторы = РезультатыЗапросов[РезультатыЗапросов.ВГраница()].Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
ВыборкаДанные = РезультатыЗапросов[РезультатыЗапросов.ВГраница() - 1].Выбрать();
МассивТекстовЗапроса = Новый Массив;
МассивТекстовЗапроса.Добавить(СтрШаблон("INSERT INTO %1 (code, search, name, working) FORMAT JSONCompactEachRow", ПараметрыКХ.БазаданныхТаблица));
ПараметрыЗаписиJSON = Новый Структура;
ПараметрыЗаписиJSON.Вставить("ПереносСтрок", ПереносСтрокJSON.Нет);
Пока ВыборкаДанные.Следующий() Цикл
МассивДанныхНоменклатуры = Новый Массив;
МассивДанныхНоменклатуры.Добавить(ВыборкаДанные.Код);
МассивДанныхНоменклатуры.Добавить(СтрШаблон("%1 %2 %3 %4 %5 %6",
ВыборкаДанные.Код,
ВыборкаДанные.Наименование,
ВыборкаДанные.АльтернативноеНаименование,
ВыборкаДанные.Артикул,
ВыборкаДанные.Бренд,
ВыборкаДанные.ОЕМ)
);
МассивДанныхНоменклатуры.Добавить(ВыборкаДанные.Наименование);
МассивДанныхНоменклатуры.Добавить(ВыборкаДанные.Рабочая);
МассивТекстовЗапроса.Добавить(КоннекторHTTPВызовСервера.ОбъектВJson(МассивДанныхНоменклатуры, , ПараметрыЗаписиJSON));
КонецЦикла;
//инсерт в клик
ТекстИнсерта = СтрСоединить(МассивТекстовЗапроса, Символы.ПС);
ПараметрыHTTP = ПараметрыКХ.ПараметрыHTTP;
Ответ = КоннекторHTTPВызовСервера.Post(ПараметрыКХ.Адрес, ТекстИнсерта, ПараметрыHTTP);
Если Ответ.КодСостояния <> 200 Тогда
ВызватьИсключение(СтрШаблон("Сервер ответил %1 с телом %2", XMLСтрока(Ответ.КодСостояния), КоннекторHTTPВызовСервера.КакТекст(Ответ)));
КонецЕсли;
//скажем, что все огонь
Пока ВыборкаИдентификаторы.Следующий() Цикл
РегистрыСведений.ОчередьИнтеграцииНоменклатурыДляПодбора.ПометитьОбработанным(ВыборкаИдентификаторы.УникальныйИдентификатор);
КонецЦикла;
КонецПроцедуры
Функция КодыНоменклатурыПоСтрокеПоиска(Знач СтрокаПоискаНоменклатуры) Экспорт
//экранируем, защищаемся от sql инъекций
СтрокаПоискаНоменклатуры = СтрЗаменить(СтрокаПоискаНоменклатуры, "'", "''");
СтрокаПоискаНоменклатуры = СтрЗаменить(СтрокаПоискаНоменклатуры, "\", "\\");
// считаем, что если ищут >5 слов, ищут точное совпадение
МассивПодстрок = СтрРазделить(СтрокаПоискаНоменклатуры, " ");
Если МассивПодстрок.Количество() > 5 Тогда
МассивПодстрок = Новый Массив;
МассивПодстрок.Добавить(СтрокаПоискаНоменклатуры);
КонецЕсли;
Результат = Новый Массив;
УстановитьПривилегированныйРежим(Истина);
ПараметрыКХ = ИнтеграцияНоменклатураДляПодбораПовтИсп.ПараметрыДляЗапросовКХ();
МассивТекстовЗапроса = Новый Массив;
МассивТекстовЗапроса.Добавить(СтрШаблон("SELECT
| groupArray(code)
|FROM
| %1 FINAL
|WHERE
| working = TRUE", ПараметрыКХ.БазаданныхТаблица));
Для Каждого Подстрока Из МассивПодстрок Цикл
//не забываем для русского языка нужны функции *UTF8
МассивТекстовЗапроса.Добавить(СтрШаблон("AND positionCaseInsensitiveUTF8(search, '%1') > 0", Подстрока));
КонецЦикла;
МассивТекстовЗапроса.Добавить("LIMIT 500");
МассивТекстовЗапроса.Добавить("FORMAT TabSeparated"); //в сочетании с одной колонкой в выборке и агрегированием в groupArray() в одну строку
//результат будет массивом JSON
ТекстСелекта = СтрСоединить(МассивТекстовЗапроса, Символы.ПС);
ПараметрыHTTP = ПараметрыКХ.ПараметрыHTTP;
Ответ = КоннекторHTTPВызовСервера.Post(ПараметрыКХ.Адрес, ТекстСелекта, ПараметрыHTTP);
Если Ответ.КодСостояния <> 200 Тогда
ВызватьИсключение(СтрШаблон("Сервер ответил %1 с телом %2", XMLСтрока(Ответ.КодСостояния), КоннекторHTTPВызовСервера.КакТекст(Ответ)));
КонецЕсли;
Результат = КоннекторHTTPВызовСервера.КакJSON(Ответ)
Возврат Результат;
КонецФункции
//падает, если какой-то параметр не заполнен
Функция ПроверитьЧтоВсеПараметрыЗаполнены() Экспорт
ПараметрыИнтеграции = ИнтеграцииВызовСервераПовтИсп.ПолучитьПараметрыИнтеграции(Справочники.Интеграции.ИнтеграцияНоменклатураДляПодбора);
ИнтеграцияНоменклатураДляПодбора.УпастьЕслиПараметрНеЗаполнен(ПараметрыИнтеграции.СоответствиеПараметров, "Адрес");
ИнтеграцияНоменклатураДляПодбора.УпастьЕслиПараметрНеЗаполнен(ПараметрыИнтеграции.СоответствиеПараметров, "Логин");
ИнтеграцияНоменклатураДляПодбора.УпастьЕслиПараметрНеЗаполнен(ПараметрыИнтеграции.СоответствиеПараметров, "Пароль");
ИнтеграцияНоменклатураДляПодбора.УпастьЕслиПараметрНеЗаполнен(ПараметрыИнтеграции.СоответствиеПараметров, "БазаданныхТаблица");
КонецФункции
Процедура УпастьЕслиПараметрНеЗаполнен(Где, ИмяПараметра) Экспорт
Если Не ЗначениеЗаполнено(Где[ИмяПараметра]) Тогда
ВызватьИсключение СтрШаблон("У интеграции ИнтеграцияНоменклатураДляПодбора не заполнен параметр %1", ИмяПараметра);
КонецЕсли;
КонецПроцедуры
В номенклатуре напишем что-то вроде:
Процедура ПриЗаписи(Отказ)
...
...
Если Не ЭтоГруппа Тогда
Если мЭтоНовый
или мСтарыеВерсииРеквизитовДляОбмена.Наименование <> Наименование
или мСтарыеВерсииРеквизитовДляОбмена.АльтернативноеНаименование <> АльтернативноеНаименование
или мСтарыеВерсииРеквизитовДляОбмена.Артикул <> Артикул
или мСтарыеВерсииРеквизитовДляОбмена.Бренд <> Бренд
или мСтарыеВерсииРеквизитовДляОбмена.Описание <> Описание
или мСтарыеВерсииРеквизитовДляОбмена.ОЕМ <> ОЕМ
или мСтарыеВерсииРеквизитовДляОбмена.Рабочая <> Рабочая
Тогда
РегистрыСведений.ОчередьИнтеграцииНоменклатурыДляПодбора.ДобавитьНоменклатуруВОчередь(Ссылка);
КонецЕсли;
КонецЕсли;
...
...
КонецПроцедуры
Создаем регламентное задание нужной частоты срабатывания процедуры ТочкаВходаРегламентноеЗаданиеИнтеграцияНоменклатурыДляПодбора()
Осталось запустить начальную синхронизацию, добавив всю рабочую номенклатуру в РС ОчередьИнтеграцииНоменклатурыДляПодбора.
Проверим, устроит ли нас производительность.
60-90 мс на 145000 записей номенклатуры (ведь не обязательно синхронизировать весь справочник 500к, если можно только рабочие?). Вполне себе неплохо. В конечном счете еще будет запрос в 1С с фильтром по кодам.
Непосредственно поиск
Мне неизвестно, какая у вас форма подбора. Вероятно, это управляемые формы и динамический список. Если так, то вот пример, как можно сделать.
&НаСервере
Процедура СтрокаПоискаНоменклатурыПриИзмененииНаСервере()
СтрокаПоискаНоменклатуры = СокрЛП(СтрокаПоискаНоменклатуры);
Если ИспользоватьВПодбореПоискНоменклатурыВКликхаузе Тогда
//этот метод по умолчанию очищает все, что содержит группа
ГруппаИ = ОбщегоНазначенияКлиентСервер.СоздатьГруппуЭлементовОтбора(СправочникНоменклатура.Отбор.Элементы, "общий поиск", ТипГруппыЭлементовОтбораКомпоновкиДанных.ГруппаИ);
Если СтрокаПоискаНоменклатуры <> "" Тогда
МассивКоды = ИнтеграцияНоменклатураДляПодбора.КодыНоменклатурыПоСтрокеПоиска(СтрокаПоискаНоменклатуры);
ОбщегоНазначенияКлиентСервер.ДобавитьЭлементКомпоновки(ГруппаИ, "Код", ВидСравненияКомпоновкиДанных.ВСписке,
МассивКоды, , Истина, РежимОтображенияЭлементаНастройкиКомпоновкиДанных.Обычный);
КонецЕсли;
Иначе
Как было
После набора поисковой строки клиент замирал на 2-8 секунд в среднем. В отдельных случаях в часы высокой нагрузки мог замирать на минуту.
Как стало
В результате мы получили инструмент:
- Более производительный.
- С ожидаемым временем исполнения на уровне трети-половины секунды на любой поисковый запрос.
Всем добра!
В заключение хочется сказать что применение ClickHouse в связке с 1С открывает перед бизнесом новые возможности для оптимизации различных процессов, выходящих за рамки традиционного анализа журнала регистрации. Рассмотренный кейс оптимизации поиска номенклатуры демонстрирует, как сочетание возможностей ClickHouse по производительности и 1С может значительно повысить производительность системы и ускорить выполнение задач, критически важных для бизнеса.
Этот опыт показывает, что, несмотря на основные сценарии использования ClickHouse в 1С, такие как статистический анализ логов, есть и другие, не менее важные области, где данная технология может оказаться полезной. Интеграция ClickHouse с 1С — это не просто способ ускорить обработку данных, но и возможность внедрять инновационные решения, которые помогают бизнесу быть более конкурентоспособным и адаптивным к изменениям.
Я надеюсь, что этот пример вдохновит вас на поиск новых путей оптимизации и улучшения ваших собственных информационных систем, а также покажет, как современные инструменты могут изменить подход к решению повседневных задач.
