The output of the EXPLAIN foreign function command for the ML Engine connector lists query steps and shows a detailed explanation of the foreign function execution query from ML Engine. For example:
EXPLAIN select * from SESSIONIZE@coprocessor
(
ON sessionize_table_integer
PARTITION BY partition_id
ORDER BY clicktime
USING
TIMECOLUMN('clicktime')
TIMEOUT(60)
RAPIDFIRE(0.2)
) AS S order by S.clicktime;
EXPLAIN generates the following response from the ML Engine connector:
Explanation
---------------------------------------------------------------------------
1) First, we lock ALICE.sessionize_table_integer in TD_MAP1 for read
on a reserved RowHash to prevent global deadlock.
2) Next, we lock ALICE.sessionize_table_integer in TD_MAP1 for read.
3) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step in TD_MAP1 from
ALICE.sessionize_table_integer by way of an all-rows scan
with no residual conditions executing table operator
TD_SYSFNLIB.QGINITIATOREXPORT in TD_MAP1 with a condition of
("(1=1)") into Spool 3 (used to materialize view, derived
table, table function or table operator drvtab_inner)
(all_amps), which is built locally on the AMPs. The size of
Spool 3 is estimated with low confidence to be 112 rows (
7,952 bytes). The estimated time for this step is 0.00
seconds.
2) We do an all-AMPs RETRIEVE step in TD_Map1 from Spool 3 (Last
Use) by way of an all-rows scan executing table operator
TD_SYSFNLIB.QGINITIATORIMPORT in TD_MAP1 with a condition of
("(1=1)") into Spool 5 (used to materialize view, derived
table, table function or table operator S) (all_amps), which
is built locally on the AMPs.
< BEGIN EXPLAIN FOR REMOTE QUERY -->
1. Temporary table Temp_sess_1003_req_3_0_1 is created with
the local table's schema and exported to the remote system.
2. Function SESSIONIZE is executed using the function clause
'TIMECOLUMN('clicktime') TIMEOUT(60) RAPIDFIRE(0.2)' and the
result is stored in table
S_f72e961c-a5ca-494e-9af8-000000001721.
3. Columns partition_id, clicktime, userid, productname,
pagetype, referrer, productprice, sessionid, and rapidfire
are retrieved from S_f72e961c-a5ca-494e-9af8-000000001721.
4. Temporary table Temp_sess_1003_req_3_0_1 is dropped as
part of clean up.
<-- END EXPLAIN FOR REMOTE QUERY >
The size of Spool 5 is estimated with low confidence to be
112 rows (21,728 bytes). The estimated time for this step is
0.00 seconds.
4) We do an all-AMPs RETRIEVE step in TD_Map1 from Spool 5 (Last Use)
by way of an all-rows scan into Spool 6 (group_amps), which is
built locally on the AMPs. Then we do a SORT to order Spool 6 by
the sort key in spool field1. The size of Spool 6 is estimated
with low confidence to be 112 rows (20,048 bytes). The estimated
time for this step is 0.00 seconds.
5) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 6 are sent back to the user as the result of
statement 1. The total estimated time is 0.01 seconds.