Category Archives: Uncategorized

Adding images from R to SQL Server

INSERT INTO dbo.Plots(plot)
EXEC   sp_execute_external_script
@language = N’R’
,@script = N’
library(ggplot2); #import ggplot2 library
image_file = tempfile(); #create a temporary file
jpeg(filename = image_file, width=600, height=800); #create a JPEG graphic device
#qplot = quick plot, a helper function of ggplot2
qplot(Sepal.Length, Petal.Length, data = iris, color = Species,
xlab = “Sepal Length”, ylab = “Petal Length”,
main = “Sepal vs Petal Length in Fishers Iris data”);
dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
#file() opens a file, in this case the image. rb = read binary
#readBin() reads binary data. what = described the mode of the data. In this case, it”s raw data. n = maximum number of records to read.
#data.frame converts the data to a data frame, which is required as output by SQL 1Server. The result is written to the OutputDataset variable.
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));

,@input_data_1 = N”
,@output_data_1_name = N’OutputDataset’;

INSERT INTO dbo.Plots(plot)
EXEC   sp_execute_external_script
@language = N’R’
,@script = N’
library(plotly); #import ggplot2 library
image_file = tempfile(); #create a temporary file
jpeg(filename = image_file, width=600, height=800); #create a JPEG graphic device
g <- ggplot(txhousing, aes(x = date, y = sales, group = city)) +
geom_line(alpha = 0.4)
ggplotly(g, tooltip = c(“city”))
dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));

,@input_data_1 = N”
,@output_data_1_name = N’OutputDataset’;

INSERT INTO dbo.Plots(plot)
EXEC   sp_execute_external_script
@language = N’R’
,@script = N’
library(ggplot2)
g <- ggplot(txhousing, aes(x = date, y = sales, group = city)) +
geom_line(alpha = 0.4)
g
dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));

,@input_data_1 = N”
,@output_data_1_name = N’OutputDataset’;

library(plotly)
g <- ggplot(txhousing, aes(x = date, y = sales, group = city)) +
geom_line(alpha = 0.4)
ggplotly(g, tooltip = c(“city”))

library(ggplot2)
g <- ggplot(txhousing, aes(x = date, y = sales, group = city)) +
geom_line(alpha = 0.4)
g

— truncate table  dbo.Plots

select * From  dbo.Plots

Using R graph in SSRS

example how to use:

https://www.mssqltips.com/sqlservertip/4127/sql-server-2016-r-services-display-r-plots-in-reporting-services/

interact R plot examples:

http://www.rebeccabarter.com/blog/2017-04-20-interactive/

 

Install R service in database:

 

Enable R:

EXEC sp_configure  ‘external scripts enabled’, 1

RECONFIGURE WITH OVERRIDE

install R package from TSQL

https://www.accelebrate.com/library/tutorials/r-add-new-packages

 

install R from command line for SQL SERVER

http://www.sqlservercentral.com/articles/R+Package/145571/

 

 

Ggplot show trend line in linear model

library(plotly)

airquality_sept <- airquality[which(airquality$Month == 9),] airquality_sept$Date <- as.Date(paste(airquality_sept$Month, airquality_sept$Day, 1973, sep = "."), format = "%m.%d.%Y") View(airquality_sept) ggplot(airquality_sept, aes(x = Date, y = Wind)) + geom_bar(stat = "identity") + geom_smooth(method = "lm")+ theme(axis.text.x = element_text(angle = 45, vjust = 0.5))

R how to show all x-axis labels

############## showing all x axis labels ###############

ID <- 1:50 A <- runif(50,1,100) df <- data.frame(ID,A) ggplot(df, aes(x = ID, y = A)) + geom_point() + theme(axis.text.x = element_text(angle = 90, vjust = 0.5)) + scale_x_continuous("ID", labels = as.character(ID), breaks = ID)

Plotly Charts Examples

library(plotly)

airquality_sept <- airquality[which(airquality$Month == 9),] airquality_sept$Date <- as.Date(paste(airquality_sept$Month, airquality_sept$Day, 1973, sep = "."), format = "%m.%d.%Y") View(airquality_sept) plot_ly(airquality_sept) %>%
add_trace(x = ~Date, y = ~Wind, type = ‘bar’, name = ‘Wind’,
marker = list(color = ‘#C9EFF9’),
hoverinfo = “text”,
text = ~paste(Wind, ‘ mph’)) %>%
add_trace(x = ~Date, y = ~Temp, type = ‘scatter’, mode = ‘lines’, name = ‘Temperature’, yaxis = ‘y2’,
line = list(color = ‘#45171D’),
hoverinfo = “text”,
text = ~paste(Temp, ‘°F’)) %>%
layout(title = ‘New York Wind and Temperature Measurements for September 1973’,
xaxis = list(title = “”),
yaxis = list(side = ‘left’, title = ‘Wind in mph’, showgrid = FALSE, zeroline = FALSE),
yaxis2 = list(side = ‘right’, overlaying = “y”, title = ‘Temperature in degrees F’, showgrid = FALSE, zeroline = FALSE))

plot_ly(airquality_sept) %>%
add_trace(x = ~Date, y = ~Wind, type = ‘bar’, name = ‘Wind’,
marker = list(color = ‘#C9EFF9’),
hoverinfo = “text”,
text = ~paste(Wind, ‘ mph’)) %>%
layout(title = ‘New York Wind and Temperature Measurements for September 1973’,
xaxis = list(title = “”),
yaxis = list(side = ‘left’, title = ‘Wind in mph’, showgrid = TRUE, zeroline = FALSE),
yaxis2 = list(side = ‘right’, overlaying = “y”, title = ‘Temperature in degrees F’, showgrid = FALSE, zeroline = FALSE))

# Create a shareable link to your chart
# Set up API credentials: https://plot.ly/r/getting-started
chart_link = api_create(p, filename=”multiple-bar_line”)

R trend line, style, other tips

#adding smooth trend linear-trend-line-to-a-scatter-plot-in-r

https://stats.stackexchange.com/questions/30975/how-to-add-non-linear-trend-line-to-a-scatter-plot-in-r

## add moving average in

https://www.quantmod.com/documentation/addMA.html

#running average
https://stackoverflow.com/questions/40857319/plot-running-average-in-ggplot2

## different chart type

https://plot.ly/r/graphing-multiple-chart-types/

## integrate with sql server

Using R in SQL Server Reporting Services (SSRS)