Author Archives: Sergey Olontsev

About Sergey Olontsev

Microsoft SQL Server DBA & DBD

Build 2019: Cosmos DB с поддержкой Spark API для AI и аналитики

Компания Майкрософт на только что прошедшей конференции Build 2019 анонсировала поддержку Apache Spark в Cosmos DB. Cosmos DB изначально проектировалась для точечных операций. Существовал также Spark Connector для доступа к этим данным из внешнего Spark кластера. Но теперь интеграция становится более тесной, и будет возможно запускать real-time аналитику и ML\AI над данными Cosmos DB.

Больше не придется управлять отдельными кластерами Apache Spark, но при этом получить SLA на уровне 99.999. Также, несмотря на то, что Cosmos DB это географически распределенная БД, обработка с помощью Spark будет запускаться на локальных данных в том регионе, где они находятся, что ускоряет получение результата и исключает ненужные перемещения данных.

Дополнительно в Data Explorer появится поддержка Jupiter Notebook – одного из самых распространенных средств для доступа и анализа данных.

A Jupyter notebook.

Несомненно – это огромный шаг в развитии Cosmos DB. Благодаря тому, что теперь можно в одной БД запускать и операционную и аналитическую нагрузку – повышает привлекательность и возможности для реализации различных сценариев. Сейчас доступ в этой опции открыт не всем, но как только я получу его – сделаю отдельный обзор этой возможности.

Краткое введение в Azure Cosmos DB

Azure Cosmos DB – это полностью облачная база данных, которая была представлена в мае 2017 года, т.е. ей всего 2 года, но она уже успела приобрести достаточную популярность, если ориентироваться на статистику DB-engines, в частности, в категориях key-value, document и graph. Давайте кратко рассмотрим основные отличительные особенности этой базы данных, а уже в последующих статьях взглянем на них более подробно.

В первую очередь стоит отметить, что Cosmos DB – это развитие другой облачной базы данных компании Майкрософт – DocumentDB, которая появилась на пару лет раньше. Изначально DocumentDB – это была документоориентированная БД, где вся информация хранилась в виде JSON документов. CosmosDB унаследовала это от своего прародителя, но обросла новыми возможностями, поэтому было неправильно оставлять старое название, и было придумано новое и громкое.

Большинство систем управления базами данных организованы вокруг единой модели данных, которая определяет, как данные могут быть организованы, сохранены и обработаны. Например, реляционная модель, которая представляет данные в виде таблиц и связей между ними, является, пожалуй, самой широко распространенной. Cosmos DB – представитель мультимодельных систем, когда вы можете в одной базе данных использовать разные подходы, выбирая тот, который больше подходит для вашего приложения или сервиса. В частности, помимо унаследованной документоориентированной модели, которая является основной, вы можете использовать key-value и графовую модель. Я думаю, в отдельных статьях я покажу, как можно использовать графовую модель для описания знакомых всем сущностей, и какие преимущества это дает по сравнению с реляционной моделью.

Cosmos DB, являсь облачной БД, позволяет вам в несколько кликов развернуть экземпляр вашей базы данных в одном из регионов, где есть дата-центр Azure. Обеспечивает уровень доступности в 99.999% на чтение и запись, позволяет прозрачно расширяться практически без ограничений и гарантирует, что запись и чтение не будут занимать более 10 миллисекунд в 99% случаев. Также вам доступна репликация данных между дата-центрами. Согласитесь, настроить и обеспечить систему с подобными характеристиками своими силами займет в разы больше времени и будет стоить сильно дороже. Конечно, если у нас очень большая БД и много ресурсов, тогда нужно считать более детально, но если у вас относительно небольшая или средняя БД, но вам нужны такие показатели, то, однозначно, вам выйдет сильно дороже обеспечить их самостоятельно, чем с помощью Cosmos DB.

Вы можете работать с Cosmos DB используя Java, .NET, Node.Js или Python. Еще больше возможностей открывается, если использовать Azure Functions в связке с Cosmos DB. На этом мы тоже более подробно остановимся в следующих статьях цикла.

