2.15 - EXPLAIN - Teradata QueryGrid

Teradata® QueryGrid™ Guía de instalación y uso

Product
Teradata QueryGrid
Release Number
2.15
Release Date
Marzo de 2021
Content Type
Administración
Configuración
Guía del usuario
Instalación
Publication ID
B035-5991-031K-ESN
Language
Español (España)

Puede utilizar el conector de Teradata QueryGrid para consultar un sistema remoto para solicitar un plan EXPLAIN del sistema remoto.

Para un sistema de destino de Presto, especifique el tipo de EXPLAIN que desee: LOGICAL o DISTRIBUTED.
  • Un plan LOGICAL ejecuta el comando EXPLAIN en un solo nodo.
  • Un plan DISTRIBUTED ejecuta el comando EXPLAIN en fragmentos en nodos simples o múltiples.
Para especificar el tipo, use el portlet QueryGrid para configurar el conector de destino con la propiedad de conector ExplainKind. Consulte Propiedades de conectores y enlaces de Teradata.

Ejemplo: Introducción al reemplazo de sesión

El ejemplo genera un plan EXPLAIN para la introducción del reemplazo de sesión.

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

Ejemplo: usar EXPLAIN SELECT de Teradata a Presto

El ejemplo genera un plan de explicación de la consulta remota SELECT e incrusta el resultado en el plan de consulta en el sistema local de Teradata.

EXPLAIN SELECT * FROM p100@tdh111 WHERE c1 > 10;

Resultado:

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.

Ejemplo: usar EXPLAIN INSERT de Teradata a Presto

El ejemplo genera un plan de explicación para la consulta remota INSERT e incrusta el resultado en el plan de consulta en el sistema local de Teradata.

EXPLAIN INSERT INTO p100@tdh111 values(1,'abc',11.1, 1, 1);

Resultado:

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.