Как я уже упоминал раньше, в языке R присутствует достаточно мощная система создания графиков всевозможных типов. Я планирую посвятить отдельные статьи разным типам графиков, а здесь я постараюсь рассказать, как построить простой график на R с помощью R Services и включить его в отчет Reporting Services.

Итак, для построения графика нам в первую очередь потребуются данные. Для этого сгенерируем небольшой тестовый набор данных. Предположим, что у нас есть магазин, который посещают клиенты из 5 различных районов города. По каждому из клиентов мы знаем, сколько раз в неделю в среднем он посещает наш магазин и на какую сумму делает покупки. Мы хотим наглядно увидеть, отличается ли поведение клиентов в нашем магазине в зависимости от района. Ниже представлен скрипт для генерации тестовых данных.

use [TEST];
go
 
if object_id('dbo.test_customer_activity_table', 'U') is not null
	drop table dbo.test_customer_activity_table;
go
 
with region as (
	select cast('A' as char(1)) as region, cast(floor(rand(checksum(newid())) * 10) as int) + 1 as n  union all
	select 'B', cast(floor(rand(checksum(newid())) * 10) as int) + 1 union all
	select 'C', cast(floor(rand(checksum(newid())) * 10) as int) + 1 union all
	select 'D', cast(floor(rand(checksum(newid())) * 10) as int) + 1 union all
	select 'E', cast(floor(rand(checksum(newid())) * 10) as int) + 1
)
select
	region.region,
	t.num_visits,
	t.amount_spent
into dbo.test_customer_activity_table
from region
cross apply (
	select top (n)
		cast(floor(rand(checksum(newid())) * 5) as int) + 1 as num_visits,
		cast(rand(checksum(newid())) * 1000 as decimal(28, 2)) + 1 as amount_spent
	from sys.all_columns
) as t
order by
	region.region;
go

Как видно, данные я сформировал совешенно случайным образом без какой-либо корреляции и вставил в таблицу dbo.test_customer_activity_table. Далее нам нужна будет хранимая процедура, которая возьмет эти данных и на базе них построит график, на котором на оси X будет обозначено количество посещений магазина, по оси Y – средний счет, а районы города мы обозначим на графике точками разного цвета. Для отрисовки я буду использовать пакет ggplot2, про установку которого я уже рассказывал ранее. Итак, процедура будет запускать код на R с помощью sp_execute_external_script, передавая ей набор данных из тестовой таблицы.

create procedure dbo.r_report_test
as
begin
 
	execute sp_execute_external_script
		@language = N'R',
		@script = N'
			library("ggplot2");
			image_file = tempfile();
			jpeg(filename = image_file, width=600, height = 800);
			print(qplot(num_visits, amount_spent, data = SqlIn, color = region,# size = amount_spent,
				xlab = "Number of visits", ylab = "Amount spent",
				main = "Customer activity by region") + geom_point(size = 5));
			dev.off();
			OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6));
		',
		@input_data_1 = N'select region, num_visits, amount_spent from dbo.test_customer_activity_table',
		@input_data_1_name = N'SqlIn'
	with result sets ((plot varbinary(max)));
 
end;
go

Хранимая процедура возвращает набор данных, состоящий из одной строки и одного поля plot, в котором в бинарном формате возвращается графический файл в формате jpeg. Теперь нам только остается отобразить его в отчете Reporting Services. В Visual Studio создаем проект Report Server Project.

После этого справа в Solution Explorer кликаем правой клавишей мыши на папке Reports и добавляем в него пустой отчет.


Сначала в отчет нам нужно добавить Dataset. Для этого слева щелкаем правой клавишей мыши на папке Datasets и добавляем новый.

В нем нужно выбрать соединение к вашему тестовому серверу и базе данных, где мы расположили таблицу и храниму процедуру для формирования графика. В первом окне выбираем Use dataset embedded in my report, далее в Data Source выбираем кнопку New.

В появившемся окне нажимаем на кнопку Edit в поле Connection string.

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

После этого нажимаем OK два раза, параметр Query type выставляем Stored procedure и в выпадающем списке выбираем нашу хранимую процедуру.

Нажимаем OK и у нас слева должен появиться наш новый Dataset.

