I'm wondering how to save an object (models like lm, loess, etc) in a DB to
retrieve and use it afterwards, an example:
wind_ms <- abs(rnorm(24*30)*4+8)
air_kgm3 <- rnorm(24*30, 0.1)*0.1 + 1.1
wind_dg <- rnorm(24*30) * 360/7
ms <- c(0:25)
kw_mm92 <- c(0,0,0,20,94,205,391,645,979,1375,1795,2000,2040)
kw_mm92 <- c(kw_mm92, rep(2050, length(ms)-length(kw_mm92)))
modelspline <- splinefun(ms, kw_mm92)
kw <- abs(modelspline(wind_ms) - (wind_dg)*2 + (air_kgm3 - 1.15)*300 +
rnorm(length(wind_ms))*10)
#plot(wind_ms, kw)
windDat <- data.frame(kw, wind_ms, air_kgm3, wind_dg)
windDat[windDat$wind_ms < 3, 'kw'] <- 0
model <- loess(kw ~ wind_ms + air_kgm3 + wind_dg, data = windDat, enp.target
= 10*5*3) #, span = 0.1)
modX <- serialize(model, connection = NULL, ascii = T)
Channel <- odbcConnect("someSysDSN; UID=aUid; PWD=aPwd")
sqlQuery(Channel,
paste(
"INSERT INTO GRT.GeneratorsModels
([cGeneratorID]
,[tModel]
VALUES
(1,",
paste("'", gsub("'",
"''", rawToChar(modX)), "'", sep = ''),
")", sep = "") )
# Up to this it is working correctly,
# in DB I have the "modX" variable
# Problem arise retrieving data and 64kb limit:
strQ <- "
SELECT CONVERT(varchar(max), tModel) AS tModel
FROM GRT.GeneratorsModels
WHERE (cGeneratorID = 1)
"
x <- sqlQuery(Channel, strQ, stringsAsFactors = F, believeNRows = FALSE)
x <- sqlQuery(Channel, strQ, stringsAsFactors = F, believeNRows = FALSE)
#read error
Above code is working for simplier models that have a shorter representation in
variable "modX".
Any advice on how to store and retieve this kind of objects?
Thanks
Daniele
ORS Srl
Via Agostino Morando 1/3 12060 Roddi (Cn) - Italy
Tel. +39 0173 620211
Fax. +39 0173 620299 / +39 0173 433111
Web Site www.ors.it
------------------------------------------------------------------------------------------------------------------------
Qualsiasi utilizzo non autorizzato del presente messaggio e dei suoi allegati ?
vietato e potrebbe costituire reato.
Se lei avesse ricevuto erroneamente questo messaggio, Le saremmo grati se
provvedesse alla distruzione dello stesso
e degli eventuali allegati.
Opinioni, conclusioni o altre informazioni riportate nella e-mail, che non siano
relative alle attivit? e/o
alla missione aziendale di O.R.S. Srl si intendono non attribuibili alla
societ? stessa, n? la impegnano in alcun modo.
Look at the serialize function, it may accomplish what you want. -- Gregory (Greg) L. Snow Ph.D. Statistical Data Center Intermountain Healthcare greg.snow at imail.org 801.408.8111> -----Original Message----- > From: r-help-bounces at r-project.org [mailto:r-help-bounces at r- > project.org] On Behalf Of Daniele Amberti > Sent: Friday, April 02, 2010 2:37 AM > To: r-help at r-project.org; r-sig-db at stat.math.ethz.ch > Subject: [R] How to save a model in DB and retrieve It > > I'm wondering how to save an object (models like lm, loess, etc) in a > DB to retrieve and use it afterwards, an example: > > wind_ms <- abs(rnorm(24*30)*4+8) > air_kgm3 <- rnorm(24*30, 0.1)*0.1 + 1.1 > wind_dg <- rnorm(24*30) * 360/7 > ms <- c(0:25) > kw_mm92 <- c(0,0,0,20,94,205,391,645,979,1375,1795,2000,2040) > kw_mm92 <- c(kw_mm92, rep(2050, length(ms)-length(kw_mm92))) > modelspline <- splinefun(ms, kw_mm92) > kw <- abs(modelspline(wind_ms) - (wind_dg)*2 + (air_kgm3 - 1.15)*300 + > rnorm(length(wind_ms))*10) > #plot(wind_ms, kw) > windDat <- data.frame(kw, wind_ms, air_kgm3, wind_dg) > windDat[windDat$wind_ms < 3, 'kw'] <- 0 > model <- loess(kw ~ wind_ms + air_kgm3 + wind_dg, data = windDat, > enp.target = 10*5*3) #, span = 0.1) > > modX <- serialize(model, connection = NULL, ascii = T) > > Channel <- odbcConnect("someSysDSN; UID=aUid; PWD=aPwd") > sqlQuery(Channel, > paste( > "INSERT INTO GRT.GeneratorsModels > ([cGeneratorID] > ,[tModel] > VALUES > (1,", > paste("'", gsub("'", "''", rawToChar(modX)), "'", sep = ''), > ")", sep = "") ) > # Up to this it is working correctly, > # in DB I have the "modX" variable > # Problem arise retrieving data and 64kb limit: > strQ <- " > SELECT CONVERT(varchar(max), tModel) AS tModel > FROM GRT.GeneratorsModels > WHERE (cGeneratorID = 1) > " > x <- sqlQuery(Channel, strQ, stringsAsFactors = F, believeNRows > FALSE) > x <- sqlQuery(Channel, strQ, stringsAsFactors = F, believeNRows > FALSE) #read error > > > > Above code is working for simplier models that have a shorter > representation in variable "modX". > Any advice on how to store and retieve this kind of objects? > Thanks > Daniele > > > ORS Srl > > Via Agostino Morando 1/3 12060 Roddi (Cn) - Italy > Tel. +39 0173 620211 > Fax. +39 0173 620299 / +39 0173 433111 > Web Site www.ors.it > > ----------------------------------------------------------------------- > ------------------------------------------------- > Qualsiasi utilizzo non autorizzato del presente messaggio e dei suoi > allegati ? vietato e potrebbe costituire reato. > Se lei avesse ricevuto erroneamente questo messaggio, Le saremmo grati > se provvedesse alla distruzione dello stesso > e degli eventuali allegati. > Opinioni, conclusioni o altre informazioni riportate nella e-mail, che > non siano relative alle attivit? e/o > alla missione aziendale di O.R.S. Srl si intendono non attribuibili > alla societ? stessa, n? la impegnano in alcun modo. > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting- > guide.html > and provide commented, minimal, self-contained, reproducible code.
A very simple option, since you're only looking to efficiently store and retrieve, is something like a key-value store. There is a new rredis (redis) package on CRAN, as well as the RBerkeley (Oracle Berkeley DB) package. RBerkeley is as simple as db_put() and db_get() calls where you specify a key and serialize/unserialize the object before and after. Caveat to RBerkeley is that it is only functional on *nix until someone contributes a Windows version or insight on what I need to do to make that work (issue is that Berkeley DB can't be compiled easily using the R version of mingw to compile). The package code is likely to work for windows if you can manage to get the db headers/libs installed with the R toolchain. HTH Jeff On Fri, Apr 2, 2010 at 3:37 AM, Daniele Amberti <daniele.amberti at ors.it> wrote:> I'm wondering how to save an object (models like lm, loess, etc) in a DB to retrieve and use it afterwards, an example: > > wind_ms <- abs(rnorm(24*30)*4+8) > air_kgm3 <- rnorm(24*30, 0.1)*0.1 + 1.1 > wind_dg <- rnorm(24*30) * 360/7 > ms <- c(0:25) > kw_mm92 <- c(0,0,0,20,94,205,391,645,979,1375,1795,2000,2040) > kw_mm92 <- c(kw_mm92, rep(2050, length(ms)-length(kw_mm92))) > modelspline <- splinefun(ms, kw_mm92) > kw <- abs(modelspline(wind_ms) - (wind_dg)*2 + (air_kgm3 - 1.15)*300 + rnorm(length(wind_ms))*10) > #plot(wind_ms, kw) > windDat <- data.frame(kw, wind_ms, air_kgm3, wind_dg) > windDat[windDat$wind_ms < 3, 'kw'] <- 0 > model <- loess(kw ~ wind_ms + air_kgm3 + wind_dg, data = windDat, enp.target = 10*5*3) #, span = 0.1) > > modX <- serialize(model, connection = NULL, ascii = T) > > Channel <- odbcConnect("someSysDSN; UID=aUid; PWD=aPwd") > sqlQuery(Channel, > paste( > "INSERT INTO GRT.GeneratorsModels > ? ? ? ? ? ([cGeneratorID] > ? ? ? ? ? ,[tModel] > ? VALUES > ? ? ? ? ? (1,", > ? ? ? ? ? paste("'", gsub("'", "''", rawToChar(modX)), "'", sep = ''), > ? ? ? ? ? ")", sep = "") ) > # Up to this it is working correctly, > # in DB I have the "modX" variable > # Problem arise retrieving data and 64kb limit: > ?strQ <- " > ? ?SELECT ?CONVERT(varchar(max), tModel) AS tModel > ? ?FROM ? ?GRT.GeneratorsModels > ? ?WHERE ? (cGeneratorID = 1) > ? ?" > x <- sqlQuery(Channel, strQ, stringsAsFactors = F, believeNRows = FALSE) > x <- sqlQuery(Channel, strQ, stringsAsFactors = F, believeNRows = FALSE) #read error > > > > Above code is working for simplier models that have a shorter representation in variable "modX". > Any advice on how to store and retieve this kind of objects? > Thanks > Daniele > > > ORS Srl > > Via Agostino Morando 1/3 12060 Roddi (Cn) - Italy > Tel. +39 0173 620211 > Fax. +39 0173 620299 / +39 0173 433111 > Web Site www.ors.it > > ------------------------------------------------------------------------------------------------------------------------ > Qualsiasi utilizzo non autorizzato del presente messaggio e dei suoi allegati ? vietato e potrebbe costituire reato. > Se lei avesse ricevuto erroneamente questo messaggio, Le saremmo grati se provvedesse alla distruzione dello stesso > e degli eventuali allegati. > Opinioni, conclusioni o altre informazioni riportate nella e-mail, che non siano relative alle attivit? e/o > alla missione aziendale di O.R.S. Srl si intendono non ?attribuibili alla societ? stessa, n? la impegnano in alcun modo. > _______________________________________________ > R-sig-DB mailing list -- R Special Interest Group > R-sig-DB at stat.math.ethz.ch > https://stat.ethz.ch/mailman/listinfo/r-sig-db >-- Jeffrey Ryan jeffrey.ryan at insightalgo.com ia: insight algorithmics www.insightalgo.com