Применение средств MS SQL R service для 1С

Обмен - Обмен с другими системами

MS SQL сервер R Python

42
Некоторое время назад Microsoft добавила в MS SQL сервер службы машинного обучения, позволяющие выполнять программный код на языках программирования R и Python. В статье будет продемонстрирована общая схема и принцип того, как можно использовать данные службы в контексте разработки на 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)))
 
 Пояснение кода и ещё один пример на R

 Тут мы получаем список установленных пакетов встроенной функцией R. Эта функция возвращает объект класса matrix из которого нас интересует первая колонка. Вернуть нам надо таблицу данных типа data.frame, поэтому используем соответствующую функцию преобразования в данный тип. В последней строке с помощью конструкции WITH RESULT SETS мы типизируем и именуем колонки полученного из внешнего скрипта результата (это не обязательно, но для дальнейшей работы с результатом незаменимо да и просто не красиво в продакшне)

Для тех, кто мало знаком с R ещё один примерчик: вот тут мы получим таблицу данных с двумя колонками. Функция с() в примере формирует вектор строкового типа с названиями нужных нам колонок.

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

 Комментарии добавил в сам код на R

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

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

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

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

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

6. Код в 1С

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

 
 1С код

ТоварныеПозиции - ТЗ с реквизитом Наименование. При создании формы заполняем нужным набором данных для поиска

Подключаемся к БД. Не забываем про права пользователя на используемые БД в MSSQL.

	
	СтрокаПодключения = "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). 

42

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. jobkostya1c8 78 25.11.18 12:10 Сейчас в теме
Полезная статья. Одно ясно, надо учить MS SQL.
2. capitan 752 25.11.18 13:30 Сейчас в теме
Грех не велик, а задумка богатая ...)
Что то я не видел поставщиков которые скульными таблицами передают данные номенклатуры.
Т.е. если решать задачу сопоставления с номенклатурой поставщика скорее надо код на стороне писать
Который прочтет номенклатуру 1С через ком коннектор и впишет степень соответствия или подходящий артикул 1С в файл поставщика
Тогда ничего нарушать не нужно будет ;)
jobkostya1c8; +1 Ответить
6. acsent 1121 26.11.18 11:08 Сейчас в теме
Показатель - это что такое, что-то не увидел
7. Robbi 43 26.11.18 12:00 Сейчас в теме
(6)
Показатель - это что такое, что-то не увидел

Значение показателя? Это величина, полученная по алгоритму сравнения строк. Степень похожести
8. logarifm 1020 26.11.18 13:24 Сейчас в теме
Правда я не понял для чего это все вообще?
в 1С есть такая классная штука как внешние источники данных.
9. Silenser 495 26.11.18 16:21 Сейчас в теме
За проделанную работу спасибо. Довольно интересный материал.
ИМХО, нужно было взять другую фактуру, что-то связанное с big data, потому как нечеткий поиск реализован в полнотекстовом поиске MS SQL, который реализуется гораздо проще, хотя для 1С он и потребует отдельной базы, т.к. нуждается в первичном ключе. К тому же R доступен с 2016, а полнотекстовый c 2008.
10. МихаилМ 26.11.18 19:06 Сейчас в теме
(0)
понятно , что задачу придумать сложно . НО...
дополните статью сравнением с реализацией Жаро-Винклера на t-sql. иначе смысл теряется. еще лучше сравнить с clr
11. Robbi 43 27.11.18 15:25 Сейчас в теме
(10) Статья не совсем о том. Вот в (9) правы, что пример не отражает возможностей.
Речь совсем не о реализации алгоритма распознавания и сравнивать его с исполнением на t-sql - тема другая.
Правильнее выбрать другую проблему, не реализуемую в лоб на t-sql и 1C, но тут может слишком специфичная задача получится.
А в статье просто подход продемонстрирован на задаче понятной большинству.
12. artfa 29 28.11.18 13:06 Сейчас в теме
Знал бы язык запросов в Sql, то же бы написал что-то подобное
13. acsent 1121 28.11.18 16:26 Сейчас в теме
Как отладка такого кода происходит?
15. Robbi 43 29.11.18 10:39 Сейчас в теме
(13)Примерно так:
Пишем запросы SQL для получения необходимых данных
Пишем на R основу, сам алгоритм проверяем на простых объектах. Если есть обращение к БД из R - помогает профайлер (в случае сложных запросов)
Затем создаем хранимую процедуру.
На каждом этапе все тестируем и проверяем, особенно если есть параметры.
В 1С остальное уже просто дописать.
14. DataReducer 71 28.11.18 18:30 Сейчас в теме
Несмотря на огромный потенциал R, эта среда у нас в стране пока не пользуется большой популярностью. Поэтому любая публикация на эту тему заслуживает внимания.
Для решения задачи из примера я бы не стал обращаться напрямую к БД, но так как это обзор компонентов MS SQL Server, пример хороший и жизненный.
Случалось ли вам на практике применять R для решения каких-нибудь других задач из сферы использования 1С?
16. Robbi 43 29.11.18 10:54 Сейчас в теме
(14) Использовал для обменов с каким-либо внешними источниками. Когда нужно предварительно обработать и "очистить" информацию. Т.е. получаем данные в R (например парсим сайт, или читаем стороннюю БД не 1Сную), обрабатываем их, получаем красивые наборы данных и отправляем в 1С, например через http-сервисы, поднятые в 1С, или в СУБД. Так же есть такая штука на R - Shiny server - для пользователей выводил в браузере аналитику по данным 1С он-лайн, графики, картинки с нужными фильтрами и прочими плюшками. Только это было больше для себя реализовано. Экономической целесообразности в таком подходе не вижу: в крупных компаниях используется специализированный софт, для мелких и средних гораздо дешевле и проще написать отчет на 1С.
DataReducer; +1 Ответить
Оставьте свое сообщение