Теперь на наш отчет мы добавляем элемент image и делаем его побольше в размерах, чтобы картинку было лучше видно. И открываем его свойства. В появившемся окне мы должны заполнить свойства Select the image source – Database, выбрать из выпадающего списка имя поля, в котором содержится картинка и выбрать ее формат image/jpeg.

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

Конечно, это всего-лишь простой пример, но я постарался сделать пошаговое руководство, как включить в отчет графики на R. Ваши отчеты в реальности скорее всего будут выглядить гораздо сложнее, содержать большее количество элементов. Надеюсь этот простой пример поможет вам быстрее начать и создать ваш первый отчет с графиками на R. Оставайтесь на связи, в следующих частях я начну рассказывать про обработку данных с помощью библиотеки ScaleR.

Как мы знаем, одним из самых больших недостатков R является то, что он может обрабатывать только данные, которые расположены в оперативной памяти. В SQL Server 2016 этот недостаток в определенной мере остается. Если вы используете стандартные функции R и попытаетесь передать в скрипт слишком большой объем данных, который не влезет в доступную оперативную память, то получите ошибку. В качесте простого примера я попробую посчитать среднее от большого объема данных (на самом деле T-SQL с этой задачей справился бы гораздо быстрее и эффективнее, но это всего-лишь простой пример).

declare @out_v decimal(28, 4);
 
execute sp_execute_external_script
	@language = N'R',
	@script = N'out_v1 <- mean(InputDataSet[, 1]);',
	@input_data_1 = N'select [n] from [dbo].[big_table];',
	@params = N'@out_v1 int output',
	@out_v1 = @out_v output
with result sets none;
 
select @out_v;
go

Msg 39004, Level 16, State 19, Line 138
A ‘R’ script error occurred during execution of ‘sp_execute_external_script’ with HRESULT 0x80004004.
Msg 39019, Level 16, State 1, Line 138
An external script error occurred:
Error in eval(expr, envir, enclos) : bad allocation
Calls: source -> withVisible -> eval -> eval -> .Call
Execution halted

(1 row(s) affected)

Поэтому эту и другие подобные задачи «в лоб» решить не получится. Однако слона можно есть по частям. В R Services есть возможность разбить входящий набор данных на несколько кусков и выполнить R скрипт отдельно на каждом куске. Для этого существует специальный параметр @r_rowsPerRead, в который вы можете передать количество строк, из которых будет состоять каждая отдельная обрабатываемая порция данных. Например, в вышеуказанном примере я пробовал вычислить среднее сразу для почти 90 млн. строк. Попробуем повторить задачу, однако разобьем входящий набор данных на несколько объемом до 10 млн. строк каждый. Для этого мне также пришлось слегка поменять скрипт, чтобы он возвращал среднее не как переменную, а как исходящий набор данных.

execute sp_execute_external_script
	@language = N'R',
	@script = N'OutputDataSet <- as.data.frame(mean(InputDataSet[, 1]))',
	@input_data_1 = N'select [n] from dbo.very_big_table;',
	@params = N'@r_rowsPerRead int',
	@r_rowsPerRead = 10000000
with result sets (([avg] decimal(28, 4) not null));
go

Мы видим, что результаты работы скрипта на каждом отдельном куске были объединены в один результирующий набор, поэтому нам вернулось 9 строк, каждая из которых является средним значением для своего набора. Очевидно, что данный подход не будет работать для всех алгоритмов. Например, даже в этом примере нельзя просто взять и посчитать среднее от получившихся девяти значений, т.к. данные могут быть искажены. Правильнее в этом случае было бы считать число строк в наборе и сумму значений, а потом считать общую сумму и общее число значений и делить первое на второе. Однако данный подход подойдет для всех алгоритмов, которые можно применять на порциях данных вместо всего целого набора. Мы могли бы с тем же успехом вручную разбить набор данных и запустить скрипт на кажом куске отдельно, однако комплексный подход гораздо проще.

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

execute sp_execute_external_script
	@language = N'R',
	@script = N'OutputDataSet <- as.data.frame(mean(InputDataSet[, 1]))',
	@input_data_1 = N'select [n] from dbo.big_table;',
	@parallel = 1
with result sets (([avg] decimal(28, 4) not null));
go

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

Т.к. T-SQL и R разные языки, каждый со своими типами данных, то сразу возникает много вопросов о совместимости между ними. R поддерживает только ограниченный набор типов: numeric, integer, complex, logical, character, date/time и raw. Все типы T-SQL будут неявно преобразованы в их соответствующие аналоги в R. Однаго, следующие типы данных T-SQL не поддерживаются в R.