Что еще стоит отметить:

  • Возможность из приложения управлять уровнями согласованности (consistency level)
  • Автоматическое резервное копирование
  • Разграничение прав доступа к данным
  • Возможность создавать триггеры, хранимые процедуры и пользовательские функции внутри БД
  • Поддержка языка SQL
  • Гибкое управление индексированием
  • Поддержка MongoDB API и Cassandra API
  • Наличие локального эмулятора для разработки приложений без доступа к облаку Azure

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

Мир активно развивается и меняется, облачные технологии получают все большую и большую популярность, в частности облачные базы данных. И Cosmos DB является одним из ярких представителей этого класса, на который однозначно стоит хотя бы взглянуть и попробовать. А уже потом решить, стоит ли использовать эту БД в одном из своих проектов.

Рейтинг баз данных

Мы верим рейтингам только тогда, когда видим свое имя на самом верху.

Боб Хоуп

Люди очень любят рейтинги. Мы уже давно привыкли видеть их в обычной жизни, например: музыкальные чарты, список самых богатых людей, самых дорогих брендов, самых надежных автомобилей, различные спортивные рекорды и т.п. Эту последовательность можно продолжать практически бесконечно. Рейтинги, в какой-то мере, помогают участвующим в них определить, насколько они хороши относительно конкурентов, а пользователям помогают выбирать лучшие и качественные продукты.

Рейтинги не смогли обойти стороной даже мир информационных технологий. Все мы слышали о рейтингах популярности языков программирования таких как TIOBE, PYPL, RedMonk также периодически публикует исследования (ссылка на данные за январь 2019) и т.п. Очень популярны квадранты Gartner по различным технологиям, но эти больше говорят о вендорах. А для баз данных, на мой взгляд, очень хороший рейтинг публикуется на сайте DB-engines. Давайте поговорим о нем чуть поподробнее.

Чем он нам может быть полезен? Для начала определить, какие СУБД сейчас наиболее популярны, чтобы помочь с выбором системы для нового проекта. Также можно отслеживать тренды на рынке хранения и обработки данных, чтобы понимать, сохраняет ли актуальность система, с которой мы в основном работаем, либо мир начал меняться, и нам стоит обратить свое внимание на что-то еще, чтобы оставаться в тренде как специалист.

Надо не забывать, что любые рейтинги не точны на 100%, и надо научиться критически их воспринимать. Например, сейчас существует очень много разных типов СУБД: классические реляционные, key-value, документоориентированные, графовые, time series, облачные и т.п. Мы понимаем, что некорректно сравнивать между собой Redis и Oracle, т.к. они заточены для принципиально разных нагрузок, хотя и могут неплохо справляться с каким-то общим классом задач. DB-engines позволяет вам видеть не просто объединенный рейтинг популярности, но и в разрезе по типам СУБД. Для этого слева в панели вы можете фильтровать базы данных по типам.

Также щелкнув на каждую отдельную СУБД можно увидеть дополнительную информацию о ней, которую предоставил вендор: описание, ключевые возможности, поддерживаемые операционные системы, API и языки программирования. Эта информация регулярно обновляется.

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

Вы можете выбрать только интересующие вас системы, чтобы график не был перегружен.

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

  • Количество результатов в выдаче основных поисковых систем
  • Google Trends
  • Количество вопросов в Stack Overflow и DBA Stack Exchange
  • Количество вакансий на популярных ресурсах поиска работы
  • Информации с LinkedIn и Upwork
  • Twitter

И в заключение, я бы хотел отметить недостаток этого рейтинга. Сразу стоит сказать, что это только мое личное мнение, но мне кажется, этот рейтинг хорошо показывает популярность СУБД среди людей, но при этом никак не может учитывать распространенность этой СУБД. Например, мы можем использовать SQLite в своем приложении, которое будет широко распространено в мире, но при этом разработчиков этого приложения единицы. Это может быть даже какая-то своя самописная, никому не известная СУБД. В остальном же, вышеупомянутые показатели позволяют достаточно точно определить популярность той или иной системы. В любом случае, хоть какой-то рейтинг лучше, чем ничего.

SQL Server 2017: возобновляемое перестроение индексов

