Contents

The Mysterious Black Box of R - For the SQL Server Guy

Took a class from Jamey Johnston @ SQLSaturday #516 in Houston. Lots of great information covered. Follow him for a much more detailed perspective on R. Jamey Johnston on Twitter @StatCowboy. Did a basic walkthrough of running an R query, and figured I’d share it as it had been a mysterious black box before this. Thanks to Jamey for inspiring me to look at the mysterious magic that is R….

Setup to Run Query

Simple-Talk: Making Data Analytics Simpler SQL Server and R This provided the core code I needed to start the process with R, recommend reading the walkthrough for details. To get started in connecting in RStudio to SQL Server run this command in the RStudio console.

1
install.packages("RODBC")

Verify the library is installed by running from the console

1
library()

Running Select from View

This was run against StackOverflow database

1
2
3
library(RODBC)
startTime1
<- Sys.time() cn <- odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=localhost;database=StackOverflow;trusted_connection=yes;") dataComment <- sqlFetch(cn, 'vw_testcomments', colnames=FALSE,rows_at_time=1000) View(dataComment) endTime1 <- Sys.time() odbcClose(cn) timeRun <- difftime(endTime1,startTime1,units="secs") print(timeRun)

I created a simple view to select from the large 15GB comments table with top(1000)

1
2
3
4
5
6
7
8
9
USE [StackOverflow]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    CREATE view [dbo].[vw_testcomments] as
    select top(10000) * from dbo.Comments as C
GO

https://d33wubrfki0l68.cloudfront.net/445a233ef8606bc43e6921e76374b8827e25247c/88e17/images/running-select-from-view.png

viewing the results of basic query in r studio

https://d33wubrfki0l68.cloudfront.net/404f164992ad3c6f15ac5d39209a07063c64c757/1a613/images/viewing-the-results-of-basic-query-in-r-studio.png

running R script in PowerBi

https://d33wubrfki0l68.cloudfront.net/4976fd86f8d605937e2be699d728a28eff20c7ef/8442f/images/running-r-script-in-powerbi.png

execute r script

https://d33wubrfki0l68.cloudfront.net/875cd7dfeeed766b1fa1a8760151d4c66fa63353/3ab21/images/execute-r-script.png

results preview

https://d33wubrfki0l68.cloudfront.net/2a29b93352210eed86cfdd0c439ba76372c16e20/9728e/images/results-preview.png

Visualized in Power Bi

https://d33wubrfki0l68.cloudfront.net/9e747cf4870f99d600abee2fdd349ab3e00159c5/68633/images/visualized-in-power-bi.png