• cursor
• timestamp
• datetime2, datetimeoffset, time
• sql_variant
• text, ntext, image
• xml
• CLR типы данных (в т.ч. hierarchyid, geometry, geography)
• binary, varbinary (но могут передаваться как параметры)

Поэтому при попытке передать значения этих типов вы получите ошибку.

execute sp_execute_external_script
    @language = N'R',
    @script = N'OutputDataSet <- InputDataSet;',
    @input_data_1 = N'select cast(''19000101'' as sql_variant);'
with result sets undefined;
go

Msg 39017, Level 16, State 1, Line 74
Input data query returns column #0 of type ‘sql_variant’ which is not supported by the runtime for ‘R’ script. Unsupported types are binary, varbinary, timestamp, datetime2, datetimeoffset, time, nchar, nvarchar, ntext, image, hierarchyid, xml, sql_variant and user-defined type.

Сейчас в документации типы nvarchar и nchar также отображаются как неподдерживаемые, и в RC версиях попытка передать их действительно вызывала ошибку, однако в RTM они уже полностью поддерживаются. Просто документацию еще не успели обновить, это должно произойти в ближайшее время.

Если вы хотите посмотреть, какие типы данных T-SQL в какие типы данных R будут конфертированы, то для этого можно воспользоваться функцией str в R.

execute sp_execute_external_script
    @language = N'R',
    @script = N'str(InputDataSet);',
    @input_data_1 = N'select cast(''20000101'' as datetime) as v1, cast(1 as int) as v2, cast(1 as bit) as v3, cast(''qwe'' as varchar(100)) as v4, cast(1.01 as numeric(5, 2)) as v5;'
with result sets none;
go

STDOUT message(s) from external script:
‘data.frame’:    1 obs. of 5 variables:
$ v1: POSIXct, format: “2000-01-01”
$ v2: int 1
$ v3: logi TRUE
$ v4: Factor w/ 1 level “qwe”: 1
$ v5: num 1.01

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

execute sp_execute_external_script
    @language = N'R',
    @script = N'OutputDataSet <- InputDataSet;',
    @input_data_1 = N'select cast(0x01 as binary(1));'
with result sets undefined;
go

Msg 39017, Level 16, State 1, Line 74
Input data query returns column #0 of type ‘binary(1)’ which is not supported by the runtime for ‘R’ script. Unsupported types are binary, varbinary, timestamp, datetime2, datetimeoffset, time, nchar, nvarchar, ntext, image, hierarchyid, xml, sql_variant and user-defined type.

Однако через переменные все отработает успешно.

declare @in_v binary(1) = 0x01, @out_v binary(1);
 
execute sp_execute_external_script
    @language = N'R',
    @script = N'out_v1 <- in_v1;',
    @params = N'@in_v1 binary(1), @out_v1 binary(1) output',
    @in_v1 = @in_v,
    @out_v1 = @out_v output
with result sets none;
 
select @out_v;
go

Также R содержит несколько специальных значений, таких как +Inf, -Inf, NaN и NA. Все они при передаче в T-SQL будут конвертированы в NULL.

execute sp_execute_external_script
    @language = N'R',
    @script = N'OutputDataSet <- data.frame(v1 = +Inf, v2 = -Inf, v3 = NaN, v4 = NA, v5 = 1);'
with result sets undefined;
go

И еще один очень важный момент, который тоже надо учитывать. Даже поддерживаемые и совместимые типы данных имеют разный допустимый диапазон значений. Например, datetime в T-SQL может принимать более широкий диапазазон значений, чем дата и время в R. Поэтому все недопустимые значения будут неявно конвертированы в NULL. При этом вы не получите никаких ошибок. Поэтому нужно быть очень аккуратным и учитывать этот факт.

execute sp_execute_external_script
    @language = N'R',
    @script = N'OutputDataSet <- InputDataSet;',
    @input_data_1 = N'select cast(''19000101'' as datetime) as v1, cast(''20000101'' as datetime) as v2, cast(''19700101'' as datetime) as v3;'
with result sets undefined;
go

