Tag Archives: SQL Server 2016

SQL Server 2016: R Services, часть 6, графики в отчетах Reporting Services

Как я уже упоминал раньше, в языке 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.

SQL Server 2016: R Services, часть 5, параллельное выполнение кода и обработка больших объемов данных

Как мы знаем, одним из самых больших недостатков 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.

SQL Server 2016: R Services, часть 4, поддерживаемые типы данных

Т.к. 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

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

SQL Server 2016: R Services, часть 3, примеры запуска команд на R

Мы подошли, пожалуй, к самому интересному, запуску команд на 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, часть 2, offline установка сервера и пакетов

Итак, давайте для начала разберемся, как поставить и активировать 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.

SQL Server 2016: R Services, часть 1, обзор

Это первая, вступительная часть, надеюсь очень большого цикла про поддержку языка R в SQL Server 2016. Если вы еще не слышали, что такое R, то, в двух словах, это функциональный язык для обработки данных и их графического представления. Он также широко используется для создания моделей машинного обучения. Это проект с исходным кодом, который стартовал в начале 90х готов, и абсолютно бесплатен. Чуть позже я планирую начать публиковать отдельную серию статей, как вводный материал для тех, кто никогда не работал с R. R за последние годы становится очень популярным среди аналитиков данных, и его поддержку добавили в SQL Server 2016.

Основным минусом языка R становится то, что все обрабатываемые данные должны находиться в памяти. А на сегодняшний день, как мы знаем, количество анализируемых данных постоянно растет и выходит за пределы оперативной памяти. В SQL Server 2016 это ограничение можно обойти за счет используемых технологий. Чуть более года назад компания Microsoft купила Revolution Analytics, которая выпускает свои коммерческие продукты на основе языка R. Одним из этих продуктов является R Server, который содержит расширенные пакеты и свои версии многих популярных R функций, которые позволяют обрабатывать данные многопоточно, подгружая в память необходимые куски по мере необходимости. Все эти технологии были встроены и в SQL Server 2016. При этом осталась 100% совместимость с языком R. Вы можете выполнять внутри любой код на R, а также устанавливать и использовать дополнительные пакеты для R, которые расширяют возможности этого языка.

Отдельно стоит отметить возможности R по графическому представлению данных. Теперь вы можете генерировать эти графики внутри SQL Server 2016 и отображать в Reporting Services или в других своих приложениях.

Существует 2 сценария работы с R в SQL Server 2016. Во-первых, ваше приложение может выполнять хранимые процедуры на SQL Server, которые могут содержать код на R, и получать к себе результаты в виде таблиц, графиков, предсказаний и т.п. А во-вторых, ваш выделенный аналитик может с помощью специального API посылать команды на SQL Server, чтобы все вычисления выполнились там, а вернулись только результаты. Таким образом он может избежать долгого процесса загрузки данных на свою машину.

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

SQL Server 2016: Database Scoped Configuration

Одним из наиболее интересных нововведений в SQL Server 2016 (а также в Azure SQL Database V12) является возможность контролировать некоторые параметры, напрямую связанные с производительностью, на уровне базы данных, а не сервера или запроса, как было раньше. Несомненно, это огромный плюс, т.к. обычно с разными базами данных работают разные приложения, которые могут требовать своих требований к настройкам. Также это позволяет более гибко управлять этими настройками, т.к. определенные пользователи с правами на указанные базы уже могут менять эти параметры, нет необходимости расширять им права до уровня сервера. И еще один интересный момент, если ваши базы данных участвуют в AlwaysOn Availability Groups или в GeoReplication для Azure, то вы можете даже выставить разные настройки для главной (PRIMARY) базы и для вторичных реплик (SECONDARY), т.к. опять же, у вторичных реплик может быть совсем другой сценарий использования и требования, чем у первичной базы данных.

Эта новая опция называется Database Scoped Configuration. На текущий релиз в нее входят следующие параметры:

  • Возможность очистить процедурный кэш только для конкретной базы данных. Раньше это можно было сделать либо для всего сервера, либо для конкретного запроса по его plan handle.
  • MAXDOP – уровень параллелизма по умолчанию. 0 – будут использоваться настройки сервера. Эта настройка перебивает соответствующую на уровне сервера, однако, подсказки на уровне запросов обладают высшим приоритетом. При этом не забываем, что Resource Governor все равно всех ограничивает сверху. Подробнее о его работе вы можете прочитать тут.
  • Parameter Sniffing – включить или выключить. Выключение аналогично trace flag 4136. На уровне запроса этим можно было управлять с помощью подсказки OPTIMIZE FOR UNKNOWN.
  • Управление, какую версию Cardinality Estimator использовать (новую, появившуюся в SQL Server 2014, либо предыдущую).
  • Оптимизатор запросов со всеми последними обновлениями. Аналог trace flag 4199.

Для того, чтобы внести изменения, необходимо обладать правами ALTER ANY DATABASE SCOPED CONFIGURATION на уровне базы данных. Такие права может выдать любой, у кого есть право CONTROL на базу данных.

grant alter any database scoped configuration to [MyUser];

