Руководство по R. Изучение и визуализация данных

Область применения: SQL Server 2016 (13.x) и более поздних версий Управляемый экземпляр SQL Azure

Во второй части этой серии руководств вы изучите образец данных и создадите несколько графиков. Далее вы узнаете, как сериализовать графические объекты на Python, а затем десериализовать эти объекты и создать графики.

Во второй части этой серии руководств вы изучите образец данных, а затем создаете несколько графиков с помощью универсальных функций barplot и hist в базовом R.

Цель этой статьи — показать, как вызывать функции R из Transact-SQL в хранимых процедурах и сохранять результаты в форматах файлов приложения.

• Создайте хранимую процедуру, используя barplot для создания диаграммы R в виде данных varbinary. Использование bcp для экспорта двоичного потока в файл изображения.
• Создайте хранимую процедуру, используя hist для создания диаграммы, сохраняя результаты в виде выходных данных JPG и PDF.

Работая с этой статьей, вы узнаете о следующем.

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

В третьей части вы узнаете, как создавать функции из необработанных данных с помощью функции Transact-SQL. Затем вы вызовите эту функцию из хранимой процедуры, чтобы создать таблицу, содержащую значения характеристик.

В четвертой части вы научитесь загружать модули и вызывать необходимые функции для создания и обучения модели с помощью хранимой процедуры SQL Server.

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

Изучение данных

Разработка решения для обработки и анализа данных обычно связана с большим числом операций по анализу и визуализации данных. Для начала немного изучите образец данных, если вы это еще не сделали.

В исходном общедоступном наборе данных идентификаторы такси и записи о поездках были предоставлены в отдельных файлах. Однако, чтобы образец данных было удобнее использовать, исходные наборы данных были объединены по столбцам medallion, hack_licenseи pickup_datetime. Кроме того, была произведена выборка записей с целью получить 1 % от их общего числа. Полученный набор данных содержит 1 703 957 строк и 23 столбца.

Идентификаторы такси

• В столбце medallion представлены уникальные идентификационные номера такси.

• В столбце hack_license содержатся номера лицензий таксистов (без указания имен).

Записи о поездках и оплате

• Каждая запись о поездке включает сведения о местах посадки и высадки, а также о расстоянии поездки.

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

• Последние три столбца можно использовать для различных задач машинного обучения. Столбец tip_amount содержит непрерывный ряд числовых значений и может использоваться в качестве столбца меток для регрессионного анализа. Столбец tipped содержит значения "Да" или "Нет" и используется для двоичной классификации. Столбец tip_class содержит несколько меток классов и поэтому может использоваться в качестве метки для задач многоклассовой классификации.

  В этом пошаговом руководстве демонстрируется только задача двоичной классификации. Вы можете самостоятельно попробовать создать модели для двух других задач машинного обучения: регрессии и многоклассовой классификации.

• Все значения, используемые для столбцов меток, основаны на столбце tip_amount с применением следующих бизнес-правил:

  Имя производного столбца	Правило
  tipped	If tip_amount > 0, tipped = 1, otherwise tipped = 0
  tip_class	Class 0: tip_amount = $0 Класс 1: tip_amount > 0 $ и tip_amount <= 5 $ Класс 2: tip_amount > 5 $ и tip_amount <= 10 $ Класс 3: tip_amount > 10 $ и tip_amount <= 20 $ Класс 4: tip_amount > 20 $

Создание графиков с помощью языка R в T-SQL

Чтобы создать график, используйте функцию R barplot. На этом шаге выполняется построение гистограммы на основе данных, полученных из запроса Transact-SQL. Эту функцию можно включить в хранимую процедуру RPlotHistogram.

1. В СРЕДЕ SQL Server Management Studio в обозреватель объектов щелкните правой кнопкой мыши базу данных NYCTaxi_Sample и выберите новый запрос. Также можно выбрать Создать записную книжку в меню Файл Azure Data Studio и подключиться к базе данных.

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

   CREATE PROCEDURE [dbo].[RPlotHistogram]
   AS
   BEGIN
     SET NOCOUNT ON;
     DECLARE @query nvarchar(max) =  
     N'SELECT tipped FROM [dbo].[nyctaxi_sample]'  
     EXECUTE sp_execute_external_script @language = N'R',  
                                        @script = N'  
      image_file = tempfile();  
      jpeg(filename = image_file);  
      #Plot histogram  
      barplot(table(InputDataSet$tipped), main = "Tip Histogram", col="lightgreen", xlab="Tipped or not", ylab = "Counts", space=0)
      dev.off();  
      OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6));  
      ',  
      @input_data_1 = @query  
      WITH RESULT SETS ((plot varbinary(max)));  
   END
   GO
   

В этом скрипте необходимо обратить внимание на следующие ключевые моменты.

