Используем R lang в SQL Server

В продолжение предыдущей статьи поговорим о том, как использовать SQL Server Machine Learning Services. В этой статье приведены варианты использования на языке R. 

Для чего должна быть установлена R 4.2.1(https://cran.r-project.org/src/base/R-4/).

Также нам потребуется пакет e1071, его можно установить в R Gui (меню) Пакеты -> Установить пакет.

Если же у вас тоже windows и это не будет работать как и у меня, скачивайте пакет c CRAN и выбирайте Пакеты -> Установить пакеты из локальных файлов, а затем скаченный архив. 

Некоторые пакеты требуют установки всех зависимостей, поэтому лучше устранить ошибки загрузки пакетов, если они у вас есть. Список ошибок и пути их решения описаны в блоге у Алексея Селезнева.

Для знакомства с возможностями SQL Server в машинном обучении используется датасет с пассажирами Титаника с Kaggle.

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

Идея SQL Server сервисов машинного обучения в том, чтобы работать с данными в БД и анализировать их никуда не выгружая, для нашего эксперимента нужно будет наоборот загрузить данные из csv в SQL Server. 

Скачиваем csv файлы с Kaggle. 

В SQL Server Management Studio запускаем скрипт создания БД titanic:

CREATE DATABASE titanic;

На созданной БД выбираем правой кнопкой в студии “Задачи -> Импорт неструктурированного файла”:

Далее указываем путь к файлу с датасетом train.csv Титаника (таблицу инструмент создаст), и проходим Далее без изменения структуры таблицы или столбцов. 

Тоже самое проделываем со вторым файлом test.csv.

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

EXEC sp_execute_external_script  @language =N'R'
    , @script=N'total_women <- sum(train_data$Sex == ''female'')
     # Get the total number of women who survived
     survived_women <- sum(train_data[which(train_data$Sex == ''female''), "Survived"])
     rate_women = survived_women/total_women
	 print(total_women)
	 print(survived_women)
	 print(rate_women)' 
    , @input_data_1 =N'SELECT * from dbo.train'
	, @input_data_1_name = N'train_data';
GO

EXEC sp_execute_external_script  @language =N'R'
    , @script=N'# Get the total number of men on the titanic
      total_men <- sum(train_data$Sex == ''male'')
      # Get the total number of women who survived
      survived_men <- sum(train_data[which(train_data$Sex == ''male''), "Survived"])
      rate_men = survived_men/total_men

      paste("% of men who survived:", rate_men * 100)
	  print(total_men)
	  print(survived_men)
	  print(rate_men)' 
    , @input_data_1 =N'SELECT * from dbo.train'
	, @input_data_1_name = N'train_data';
GO

Далее нам потребуются библиотеки R. Иногда SQL Server не видит библиотек, даже если вы установили их через R приложение:

Для того, чтобы понять есть ли нужная библиотека в доступе SQL Server запустите скрипт:

EXEC sp_execute_external_script @language = N'R', @script = N'OutputDataSet <- data.frame(installed.packages())'

Если нужной библиотеки не нашлось, то нужно перейти в каталог SQL Server и установить ее с помощью R.exe (подробнее описано https://www.mssqltips.com/sqlservertip/4760/common-issues-with-r-services-packages/)

У меня SQL Server находится по пути E:\Microsoft SQL Server\MSSQL15.MSSQL2019\R_SERVICES\bin

В норме это будет C:\Program files\Microsoft SQL Server\MSSQL15.<instance_name>\R_SERVICES\bin

В окне R.exe запустите команду установки пакетов (Install Packages from Repositories or Local Files)

install.packages("название пакета", "директория MS SQL Server R Library")

В моем случае это:

install.packages("e1071", "E:\\Microsoft SQL Server\\MSSQL15.MSSQL2019\\R_SERVICES\\library", dependencies=TRUE, repos='http://cran.rstudio.com/')

Проверяем наличие пакета в SSMS:

Запускаем создание модели:

EXEC sp_execute_external_script  
      @language = N'R'  
     , @script = N'  
          library(e1071);  
          fit <-naiveBayes(as.factor(Survived) ~ Pclass + Sex + SibSp + Parch,  data=train_data);  
          trained_model <- data.frame(payload = as.raw(serialize(fit, connection=NULL)));  
'  
     , @input_data_1 = N'SELECT * from dbo.train'  
     , @input_data_1_name = N'train_data'  
     , @output_data_1_name = N'trained_model'  
    WITH RESULT SETS ((model varbinary(max)));

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

Далее используем несколько входных данных. Для того, чтобы к вашему инстансу можно было подключиться включите TCP/IP

Затем создайте пользователя для подключения к БД

CREATE LOGIN [usrdemo] WITH PASSWORD=N'usrdemo', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE USER [usrdemo] FOR LOGIN [usrdemo] WITH DEFAULT_SCHEMA=[dbo]
GO

Запускаем создание модели, а затем предсказание на данных из датасета test

USE titanic;
  
  DECLARE @rscript NVARCHAR(MAX),
          @sqlscript NVARCHAR(MAX);

  SET @rscript = N'
    library(e1071);
    # train data from InputDataSet
    train_data <- InputDataSet
    # test from table
    conn <- "Driver={SQL Server};Server=VKS\\MSSQL2019;Database=titanic;Uid=usrdemo;Pwd=usrdemo"
    query <- "SELECT * FROM dbo.test;"
    test_data <- RxSqlServerData(connectionString = conn, sqlQuery = query)
    test_data <- rxDataStep(test_data)

	fit <-naiveBayes(as.factor(Survived) ~ Pclass + Sex + SibSp + Parch,  data=train_data);  
    trained_model <- data.frame(payload = as.raw(serialize(fit, connection=NULL))); 

    pred <- predict(fit, test_data, writeModelVars = TRUE)

	submission <- data.frame(PassengerId = test_data$PassengerId, Survived = pred)
 ';
  
  SET @sqlscript = N'SELECT * FROM dbo.train;';
  
  EXEC sp_execute_external_script
    @language = N'R',
    @script = @rscript,
    @input_data_1 = @sqlscript, 
	@output_data_1_name = N'submission'  
    WITH RESULT SETS ((PassengerId int, Survived BIT));
  GO

Далее можно поэкспериментировать с датасетом - обогатить другими полями и добавить их в формулу, чтобы предсказание получилось более точным. 

Также с помощью R можно делать визуализацию данных, но об этом мы поговорим на открытом уроке.