APEX and XE - A Match for Data Science

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?

A Docker Project for ORE

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.

APEX and ORE

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.

1. Create the Table GAPMINDER

create table gapminder(
  row_id number generated by default as identity
  , country varchar2(100)
  , year number
  , pop number 
  , continent varchar2(100)
  , lifeExp number
  , gdpPercap number
)
/

alter table gapminder
add constraint gapminder_pk
primary key (row_id)
;

2. Create the scripts PLOTDEMO and GGPLOTDEMO

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.

begin
  -- Demo using core R function plot
  sys.rqScriptCreate(
    v_name => 'PLOTDEMO'
    , v_script => q'[
function(data, title) {
  plot(data, main=title, type="o", col="blue")
}
]'
    , v_global => false
    , v_overwrite => true
  );

  -- Demo using a third-party library ggplot2
  sys.rqScriptCreate(
    v_name => 'GGPLOTDEMO'
    , v_script => q'[
function(mdata) {
  library(ggplot2)

  gplot <- ggplot(
    mdata
    , aes(
        x = GDPPERCAP
        , y = LIFEEXP
        , color = CONTINENT
    )
  ) + geom_point()
  plot(gplot)
}
]'
    , v_global => false
    , v_overwrite => true
  );
end;
/

3. Grant Rights

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 APEX_PUBLIC_USER instead.

begin 
  rqGrant('PLOTDEMO', 'rqscript', 'APEX_PUBLIC_USER');
  rqGrant('GGPLOTDEMO', 'rqscript', 'APEX_PUBLIC_USER');
end;
/

Alternatively, with security in mind, you may also compile the scripts as "public". Do this by setting the sys.rqScriptCreate argument v_global to true.

4. Applying the Scripts in APEX

For displaying both charts, a Display Image Page Item was used. With some guidance, here's how I have them set up.

1. Set the "Based On" setting to BLOB Column returned by SQL statement.
2. For the "SQL Statement", something like these should work.

Using R plot

with data as (
  select year as "Year", lifeexp as "Life Expectancy"
  from gapminder
  where 1 = 1
    and lower(country) = 'singapore'
  order by year
)
select image
from table(
  rqTableEval(
    cursor(select * from data)
    , cursor(
        select 'Life Expectancy in Singapore' as "title" 
        from dual
    )
    , 'PNG'
    , 'DEMO.PLOTDEMO'
  )
)
where id = 1

Using ggplot

with data as (
  select gdppercap, lifeexp, continent
  from gapminder
  where 1 = 1
)
select image
from table(
  rqTableEval(
    cursor(select * from data)
    , null
    , 'PNG'
    , 'DEMO.GGPLOTDEMO'
  )
)
where id = 1

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.

Besides Docker

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 Dockerfile).
• Install R packages listed in enableORE.sh.
• Run the SQL script $ORACLE_HOME/R/server/rqcfg.sql against the target PDB (see enableORE.sh).

Conclusion

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.