Save Json example

library(RODBC)
library(jsonlite)
dbconn<-odbcDriverConnect(“driver={SQL Server};server=localhost\\AMT2016;database=Test;trusted_connection=true”)
dt2 <- fromJSON(“https://api.iextrading.com/1.0/stock/chk/chart/date/20181218”)
sqlSave(dbconn, dt2, “TIntra5”, rownames =NULL, append= TRUE )

 


library(RODBC)
DBHANDLE<-odbcDriverConnect(‘driver={SQL Server};server=localhost\\AMT2016;database=Adventureworks2016;trusted_connection=true’)

fileds<-sqlQuery(DBHANDLE,’SELECT ProductID,LineTotal FROM [AdventureWorks2016].[Sales].[SalesOrderDetail]’)
View(fileds)
library(ggplot2)
ggplot(fileds,aes(x=fileds$ProductID,y=fileds$LineTotal,color=factor(fileds$ProductID)))+geom_boxplot()+facet_grid(fileds$ProductID~.)
EXEC sp_execute_external_script
@language = N’R’
,@script = N’ df <- inputDataSet; #read input data
image_file = tempfile(); #create a temporary file
jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
hist(df$Ages); #plot the histogram
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 Server. 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’SELECT Ages = DATEDIFF(MM,[ModifiedDate],GETDATE()) FROM [AdventureWorks2016].Sales.Customer;’
,@input_data_1_name = N’inputDataSet’
,@output_data_1_name = N’OutputDataset’
WITH RESULT SETS ((plot varbinary(max)));

inputDataSet<-sqlQuery(DBHANDLE,’SELECT Ages = DATEDIFF(MM,[ModifiedDate],GETDATE()) FROM [AdventureWorks2016].Sales.Customer’)
df <- inputDataSet; #read input data
image_file = tempfile(); #create a temporary file
jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
hist(df$Ages); #plot the histogram
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 Server. The result is written to the OutputDataset variable.
OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));
#housing example
library(plotly)
g <- ggplot(txhousing, aes(x = date, y = sales, group = city)) +
geom_line(alpha = 0.4)
ggplotly(g, tooltip = c(“city”))
library(ggplot2); #import ggplot2 library
qplot(Sepal.Length, Petal.Length, data = iris, color = Species,
xlab = “Sepal Length”, ylab = “Petal Length”,
main = “Sepal vs Petal Length in Fisher”s Iris data”);

Leave a Reply

Your email address will not be published. Required fields are marked *