Создание диаграмм и графиков с использованием SQL и R (пошаговое руководство)

Статья
05/30/2023

Применимо к: SQL Server 2016 (13.x) и более поздних версий

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

Предварительные условия

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

Rgui.exe для выполнения команд R
Management Studio для выполнения T-SQL
googMap
Пакет ggmap
Пакет mapproj

Создание гистограммы

Создайте первую диаграмму с помощью функции rxHistogram. Функция rxHistogram предоставляет возможности, аналогичные возможностям пакетов R с открытым исходным кодом, однако она может работать в контексте удаленного выполнения.
```
# Plot fare amount on SQL Server and return the plot
start.time <- proc.time()
rxHistogram(~fare_amount, data = inDataSource, title = "Fare Amount Histogram")
used.time <- proc.time() - start.time
print(paste("It takes CPU Time=", round(used.time[1]+used.time[2],2), " seconds, Elapsed Time=", round(used.time[3],2), " seconds to generate plot.", sep=""))
```
Изображение возвращается в графическом устройстве R вашей среды разработки. Например, в RStudio откройте окно Plot (График). В cредства R для Visual Studioоткрывается отдельное графическое окно.

Примечание

Ваш график выглядит иначе?

Это связано с тем, что inDataSource использует только первые 1000 строк. Поскольку упорядочение строк с помощью предложения TOP является недетерминированным без предложения ORDER BY, данные и полученный на их основе график могут отличаться. Конкретно это изображение было создано с использованием приблизительно 10 000 строк данных. Мы рекомендуем поэкспериментировать с разным числом строк, чтобы получить разные графики, и обратить внимание на то, как долго возвращаются результаты в вашей среде.

Создание диаграммы-карты

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

Определите функцию, создающую объект графика R. Пользовательская функция mapPlot создает точечную диаграмму, на которой представлено количество поездок с каждого места посадки. Она использует пакеты ggplot2 и ggmap, которые уже должны быть установлены и загружены.
```
mapPlot <- function(inDataSource, googMap){
    library(ggmap)
    library(mapproj)
    ds <- rxImport(inDataSource)
    p <- ggmap(googMap)+
    geom_point(aes(x = pickup_longitude, y =pickup_latitude ), data=ds, alpha =.5,
color="darkred", size = 1.5)
    return(list(myplot=p))
}
```
- Функция mapPlot принимает два аргумента: имеющийся объект данных, который вы определили ранее с помощью функции RxSqlServerData, и представление карты, переданное из клиента.
- В строке, начинающейся с переменной ds, функция rxImport используется для загрузки в память данных из ранее созданного источника inDataSource. (Этот источник содержит только 1000 строк; если вы хотите создать карту с использованием большего количества точек данных, выберите другой источник.)
- При использовании функций R с открытым исходным кодом данные должны быть загружены в кадры данных в локальной памяти. Тем не менее, вызов функции rxImport позволяет осуществлять выполнение в контексте удаленных вычислений.
Измените контекст вычислений на локальный и загрузите библиотеки, необходимые для создания карт.
```
rxSetComputeContext("local")
library(ggmap)
library(mapproj)
gc <- geocode("Times Square", source = "google")
googMap <- get_googlemap(center = as.numeric(gc), zoom = 12, maptype = 'roadmap', color = 'color');
```
- В переменной gc хранится набор координат площади Таймс-сквер в Нью-Йорке.
- Строка, начинающаяся с googmap , создает карту с указанными координатами в центре.
Перейдите в контекст вычислений SQL Server и преобразуйте результаты для просмотра. Для этого необходимо упаковать функцию построения графика в rxExec, как показано ниже. Функция rxExec входит в состав пакета RevoScaleR и поддерживает выполнение произвольных функций R в удаленном контексте вычислений.
```
rxSetComputeContext(sqlcc)
myplots <- rxExec(mapPlot, inDataSource, googMap, timesToRun = 1)
plot(myplots[[1]][["myplot"]]);
```
- Данные карты в googMap передаются в виде аргумента в функцию mapPlot, выполняемую удаленно. Это связано с тем, что карты были созданы в локальной среде и их необходимо передать в функцию, чтобы создать график в контексте SQL Server.
- При выполнении строки, начинающейся с plot, преобразованные для просмотра данные сериализуются в локальную среду R, чтобы вы могли просматривать их в своем клиенте R.
Примечание

Если вы используете SQL Server на виртуальной машине Azure, на этом этапе может появиться ошибка. Она возникает в том случае, если заданное по умолчанию в Azure правило брандмауэра блокирует доступ кода R к сети. Дополнительные сведения об устранении этой ошибки см. в статье Установка служб машинного обучения (R) на виртуальной машине Azure.
На следующем рисунке показана итоговая диаграмма. Места посадки обозначены на карте красными точками. Изображение может отличаться в зависимости от того, сколько мест содержится в вашем источнике данных.

Дальнейшие шаги

Создание характеристик данных с помощью R и SQL

Share via