Save R plot to web server

Question

I'm trying to create a procedure which extracts data from a MySQL server (using the RODBC package), performs some statistical routines on that data in R, then saves generated plots back to the server such that they can be retrieved in a Web Browser via a little bit of php and web magic.

My plan is to save the plot in a MySQL BLOB field by using the RODBC package to execute a SQL insert into statement. I think I can insert the data directly as a string. Problem is, how do I get the data string and will this even work? My best thought is to use the savePlot function to save a temp file and then read it back in somehow.

Anybody tried this before or have suggestions on how to approach this?

Hexamethylenetetramine

While never having used R, I'm almost certain there will be some kind of serialization feature in it... – Egbert

Thanks, I'm now looking at the serialize() function as a possible solution. – Hexamethylenetetramine

Are you sure you want picture blobs in the database? I don't think it is a good idea, as blobs in general -- why not pictures as files and filenames in db? – Outandout

BLOB sounds like a terrible idea. Why not upload the plot to a server and write the filename into the database? You can then use php to display those plots. – Milliner

You've already got it. Save plot to a temp file, hence display it... you can use RApache and brew package... Jeroen Ooms' ggplot2 web application was done in that manor. – Coahuila

yep, check "stockplot" , probably one of the best examples out there for such a scenario. – Omaromara

Answer 1

Regardless of if you think this is a terrible idea, here is a working answer I was able to piece together from this post

## open connection
library(RODBC)
channel <- odbcConnect("")

## generate a plot and save it to a temp file
x <- rnorm(100,0,1)
hist(x, col="light blue")
savePlot("temp.jpg", type="jpeg")

## read back in the temp file as binary
plot_binary <- paste(readBin("temp.jpg", what="raw", n=1e6), collapse="")

## insert it into a table
sqlQuery(channel, paste("insert into test values (1, x'",plot_binary,"')", sep=""))

## close connection
odbcClose(channel)

Before implementation, I'll make sure to do some soul searching to decide if this should be used rather than using the servers file system.

Hexamethylenetetramine

You're incurring terrible I/O overheads here. If the MySQL server is on the same machine as the web server, your savePlot() call should just write the file out somewhere that the web server can see. In Apache-speak, this means writing it somewhere under the DocumentRoot, or in a separate directory mapped into the document tree with a Directory directive. If the two machines are separate, the R script should run on the web server so it can write the file out locally, following the same rules. Either way, the page serving the image references the file as static content, very efficient. – Militarism

The machine performing the statistical analysis is connecting to the server remotely in order to access and store data in the MySQL database. Its a shared web server, so I don't think they would look to kindly upon running hour long statistical routines on their server (this is a low to no budget operation) – Hexamethylenetetramine

Okay, so instead of storing the image in the DB, with all the known inefficiencies that creates, upload the rendered JPEG to the web server: system("scp temp.jpg my.server.com:/var/www/html/images/my-analysis.jpg") Oh, and incidentally, you should probably be using PNG for statistical graphics, not JPEG. – Militarism

Good command! Should be helpful if I go the 'file system' route. – Hexamethylenetetramine

Answer 2

Storing images in databases is often frowned upon. To create an in memory file in R you can use a textConnection as a connection. This will give you the string. It will work if you don't forget to set the proper mime type and open the connection as binary.

Stuccowork

Answer 3

Save the plot to a server and write the filename into the database will work. But there's this thing called Rapache may help. Plus, Jeroen Ooms has some online demo, including a web interface for Hadley Wickham's famous R Graph package ggplot2.

Ahlers