Если вы изменяете настройки в Database Scoped Configuration, то они автоматически транслируются на вторичные реплики, если база данных участвует в AlwaysOn AG или Geo Replication. Если же вы хотите для вторичных реплик выставить свои отдельные значения, то необходимо будет использовать ключевое слово FOR SECONDARY. Например, ниже вы увидите пример изменения настройки MAXDOP для главной базы данных, а во втором случае для вторичных реплик. Все вторичные реплики получают одинаковые настройки. Нет возможности выставить свои отдельные параметры для разных вторичных реплик.

alter database scoped configuration set maxdop = 4;
alter database scoped configuration for secondary set maxdop = 2;

Также параметры с опцией FOR SECONDARY могут принимать значение PRIMARY. Это будет означать, что настройки будут идентичными, что и на основной базе данных.

alter database scoped configuration for secondary set maxdop = primary;

Примеры выключения parameter sniffing для основной базы данных и вторичных реплик:

alter database scoped configuration set parameter_sniffing = off;
alter database scoped configuration for secondary set parameter_sniffing = off;

Возможность вернуть старую версию Cardinality Estimator:

alter database scoped configuration set legacy_cardinality_estimation = on;

Активация нового оптимизатора запросов со всеми исправлениями:

alter database scoped configuration set query_optimizer_hotfixes = on;

Очистка процедурного кэша для отдельной базы данных (доступно только для основной БД):

alter database scoped configuration clear procedure_cache;

Также вам доступен просмотр всех текущи значений в Database Scoped Configuration через системное представление sys.database_scoped_configurations.

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

SQL Server 2016: JSON, часть 6, сравнение производительности с XML

Мы подошли, пожалуй, к самой интересной части серии статей про работу с JSON в SQL Server 2016. Я уже упоминал, что разработчики полюбили JSON за его ясную и понятную глазу структуру, а также за меньший размер данных, чем XML. Осталось проверить самое главное, работает ли SQL Server с JSON быстрее, чем с XML. Для этого я написал небольшой тест на замер скорости работы функции JSON_VALUE и метода value() у типа XML. Для JSON я решил проверить, как работает извлечение данных как из типа varchar(max), так и из nvarchar(max). Я составил похожие по структуре JSON и XML и попробую выбирать из них разные типы данных, числовой и строковый и из разных частей документов. Ну и давайте посмотрим, что же получилось.

declare @json varchar(max) = '[
	{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"]},
	{"first name":"John","last name":"Smith","sex":"m","skills":["SQL Server 2014","In-Memory OLTP"]},
	{"first name":"Mary","last name":"Brown","age":25,"skills":["SQL Server 2016","In-Memory OLTP"]}]';

declare @json_u nvarchar(max) = N'[
	{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"]},
	{"first name":"John","last name":"Smith","sex":"m","skills":["SQL Server 2014","In-Memory OLTP"]},
	{"first name":"Mary","last name":"Brown","age":25,"skills":["SQL Server 2016","In-Memory OLTP"]}]';

declare @xml xml = N'
	32SQL Server 2016T-SQLJSON
	mSQL Server 2014In-Memory OLTP
	25SQL Server 2016In-Memory OLTP
';

declare
	@i int,
	@v1 int,
	@v2 varchar(100),
	@start_time datetime,
	@end_time datetime,
	@iterations int = 1000000,
	@path_expression nvarchar(1000),
	@returned_type varchar(100);

declare @results table (
	data_type varchar(100) not null,
	test_id tinyint not null,
	path_expression varchar(1000) not null,
	returned_type varchar(1000),
	elapsed_time_ms int not null
);


set @returned_type = 'int';
set @path_expression = '$[0].age'

set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v1 = json_value(@json, '$[0].age');
	set @i += 1;
end
set @end_time = getutcdate();

insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json', 1, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);


set @returned_type = 'varchar';
set @path_expression = '$[2]."first name"';

set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json, '$[2]."first name"');
	set @i += 1;
end
set @end_time = getutcdate();

insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json', 2, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);


set @returned_type = 'varchar';
set @path_expression = '$[2].skills[0]';

set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json, '$[2].skills[0]');
	set @i += 1;
end
set @end_time = getutcdate();

insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json', 3, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);


set @returned_type = 'int';
set @path_expression = '$[0].age'

set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v1 = json_value(@json_u, '$[0].age');
	set @i += 1;
end
set @end_time = getutcdate();

insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json u', 1, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);


set @returned_type = 'varchar';
set @path_expression = '$[2]."first name"';

set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json_u, '$[2]."first name"');
	set @i += 1;
end
set @end_time = getutcdate();

insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json u', 2, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);


set @returned_type = 'varchar';
set @path_expression = '$[2].skills[0]';

set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json_u, '$[2].skills[0]');
	set @i += 1;
end
set @end_time = getutcdate();

insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json u', 3, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);


set @returned_type = 'int';
set @path_expression = '(/root/rec/age)[1]';

set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v1 = @xml.value('(/root/rec/age)[1]', 'int');
	set @i += 1;
end
set @end_time = getutcdate();

insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'xml', 1, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);


set @returned_type = 'varchar';
set @path_expression = '(/root/rec/@first_name)[3]';

