Бөлісу құралы:

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

Область применения: SQL Server 2016 (13.x) и более поздних версий

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

Необходимые компоненты

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

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

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

  1. Создайте первую диаграмму с помощью функции 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=""))

  2. Изображение возвращается в графическом устройстве R вашей среды разработки. Например, в RStudio откройте окно Plot (График). В Инструменты R для Visual Studio открывается отдельное окно графики.

    Построение графика тарифов с использованием функции rxHistogram

    Примечание.

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

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

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

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

  1. Определите функцию, создающую объект графика 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 позволяет осуществлять выполнение в контексте удаленных вычислений.

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

    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 , создает карту с указанными координатами в центре.

  3. Перейдите в контекст вычислений 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.

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

    Построение графика поездок на такси с помощью пользовательской функции R

Следующие шаги

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

  • Last updated on