Применение средств 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_SERVICESbin[ВашаРазрядность]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)))

EXECUTE sp_execute_external_script
@language = N'R'
,@script = N'str(OutputDataSet);
packagematrix <- installed.packages();
OutputDataSet <- as.data.frame(packagematrix[, c("Package", "Version")]);'
WITH RESULT SETS ((PackageName nvarchar(250), PackageVersion nvarchar(20)))

Если все работает и виден результат, то можно переходить к следующей части.

3. Запросы SQL

Все наверняка знают, что прямые запросы к СУБД в 1С не приветствуются и об этом явно говорится в лицензионном соглашении. Поэтому все что тут описано сделано в исследовательских целях и конечно же повторять это не рекомендуется.

Итак составим простейший прямой запрос к справочнику номенклатуры. Прямой запрос составляем кто как привык — через стандартную консоль запросов или руками, глядя в описание структуры хранения СУБД или же через профайлер MS SQL. 

SELECT Items._Code AS Code
,Items._Description AS Name
,Items._Fld5586 AS Description
,Kind._Description AS Kind
,NULL AS ItemName
,0 AS ItemNum
FROM [ut_demo].[dbo].[_Reference215] AS Items
LEFT JOIN [ut_demo].[dbo]._Reference76 AS Kind
ON Items._Fld5567RRef = Kind._IDRRef

Получим таблицу с наименованием и кодом номенклатуры, ее описанием, и наименованием вида номенклатуры (просто для дополнительной информации)

4. Скрипты на R, sp_execute_external_script

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

DECLARE @par1 int = 2, @par2 int = 3    -- Добавляем переменные со значениями 1 и 2
EXECUTE sp_execute_external_script      -- Объявляем начало внешнего исполняемого скрипта
@language=N'R'                          -- Указываем язык скрипта
,@script = N'                            # А тут уже комментарии на R
q1 <- Q_in[1,1]                          # Это исходные данные, всегда типа data.frame
# В данном примере получаем значение первой ячейки таблицы
q2 <- q_1;                               # А это первый входной параметр
q3 <- q_2;                               # А это второй
QQ <- data.frame(Q = c(q1, q2, q3));     # Составили список и привели его к типу data.frame, и возвращаем из скрипта результат
'
,@input_data_1 = N'SELECT 1 as col'     -- Передаем в скрипт результат этого сложнейшего запроса
,@input_data_1_name = N'Q_in'           -- Задаем наименование по которому будем обращаться в скрипте за входными данными
,@output_data_1_name = N'QQ'            -- Задаем наименование для возвращаемых из скрипта данных
,@params = N'@q_1 int, @q_2 int'        -- Задаем наименования параметров, в данном примере их два
,@q_1 = @par1                           -- Значение первого параметра
,@q_2 = @par2                           -- Значение второго параметра
WITH RESULT SETS ((Q int))              -- Тип и наименования результата

При решении подобной задачи возникает вопрос: нам же надо сравнивать одну таблицу (наименования товаров) с данными, полученными на прошлом этапе прямым запросом. Как нам передать в скрипт наши искомые данные? Поиск в интернете дает следующий ответ: пока никак. И цифра 1 в "input_data_1" никого не должна вводить в заблуждение, т.к. "input_data_2" не существует, так же как и не существует всех последующих "input_data_N". Конечно это неприятность, но маленькая. Вариантов обойти эту проблему несколько. В случае небольших наборов искомых данных вполне можно передать строковой параметр, например записать данные в формат json, с которым R прекрасно дружит (пакет jsonlite). Можно подключаться из самого R к базе данных (т.е. внутри скрипта), что, на первый взгляд, не совсем красиво выглядит и требует отдельного изучения.

В рассмотренном примере мы пошли другим путем и выбираем примитивное решение — создаем служебную таблицу, в которую записываем искомые данные. И затем объединяем с таблицей номенклатуры. И эти объединенные данные передаем в скрипт для обработки. Возможно это получится реализовать более элегантно, с помощью временных таблиц, но в рамках задачи для примера было решено сохранять искомые данные физически для определенных манипуляций по параллельным задачам и для тестирования. Так что просто запомним, что можно попробовать оптимизировать данную часть и уйти от хранения по сути лишних данных в физической таблице.

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