execute sp_execute_external_script
    @language = N'R',
    @script = N'str(InputDataSet);',
    @input_data_1 = N'select cast(''19000101'' as datetime) as v1, cast(''20000101'' as datetime) as v2, cast(''19700101'' as datetime) as v3;'
with result sets none;
go

STDOUT message(s) from external script:
‘data.frame’:    1 obs. of 3 variables:
$ v1: POSIXct, format: NA
$ v2: POSIXct, format: “2000-01-01”
$ v3: POSIXct, format: “1970-01-01”

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

execute sp_execute_external_script
    @language = N'R',
    @script = N'OutputDataSet <- InputDataSet;',
    @input_data_1 = N'select cast(1.000000000000321 as numeric(28, 20)) as v1;'
with result sets undefined;
go

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

Мы подошли, пожалуй, к самому интересному, запуску команд на R внутри SQL Server. Это можно сделать с помощью отдельной хранимой процедуры sp_execute_external_script, у которой есть несколько параметров.

В первую очередь это обязательные параметры @language и @script. Первый пока может принимать только одно значение – R. В будущем планируется добавить поддержку других языков. А во второй вы передаете код на языке R, который хотите выполнить. Ниже я привожу пример самого простого скрипта, который вам вернет информацию по встроенному набору данных iris, а также результат его выполнения.

execute sp_execute_external_script
	@language = N'R',
	@script = N'str(iris);'
with result sets none;
go

Все остальные параметры опциональны, однако, скорее всего, вы ими постоянно будет пользоваться. Например, параметр @input_data_1 принимает запрос на языке T-SQL, возвращающий набор данных, который может быть использован внутри скрипта на R. В R скрипте вы сможете обращаться к этому набору данных по имени InputDataSet, если вы не задали другое значение в параметре @input_data_1_name. На текущий момент поддерживается только один входящий набор данных. Если в результате работы вашего скрипта вы хотите сформировать набор данных и вернуть его назад в SQL Server, то в скрипте вы должны сформировать data frame с именем OutputDataSet, либо любым другим именем, которое вы должны передать в параметре @output_data_1_name. Ниже вы можете увидеть пример скрипта, в который я передаю запрос, возвращающий всего лишь одно значение 1, а R скрипт просто возвращает входящий набор как есть, без изменений.

execute sp_execute_external_script
	@language = N'R',
	@script = N'OutputDataSet <- InputDataSet;',
	@input_data_1 = N'select 1;'
with result sets undefined;
go

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

execute sp_execute_external_script
	@language = N'R',
	@script = N'SQLOut <- SQLIn;',
	@input_data_1 = N'select 1;',
	@input_data_1_name = N'SQLIn',
	@output_data_1_name = N'SQLOut'
with result sets undefined;
go

Результат работы будет тот же самый. И если вы внимательно посмотрите, то у вазвращаемого набора данных нет даже названия колонки. Это происходит из-за того, что я указал опцию with results set undefined. Если же я хочу возвращать строго типизированный набор, то я должен его описать, например вот так:

execute sp_execute_external_script
	@language = N'R',
	@script = N'SQLOut <- SQLIn;',
	@input_data_1 = N'select 1 as id, N''qwe'' as name, cast(''20000101'' as datetime) as dt;',
	@input_data_1_name = N'SQLIn',
	@output_data_1_name = N'SQLOut'
with result sets undefined;
go
 
execute sp_execute_external_script
	@language = N'R',
	@script = N'SQLOut <- SQLIn;',
	@input_data_1 = N'select 1 as id, N''qwe'' as name, cast(''20000101'' as datetime) as dt;',
	@input_data_1_name = N'SQLIn',
	@output_data_1_name = N'SQLOut'
with result sets ((id int not null, name varchar(100) not null, dt datetime not null));
go

Кроме передачи данных как набора, можно также передать и вернуть значения в виде переменных. Для этого существует отдельный параметр @params, в котором вы должны объявить все переменные, которые будут использоваться. После этого вы все их также передаете в хранимую процедуру, а для возвращаемых параметров указываете ключевое слово output.

declare @in_v int = 100, @out_v int;
 
execute sp_execute_external_script
	@language = N'R',
	@script = N'out_v1 <- in_v1;',
	@params = N'@in_v1 int, @out_v1 int output',
	@in_v1 = @in_v,
	@out_v1 = @out_v output
with result sets none;
 
select @out_v;
go

