10.5.4 pyqRowEval Function (On-Premises Database)
This topic describes the pyqRowEval function when used in
an on-premises Oracle Database. The pyqRowEval function chunks data into sets
of rows and then runs a user-defined Python function on each chunk.
The pyqRowEval function passes the data specified by the
INP_NAM parameter to the Python
function specified by the EXP_NAM
parameter. You can pass arguments to the Python function with the PAR_QRY parameter.
The ROW_NUM parameter
specifies the maximum number of rows to pass to each invocation of the Python function. The
last set of rows may have fewer rows than the number specified.
The Python function can return a boolean, a
dict, a float, an int, a
list, a str, a tuple or a
pandas.DataFrame object. You may define the form of the returned value
with the OUT_QRY parameter.
Syntax
pyqRowEval (
INP_NAM VARCHAR2 IN
PAR_QRY VARCHAR2 IN
OUT_QRY VARCHAR2 IN
ROW_NUM NUMBER IN
EXP_NAM VARCHAR2 IN)Parameters
| Parameter | Description |
|---|---|
|
|
The name of a table or view that specifies the data
to pass to the Python function specified by the
|
|
|
A JSON string that contains additional parameters to
pass to the user-defined Python function specified by the
For example, to specify the input data type as
|
|
|
The format of the output returned by the function. It can be one of the following:
|
ROW_NUM |
The number of rows to include in each invocation of the Python function. |
|
|
The name of a user-defined Python function in the OML4Py script repository. |
Returns
Function pyqRowEval returns a table that has the structure
specified by the OUT_QRY parameter value.
Example 10-17 Using the pyqRowEval Function
This example loads the Python model linregr to predict row chunks of sample iris data. The model is created and saved in the datastore pymodel in Example 10-16.
The example defines a Python function and stores it in the OML4Py script repository. It uses the user-defined Python function to create a
database table as the result of the pyqEval function. It defines a Python
function that runs a prediction function on a model loaded from the OML4Py datastore. It then invokes the pyqTableEval function to
invoke the function on chunks of rows from the database table.
In a PL/SQL block, define the function sample_iris_table and
store it in the script repository. The function loads the iris data set, creates two
pandas.DataFrame objects, and then returns a sample of the concatenation
of those objects.
BEGIN
sys.pyqScriptCreate('sample_iris_table',
'def sample_iris_table(size):
from sklearn.datasets import load_iris
import pandas as pd
iris = load_iris()
x = pd.DataFrame(iris.data, columns = ["Sepal_Length",\
"Sepal_Width","Petal_Length","Petal_Width"])
y = pd.DataFrame(list(map(lambda x: {0:"setosa", 1: "versicolor",\
2: "virginica"}[x], iris.target)),\
columns = ["Species"])
return pd.concat([y, x], axis=1).sample(int(size))',
FALSE, TRUE); -- V_GLOBAL, V_OVERWRITE
END;
/
Create the SAMPLE_IRIS table in the database as the result of a
SELECT statement, which invokes the pyqEval function on
the sample_iris_table user-defined Python function saved in the script
repository with the same name. The sample_iris_table function returns an
iris data sample of size size.
CREATE TABLE sample_iris AS
SELECT *
FROM TABLE(pyqEval(
'{"size":20}',
'{"Species":"varchar2(10)","Sepal_Length":"number",
"Sepal_Width":"number","Petal_Length":"number",
"Petal_Width":"number"}',
'sample_iris_table'));Define the Python function predict_model and store it with the
name linregrPredict in the script repository. The function predicts the
data in dat with the Python model specified by the
modelName argument, which is loaded from the datastore specified by the
datastoreName argument. The predictions are finally concatenated and
returned with dat as the object that the function returns.
BEGIN
sys.pyqScriptCreate('linregrPredict',
'def predict_model(dat, modelName, datastoreName):
import oml
import pandas as pd
objs = oml.ds.load(name=datastoreName, to_globals=False)
pred = objs[modelName].predict(dat[["Sepal_Length","Sepal_Width",\
"Petal_Length"]])
return pd.concat([dat, pd.DataFrame(pred, \
columns=["Pred_Petal_Width"])], axis=1)',
FALSE, TRUE);
END;
/Run a SELECT statement that invokes the
pyqRowEval function, which runs the specified Python function on each
chunk of rows in the specified data set.
The INP_NAM argument specifies the data in the
SAMPLE_IRIS table to pass to the Python function.
The PAR_QRY argument specifies connecting to
the OML4Py server with the special control argument
oml_connect, passing the input data as a
pandas.DataFrame with the special control argument
oml_input_type, along with values for the function arguments
modelName and datastoreName.
In the OUT_QRY argument, the JSON string
specifies the column names and data types of the table returned by
pyqRowEval.
The ROW_NUM argument specifies that five rows are included in each invocation of the function specified by EXP_NAM.
The EXP_NAM parameter specifies
linregrPredict, which is the name in the script repository of the
user-defined Python function to invoke.
SELECT *
FROM table(pyqRowEval(
'SAMPLE_IRIS',
'{"oml_connect":1,"oml_input_type":"pandas.DataFrame",
"modelName":"linregr", "datastoreName":"pymodel"}',
'{"Species":"varchar2(10)", "Sepal_Length":"number",
"Sepal_Width":"number", "Petal_Length":"number",
"Petal_Width":"number","Pred_Petal_Width":"number"}',
5,
'linregrPredict'));The output is the following:
Species Sepal_Length Sepal_Width Petal_Length Petal_Width Pred_Petal_Width
---------- ------------ ----------- ------------ ----------- ------------------
versicolor 5.4 3 4.5 1.5 1.66731546068336
versicolor 6 3.4 4.5 1.6 1.63208723397328
setosa 5.5 4.2 1.4 0.2 0.289325450127603
virginica 6.4 3.1 5.5 1.8 2.00641535609046
versicolor 6.1 2.8 4.7 1.2 1.58248012323666
setosa 5.4 3.7 1.5 0.2 0.251046097050724
virginica 7.2 3 5.8 1.6 1.97554457713195
versicolor 6.2 2.2 4.5 1.5 1.32323976658868
setosa 4.8 3.1 1.6 0.2 0.294116926466465
virginica 6.7 3.3 5.7 2.5 2.0936178656911
virginica 7.2 3.6 6.1 2.5 2.26646663788204
setosa 5 3.6 1.4 0.2 0.259261360689759
virginica 6.3 3.4 5.6 2.4 2.14639883810232
virginica 6.1 3 4.9 1.8 1.73186245496453
versicolor 6.1 2.9 4.7 1.4 1.60476297762276
versicolor 5.7 2.8 4.5 1.3 1.56056992978395
virginica 6.4 2.7 5.3 1.9 1.8124673155904
setosa 5 3.5 1.3 0.3 0.184570194825823
versicolor 5.6 2.7 4.2 1.3 1.40178874834007
setosa 4.5 2.3 1.3 0.3 0.0208089790714202