Hive Query Execution Plan: Stage Dependencies and Plan Details

{'title': 'STAGE DEPENDENCIES: Stage-1 is a root stage Stage-0 depends on stages: Stage-1

STAGE PLANS: Stage: Stage-1 Tez DagId: omm_20230505154043_6d5bdd3f-3e27-431c-9ffd-5a6f9b4633d7:6535 Edges: Map 1 <- Map 2 (BROADCAST_EDGE), Map 3 (BROADCAST_EDGE), Map 4 (BROADCAST_EDGE) DagName: omm_20230505154043_6d5bdd3f-3e27-431c-9ffd-5a6f9b4633d7:6535 Vertices: Map 1 Map Operator Tree: TableScan alias: t1 Statistics: Num rows: 26836802 Data size: 20342296576 Basic stats: COMPLETE Column stats: NONE Map Join Operator condition map: Left Outer Join 0 to 1 keys: 0 prodcd (type: varchar(30)) 1 prodcd (type: varchar(30)) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col17, _col18, _col19, _col20, _col21, _col22, _col31 input vertices: 1 Map 2 Statistics: Num rows: 29520482 Data size: 22376526718 Basic stats: COMPLETE Column stats: NONE HybridGraceHashJoin: true Map Join Operator condition map: Left Outer Join 0 to 1 keys: 0 _col4 (type: varchar(20)) 1 itemcd (type: varchar(20)) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col17, _col18, _col19, _col20, _col21, _col22, _col31, _col43 input vertices: 1 Map 3 Statistics: Num rows: 32472530 Data size: 24614179923 Basic stats: COMPLETE Column stats: NONE HybridGraceHashJoin: true Map Join Operator condition map: Left Outer Join 0 to 1 keys: 0 _col17 (type: varchar(50)) 1 src_cd_val (type: varchar(50)) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col17, _col18, _col19, _col20, _col21, _col22, _col31, _col43, _col66 input vertices: 1 Map 4 Statistics: Num rows: 35719783 Data size: 27075598502 Basic stats: COMPLETE Column stats: NONE HybridGraceHashJoin: true Select Operator expressions: COALESCE(_col0,'') (type: string), COALESCE(_col1,'') (type: string), COALESCE(_col18,'') (type: string), COALESCE(_col19,'') (type: string), COALESCE(_col2,'') (type: string), COALESCE(_col1,'') (type: string), CASE WHEN ((_col15 = '01')) THEN ('CNY') WHEN ((_col15 = '12')) THEN ('GBP') WHEN ((_col15 = '13')) THEN ('HKD') WHEN ((_col15 = '14')) THEN ('USD') WHEN ((_col15 = '27')) THEN ('JPY') WHEN ((_col15 = '28')) THEN ('CAD') WHEN ((_col15 = '29')) THEN ('AUD') WHEN ((_col15 = '38')) THEN ('EUR') ELSE (_col15) END (type: string), CASE WHEN ((_col15 = '01')) THEN ('人民币') WHEN ((_col15 = '12')) THEN ('英镑') WHEN ((_col15 = '13')) THEN ('港元') WHEN ((_col15 = '14')) THEN ('美元') WHEN ((_col15 = '27')) THEN ('日元') WHEN ((_col15 = '28')) THEN ('加拿大元') WHEN ((_col15 = '29')) THEN ('澳大利亚元') WHEN ((_col15 = '38')) THEN ('欧元') ELSE (_col15) END (type: string), COALESCE(_col20,'') (type: string), COALESCE(_col31,'') (type: string), COALESCE(_col3,'') (type: string), COALESCE(_col4,'') (type: string), COALESCE(_col43,'') (type: string), CASE WHEN (((substr(_col4, 1, 6) = '200425') or (substr(_col4, 1, 6) = '200240'))) THEN ('01') WHEN (((substr(_col4, 1, 4) = '2013') or (substr(_col4, 1, 4) = '2014') or (substr(_col4, 1, 4) = '2002'))) THEN ('01') ELSE ('02') END (type: string), CASE WHEN (((substr(_col4, 1, 6) = '200425') or (substr(_col4, 1, 6) = '200240'))) THEN ('固定利率') WHEN (((substr(_col4, 1, 4) = '2013') or (substr(_col4, 1, 4) = '2014') or (substr(_col4, 1, 4) = '2002'))) THEN ('固定利率') ELSE ('浮动利率') END (type: string), CASE WHEN ((_col3 = 'P01')) THEN ('Y') ELSE ('N') END (type: string), CASE WHEN (((_col17 = '1') or (_col17 = 'A') or (_col17 = 'F'))) THEN ('Y') ELSE ('N') END (type: string), COALESCE(_col5,'') (type: string), COALESCE(_col6,'') (type: string), COALESCE(_col7,'') (type: string), COALESCE(_col66,'') (type: string), COALESCE(_col8,'') (type: string), _col21 (type: string), _col22 (type: string), CASE WHEN ((_col9 = '1')) THEN ('03') WHEN ((_col9 = '0')) THEN ('02') ELSE ('01') END (type: string), CASE WHEN (((substr(_col4, 1, 4) = '2004') or (substr(_col4, 1, 4) = '2002') or (substr(_col4, 1, 4) = '2014'))) THEN ('02') ELSE ('01') END (type: string), COALESCE(_col10,'') (type: string), COALESCE(_col11,0) (type: decimal(17,7)), COALESCE(_col12,'') (type: string), COALESCE(_col13,'') (type: string), COALESCE(_col14,'') (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27, _col28, _col29, _col30 Statistics: Num rows: 35719783 Data size: 27075598502 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 35719783 Data size: 27075598502 Basic stats: COMPLETE Column stats: NONE table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe Map 2 Map Operator Tree: TableScan alias: t6 Statistics: Num rows: 909 Data size: 705820 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: (etl_dt = '2023-01-12') (type: boolean) Statistics: Num rows: 454 Data size: 352521 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: prodcd (type: varchar(30)) sort order: + Map-reduce partition columns: prodcd (type: varchar(30)) Statistics: Num rows: 454 Data size: 352521 Basic stats: COMPLETE Column stats: NONE value expressions: prodna (type: varchar(50)) Map 3 Map Operator Tree: TableScan alias: t7 Statistics: Num rows: 1978 Data size: 1032520 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: itemcd (type: varchar(20)) sort order: + Map-reduce partition columns: itemcd (type: varchar(20)) Statistics: Num rows: 1978 Data size: 1032520 Basic stats: COMPLETE Column stats: NONE value expressions: itemna (type: varchar(80)) Map 4 Map Operator Tree: TableScan alias: t10 Statistics: Num rows: 89 Data size: 250000 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: ((src_tab_en_name = 'cbs_kna_dpac_h') and (src_field_en_name = 'spectp')) (type: boolean) Statistics: Num rows: 22 Data size: 61797 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: src_cd_val (type: varchar(50)) sort order: + Map-reduce partition columns: src_cd_val (type: varchar(50)) Statistics: Num rows: 22 Data size: 61797 Basic stats: COMPLETE Column stats: NONE value expressions: src_cd_val_desc (type: varchar(300))

Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: ListSink内容：This is a Hive query execution plan for a job with three map tasks and a single reduce task. The query involves joining three tables, t1, t6, and t7, and filtering them using various predicates. The final output is written to a file using a file output operator.

Stage-1 is the root stage and has three map tasks (Map 1, Map 2, and Map 3) and a single reduce task. Map 1 reads data from table t1 and performs a left outer join with Map 2 on the prodcd column and with Map 3 on the _col4 column. The output of this join is then joined with Map 4 on the _col17 column. Finally, the resulting data is transformed and written to a file using a file output operator. Map 2 reads data from table t6 and filters it based on a predicate. Map 3 reads data from table t7. Map 4 reads data from table t10 and filters it based on a predicate.

Stage-0 is a single fetch operator that retrieves the final output from the file generated in Stage-1.