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

SQL Server 2016: STRING_SPLIT, описание и сравнение производительности

Свершилось! Наконец-то в SQL Server 2016 появляется встроенная функция для разбивки строк. Пожалуй, это одна из самых распространённых функций, которые, пожалуй, присутствуют на каждом SQL Server, но до текущей версии были реализованы своими силами. Это та функция, для которой существует множество самых различных реализаций, и, зачастую, не самых быстрых. Давайте поближе взглянем на возможности функции STRING_SPLIT, ее ограничения и сравним ее по скорости с самыми распространенными решениями.

Функция принимает всего 2 параметра: собственно, саму строку, которую необходимо разбить и разделитель. Существует одно очень важное и не очень понятное ограничение, разделитель может быть только длиной один символ. Поэтому, если вам требуется разбить строку по большему разделителю, то эта функция для вас не подойдет, и вам нужно искать другое решение.

На текущий момент одним из самых быстрых решений по разбивки строк считается CLR функция, написанная Adam Machanic (ссылка на статью). Также я решил привести пару других примеров функции, реализация в лоб, а также через inline функцию с соединением с таблицей чисел. Последний из этих вариантов на строках небольшой длины обычно показывает неплохие результаты по сравнению с реализацией в лоб. Ну и сравним скорость со стандартной функцией, входящей в SQL Server 2016. Для начала подготовим таблицу с числами и добавим 2 вышеуказанные функции. Как скомпилировать и добавить CLR функцию, я не привожу, т.к. надеюсь, каждый сможет это сделать самостоятельно.

use test;
go
 
if object_id('dbo.num', 'U') is not null
	drop table dbo.num;
go
 
create table dbo.num (
	n int primary key clustered
);
go
 
insert into dbo.num(n)
select top (1000000) row_number() over (order by (select 1)) as rn
from sys.all_columns as t1
cross apply sys.all_columns as t2
go
 
if object_id('dbo.fn_split', N'TF') is not null
	drop function dbo.fn_split;
go
 
create function dbo.fn_split
(
    @str nvarchar(max),
	@del nchar(1)
)
returns @returntable table 
(
	st nvarchar(4000)
)
as
begin
 
	while charindex(@del, @str) > 0
	begin
		insert into @returntable select substring(@str, 1, charindex(@del, @str) - 1)
		set @str = substring(@str, charindex(@del, @str) + 1, len(@str))
	end
	if @str <> ''
		insert into @returntable values(@str)
 
	return
end
go
 
if object_id('dbo.fn_split2', 'if') is not null
	drop function dbo.fn_split2;
go
 
create function dbo.fn_split2 (
	@str nvarchar(max),
	@del nchar(1) = ','
) returns table
as
return
	select
		substring(@str, n, case when charindex(@del, @str, n) = 0 then len(@str) else charindex(@del, @str, n) - n end) as st
	from dbo.num
	where
		n <= len(@str)
		and (n = 1 or substring(@str, n - 1, 1) = @del)
go

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

set statistics time on
 
declare @str varchar(max) = (select top (20000) t1.name + ',' as 'text()'
from sys.all_columns as t1
cross apply sys.all_columns as t2
for xml path(''))
 
set @str = substring(@str, 1, len(@str) - 1)
 
select *
from dbo.split_string_clr(@str, ',');
 
select *
from dbo.fn_split(@str, ',') as f;
 
select *
from dbo.fn_split2(@str, ',') as f;
 
select *
from string_split(@str, ',');

Ну и приведу один из полученных результатов.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 113 ms.

SQL Server Execution Times:
CPU time = 13468 ms, elapsed time = 13759 ms.

SQL Server Execution Times:
CPU time = 143875 ms, elapsed time = 143989 ms.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 216 ms.

Как мы видим, CLR функция и STRING_SPLIT оставляют далеко позади остальные решения, а вот выбрать, кто из них быстрее становится проблематично, т.к. их время запуска практически не отличается и варьируется от теста к тесту. В данном случае расходы на CPU одинаковые, а время выполнения медленнее. Однако, в каких-то запусках, время выполнения становится сравнимым, поэтому я не берусь отдать пальму первенства ни одной из них. Это действительно круто, что наконец-то появляется встроенное решение с превосходной производительностью. Но, если вам потребуется использовать разделитель больше чем в один символ, то рекомендую пользоваться CLR решением.

SQLSaturday в Краснодаре 4 июня 2016 г.

sqlsat503_webОсталось чуть меньше двух недель до, пожалуй, главного события на юге России, посвященного SQL Server – это, уже ставшая традиционной, конференция SQLSaturday. Если честно, я уже сбился со счета, какой год подряд она уже проходит в Краснодаре, но факт, что уже точно больше, чем в Москве.

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

Официальный сайт конференции доступен по следующей ссылке. Там же вы сможете зарегистрироваться. Ознакомиться с полным списком докладов и докладчиков вы можете на этой странице. Докладов немного, но они достаточно жесткие и интересные, на мой взгляд.

Сам я выступлю с двумя докладами: по языку R в SQL Server 2016, а также с новым, улучшенным докладом про нетрадиционные и провокационные методы оптимизации запросов. Надо отметить, что этот доклад я в ближайшем будущем больше нигде читать не планирую.

Буду рад всех увидеть на своих докладах в Краснодаре. Удачи вам и приятного посещения конференции!

SQL Server 2016: JSON, часть 3, преобразование в реляционные данные

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

