Using Conda Environments with Embedded Python Execution

Running UDFs in the SQL and REST APIs for embedded Python execution

The conda environments can be used by OML4Py Python, SQL, and REST APIs. To use the SQL and REST API for embedded Python execution, the following information is needed.

The token URL from the OML service console in Autonomous Database. For more information on how to obtain the token URL and set the access token see Access and Authorization Procedures and Functions (Autonomous Database).
A script containing a user-defined Python function in the Oracle Machine Learning for Python (OML4Py) script repository. For information on creating a script and saving it to the script repository, see About Embedded Python Execution and the Script Repository.

Note:

To use a conda environment when calling OML4Py script execution endpoints, specify the conda environment in the env_name field when using SQL, and the envName field when using REST.

Run the Python UDF using the SQL API for embedded Python execution - Asynchronous mode

Run a SELECT statement that calls the pyqEval function. The PAR_LST argument specifies the special control argument oml_graphics_flag to true so that the web server can capture images rendered in the invoked script, the oml_async_flag is set to true to submit the job asynchronously. In the OUT_FMT argument, the string 'PNG', specifies that the table returns the response in a table with fixed columns (including an image bytes column). The SCR_NAME parameter specifies the function sb_plot stored in the script repository. The ENV_NAME specifies the environment name mysbenv in which the script is called.

%script

set long 2000

SELECT * FROM table(pyqEval(
    par_lst => '{"oml_graphics_flag":true, "oml_async_flag":true}',
    out_fmt => 'PNG',
    scr_name => 'sb_plot',
    scr_owner=> NULL,
    env_name => 'mysbenv'));

The output is similar to the following:

NAME VALUE
---------------------------
             https://gcc59e2cf7a6f5f-oml4.adb-compdev1.us-phoenix-1.oraclecloudapps.com/oml/api/py-scripts/v1/jobs/b82947a7-ec3a-4ca6-bf86-54b3f2b3a4b0

Get the job status

Poll the job status using the pyqJobStatus function. If the job is still running, the return value will note that the job is still running. When the job completes, a job ID and result location are returned.

%script

set long 1000
SELECT VALUE from pyqJobStatus(job_id => 'b82947a7-ec3a-4ca6-bf86-54b3f2b3a4b0');

The output returns a job ID:

NAME VALUE
---------------------------
             https://gcc59e2cf7a6f5f-oml4.adb-compdev1.us-phoenix-1.oraclecloudapps.com/oml/api/py-scripts/v1/jobs/b82947a7-ec3a-4ca6-bf86-54b3f2b3a4b0/result

Retrieve the result

%script

set long 500
SELECT NAME, ID, VALUE, dbms_lob.substr(image,100,1) image FROM pyqJobResult(job_id => 'b82947a7-ec3a-4ca6-bf86-54b3f2b3a4b0', out_fmt=>'PNG');

The output is similar to the following:

NAME ID VALUE IMAGE
---------------------------
           1 [{"0":0.0,"1":0.0,"2":0.2333333333,"accuracy":0.2333333333,"macro avg":0.0777777778,"weighted avg":0.0544444444},{"0":0.0,"1":0.0,"2":1.0,"accuracy":0.2333333333,"macro avg":0.3333333333,"weighted avg":0.2333333333},{"0":0.0,"1":0.0,"2":0.3783783784,"accuracy":0.2333333333,"macro avg":0.1261261261,"weighted avg":0.0882882883},{"0":11.0,"1":12.0,"2":7.0,"accuracy":0.2333333333,"macro avg":30.0,"weighted avg":30.0}] 89504E470D0A1A0A0000000D494844520000046A000003E808060000008668185B0000003974455874536F667477617265004D6174706C6F746C69622076657273696F6E332E362E322C2068747470733A2F2F6D6174706C6F746C69622E6F72672F28E8

Run the Python UDF using the REST API for embedded Python execution

The following example runs the script named sb_plot in the OML4Py REST API for embedded Python execution. The environment name parameter envName is set to mysbenv. The graphicsFlag parameter is set to true to return the PNG image and the data from the function in JSON format.


$ curl -i -X POST --header "Authorization: Bearer ${token}" \
--header 'Content-Type: application/json' --header 'Accept: application/json' \
-d '{"envName":"mysbenv", "graphicsFlag":true, "service":"LOW"}' \
"${omlserver}/oml/api/py-scripts/v1/do-eval/sb_plot"

The output is similar to the following:

NAME ID VALUE IMAGE
---------------------------
1 [{"0":0.0,"1":0.0,"2":0.2333333333,"accuracy":0.2333333333,"macro avg":0.0777777778,"weighted avg":0.0544444444},{"0":0.0,"1":0.0,"2":1.0,"accuracy":0.2333333333,"macro avg":0.3333333333,"weighted avg":0.2333333333},{"0":0.0,"1":0.0,"2":0.3783783784,"accuracy":0.2333333333,"macro avg":0.1261261261,"weighted avg":0.0882882883},{"0":11.0,"1":12.0,"2":7.0,"accuracy":0.2333333333,"macro avg":30.0,"weighted avg":30.0}] 89504E470D0A1A0A0000000D494844520000046A000003E808060000008668185B0000003974455874536F667477617265004D6174706C6F746C69622076657273696F6E332E362E322C2068747470733A2F2F6D6174706C6F746C69622E6F72672F28E8