CREATE TABLE [integration].[dbo].[itemstofind](
[id] [int] NOT NULL IDENTITY(1,1),
[ItemName] [nvarchar](100) NULL,
[ItemNum] [int] NULL,
[Value] [int] NULL,
[Code] [nchar](11) NULL,
[Name] [nvarchar](100) NULL,
[Description] [nvarchar](1024) NULL,
[Kind] [nvarchar](100) NULL
CONSTRAINT PK_ID PRIMARY KEY(id)
)

4. Алгоритм поиска строк

Для поиска строк будет использоваться пакет stringdist. У данного не большого пакета есть несколько вариантов определения похожести строк. После некоторых тестов была выбрана метрика Жаро-Винклера

На R это будет выглядеть так:

stringdist("MARTHA", "MARHTA", method = 'jw', p = 0.1)

Первые два параметра — две строки, или строка и список строк, для определения "степени похожести". Вторым параметром указываем, что нам нужна именно метрика Жаро-Винклера. p — служебный параметр самого алгоритма. Схожесть в реализации алгоритма на R для одинаковых слов равна 0, для различных стремится к 1 (в алгоритме на вики наоборот)

Примеры:

> stringdist("MARTHA", "MARHTA", method = 'jw', p = 0.1)
[1] 0.03888889
> stringdist("DWAYNE", "DUANE", method = 'jw', p = 0.1)
[1] 0.16

Метод регистрозависим:

> stringdist("dWAYNE", "DUANE", method = 'jw', p = 0.1)
[1] 0.3
> stringdist("dWaYne", "DUANE", method = 'jw', p = 0.1)
[1] 1

5. Реализация через хранимые процедуры

Используем stored procedures в MS SQL для удобства использования алгоритма. 

USE [integration]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CompareItems]
@quant int

AS
BEGIN
EXEC
sp_execute_external_script
@language = N'R'
, @script = N'
# Подключаем библиотеки
library(dplyr);
library(stringdist);

# Функция, для получения q минимальных совпадений с искомой строкой
get.res <- function(items.list, items.cat, q){
cat.names <- items.cat$Name;
item.name <- items.list[1];
item.num <- items.list[2];

# Функция сравнения "похожести" строк
m <- stringdist(items.list[1], as.character(items.cat$Name), method = "jw", p = 0.1);

# Приводим результат к чистому виду, удаляем мусор, сортируем
res <- as.data.frame(cbind(m, items.cat)) %>%
rename(Value = m) %>%
filter(tolower(Value)!="inf"&!is.na(Value)&!is.infinite(Value)) %>%
mutate(ItemName = item.name, ItemNum = item.num) %>%
arrange(Value);

# Возвращаем первые q строк
head(res, q)
}

# Формируем объект для поиска из входных данных
i.list <- items_cat %>%
filter(ItemNum > 0) %>%
select(ItemName, ItemNum) %>%
mutate(ItemName = tolower(as.character(ItemName)))

# Формируем данные среди которых происходит поиск
i.cat <- items_cat %>%
filter(ItemNum == 0) %>%
select(Code, Name, Description, Kind) %>%
mutate(Name = tolower(as.character(Name)))

# Для каждой строки датафрэйма искомых данных применяем функцию поиска
mres <- apply(i.list, 1, get.res, items.cat = i.cat, q = quant)

# Соединяем полученные результаты для возврата из скрипта
OutputDataSet <- do.call("rbind", mres);'
, @input_data_1 = N'
SELECT Items._Code AS Code
,Items._Description AS Name
,Items._Fld5586 AS Description
,Kind._Description AS Kind
,NULL AS ItemName
,0 AS ItemNum
FROM ut_demo.dbo._Reference215 AS Items
LEFT JOIN ut_demo.dbo._Reference76 AS Kind
ON Items._Fld5567RRef = Kind._IDRRef
UNION ALL
SELECT Code, Name, Description, Kind, ItemName, ItemNum FROM integration.dbo.itemstofind;'
, @input_data_1_name = N'items_cat'
, @output_data_1_name = N'OutputDataSet'
, @params = N'@quant int'
, @quant = @quant
WITH RESULT SETS ((Value real, Code [nchar](11), Name [nvarchar](100), Description [nvarchar](1024), Kind [nvarchar](100), ItemName [nvarchar](100), ItemNum int));
END