Сразу стоит отметить, что OPENJSON будет работать только в базах данных с уровнем совместимости 130.

Существует 2 режима работы функции OPENSON. Самый простой способ – это без указания схемы для результирующей выборки.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}}';
 
select * from openjson(@json);

В данном случае результат будет представлен в виде трех столбцов: key, value и type. Соответственно в первом будет выведено название свойства, во втором его значение, а в последнем – тип. Т.к. JSON в приведенном выше примере иерархический, да еще и с массивом для одного из свойств, то функция OPENJSON просто вытащила все свойства с первого уровня и вывела их в виде списка. Мы также можем вытащить отдельно все элементы массива или же свойства с нужного нам уровня документа указав необходимый путь.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}}';
 
select * from openjson(@json, '$.skills');
select * from openjson(@json, 'strict$."additional info"');

Данные в столбце type могут принимать следующие значения.

Значение Тип данных JSON
0 null
1 string
2 int
3 true/false
4 array
5 object

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

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}}';
 
select *
from openjson(@json)
with (
	fisrt_name varchar(100) '$."first name"',
	last_name varchar(100) '$."last name"',
	age tinyint '$.age',
	skill1 varchar(50) '$.skills[0]',
	skill2 varchar(50) '$.skills[1]',
	data1 varchar(50) '$."additional info".data1'
);

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

declare @json varchar(max) = '[
	{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}},
	{"first name":"John","last name":"Smith","age":18,"skills":["SQL Server 2014","In-Memory OLTP"],"additional info":{"data2":4}}
]';
 
select *
from openjson(@json)
with (
	fisrt_name varchar(100) '$."first name"',
	last_name varchar(100) '$."last name"',
	age tinyint '$.age',
	skill1 varchar(50) '$.skills[0]',
	skill2 varchar(50) '$.skills[1]',
	data1 varchar(50) '$."additional info".data1'
);

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

SQL Server 2016: JSON, часть 2, формирование данных

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

Самый простой вариант – это использовать FOR JSON AUTO. В этом случае будет сгенерирован массив JSON объектов, где каждая строка в выборке будет отдельным объектом, а колонка свойством.

use tempdb;
go
 
drop table if exists dbo.test_table;
go
 
create table dbo.test_table (
	id int not null,
	name varchar(100) null,
	dt datetime null
);
go
 
insert into dbo.test_table (id, name, dt)
values
	(1, 'qwe', '19000101'),
	(2, 'asd', null),
	(3, null, '20000101');
go
 
select id, name, dt
from dbo.test_table
for json auto;
go

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}]

Как мы видим, null значения игнорируются. Если мы хотим их включать в JSON, то можем воспользоваться дополнительной опцией INCLUDE_NULL_VALUES.

select id, name, dt
from dbo.test_table
for json auto, include_null_values;
go

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”,”dt”:null},

{“id”:3,”name”:null,”dt”:”2000-01-01T00:00:00″}]

Опция WITHOUT_ARRAY_WRAPPER поможет, если мы хотим вывести JSON без квадратных скобок.

select id, name, dt
from dbo.test_table
for json auto, without_array_wrapper;
go

{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}

Если же мы хотим объединить результаты с корневым элементом, то необходимо воспользоваться опцией ROOT и передать ей нужное название.

select id, name, dt
from dbo.test_table
for json auto, root('root');
go

{“root”:

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}]

}

Ну и наконец, если мы хотим создать JSON с более сложной структурой, самим присвоить нужные название свойствам, сгруппировать их, то необходимо использовать выражение FOR JSON PATH. Я рекомендую обратиться к первой части цикла статей про JSON, чтобы узнать поподробнее про path выражения для JSON. Ниже я приведу просто пример запроса, который будет генерировать JSON с более сложной структурой.

select
	id,
	name as 'data.full_name',
	dt as 'data.add date'
from dbo.test_table
for json path;
go

[{“id”:1,”data”:{“full_name”:”qwe”,”add date”:”1900-01-01T00:00:00″}},

{“id”:2,”data”:{“full_name”:”asd”}},

{“id”:3,”data”:{“add date”:”2000-01-01T00:00:00″}}]

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

Виртуальный Форум – «Данные. Технологии. SQL Server 2016»

Компания Майкрософт организует Виртуальный Форум Microsoft «Данные. Технологии. SQL Server 2016», который состоится 8 июня 2016 года. Форум приурочен к выходу новой версии платформы управления данными Microsoft на базе SQL Server 2016.

Успех цифровой трансформации бизнеса сегодня напрямую зависит от способности организации использовать передовые технологии и инструменты по работе с данными, которые становятся важнейшим ресурсом развития бизнеса – сравнимым, пожалуй, с ролью электричества в эпоху трансформации XIX века. В рамках форума мы обсудим, какие требования сегодня предъявляются к современным платформам по работе с данными, какие ключевые технологии must have в арсенале любой компании.

Основная программа форума состоит из трех паралельных технологических сесссий:

  • SQL Server 2016: новые стандарты в мире транзакции;
  • Бизнес-aналитика: SQL, Power BI, R, Mobile;
  • Azure: новое поколение решений для аналитики, Big Data & IoT.

Подробная программа доступна на сайте мероприятия.

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

И это еще не все — увидеть технологии в действии Вы сможете в течение дня, приняв участие в тест-драйве платформы с помощью онлайн лабораторных работ под руководством тренера.

Участие в форуме бесплатное. Необходима предварительная регистрация.