• Переменная @query определяет текст запроса ('SELECT tipped FROM nyctaxi_sample'), который передается в скрипт R в качестве аргумента входной переменной @input_data_1. Для скриптов R, которые выполняются в виде внешних процессов, требуется сопоставление "один-к-одному" между входными данными скрипта и входными данными системной хранимой процедуры sp_execute_external_script, запускающей сеанс R на SQL Server.

• В скрипте R переменная (image_file) определяется для хранения изображения.

• Функция barplot вызывается для создания графика.

• Для устройства R задается значение off, так как эта команда выполняется в виде внешнего скрипта в SQL Server. Обычно при использовании высокоуровневой команды построения диаграммы в среде R открывается графическое окно, называемое устройством. Если вы записываете данные в файл или обрабатываете результат каким-либо иным способом, устройство можно отключить.

• Графический объект R сериализуется в кадр данных R для вывода.

Выполнение хранимой процедуры и экспорт двоичных данных в файл изображения с помощью служебной программы bcp

Эта хранимая процедура возвращает изображение в виде потока данных varbinary, которые, очевидно, нельзя просмотреть напрямую. Однако вы можете использовать служебную программу bcp для получения данных varbinary и сохранения их в виде файла изображения на клиентском компьютере.

1. В Management Studio выполните следующую инструкцию:

   EXEC [dbo].[RPlotHistogram]
   

   Результаты

   plot0xFFD8FFE000104A4649...

2. Откройте окно командной строки PowerShell и выполните следующую команду, указав в качестве аргументов соответствующие имя экземпляра, имя базы данных, имя пользователя и учетные данные. Пользователи, которые используют удостоверения Windows, могут заменить -U и -P на -T.

   bcp "exec RPlotHistogram" queryout "plot.jpg" -S <SQL Server instance name> -d  NYCTaxi_Sample  -U <user name> -P <password> -T
   

   Примечание.

   В параметрах команды bcp учитывается регистр.

3. Если подключение успешно установлено, появится запрос на ввод дополнительных сведений о формате графического файла.

   Нажимайте клавишу ВВОД, чтобы принять значения по умолчанию, за исключением указанных ниже изменений.

   • Для длины префикса графика поля введите значение 0.

   • Введите Y , если необходимо сохранить выходные параметры для повторного использования в будущем.

   Enter the file storage type of field plot [varbinary(max)]: 
   Enter prefix-length of field plot [8]: 0
   Enter length of field plot [0]:
   Enter field terminator [none]:
   
   Do you want to save this format information in a file? [Y/n]
   Host filename [bcp.fmt]:
   

   Результаты

   Starting copy...
   1 rows copied.
   Network packet size (bytes): 4096
   Clock Time (ms.) Total     : 3922   Average : (0.25 rows per sec.)
   

   Совет

   Если сохранить сведения о формате в файл (bcp.fmt), программа bcp создаст определение формата, которое можно применять к схожим командам в дальнейшем, не получая запросы на задание параметров формата графического файла. Чтобы использовать файл формата, добавьте -f bcp.fmt в конец любой командной строки после аргумента пароля.

4. Выходной файл будет создан в том же каталоге, в котором выполнялась команда PowerShell. Чтобы просмотреть диаграмму, просто откройте файл plot.jpg.

   

Создание хранимой процедуры с помощью hist

Как правило, специалисты по анализу создают несколько визуализаций, чтобы рассмотреть данные с разных сторон. В этом примере вы создадите хранимую процедуру с именем RPlotHist для построения гистограмм, точечных и других диаграмм R в форматах JPG и PDF.

Эта хранимая процедура использует hist функцию для создания гистограммы, экспорта двоичных данных в популярные форматы, такие как .JPG, .PDF и .PNG.

