Integrating Embedded R Execution with ADF

17 maart 2015 door Rob Nobel Tags: R

Before I came involved in software development, I used to work as a statistical researcher. While doing so, I spent quite some time programming in R, an open statistical programming language with a wide range of possibilities for statistical computing and data visualization. Nowadays, R is gaining popularity at a rapid pace, within several domains, including that of Big Data. Oracle has also recognized this development and defined a strategy for supporting open-source R, resulting in new interesting technologies, such as Oracle R Enterprise (ORE, part of the Advanced Analytics option in Oracle DB 12c en 11g) and ROracle, an open-source Oracle database interface for R.

Embedded R Execution

An interesting feature of ORE is the possibility of Embedded R Execution, which facilitates embedded R script execution on the database server machine. To support this feature, both an R- and a SQL- interface is provided. A very nice introduction to these technologies, features etc. is given in a tutorial series on Oracle R Enterprise v 1.4 in the Oracle Learning Library:
https://apexapps.oracle.com/pls/apex/f?p=44785:24:0::::P24_CONTENT_ID,P24_PREV_PAGE:8984,1

Being an ADF-developer myself, going through this tutorial series made me wonder : what are the possibilities of integrating R, or more specifically, Embedded R Execution within an ADF application ? For example, the graphical facilities of R could surely add some useful functionality to a typical ADF database application. So I tried out a couple of things, resulting in this blogpost in which I will show a very basic example of using Embedded R Execution in an ADF application.

Basic Example

Consider the following line of R script:

plot( 1:100, rnorm(100), pch = 21, bg = “red”, cex = 2 )

This piece of code plots 100 random numbers, drawn from a normal distribution with mean 0 and variance 1. Now, suppose we would like to store this script in a database repository, making it available for execution by other clients. Assuming that the database environment is properly configured (this is all explained in the aforementioned tutorial), we could do this as follows:

begin
sys.rqScriptCreate(‘RndDotsPlot’,
‘function(param){
plot( 1:param, rnorm(param), pch = 21, bg = “red”, cex = 2 )
}’);
end;

The function defined in this little script wraps the R plot-function. I have added an extra parameter to the function, making it possible to vary the number of points to be plotted. After storing this script in the database repository, we can use the SQL interface to call/execute this script as follows :

select id image_id,image image
from table(rqTableEval(
cursor(SELECT :BParam VAL FROM DUAL),
NULL,
‘PNG’,
‘RndDotsPlot’))

The rqTableEval function is one of several functions provided by the SQL interface (again, for more information see the tutorial provided by Oracle). The first argument is a cursor with input data for the function defined in the RndDotsPlots script, where the bind variable represents the param-argument. The second argument is an optional parameter-cursor, in this case NULL. The third argument specifies the format of the result, and the last argument the name of the script to be executed.

ADF

So, having our script ready to be executed, let’s switch to our ADF application. Of course we need a connection to a properly configured database. In my case, I took the easy way and used the 12c Big Data Lite VM provided by Oracle: http://www.oracle.com/technetwork/database/bigdata-appliance/oracle-bigdatalite-2104726.html.

With port forwarding, I used my local JDeveloper (12c) to set up the connection with the database on the virtual machine. I then created a model project, with a custom SQL-query-based view object using the earlier described SELECT statement. A SQL-type bind variable BParam is defined as well, so that the number of plotted point can be manipulated by changing the parameter value. The datatype of the image-attribute is set to BlobDomain.

Now if we create an application module containing this view object and run the Business Component tester, we can see that this indeed gives us a proper result (although we still have to await how the graph looks of course):

To eventually show/render this Blob as an image in the browser, we need to create an image-servlet. A large amount of blogs/tutorials have been devoted to this subject. Several strategies can be followed, such as defining a prepared statement within the image servlet class itself, or creating and releasing a root application module ‘on the fly’. More in line with the ADF framework though, would be to use the existing binding layer of the framework. I will not show exactly how I did this here, as the why-and-how of this has been well described by others, such as Timo Hahn: https://tompeez.wordpress.com/2011/12/16/jdev11-1-2-1-0-handling-imagesfiles-in-adf-part-3/

The image servlet that I created is also based on the example by Timo Hahn , and the doGet() method is defined as follows:

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
synchronized (this)
{
BlobDomain imageBlob = null;
try
{
// get parameter from request
Map paramMap = request.getParameterMap();
Number id = null;
Number param = null;
String[] pVal;
if (paramMap.containsKey(“param”))
{
pVal = (String[]) paramMap.get(“param”);
param = new Number(pVal[0]);
}

// get method action from pagedef
BindingContext bindingContext = BindingContext.getCurrent();
DCBindingContainer amx =
bindingContext.findBindingContainer(“nl_insystems_demo_view_imageContentPageDef”);
JUCtrlActionBinding lBinding =
(JUCtrlActionBinding) amx.findCtrlBinding(“getRndDotsImage”);

// set parameter,execute method and get result
lBinding.getParamsMap().put(“param”, param);
lBinding.invoke();
imageBlob = (BlobDomain) lBinding.getResult();
if (imageBlob != null)
{
// Get Blob data
response.setContentType(CONTENT_TYPE);
OutputStream os = response.getOutputStream();
BufferedInputStream in = new BufferedInputStream(imageBlob.getBinaryStream());
int b;
byte[] buffer = new byte[10240];
while((b = in.read(buffer, 0, 10240)) != -1)
{
os.write(buffer, 0,b);
}
in.close();
os.flush();
}
else
{
mLogger.warning(“No image found! “);
return;
}
}
catch (Exception e)
{
mLogger.warning(“Error:” + e.getMessage());
}
}
}

As can be seen, we get the parameter value from the request and then access the binding container to execute a methodAction with this request parameter. The method that is executed is a simple client interface method, which is defined in an application module class:

public void newRandomDots(Number param){
EmbRScriptViewImpl embRScript = getEmbRScriptView();
embRScript.setBParam(param);
embRScript.executeQuery();
}

In order to display the image, I created a simple .jspx page containing the following form layout:

<af:panelFormLayout id=”pfl1″>
<af:button text=”Generate plot” id=”b1″ partialSubmit=”true”/>
<af:inputText label=”Nr of points:” id=”it1″ value=”#{embRBean.param}”/>
<af:image source=”/imageservlet?param=#{embRBean.param}”
shortDesc=”RImage” id=”i1″ partialTriggers=”b1″/>
</af:panelFormLayout>

The inputText element has it’s value bound to a managed bean and makes it possible to enter the parameter value (i.e. the number of points to be plotted). The image element has it’s source attribute set to the proper URI and takes the input value as a request parameter (The servlet name, class and the URI it listens to is defined in the web.xml file). The partial trigger refers to a button, which has it’s partialSubmit attribute set to true , so that the image refreshes after every button press.

The result

Running the page gives the following result:

Changing the number of points to 10, and pressing the button, executes the script again, and gives us a new plot based on a random sample of 10 numbers drawn from a normal distribution:

Thus, with just a few basic steps, we have the graphic possibilities of R right at our disposal.
Of course there is not much practical use in this example, I just wanted to present the basic idea of using Embedded R execution in ADF. In the near future, I hope to find the time to provide a more practical and ‘data-bound’ use case as well.