Serialize and deserialize model to Postgres in R

Question

Wondering if anyone has successfully managed to serialize and unserialize a R model into a Postgres database. I have tried different ways of serializeing JSON, raw e.t.c without success. I'm using the RPostgreSQL package

Pseudo code, not working

  # SERILIZE
  fit <- lm(reading ~ ., mdata.sel)
  pgcon <- mpr.getDBConnection()

  on.exit(dbDisconnect(pgcon))

  df <- data.frame(serialize(fit,NULL))
  vector <- vector()
  vector[1] <- "poly"

  colnames(df) <- vector

  dbWriteTable(pgcon, "ptest",
               value = df , append = TRUE, row.names = FALSE)



  # UNSERIALIZE    
  rows<-dbGetQuery(pgcon, "SELECT encode(poly::bytea,'escape') from ptest")

  iter_model<-postgresqlUnescapeBytea(rows[["encode"]])

  model<-unserialize(iter_model)

EDIT

Found a sample in How to write and read binary data with RPostgresql, which stores the model, unfortunately when unserializing the retrieved object, it becomes corrupt

      con <- mpr.getDBConnection()

      on.exit(dbDisconnect(pgcon))

      dbGetQuery(con,"CREATE TABLE byteatable (name text NOT NULL, val bytea, PRIMARY KEY (name))")

      ser <- serialize(fit,NULL,ascii=F)
      postgresqlEscapeBytea(con, ser)
      iq <- sprintf("INSERT INTO byteatable values('%s',E'%s');","name1", postgresqlEscapeBytea(con, ser))
      dbGetQuery(con, iq)
      rows<-dbGetQuery(con, "SELECT * from byteatable")
      ser2<-postgresqlUnescapeBytea(rows[[2]])
      unserialize(ser2) # CORRUPT

score 1 · Answer 1 · answered Jul 04 '19 at 14:10

Serializing/unseralizing to json , seems to work

library(RPostgreSQL)
library(jsonlite)
mpr.test <- function(){

  con <- mpr.getDBConnection()

  on.exit(dbDisconnect(pgcon))

  dbGetQuery(con,"CREATE TABLE public.strtable(poly character varying COLLATE pg_catalog.'default'')")

  dbGetQuery(con, "DELETE FROM strtable")

  md <- mpr.measurementdata_get("bbbb") #getting meaurement data
  fit <- mpr.poly_calc(md) # lm model

  iq <- sprintf("INSERT INTO strtable values('%s');", serializeJSON(fit, digits = 8, pretty = FALSE))

  dbGetQuery(con, iq)

  rows<-dbGetQuery(con, "SELECT * from strtable")

  fit.db <- unserializeJSON(rows[[1]])


}

Serialize and deserialize model to Postgres in R

1 Answers1