Return forecast results to SQL Server from R

Question

To conduct forecast, I get data from my SQL Server to R, using library sqldf.

library("RODBC")
library(sqldf)

dbHandle <- odbcDriverConnect("driver={SQL Server};server=MYSERVER;database=MYBASE;trusted_connection=true")

sql <- 
  "select
yearMon
,  new
from dbo.mytable

w <- sqlQuery(dbHandle, sql)

Now perform simple forecast

w=ts(w$new,frequency = 12,start=c(2015,1)) 

#forecast for the next months
library("forecast")
m <- stats::HoltWinters(w)
test=forecast:::forecast.HoltWinters(m,h=4) #h is how much month do you want to predict

#result of forecast

test

How can I return this forecast result back to SQL Server? There is table dbo. mytableforecast, I must insert the forecasted data into this table.

Here is a reproducible example:

w=

structure(list(yearMon = structure(c(9L, 7L, 15L, 1L, 17L, 13L, 
11L, 3L, 23L, 21L, 19L, 5L, 10L, 8L, 16L, 2L, 18L, 14L, 12L, 
4L, 24L, 22L, 20L, 6L), .Label = c("1-Apr-15", "1-Apr-16", "1-Aug-15", 
"1-Aug-16", "1-Dec-15", "1-Dec-16", "1-Feb-15", "1-Feb-16", "1-Jan-15", 
"1-Jan-16", "1-Jul-15", "1-Jul-16", "1-Jun-15", "1-Jun-16", "1-Mar-15", 
"1-Mar-16", "1-May-15", "1-May-16", "1-Nov-15", "1-Nov-16", "1-Oct-15", 
"1-Oct-16", "1-Sep-15", "1-Sep-16"), class = "factor"), new = c(8575L, 
8215L, 16399L, 16415L, 15704L, 19805L, 17484L, 18116L, 19977L, 
14439L, 9258L, 12259L, 4909L, 9539L, 8802L, 11253L, 11971L, 7838L, 
2095L, 4157L, 3910L, 1306L, 3429L, 1390L)), .Names = c("yearMon", 
"new"), class = "data.frame", row.names = c(NA, -24L))

Something like this? https://stackoverflow.com/questions/4253804/insert-new-column-into-table-in-sqlite — Roman Luštrik, May 05 '18 at 13:05
@RomanLuštrik i have read this topic and didn't find answer, taking account that i work with forecast FROM R. if you think that there is answer, can you show me on my code? This answer more for SQL)) — psysky, May 05 '18 at 13:10

score 2 · Answer 1 · answered May 05 '18 at 14:23

This is basically from this post. See comments in the code.

library(DBI)

db <- dbConnect(RSQLite::SQLite(), ":memory:")

# load iris dataset into memory sqldf
dbWriteTable(conn = db, name = "iris", value = iris)

# generate new variable values
set.seed(357)
to_add <- rnorm(nrow(iris), mean = 10, sd = 10)

# add new column into the database
dbExecute(conn = db, "ALTER TABLE iris ADD COLUMN prediction REAL")
dbGetQuery(conn = db, statement = "SELECT * FROM iris", n = 6)

  Sepal.Length Sepal.Width Petal.Length Petal.Width Species prediction
1          5.1         3.5          1.4         0.2  setosa         NA
2          4.9         3.0          1.4         0.2  setosa         NA
3          4.7         3.2          1.3         0.2  setosa         NA
4          4.6         3.1          1.5         0.2  setosa         NA
5          5.0         3.6          1.4         0.2  setosa         NA
6          5.4         3.9          1.7         0.4  setosa         NA

# insert values based on row id
dbExecute(conn = db, "UPDATE iris SET prediction = :to_add WHERE rowid = :id", 
          params = data.frame(to_add = to_add, id = rownames(iris)))

  Sepal.Length Sepal.Width Petal.Length Petal.Width Species prediction
1          5.1         3.5          1.4         0.2  setosa  -2.411173
2          4.9         3.0          1.4         0.2  setosa   4.167950
3          4.7         3.2          1.3         0.2  setosa  13.947471
4          4.6         3.1          1.5         0.2  setosa  25.042111
5          5.0         3.6          1.4         0.2  setosa  17.667997
6          5.4         3.9          1.7         0.4  setosa  13.174604

Alternatively you could fetch the table, append the prediction in R and then drop the table back into the database.

R Yoda · Answer 2 · 2018-05-05T15:07:25.403

Given your code and the fact that I have no DB at hand ATM to test my code your code should look like this:

# install.packages("forecast")
library(RODBC)
library(forecast)

w <-  structure(list(yearMon = structure(c(9L, 7L, 15L, 1L, 17L, 13L, 
         11L, 3L, 23L, 21L, 19L, 5L, 10L, 8L, 16L, 2L, 18L, 14L, 12L, 
         4L, 24L, 22L, 20L, 6L),
         .Label = c("1-Apr-15", "1-Apr-16", "1-Aug-15", 
         "1-Aug-16", "1-Dec-15", "1-Dec-16", "1-Feb-15", "1-Feb-16", "1-Jan-15", 
         "1-Jan-16", "1-Jul-15", "1-Jul-16", "1-Jun-15", "1-Jun-16", "1-Mar-15", 
         "1-Mar-16", "1-May-15", "1-May-16", "1-Nov-15", "1-Nov-16", "1-Oct-15", 
         "1-Oct-16", "1-Sep-15", "1-Sep-16"), class = "factor"),
         new = c(8575L, 8215L, 16399L, 16415L, 15704L, 19805L, 17484L, 18116L, 19977L, 
         14439L, 9258L, 12259L, 4909L, 9539L, 8802L, 11253L, 11971L, 7838L, 
         2095L, 4157L, 3910L, 1306L, 3429L, 1390L)), .Names = c("yearMon", 
         "new"), class = "data.frame", row.names = c(NA, -24L))


dbHandle <- odbcDriverConnect("driver={SQL Server};server=MYSERVER;database=MYBASE;trusted_connection=true")

# we already have w in this code example
# w <- sqlQuery(dbHandle, ""select yearMon, new from dbo.mytable")

w    <- ts(w$new,frequency = 12,start=c(2015,1)) 

#forecast for the next months
m    <- stats::HoltWinters(w)
test <- forecast:::forecast.HoltWinters(m,h=4) #h is how much month do you want to predict

sqlSave(dbHandle, as.data.frame(test), "dbo.mytableforecast", verbose = TRUE)  # use "append = TRUE" to add rows to an existing table

odbcClose(dbHandle)

No need for the package sqlDF - all DB functions are from RODBC

PS: If have do have large forecast results the package odbc (as illustrated in the answer of Roman Luštrik) is the right way to go for decent performance (see https://github.com/r-dbi/odbc).

Return forecast results to SQL Server from R

2 Answers2