You can use the QueryGrid connector to query a remote system to request an EXPLAIN plan from the remote system.
For a Presto target system, specify the kind of EXPLAIN you want: either LOGICAL or DISTRIBUTED.
- A LOGICAL plan executes the EXPLAIN command on a single node.
- A DISTRIBUTED plan executes the EXPLAIN command in fragments on single or multiple nodes.
Example: Session Override Introduction
The example generates an EXPLAIN plan for a session override introduction.
< BEGIN EXPLAIN FOR REMOTE QUERY -->
----- BEGIN SESSION OVERRIDDEN PROPERTIES -----
'linkBufferSize' = '1048576'
'schemaName' = 'default'
----- END SESSION OVERRIDDEN PROPERTIES -----
Remote Queries:
1. CREATE TABLE
qgremote.DEFAULT.TEMP_38b505c0_9fb4_4a3d_bdc8_000000000017_EXEC
...
Example: Using Teradata to Presto EXPLAIN SELECT
The example generates an EXPLAIN plan from the remote SELECT query and embeds the result into the query plan on the local Teradata system.
EXPLAIN SELECT * FROM p100@tdh111 WHERE c1 > 10;
Result:
Explanation
---------------------------------------------------------------------------
1) First, we do an all-AMPs RETRIEVE step executing table operator
TD_SYSFNLIB.QGINITIATORIMPORT with a condition of ("p100.C1 >= 11").
< BEGIN EXPLAIN FOR REMOTE QUERY -->
Remote Queries:
1. EXPLAIN CREATE TABLE
qgremote.DEFAULT.TEMP_a1cd2570_d4ef_4b38_b3ed_00000000014e_EXEC AS
SELECT c1 ,c2 ,c3 ,c4 ,c5 FROM "default"."p100" WHERE C1 >= 11
[- Output[rows] => [rows:bigint]
-
TableCommit[qgremote:QGRemoteTransactionHandle{uuid=280273f9-403c-4cc7
-b0b5-dc148b36285f}:com.teradata.querygrid.qgc.presto.QGRemoteInsertTa
bleHandle@e32e808] => [rows:bigint]
- Exchange[GATHER] => partialrows:bigint, fragment:varbinary
- TableWriter => [partialrows:bigint, fragment:varbinary]
c1 := c1
c2 := c2
c3 := c3
c4 := c4
c5 := c5
- Exchange[REPARTITION] => c1:bigint, c2:varchar, c3:double,
c4:boolean, c5:varbinary
- ScanFilterAndProject[table = hive:hive:default:p100,
originalConstraint = ("c1" >= 11), filterPredicate = ("c1" >= 11)]
=> [c1:bigint, c2:varchar, c3:double, c4:boolean, c5:varbinary]
LAYOUT: hive
c1 := HiveColumnHandle{clientId=hive, name=c1, hiveType=bigint,
hiveColumnIndex=0, partitionKey=false}
c2 := HiveColumnHandle{clientId=hive, name=c2, hiveType=string,
hiveColumnIndex=1, partitionKey=false}
c3 := HiveColumnHandle{clientId=hive, name=c3, hiveType=double,
hiveColumnIndex=2, partitionKey=false}
c4 := HiveColumnHandle{clientId=hive, name=c4, hiveType=boolean,
hiveColumnIndex=3, partitionKey=false}
c5 := HiveColumnHandle{clientId=hive, name=c5, hiveType=binary,
hiveColumnIndex=4, partitionKey=false}
]
<-- END EXPLAIN FOR REMOTE QUERY >
The size of Spool 1 is estimated with low confidence to be 8 rows
(33,096 bytes). The estimated time for this step is 0.15 seconds.
2) Next, we do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by
way of an all-rows scan with a condition of ("p100.C1 >= 11") into
Spool 2 (group_amps), which is built locally on the AMPs. The
size of Spool 2 is estimated with low confidence to be 8 rows (
98,840 bytes). The estimated time for this step is 0.16 seconds.
3) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 2 are sent back to the user as the result of
statement 1. The total estimated time is 0.31 seconds.
Example: Using Teradata to Presto EXPLAIN INSERT
The example generates an EXPLAIN plan for the remote INSERT query and embeds the result into the query plan on the local Teradata system.
EXPLAIN INSERT INTO p100@tdh111 values(1,'abc',11.1, 1, 1);
Result:
Explanation
---------------------------------------------------------------------------
1) First, we do an INSERT into Spool 4.
2) Next, we do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by
way of an all-rows scan executing table operator
TD_SYSFNLIB.QGINITIATOREXPORT with a condition of ("(1=1)") into
Spool 2 (used to materialize view, derived table, table function
or table operator p100) (all_amps), which is built locally on the
AMPs.
< BEGIN EXPLAIN FOR REMOTE QUERY -->
Remote Queries:
1. CREATE TABLE
qgremote.DEFAULT.TEMP_a1cd2570_d4ef_4b38_b3ed_00000000014f_EXEC AS
SELECT * FROM "p100" WITH NO DATA
2. EXPLAIN INSERT INTO "p100" SELECT * FROM
qgremote.DEFAULT.TEMP_a1cd2570_d4ef_4b38_b3ed_00000000014f_EXEC
3. DROP TABLE IF EXISTS
qgremote.DEFAULT.TEMP_a1cd2570_d4ef_4b38_b3ed_00000000014f_EXEC
[- Output[rows] => [rows:bigint]
- TableCommit[hive:LegacyTransactionHandle{connectorId=hive,
uuid=f15a3b4c-89a8-427d-8dcd-c147334ff2ca}:hive:default.p100] =>
[rows:bigint]
- Exchange[GATHER] => partialrows:bigint, fragment:varbinary
- TableWriter => [partialrows:bigint, fragment:varbinary]
c1 := c1
c2 := c2
c3 := c3
c4 := c4
c5 := c5
- Exchange[REPARTITION] => c1:bigint, c2:varchar, c3:double,
c4:boolean, c5:varbinary
-
TableScan[qgremote:default:temp_a1cd2570_d4ef_4b38_b3ed_00000000014f_e
xec, originalConstraint = true] => [c1:bigint, c2:varchar,
c3:double, c4:boolean, c5:varbinary]
LAYOUT:
com.teradata.querygrid.qgc.presto.QGRemoteTableLayoutHandle@6029ca45
c1 := {name = c1, type = bigint}
c2 := {name = c2, type = varchar}
c3 := {name = c3, type = double}
c4 := {name = c4, type = boolean}
c5 := {name = c5, type = varbinary}
]
<-- END EXPLAIN FOR REMOTE QUERY >
The size of Spool 2 is estimated with high confidence to be 1 row
(29 bytes). The estimated time for this step is 0.03 seconds.
3) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
an all-rows scan into Spool 3 (Last Use), which is built locally
on the AMPs. The size of Spool 3 (Last Use) is estimated with
high confidence to be 1 row (41 bytes). The estimated time for
this step is 0.16 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.