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.
How to save a model in DB and retrieve It
5 messages · Daniele Amberti, Greg Snow, Joe Conway +1 more
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.
On 04/02/2010 01:37 AM, Daniele Amberti 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:
# Up to this it is working correctly, # in DB I have the "modX" variable # Problem arise retrieving data and 64kb limit:
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?
You can mostly do what you want with PL/R and PostgreSQL (see http://www.joeconway.com) For example: 8<------------------------------ CREATE OR REPLACE FUNCTION get_model() RETURNS bytea AS $$ 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) 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) return(model) $$ LANGUAGE plr; CREATE OR REPLACE FUNCTION extract_fitted(bytea) RETURNS SETOF float8 AS $$ return(arg1$fitted) $$ LANGUAGE plr; 8<------------------------------ select length(get_model()); length -------- 219361 (1 row) select * from extract_fitted(get_model()); extract_fitted --------------------- 429.108839675228 994.109512291517 858.011203975038 [...] 349.87845982039 884.26297556709 155.996698202327 117.920754039095 (720 rows) 8<------------------------------ However while working up this example I discovered a bug in PL/R. In the *next* release you will be able to do the following, which with the current release does not quite work: 8<------------------------------ CREATE TABLE model_store (id int primary key, model bytea); INSERT INTO model_store VALUES (1, get_model()), (2, get_model()), (3, get_model()); select * from extract_fitted((select model from model_store where id = 1)); extract_fitted --------------------- -29.4074157828657 -3.7532027827115 803.524262857045 [...] 1463.03742124991 2051.01939505223 13.4600408221842 (720 rows) 8<------------------------------ Joe -------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 899 bytes Desc: OpenPGP digital signature URL: <https://stat.ethz.ch/pipermail/r-sig-db/attachments/20100402/664f491f/attachment.sig>
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
On 04/02/2010 01:45 PM, Joe Conway wrote:
However while working up this example I discovered a bug in PL/R. In the *next* release you will be able to do the following, which with the current release does not quite work:
FWIW, I released a new tarball with the fix for this issue. See: http://www.joeconway.com/plr/plr-8.3.0.10.tar.gz Joe -------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 899 bytes Desc: OpenPGP digital signature URL: <https://stat.ethz.ch/pipermail/r-sig-db/attachments/20100402/00d3d61b/attachment.sig>