1. В СРЕДЕ SQL Server Management Studio в обозреватель объектов щелкните правой кнопкой мыши базу данных NYCTaxi_Sample и выберите новый запрос.

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

   CREATE PROCEDURE [dbo].[RPlotHist]  
   AS  
   BEGIN  
     SET NOCOUNT ON;  
     DECLARE @query nvarchar(max) =  
     N'SELECT cast(tipped as int) as tipped, tip_amount, fare_amount FROM [dbo].[nyctaxi_sample]'  
     EXECUTE sp_execute_external_script @language = N'R',  
     @script = N'  
      # Set output directory for files and check for existing files with same names   
       mainDir <- ''C:\\temp\\plots''  
       dir.create(mainDir, recursive = TRUE, showWarnings = FALSE)  
       setwd(mainDir);  
       print("Creating output plot files:", quote=FALSE)
   
       # Open a jpeg file and output histogram of tipped variable in that file.  
       dest_filename = tempfile(pattern = ''rHistogram_Tipped_'', tmpdir = mainDir)  
       dest_filename = paste(dest_filename, ''.jpg'',sep="")  
       print(dest_filename, quote=FALSE);  
       jpeg(filename=dest_filename);  
       hist(InputDataSet$tipped, col = ''lightgreen'', xlab=''Tipped'',   
           ylab = ''Counts'', main = ''Histogram, Tipped'');  
        dev.off();  
   
       # Open a pdf file and output histograms of tip amount and fare amount.   
       # Outputs two plots in one row  
       dest_filename = tempfile(pattern = ''rHistograms_Tip_and_Fare_Amount_'', tmpdir = mainDir)  
       dest_filename = paste(dest_filename, ''.pdf'',sep="")  
       print(dest_filename, quote=FALSE);  
       pdf(file=dest_filename, height=4, width=7);  
       par(mfrow=c(1,2));  
       hist(InputDataSet$tip_amount, col = ''lightgreen'',   
           xlab=''Tip amount ($)'',   
           ylab = ''Counts'',   
           main = ''Histogram, Tip amount'', xlim = c(0,40), 100);  
       hist(InputDataSet$fare_amount, col = ''lightgreen'',   
           xlab=''Fare amount ($)'',   
           ylab = ''Counts'',   
           main = ''Histogram,   
           Fare amount'',   
           xlim = c(0,100), 100);  
       dev.off();  
   
       # Open a pdf file and output an xyplot of tip amount vs. fare amount using lattice;  
       # Only 10,000 sampled observations are plotted here, otherwise file is large.  
       dest_filename = tempfile(pattern = ''rXYPlots_Tip_vs_Fare_Amount_'', tmpdir = mainDir)  
       dest_filename = paste(dest_filename, ''.pdf'',sep="")  
       print(dest_filename, quote=FALSE);  
       pdf(file=dest_filename, height=4, width=4);  
       plot(tip_amount ~ fare_amount,   
           data = InputDataSet[sample(nrow(InputDataSet), 10000), ],   
           ylim = c(0,50),   
           xlim = c(0,150),   
           cex=.5,   
           pch=19,   
           col=''darkgreen'',    
           main = ''Tip amount by Fare amount'',   
           xlab=''Fare Amount ($)'',   
           ylab = ''Tip Amount ($)'');   
       dev.off();',  
     @input_data_1 = @query  
   END
   

В этом скрипте необходимо обратить внимание на следующие ключевые моменты.

• Выходные данные запроса SELECT в хранимой процедуре сохраняются в кадре данных R по умолчанию ( InputDataSet). После этого можно вызывать различные функции построения диаграмм R для создания графических файлов. В большей части встроенного скрипта R представлены параметры для этих графических функций, такие как plot и hist.

• Для устройства R задается значение off, так как эта команда выполняется в виде внешнего скрипта в SQL Server. Обычно при использовании высокоуровневой команды построения диаграммы в среде R открывается графическое окно, называемое устройством. Если вы записываете данные в файл или обрабатываете результат каким-либо иным способом, устройство можно отключить.

• Все файлы сохраняются в локальной папке C:\temp\Plots. Папка назначения определяется аргументами, предоставляемыми скрипту R в рамках хранимой процедуры. Чтобы вывести файлы в другую папку, измените значение переменной mainDir в скрипте R, встроенном в хранимую процедуру. Скрипт также можно изменить так, чтобы данные выводились в других форматах, в большее количество файлов и т. д.

Выполнение хранимой процедуры

Выполните следующую инструкцию, чтобы экспортировать двоичные данные диаграммы в форматы файлов JPEG и PDF.

EXEC RPlotHist

Результаты

STDOUT message(s) from external script:
[1] Creating output plot files:[1] C:\temp\plots\rHistogram_Tipped_18887f6265d4.jpg[1] 

C:\temp\plots\rHistograms_Tip_and_Fare_Amount_1888441e542c.pdf[1]

C:\temp\plots\rXYPlots_Tip_vs_Fare_Amount_18887c9d517b.pdf

Числа в именах файлов создаются случайным образом, чтобы исключить ошибку при попытке записи в существующий файл.

Просмотр выходных данных

Чтобы просмотреть диаграмму, откройте папку назначения и просмотрите файлы, созданные кодом R в хранимой процедуре.

1. Перейдите в папку, указанную в сообщении STDOUT (в примере это C:\temp\plots).

2. Откройте rHistogram_Tipped.jpg, чтобы отобразить количество поездок с чаевыми и без них (эта гистограмма похожа на ту, которая была создана на предыдущем шаге).

3. Откройте rHistograms_Tip_and_Fare_Amount.pdf, чтобы просмотреть распределение размеров чаевых в сравнении с суммами по тарифу.

   

4. Откройте rXYPlots_Tip_vs_Fare_Amount.pdf, чтобы просмотреть точечную диаграмму с суммой по тарифу по оси X и размером чаевых по оси Y.

   

Следующие шаги

Работая с этой статьей, вы выполните следующие задачи: