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 |
|---|---|
|
|
A cursor that specifies the data to pass to the R function specified by the |
|
|
A cursor that contains argument values to pass to the input function. |
|
|
One of the following:
|
|
|
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"