I wrote, quite a while ago, on how Oracle Application Express (APEX) developers could integrate R functionality/code in their applications. This was done primarily through Node.js packages and exposed as RESTful Web Services. Not the ideal solution, but useful for people wanting to integrate R statistical calculations and complex charting, but could not afford the hefty price tag for an Oracle Database Enterprice Edition license, plus the Oracle Advanced Analytics database option.
The revolutionary 18c release of the Oracle Database Express Edition (XE) significantly changed the Data Analytics playing field. The no-cost platform has resource caps (2 CPU, 2 GB RAM and 12 GB data storage). It’s not going to help perform complex GWAS (Genome-wide Association Study), but should suffice for many smaller research projects, statistical reporting and creating useful prediction models.
Unfortunately, the XE website has only a single line that says anything about this cool feature of the database:
If you prefer R programming, Oracle Database supports that too.
How do we get started?
The Oracle R Enterprise (ORE) Documentation provides quite a lot of information on what’s involved and how to setup a ORE environment. However, looking at what’s installed in XE, it looks like the server components have already been installed (see
$(ORACLE_HOME/R)). And if you attempted a silent ORE server install, the script complains about how a later version has already been installed and would not proceed.
I took it upon myself to waddle through the mud and figure out what’s involved to get ORE going on my workstation. The end result was a suite of scripts and Dockerfile for creating an ORE server and client for running R. These have been published in this public Github repository.
NOTE that this environment is meant for an explorer to bash quickly through the undergrowth, and head right into some interesting R and APEX coding. The instructions and simplicity of running Docker containers should provide you a clear path forward.
I’m an APEX developer, so including the environment to create great apps is a must! In this Docker environment, you will find APEX 18.2 and ORDS 18.3 pre-installed, configured and running. The banner image in this post is an actual screenshot of a running APEX app with ORE integration. Both charts are produced by R functions plot and ggplot2 using Gapminder data imported into the database.
create table gapminder(
We have an option of using R or SQL APIs to create scripts for embedded execution. The following uses the SQL API
sys.rqScriptCreate. In order to create scripts, the database user must have the
RQADMIN role. Be wary of who you assigned this to, as R scripts allow access to the database server and there are inherent security risks involved.
BrendanTierney wrote an excellent blog post on the new security model that ORE uses and what is needed to grant the appropriate privileges to allow embedded R scripts to run in APEX. The parsing schema does not require the rights to run these scripts, and in fact, the script will not run in APEX, even if the parsing schema created the script. The grants should be made to the user
Alternatively, with security in mind, you may also compile the scripts as “public”. Do this by setting the
For displaying both charts, a Display Image Page Item was used. With some guidance, here’s how I have them set up.
- Set the “Based On” setting to
BLOB Column returned by SQL statement.
- For the “SQL Statement”, something like these should work.
with data as (
with data as (
In both queries, using the
rqTableEval funcction, data is retrieved from the database and passed into the scripts’ function parameters as a
data.frame. R developers can then use the data and run various operations, returning the results as a
data.frame, (PNG) image or XML.
Also, when referencing scripts, include the schema name that created the script. Without it, only references to public scripts are valid.
Brendan is a well-known presenter and writer on all Data Analytics and Machine Learning with Oracle tools. Be sure to follow him, read his work and maybe buy his books if this is something you wish to pursue further. He’s been doing this for quite a while and evidently, I saved a lot of time by looking up his blog in this mini project.
The Docker environment I have put together isn’t production ready. To set this up without Docker and have it running in your environment, take a look at the
Dockerfile and relevant shell scripts to figure out what is involved. The following is a summary of activities that need to happen on the server-side.
- Install a compatible R environment including dependencies for building and install R packages (see
- Install R packages listed in
- Run the SQL script
$ORACLE_HOME/R/server/rqcfg.sqlagainst the target PDB (see
APEX is an outstanding tool for rapidly building modern day web applications at minimal costs and resources. Combine this with the power of R, it should be a no-brainer for anyone choosing a development environment for Research Data Management.