rqTableEval Function

10.5.7 rqTableEval Function

The rqTableEval function executes the R function in the script specified by the EXP_NAM parameter.

You pass data to the R function with the INP_CUR parameter. You can pass arguments to the R function with the PAR_CUR parameter.

The R function returns an R data.frame object, which appears as a SQL table in the database. You define the form of the returned value with the OUT_QRY parameter.

Syntax

rqTableEval (
     INP_CUR     REF CURSOR     IN
     PAR_CUR     REF CURSOR     IN
     OUT_QRY     VARCHAR2       IN
     EXP_NAM     VARCHAR2       IN)

Parameters

Table 10-18 Parameters of the rqTableEval Function

Parameter	Description
`INP_CUR`	A cursor that specifies the data to pass to the R function specified by the `EXP_NAME` parameter.
`PAR_CUR`	A cursor that contains argument values to pass to the input function.
`OUT_QRY`	One of the following: NULL, which returns a serialized object that can contain both data and image objects. A SQL `SELECT` statement that specifies the column names and data types of the table returned by `rqEval`. Any image data is discarded. You can provide a prototype row using the dual dummy table or you can base the SELECT statement on an existing table or view. The R function must return a `data.frame`. The string `'XML'`, which specifies that the table returned contains a CLOB that is an XML string. The XML can contain both structured data and images, with structured or semi-structured R objects first, followed by the image or images generated by the R function. Images are returned as a base 64 encoding of the PNG representation. The string `'PNG'`, which specifies that the table returned contains a BLOB that has the image or images generated by the R function in PNG format.
`EXP_NAM`	The name of a script in the OML4R script repository.

Return Value

Function rqTableEval returns a table that has the structure specified by the OUT_QRY parameter value.

Examples

This example first has a PL/SQL block that drops the script myNaiveBayesModel to ensure that the script does not exist in the OML4R script repository. It then creates a function and stores it as the script myNaiveBayesModel in the repository.

The R function accepts two arguments: the data on which to operate and the name of a datastore. The function builds a Naive Bayes model on the iris data set. Naive Bayes is found in the e1071 package.

The myNaiveBayesModel function loads the e1071 package so that the function body has access to it when the function executes in an R engine on the database server. Because factors in the data.frame are converted to character vectors when they are loaded in the user-defined embedded R function, the myNaiveBayesModel function explicitly converts the character vector to an R factor.

The myNaiveBayesModel function gets the data from the specified column and then creates a model and saves it in a datastore. The R function returns TRUE to have a simple value that can appear as the result of the function execution.

The example next executes a SELECT statement that invokes the rqTableEval function. In the INP_CUR argument of the rqTableEval function, the SELECT statement specifies the data set to pass to the R function. The data is from the IRIS table that was created by invoking ore.create(iris, "IRIS"), which is not shown in the example. The INP_CUR argument of the rqTableEval function specifies the name of a datastore to pass to the R function and specifies the ore.connect control argument to establish an OML4R connection to the database during the embedded R execution of the user-defined R function. The OUT_QRY argument specifies returning the value in XML format, and the EXP_NAM argument specifies the myNaiveBayesModel script in the script repository as the R function to invoke.

Example 10-25 Using the rqTableEval Function

BEGIN
  sys.rqScriptDrop('myNaiveBayesModel');
  sys.rqScriptCreate('myNaiveBayesModel',
    'function(dat, datastoreName) {
       library(e1071)
       dat$Species <- as.factor(dat$Species)
       nbmod <- naiveBayes(Species ~ ., dat)
       ore.save(nbmod, name = datastoreName)
       TRUE
     }');
END;
/

SELECT *
  FROM table(rqTableEval(
    cursor(SELECT * FROM IRIS),
    cursor(SELECT 'myNaiveBayesDatastore' "datastoreName", 
           1 as "ore.connect" FROM dual),
    'XML', 'myNaiveBayesModel'));

The SELECT statement returns from the rqTableEval table function an XML string that contains the value TRUE.

The myNaiveBayesDatastore datastore now exists and contains the object nbmod, as shown by the following SELECT statement.

SQL> SELECT * from RQUSER_DATASTORECONTENTS 
  2     WHERE dsname = 'myNaiveBayesDatastore';
 
DSNAME                  OBJNAME  CLASS       OBJSIZE  LENGTH  NROW  NCOL
---------------------   -------  ----------  -------  ------  ----  ----
myNaiveBayesDatastore   nbmod    naiveBayes     1485       4

In a local R session, you could load the model and display it, as in the following:

R> ore.load("myNaiveBayesDatastore")
[1] "nbmod"
R> nbmod
$apriori
Y
    setosa versicolor  virginica 
        50         50         50 
 
$tables
$tables$Sepal.Length
            Sepal.Length
Y             [,1]      [,2]
  setosa     5.006 0.3524897
  versicolor 5.936 0.5161711
  virginica  6.588 0.6358796
 
$tables$Sepal.Width
            Sepal.Width
Y             [,1]      [,2]
  setosa     3.428 0.3790644
  versicolor 2.770 0.3137983
  virginica  2.974 0.3224966
 
$tables$Petal.Length
            Petal.Length
Y             [,1]      [,2]
  setosa     1.462 0.1736640
  versicolor 4.260 0.4699110
  virginica  5.552 0.5518947
 
$tables$Petal.Width
            Petal.Width
Y             [,1]      [,2]
  setosa     0.246 0.1053856
  versicolor 1.326 0.1977527
  virginica  2.026 0.2746501
 
 
$levels
[1] "setosa"     "versicolor" "virginica" 
 
$call
naiveBayes.default(x = X, y = Y, laplace = laplace)
 
attr(,"class")
[1] "naiveBayes"

Parent topic: SQL API for Embedded R Execution with On-premises Database