Каталог решений - Применение средств MS SQL R service для 1С

Думаю, что Python не нуждается в представлении, а вот про R возможно кто-то и не слышал. Если коротко, то данные ЯП обладают огромным количеством свободно распространяемых библиотек на все случаи жизни, и поддержка их сервером MS SQL, на мой взгляд, предоставляет разработчикам новые возможности, особенно в части работы с big data, статистикой, анализом данных и т.п. В данной статье будут описаны только начальные принципы использования связки 1С-SQL-R. В дальнейшем, возможно, доберемся и до Python, но R, на данный момент, я знаю гораздо лучше и поэтому все примеры будут именно на данном языке.

1. Установка и настройка необходимых программ

Для начала необходимо установить SQL server с необходимыми дополнениями. На установке подробно останавливаться смысла нет, все стандартно и подробно расписано в msdn. С поддержкой R от MS не все прозрачно — для применения непосредственно из SQL нам нужна версия R in-database server. Имеются ещё варианты standalone — отдельной установки сервера machine learning и просто дистрибутив R open. У каждого продукта свое предназначение, свои версии R, свои пути установки — тут важно не запутаться и следить, например, за тем из под какой версии R и куда вы устанавливаете дополнительные пакеты. 

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

EXEC sp_configure 'external scripts enabled', 1

Рестартуем SQL server и проверяем, что все сделали правильно

EXEC sp_execute_external_script  @language = N'R',
@script = N'
OutputDataSet <- InputDataSet;',
@input_data_1 = N'SELECT 1 AS hello'
WITH RESULT SETS (([hello] int not null));
GO

Результат должен быть следующим:

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

Переходим к практике.

2. Постановка тестовой задачи

Выше уже я описал в качестве большого плюса R огромное количество библиотек. Так же у R имеется простая и удобная IDE R Studio. Считаю ее не заменимым и почти идеальным инструментом, хотя для наших задач будет достаточно стандартной консоли R.

Задача не сложная — для теста с некоторыми допущениями и условностями: предположим имеется УТ 11.х и справочник номенклатуры. Требуется загрузить в базу большое количество новой номенклатуры из какого-либо внешнего источника данных, при этом определить наличие пересечений по наименованию или просто найти и сопоставить свою номенклатуру с номенклатурой поставщика. Попробуем реализовать это с помощью R и MS SQL. 

Задача сводится к обработке двух таблиц данных, для упрощения допустим следующую структуру:

Искомые данные — два столбца: код или порядковый номер (целочисленный, не отрицательный) и наименование товара (строка). В дальнейшем типы будут важны, т.к. будем писать все в таблицу БД

Каталог номенклатуры для поиска — код, имя, описание, вид номенклатуры: типы кода, имя и описания соответствуют типам MS SQL для справочника номенклатуры 1С, а вид номенклатуры пусть будет выведен строкой (для упрощения, т.к. если хотим иметь ссылку, то придется передавать в скрипт строку, а в обратном направлении  преобразовывать обратно в varbinary). 

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

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

Сначала нам надо установить дополнительные пакеты, packages, ибо без них использование R теряет смысл. Открываем Rgui, который находится где-то в районе [Program Files]\Microsoft SQL Server\[НазваниеВашегоЭкземпляраСервераСкуль]\R_SERVICES\bin\[ВашаРазрядность]\Rgui.exe (можно открыть R Studio с настройками применения данного экземпляра R) и вводим следующий код:

install.packages(c("dplyr", "stringdist"))

Таким нехитрым образом мы установили два пакета: dplyr, без которого ну просто никуда, используется для работы с типом data.frame (в переводе на язык 1С — таблица значений), и stringdist, в котором имеются различные метрики для сверки строк. 

Проверим список установленных пакетов

EXECUTE sp_execute_external_script
@language=N'R'  
,@script = N'str(OutputDataSet);  
packagematrix <- installed.packages();  
OutputDataSet <- as.data.frame(packagematrix[, 1]);'  
,@input_data_1 = N'SELECT 1 as col'  
WITH RESULT SETS ((PackageName nvarchar(250)))