Перестроение индексов на критических БД зачастую может быть сложной операцией по ряду причин. Наверное, практически каждый администратор БД сталкивался с длительными блокировками по время обслуживания индексов и ростом журнала транзакций. Отчасти может помочь перестроение индексов в режиме ONLINE или же операция REORGANIZE, но в SQL Server 2017 также в помощь приходит такая возможность, как возобновляемое перестроение, которая позволяет обходить проблемы, которые до этого не решались стандартными способами.

Операция ALTER INDEX теперь может ставить перестроение на паузу или возобновлять его, что позволяет управлять процессом обслуживания индексов более гранулярно. Представьте ситуацию: вы обнаружили, что процесс перестроения большого индекса сильно нагружает диск или блокирует важные процессы, и, вместо того, чтобы его прервать, вы просто ставите его на паузу и возобновляете, когда снова появляется возможность.

Конечно, приходится чем-то жертвовать ради удобства. В данном случае в БД необходимо будет хранить обе копии индекса: старую и новую до тех пор пока операция не завершится успешно или не будет прервана. Еще одним неприятным моментом может стать то, что опция SORT_IN_TEMPDB не работает в паре с возобновляемым перестроением.

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

Итак, давайте посмотрим на примере, как это работает. Для того, чтобы инициировать процесс перестроения индекса, который затем можно будет прервать, необходимо указать опции ONLINE=ON и RESUMABLE=ON.

alter index pk_MyBigDemoTable on [dbo].[MyBigDemoTable] rebuild with (resumable = on, online = on)

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

select * from sys.index_resumable_operations

Очень удобно, что отображается, идет ли сейчас перестроение, когда началось, когда прерывалось, процент выполнения.

Во время выполнения этого перестроения в отдельной сессии вы можете управлять им с помощью следующих команд.

alter index pk_MyBigDemoTable on [dbo].[MyBigDemoTable] pause

alter index pk_MyBigDemoTable on [dbo].[MyBigDemoTable] resume

alter index pk_MyBigDemoTable on [dbo].[MyBigDemoTable] abort

PAUSE\RESUME позволяют останавливать и продолжать перестроение, в то время как команда ABORT может его полностью прервать, если вам вдруг это понадобилось.

При возобновление вы можете менять параметр MAXDOP.

alter index pk_MyBigDemoTable on [dbo].[MyBigDemoTable] resume with (maxdop = 4)

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

alter index pk_MyBigDemoTable on [dbo].[MyBigDemoTable] rebuild with (resumable = on, online = on, max_duration = 1)

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

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 User Group в Москве 28 июня 2016 г.

Очередная встреча группы пользователей SQL Server в Москве пройдет 28 июня 2016 г. в Microsoft Technology Center в 17:00, м. Белорусская, ул. Лесная, 9, reception B.

В программе будут следующие выступления.

Рукописи не горят – SQL Server Central scripts.
Первая часть мероприятия будет посвящена T-SQL и PowerShell скриптам, которые размещены на заполняемом силами читателей сайте SQLServerCentral.com. Будут рассмотрены несколько примеров скриптов и проблемы, которые авторы пытались ими решить.

Александр Сигачёв
Руководитель группы разработки хранилищ данных в “Связном”.
Microsoft Certified Solutions Expert Data Platform / Business Intelligence.
SQL Server 2016 R Services: примеры обработки данных, часть 2.
Данный доклад является продолжением серии про SQL Server 2016 R Services. В нем в деталях будут рассмотрены особенности запуска скриптов на R внутри SQL Server, существующие при этом ограничения и способы их обхода. Также будет продемонстрирована работа с большими данными, которые не могут быть обработаны в оперативной памяти.

Сергей Олонцев
Разработчик баз данных в “Лабаратории Касперского”.
Обладатель сертификата Microsoft Certified Master и награды MVP по SQL Server.

16:30 – 17:00 Сбор и регистрация участников
17:00 – 18:15 Рукописи не горят – SQL Server Central Scripts
18:15 – 18:30 Перерыв на кофе
18:30 – 20:00 SQL Server 2016 R Services: примеры обработки данных, часть 2

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

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.