На этом базовое введение в запуск команд на R внутри SQL Server можно считать оконченным. В следующих частях я подробно расскажу о тонкостях поведения R Services при передаче и возвращении данных, поддержке различных типов данных, а также мы начнем рассматривать более сложные примеры, параллельное выполнение кода и обработку больших объемов данных.

Итак, давайте для начала разберемся, как поставить и активировать SQL Server 2016 R Services. В первую очередь при установке вам необходимо выбрать соответствующий компонент. В ранних CTP сборках он назывался Advanced Analytics Extensions, в финальной RTM версии вам нужно выбрать R Services (In-Database).

Если ваш сервер подключен к интернету, то инсталлятор сам скачает и установит необходимые компоненты с интернета. Но, как мы знаем, большая часть серверов не имеет прямого доступа в интернет, т.к. это не безопасно. Поэтому я и решил рассказать в деталях, что нужно будет сделать в этом случае. До этого момента действуем также как и обычно, выбираем R Services и идем дальше. На определенном шаге установки вам предложат согласится с установкой Microsoft R Open.

Нажимаете Accept, соглашаетесь, далее у вас снова становится активна кнопка Next, нажимаете ее. После этого установщик попросит вас выбрать директорию, где лежат дистрибутивы Microsoft R Open и Microsoft R Server.

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

Все достаточно просто. Установка даже в offline режиме упрощена и предусмотрена. Однако, это еще далеко не все. R Services необходимо активировать выполнив следующую команду, в противном случае вы будет получать ошибку при попытке запустить любой код на R.

sp_configure 'external scripts enabled', 1
go

Внимание! После этого требуется перезапуск экземпляра SQL Server. После этого вы сможете выполнять базовые команды на языке R внутри SQL Server. Но мы знаем, что основная мощь языка R достигается за счет пакетов, которые расширяют его возможности, принося дополнительные функции. Для установки пакетов необходимо запустить консоль R. Для этого идем в директорию C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\bin и запускаем R.exe от имени администратора.

Директория может отличаться, если при установке SQL Server у вас была выбрана другая. Поэтому просто ищете директорию R_SERVICES\bin внутри того места, куда вы устанавливали SQL Server. Также, если вы не запустите R от имени админитратора, то у вас не будет прав на запись в диреторию с пакетами.

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

Давайте попробуем рассмотреть установку пакетов на примере очень популярного графического расширения ggplot2. Если ваш сервер полючен к интернету, то вам всего лишь необходимо ввести следующую команду:

install.packages("ggplot2")

R сам скачает необходимый пакет с центрального хранилища CRAN, а также все зависимости и установит их. Но, если ваш сервер не подключен к интернету, то придется немного попотеть. Для начала будет необходимо скачать нужные пакеты. Для этого идем на центральное хранилище всех пакетов CRAN. Для ggplot2 ссылка будет следующая: https://cran.r-project.org/web/packages/ggplot2/index.html.

Обратите внимание, внизу я отметил ссылку на ZIP файл для самого пакета, который нужно скачать, но этот пакет зависит от других пакетов, который в свою очедь тоже зависят. Поэтому придется походить по ссылкам вверху, скачать все пакеты из разделов Depends и Imports, а также все зависимости этих пакетов. На самом деле не пугайтесь, у вас их получится не так много. У меня получилось 13 штук. Я их поместить в директорию C:\temp.

После этого вам необходимо создать файл PACKAGES в этой директории, который будет своего рода индексом для находящихся в ней пакетов. Для этого нужно вернуться в консоль R и выполнить следующие команды.

library(tools)
write_PACKAGES("C:/temp/")

После этого вы уже можете запустить команду для установки пакета ggplot2 с указанием нашей директории.

install.packages("ggplot2", contriburl = "file:C:/temp")

Эта команда выполнит установку пакета ggplot2, а также всех его зависимостей из указанной нами директории. Вы можете проверить, установлен ли этот пакет, попробовав его загрузить.

library(ggplot2)

Если вдруг вы забыли скачать какой-то пакет из зависимостей, то вы увидите ошибку и название пакета, которого не хватает. Не отчаивайтесь, просто скачивайте отсутствующий пакет в указанную директорию, его зависимости, заново перестраивайте на ней индекс и запускайте команду установки для этого пакета. Потом заново пробуйте загрузить пакет ggplot2.

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