Заполним тестовые данные для поиска

Проверим работоспособность запросом в SQL

DECLARE @return_value int
EXEC @return_value = [dbo].[CompareItems]
@quant = 5

Результат на демобазе УТ 11:

6. Код в 1С

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

СтрокаПодключения = "DRIVER={SQL Server};
|SERVER=XXX;
|UID=XXX;
|PWD=XXX;
|DATABASE=integration";

Connection = Новый COMОбъект("ADODB.Connection");
Command    = Новый COMОбъект("ADODB.Command");
RecordSet  = Новый COMОбъект("ADODB.RecordSet");

Попытка
Connection.Open(СокрЛП(СтрокаПодключения));
Command.ActiveConnection   = Connection;
Command.CommandText = "select * from sys.databases where name = 'integration'";
RecordSet = Command.Execute();

Если RecordSet.EOF() И RecordSet.BOF() Тогда
Возврат;
КонецЕсли;
RecordSet.MoveFirst();
Исключение
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;

// Почистим табличку скуля
Command.CommandText =
"USE integration
|TRUNCATE table dbo.itemstofind";
Command.Execute();

// Поместим в таблицу нужные нам данные
мСчет = 1;
мТекст = "USE integration
|INSERT INTO dbo.itemstofind (ItemName, ItemNum) VALUES";

Для Каждого Стр Из ТоварныеПозиции Цикл
мТекст = мТекст + "
|('" + Строка(Стр.Наименование) + "',
|" + Формат(мСчет, "ЧРД=.; ЧН=0; ЧГ=0") + "),";
КонецЦикла;

Command.CommandText = Лев(мТекст, СтрДлина(мТекст) - 1) + ";";
Command.Execute();

 // дернем процедуру
Command.CommandText =
"USE [integration]
|SET NOCOUNT ON
|DECLARE @return_value int
|EXEC @return_value = [dbo].[CompareItems]
|  @quant = 5
|";
RecordSet = Command.Execute();

Пока RecordSet <> Неопределено Цикл
Если RecordSet.Fields.Count > 0 Тогда
RecordSet.MoveFirst();

COMSafeArray = RecordSet.GetRows();
Array = COMSafeArray.Unload();
Для i = 0 По Array.UBound() Цикл
strArray = Array[i];

Если strArray.Count() < 2 Тогда
Продолжить;
КонецЕсли;

НовСтр = РезультатТест1.Добавить();

НовСтр.Наименование = strArray[5];
НовСтр.Код    = strArray[1];
НовСтр.Номенклатура = Справочники.Номенклатура.НайтиПоКоду(НовСтр.Код);
НовСтр.ЗначениеПоказателя = strArray[0];
КонецЦикла;
КонецЕсли;
RecordSet = RecordSet.NextRecordSet();
КонецЦикла;

Command = Неопределено;
Connection = Неопределено;
RecordSet = Неопределено; 

Берем первые 10 записей справочника номенклатуры. Заполняем таблицу ТоварныеПозиции.

Вот это мы ищем:

А вот это получаем:

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

7. Заключение

Думаю, данный подход является интересным опытом и в некоторых будущих задачах метод будет проверяться в рабочем высоконагруженном режиме. То, в каком виде он тут продемонстрирован, является лишь направлением к действию. Пример полностью работоспособный, но есть варианты оптимизации и более удобного использования. В первую очередь хочется уйти от использования физической таблицы с данными для поиска и улучшить расширяемость запроса SQL где идет объединение перед передачей в скрипт. Ещё будет хорошо сравнить производительность по различным вариантам. Есть ощущение, что обращение из R к SQL будет выигрывать в определенных случаях, особенно при использовании специализированных пакетов для R от MS (RevoScaleR).