set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = @xml.value('(/root/rec/@first_name)[3]', 'varchar(100)');
	set @i += 1;
end
set @end_time = getutcdate();

insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'xml', 2, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);


set @returned_type = 'varchar';
set @path_expression = '/root[1]/rec[3]/skills[1]/skill[1]';

set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = @xml.value('/root[1]/rec[3]/skills[1]/skill[1]', 'varchar(100)');
	set @i += 1;
end
set @end_time = getutcdate();

insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'xml', 3, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);


select *
from @results;

Результаты, если честно, удивили меня самого. В первую очередь стоит отметить, что выбор данных из JSON, хранящегося в типе nvarchar(max), проиходит быстрее на 5-15%, чем из обычного типа без поддержки Unicode. Хотя, должно было бы быть наоборот, т.к. этот тип занимает в 2 раза больше. Но результаты теста опровергают догадки. Выходит, что выгоднее обрабатывать JSON, который хранится в unicode формате. С чем это связано, мне пока не ясно. Ну и, что особенно радует, извлечение данных из JSON происходит от 2-3 до почти 10 раз быстрее, чем из XML. Поэтому можно смело рекомендовать использовать JSON вместо XML там, где это возможно.

SQL Server 2016: JSON, часть 5, хранение и индексирование

Продолжаем серию статей про поддержку JSON в SQL Server 2016. В этой части мы рассмотрим, как можно хранить и индексировать JSON объекты в базе данных.

Как я уже раньше упоминал, в SQL Server 2016 не был добавлен отдельный тип для JSON, в отличие от XML. Поэтому для его хранения вы можете использовать любые строковые типы данных. Лучше всего, пожалуй, подойдут varchar(max) и nvarchar(max) в зависимости от того, есть ли у вас Unicode символы в JSON документах или нет. Однако, вы можете использовать типы данных с меньше длиной, если вы точно уверены, что ваши JSON объекты не выйдут по размеру за их пределы.

Если кто-то работал с XML, то помнит, что для этого формата в SQL Server существует несколько типов индексов, позволяющих ускорить определенные выборки. Для строковых же типов, в которых предполагается хранение JSON таких индексов просто не существует. Тут нам на помощь приходят вычисляемые столбцы, которые могут представлять из себя определенные свойства из JSON документов, по которым мы хотим производить поиск, а индексы создать уже на этих столбцах.

use test;
go

drop table if exists dbo.test_table;
go

create table dbo.test_table (
	id int not null,
	json_data varchar(max) null,

	constraint pk_test_table primary key clustered (id)
);
go

insert into dbo.test_table (
	id,
	json_data
)
values
	(1, '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"]}'),
	(2, '{"first name":"John","last name":"Smith","sex":"m","skills":["SQL Server 2014","In-Memory OLTP"]}'),
	(3, '{"first name":"Mary","last name":"Brown","age":25,"skills":["SQL Server 2016","In-Memory OLTP"]}');
go

alter table dbo.test_table
add v_age as json_value(json_data, '$.age') persisted;
go

alter table dbo.test_table
add v_skills as json_query(json_data, '$.skills') persisted;
go


create nonclustered index ix_nc_test_table_v_age on [dbo].[test_table] (v_age);
go

create fulltext catalog [jsonFullTextCatalog] with accent_sensitivity = on authorization [dbo];
go

create fulltext index on [dbo].[test_table] (v_skills)
key index pk_test_table ON jsonFullTextCatalog;
go
select *
from [dbo].[test_table] as t
where
	t.[v_age] = 32;

select *
from [dbo].[test_table] as t
where
	contains(t.v_skills, 'OLTP');

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

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

SQL Server 2016: JSON, часть 4, модификация данных

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

Для модификации данных присутствует функция JSON_MODIFY, которой на вход передается JSON документ, пусть до нужного свойства и новое значение.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":30,"skills":["SQL Server 2014","T-SQL","JSON"]}';

set @json = json_modify(@json, 'lax$.age', json_value(@json, '$.age') + 2);
set @json = json_modify(@json, '$.skills[0]', 'SQL Server 2016');
set @json = json_modify(@json, 'append strict$.skills', 'In-Memory OLTP');

select * from openjson(@json);

Как мы видим, все достаточно просто. Дополнительно хотелось бы сразу отметить, что можно использовать ключевое слово append, которое вы уже, наверное, заметили выше, если мы хотим добавить новое значение в массив данных.

Как же можно удалить какое свойство в существующем документе? Для этого нужно присвоить ему значение null в lax режиме.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":30,"skills":["SQL Server 2014","T-SQL","JSON"]}';

select * from openjson(json_modify(@json, 'lax$.age', null));
select * from openjson(json_modify(@json, 'strict$.age', null));


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

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":30,"skills":["SQL Server 2014","T-SQL","JSON"]}';

select * from openjson(json_modify(@json, 'strict$.hobby', null));

Msg 13608, Level 16, State 2, Line 3
Property cannot be found on the specified JSON path.

Вот и все про модификацию JSON документов. В следующей статье мы рассмотрим, как можно хранить JSON объекты в базе данных и создавать на них индексы.