10.6.3.4 Asynchronous Job Example
The following examples shows how to submit asynchronous jobs with non-XML output and with XML output.
Non-XML Output
When submitting asynchronous jobs, for JSON, PNG and relational outputs, set the OUT_FMT argument to NULL when submitting the job. When fetching the job result, specify OUT_FMT in the pyqJobResult call.
This example uses the IRIS table created in the example shown in the pyqTableEval Function (Autonomous Database) topic and the linregrPredict script created in the example shown in the pyqRowEval Function (Autonomous Database) topic.
Issue a pyqGroupEval function call to submit an asynchronous job. In the function, the INP_NAM argument specifies the data in the IRIS table to pass to the function.
The PAR_LST argument specifies submitting the job asynchronously with the special control argument oml_async_flag, capturing the images rendered in the script with the special control argument oml_graphics_flag, 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.
The OUT_FMT argument is NULL.
The GRP_COL parameter specifies the column to group by.
The SCR_NAME parameter specifies the user-defined Python function stored with the name linregrPredict in the script repository.
The asynchronous call returns a job status URL in CLOB, you can call set long [length] to get the full URL.
set long 150
select *
from table(pyqGroupEval(
inp_nam => 'IRIS',
par_lst => '{"oml_input_type":"pandas.DataFrame",
"oml_async_flag":true, "oml_graphics_flag":true,
"modelName":"linregr", "datastoreName":"pymodel"}',
out_fmt => NULL,
grp_col => 'Species',
ord_col => NULL,
scr_name => 'linregrPredict',
scr_owner => NULL
));
The output is the following:
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
https://<host name>/oml/tenants/<tenant name>/databases/<database name>/api/py-scripts/v1/jobs/<job id>
1 row selected. Run a SELECT statement that invokes the pyqJobStatus function, which returns a resource URL containing the job ID when the job result is ready.
select * from pyqJobStatus(
job_id => '<job id>'); The output is the following when the job is still pending.
NAME
----------------------------------------------------------------------
VALUE
----------------------------------------------------------------------
job is still running
1 row selected. The output is the following when the job finishes.
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
https://<host name>/oml/tenants/<tenant name>/databases/<database name>/api/py-scripts/v1/jobs/<job id>/result
1 row selected. Run a SELECT statement that invokes the pyqJobResult function.
In the OUT_FMT argument, the string 'PNG' specifies to include both return value and images (titles and image bytes) in the result.
column name format a7
column value format a15
column title format a16
column image format a15
select * from pyqJobResult(
job_id => '<job id>',
out_fmt => 'PNG'
);The output is the following.
NAME ID VALUE TITLE IMAGE
------- ---------- --------------- ---------------- ---------------
GROUP_s 1 [{"Species":"se Prediction of Pe 6956424F5277304
etosa tosa","Sepal_Le tal Width B47676F41414141
ngth":4.6,"Sepa 4E5355684555674
l_Width":3.6,"P 141416F41414141
etal_Length":1. 486743415941414
0,"Petal_Width" 1413130647A6B41
:0.2,"Pred_Peta 41414142484E435
l_Width":0.1325 356514943416749
345443},{"Speci 6641686B6941414
es":"setosa","S 141416C7753466C
7A4141415059514
141443245427144
2B6E61514141414
468305256683055
32396D644864686
36D554162574630
634778766447787
0596942325A584A
7A615739754D793
4784C6A49734947
GROUP_v 1 [{"Species":"ve Prediction of Pe 6956424F5277304
ersicol rsicolor","Sepa tal Width B47676F41414141
or l_Length":5.1," 4E5355684555674
Sepal_Width":2. 141416F41414141
5,"Petal_Length 486743415941414
":3.0,"Petal_Wi 1413130647A6B41
dth":1.1,"Pred_ 41414142484E435
Petal_Width":0. 356514943416749
8319563387},{"S 6641686B6941414
pecies":"versic 141416C7753466C
7A4141415059514
141443245427144
2B6E61514141414
468305256683055
32396D644864686
36D554162574630
634778766447787
0596942325A584A
7A615739754D793
4784C6A49734947
GROUP_v 1 [{"Species":"vi Prediction of Pe 6956424F5277304
irginic rginica","Sepal tal Width B47676F41414141
a _Length":5.7,"S 4E5355684555674
epal_Width":2.5 141416F41414141
,"Petal_Length" 486743415941414
:5.0,"Petal_Wid 1413130647A6B41
th":2.0,"Pred_P 41414142484E435
etal_Width":1.7 356514943416749
55762924},{"Spe 6641686B6941414
cies":"virginic 141416C7753466C
7A4141415059514
141443245427144
2B6E61514141414
468305256683055
32396D644864686
36D554162574630
634778766447787
0596942325A584A
7A615739754D793
4784C6A49734947
3 rows selected. XML Ouput
If XML output is expected from the asynchronous job, set the OUT_FMT argument to 'XML' when submitting the job and fetching the job result.
This example uses the script myFitMultiple created in the example shown in the pyqIndexEval Function (Autonomous Database) topic.
Issue a pyqIndexEval function call to submit an asynchronous job. In the function, the PAR_LST argument specifies submitting the job asynchronously with the special control argument oml_async_flag, along with values for the function arguments sample_size.
The asynchronous call returns a job status URL in CLOB, you can call set long [length] to get the full URL.
set long 150 select *
from table(pyqIndexEval(
par_lst => '{"sample_size":80,"oml_async_flag":true}',
out_fmt => 'XML',
times_num => 3,
scr_name => 'myFitMultiple',
scr_owner => NULL
)); The output is the following.
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
https://<host name>/oml/tenants/<tenant name>/databases/<database name>/api/py-scripts/v1/jobs/<job id>
1 row selected.Run a SELECT statement that invokes the pyqJobStatus function, which returns a resource URL containing the job id when the job result is ready.
select * from pyqJobStatus(
job_id => '<job id>'
);
The output is the following when the job is still pending.
NAME
----------------------------------------------------------------------
VALUE
----------------------------------------------------------------------
job is still running
1 row selected. The output is the following when the job result is ready.
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
https://<host name>/oml/tenants/<tenant name>/databases/<database name>/api/py-scripts/v1/jobs/<job id>/result
1 row selected. Run a SELECT statement that invokes the pyqJobResult function.
In the OUT_FMT argument, the string 'XML' specifies that the table returned contains a CLOB that is an XML string.
select * from pyqJobResult(
job_id => '<job id>',
out_fmt => 'XML'
);The output is the following.
NAME
----------------------------------------------------------------------
VALUE
----------------------------------------------------------------------
1
<root><pandas_dataFrame><ROW-pandas_dataFrame><id>1</id><score>0.94355
0631313753</score></ROW-pandas_dataFrame></pandas_dataFrame></root>
2
<root><pandas_dataFrame><ROW-pandas_dataFrame><id>2</id><score>0.92783
6941437123</score></ROW-pandas_dataFrame></pandas_dataFrame></root>
3
<root><pandas_dataFrame><ROW-pandas_dataFrame><id>3</id><score>0.93719
6049031545</score></ROW-pandas_dataFrame></pandas_dataFrame></root>
3 rows selected. Parent topic: Asynchronous Jobs (Autonomous Database)