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

INP_NAM

The name of a table or view that specifies the data to pass to the Python function specified by the EXP_NAM parameter. If using a table or view owned by another user, use the format <owner name>.<table/view name>. You must have read access to the specified table or view.

PAR_QRY

A JSON string that contains additional parameters to pass to the user-defined Python function specified by the EXP_NAM parameter. Special control arguments, which start with oml_, are not passed to the function specified by EXP_NAM, but instead control what happens before or after the invocation of the function.

For example, to specify the input data type as pandas.DataFrame, use:

'{"oml_input_type":"pandas.DataFrame"}'

OUT_QRY

The format of the output returned by the function. It can be one of the following:

  • A JSON string that specifies the column names and data types of the table returned by the function. Any image data is discarded.
  • The name of a table or view to use as a prototype. If using a table or view owned by another user, use the format <owner name>.<table/view name>. You must have read access to the specified table or view.
  • 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 Python objects first, followed by the image or images generated by the Python function.
  • The string 'PNG', which specifies that the table returned contains a BLOB that has the image or images generated by the Python function. Images are returned as a base 64 encoding of the PNG representation.
ROW_NUM

The number of rows to include in each invocation of the Python function.

EXP_NAM

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 9-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 9-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