Using REST to access Oracle APEX Cloud data in R

A while back I wrote a blog post on The Power of R, and I want to extend that a little now by looking at how easy it is to source data from an APEX Cloud instance directly into a R program.

To give a bit of background on this, for the past few weeks I’ve been looking after 5 baby rabbits that were rescued by our friend who is a vet. We had to weigh them daily, and keep the vets updated with these weights, so I knocked up a very simple APEX application on apex.oracle.com which let me add weights on my mobile.

Rabbit App Maintenance  Rabbit App Maintenance

You can take a look at the app here (no login required) and see how they have progressed!

Anyway, this got me thinking… if I have this data in APEX Cloud instance, how difficult can it be to access that from other systems, such as an R script. Well… not difficult at all it would seem.

First of all I created a REST Service using the RESTful Services utility in APEX, which simply returns the weight data in JSON format.

 

REST

REST Query

Then with a bit of help from Google, I found I could use the httr and jsonlite R packages to retrieve the data into a new data frame. It was a simple as:

require("httr") require("jsonlite")  rest_call Now we can see we have the data (I made the headings a bit more friendly and typed the Weight_Date field), plus the reference URL:
 > head(rabs)      Name Weight Weight_Date                                                               R1 1 Charlie    168  2018-07-28 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500 2 Charlie    179  2018-07-29 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500 3 Charlie    192  2018-07-30 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500 4  Tinker    157  2018-07-29 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500 5  Tinker    190  2018-07-31 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500 6   Tilly    132  2018-07-28 https://apex.oracle.com/pls/apex/keymej/keymej/weights?limit=500 >

Then using ggplot, I could very quickly produce a graph based on that data.

 require("ggplot2") ggplot(data=rabs, aes(x=rabs$Weight_Date,y=rabs$Weight, col=rabs$Name)) + geom_line() + labs(colour="Name",x="Date", y="Weight (g)")

ggplot Graph

I can also easily see statistics about any individual bunny!

 > charlie summary(charlie$Weight)    Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    168.0   262.5   358.0   357.5   438.0   550.0

The great thing about this though of course, is that my data set in R is live – there’s no manual downloading, no intermediate spreadsheets, and I could of course have made the data secured by authentication had I wanted to. So whilst APEX is a wonderful tool with great power and flexibility, it’s good to know it’s equally as easy to integrate it with other tools as well.

Don’t forget to keep a watch on the progress of the little bunnies! 🙂

Bunnies1 Bunnies2

Leave a Reply

Your email address will not be published. Required fields are marked *

eleven + five =

This site uses Akismet to reduce spam. Learn how your comment data is processed.