| Warning: Shuffle Join MERGEJOIN[112][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 3' is a cross product |
| PREHOOK: query: explain |
| select asceding.rnk, i1.i_product_name best_performing, i2.i_product_name worst_performing |
| from(select * |
| from (select item_sk,rank() over (order by rank_col asc) rnk |
| from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col |
| from store_sales ss1 |
| where ss_store_sk = 410 |
| group by ss_item_sk |
| having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col |
| from store_sales |
| where ss_store_sk = 410 |
| and ss_hdemo_sk is null |
| group by ss_store_sk))V1)V11 |
| where rnk < 11) asceding, |
| (select * |
| from (select item_sk,rank() over (order by rank_col desc) rnk |
| from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col |
| from store_sales ss1 |
| where ss_store_sk = 410 |
| group by ss_item_sk |
| having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col |
| from store_sales |
| where ss_store_sk = 410 |
| and ss_hdemo_sk is null |
| group by ss_store_sk))V2)V21 |
| where rnk < 11) descending, |
| item i1, |
| item i2 |
| where asceding.rnk = descending.rnk |
| and i1.i_item_sk=asceding.item_sk |
| and i2.i_item_sk=descending.item_sk |
| order by asceding.rnk |
| limit 100 |
| PREHOOK: type: QUERY |
| PREHOOK: Input: default@item |
| PREHOOK: Input: default@store_sales |
| PREHOOK: Output: hdfs://### HDFS PATH ### |
| POSTHOOK: query: explain |
| select asceding.rnk, i1.i_product_name best_performing, i2.i_product_name worst_performing |
| from(select * |
| from (select item_sk,rank() over (order by rank_col asc) rnk |
| from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col |
| from store_sales ss1 |
| where ss_store_sk = 410 |
| group by ss_item_sk |
| having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col |
| from store_sales |
| where ss_store_sk = 410 |
| and ss_hdemo_sk is null |
| group by ss_store_sk))V1)V11 |
| where rnk < 11) asceding, |
| (select * |
| from (select item_sk,rank() over (order by rank_col desc) rnk |
| from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col |
| from store_sales ss1 |
| where ss_store_sk = 410 |
| group by ss_item_sk |
| having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col |
| from store_sales |
| where ss_store_sk = 410 |
| and ss_hdemo_sk is null |
| group by ss_store_sk))V2)V21 |
| where rnk < 11) descending, |
| item i1, |
| item i2 |
| where asceding.rnk = descending.rnk |
| and i1.i_item_sk=asceding.item_sk |
| and i2.i_item_sk=descending.item_sk |
| order by asceding.rnk |
| limit 100 |
| POSTHOOK: type: QUERY |
| POSTHOOK: Input: default@item |
| POSTHOOK: Input: default@store_sales |
| POSTHOOK: Output: hdfs://### HDFS PATH ### |
| Plan optimized by CBO. |
| |
| Vertex dependency in root stage |
| Reducer 11 <- Map 10 (SIMPLE_EDGE) |
| Reducer 2 <- Map 1 (SIMPLE_EDGE) |
| Reducer 3 <- Reducer 11 (CUSTOM_SIMPLE_EDGE), Reducer 2 (CUSTOM_SIMPLE_EDGE) |
| Reducer 4 <- Reducer 3 (SIMPLE_EDGE) |
| Reducer 5 <- Reducer 4 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE) |
| Reducer 6 <- Map 12 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE) |
| Reducer 7 <- Map 12 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE) |
| Reducer 8 <- Reducer 7 (SIMPLE_EDGE) |
| Reducer 9 <- Reducer 3 (SIMPLE_EDGE) |
| |
| Stage-0 |
| Fetch Operator |
| limit:100 |
| Stage-1 |
| Reducer 8 vectorized |
| File Output Operator [FS_149] |
| Limit [LIM_148] (rows=100 width=218) |
| Number of rows:100 |
| Select Operator [SEL_147] (rows=6951 width=218) |
| Output:["_col0","_col1","_col2"] |
| <-Reducer 7 [SIMPLE_EDGE] |
| SHUFFLE [RS_70] |
| Select Operator [SEL_69] (rows=6951 width=218) |
| Output:["_col0","_col1","_col2"] |
| Top N Key Operator [TNK_99] (rows=6951 width=218) |
| keys:_col1,top n:100 |
| Merge Join Operator [MERGEJOIN_116] (rows=6951 width=218) |
| Conds:RS_66._col2=RS_146._col0(Inner),Output:["_col1","_col5","_col7"] |
| <-Map 12 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_146] |
| PartitionCols:_col0 |
| Select Operator [SEL_144] (rows=462000 width=111) |
| Output:["_col0","_col1"] |
| TableScan [TS_56] (rows=462000 width=111) |
| default@item,i1,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_product_name"] |
| <-Reducer 6 [SIMPLE_EDGE] |
| SHUFFLE [RS_66] |
| PartitionCols:_col2 |
| Merge Join Operator [MERGEJOIN_115] (rows=6951 width=115) |
| Conds:RS_63._col0=RS_145._col0(Inner),Output:["_col1","_col2","_col5"] |
| <-Map 12 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_145] |
| PartitionCols:_col0 |
| Please refer to the previous Select Operator [SEL_144] |
| <-Reducer 5 [SIMPLE_EDGE] |
| SHUFFLE [RS_63] |
| PartitionCols:_col0 |
| Merge Join Operator [MERGEJOIN_114] (rows=6951 width=12) |
| Conds:RS_138._col1=RS_143._col1(Inner),Output:["_col0","_col1","_col2"] |
| <-Reducer 4 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_138] |
| PartitionCols:_col1 |
| Select Operator [SEL_137] (rows=6951 width=8) |
| Output:["_col0","_col1"] |
| Filter Operator [FIL_136] (rows=6951 width=116) |
| predicate:(rank_window_0 < 11) |
| PTF Operator [PTF_135] (rows=20854 width=116) |
| Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col1 ASC NULLS LAST","partition by:":"0"}] |
| Select Operator [SEL_134] (rows=20854 width=116) |
| Output:["_col0","_col1"] |
| <-Reducer 3 [SIMPLE_EDGE] |
| SHUFFLE [RS_21] |
| PartitionCols:0 |
| Top N Key Operator [TNK_100] (rows=20854 width=228) |
| keys:_col1,top n:11 |
| Filter Operator [FIL_20] (rows=20854 width=228) |
| predicate:(_col1 > (0.9 * _col2)) |
| Merge Join Operator [MERGEJOIN_112] (rows=62562 width=228) |
| Conds:(Inner),Output:["_col0","_col1","_col2"] |
| <-Reducer 11 [CUSTOM_SIMPLE_EDGE] vectorized |
| PARTITION_ONLY_SHUFFLE [RS_133] |
| Select Operator [SEL_132] (rows=1 width=112) |
| Output:["_col0"] |
| Filter Operator [FIL_131] (rows=1 width=120) |
| predicate:(_col1 is not null and _col2 is not null) |
| Select Operator [SEL_130] (rows=1 width=120) |
| Output:["_col1","_col2"] |
| Group By Operator [GBY_129] (rows=1 width=124) |
| Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","count(VALUE._col1)"],keys:KEY._col0 |
| <-Map 10 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_128] |
| PartitionCols:_col0 |
| Group By Operator [GBY_127] (rows=258 width=124) |
| Output:["_col0","_col1","_col2"],aggregations:["sum(_col1)","count(_col1)"],keys:true |
| Select Operator [SEL_126] (rows=287946 width=114) |
| Output:["_col1"] |
| Filter Operator [FIL_125] (rows=287946 width=114) |
| predicate:(ss_hdemo_sk is null and (ss_store_sk = 410)) |
| TableScan [TS_8] (rows=575995635 width=114) |
| default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_hdemo_sk","ss_store_sk","ss_net_profit"] |
| <-Reducer 2 [CUSTOM_SIMPLE_EDGE] vectorized |
| PARTITION_ONLY_SHUFFLE [RS_124] |
| Select Operator [SEL_123] (rows=62562 width=116) |
| Output:["_col0","_col1"] |
| Filter Operator [FIL_122] (rows=62562 width=124) |
| predicate:(_col1 is not null and _col2 is not null) |
| Group By Operator [GBY_121] (rows=62562 width=124) |
| Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","count(VALUE._col1)"],keys:KEY._col0 |
| <-Map 1 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_120] |
| PartitionCols:_col0 |
| Group By Operator [GBY_119] (rows=3199976 width=124) |
| Output:["_col0","_col1","_col2"],aggregations:["sum(ss_net_profit)","count(ss_net_profit)"],keys:ss_item_sk |
| Select Operator [SEL_118] (rows=6399952 width=114) |
| Output:["ss_item_sk","ss_net_profit"] |
| Filter Operator [FIL_117] (rows=6399952 width=114) |
| predicate:(ss_store_sk = 410) |
| TableScan [TS_0] (rows=575995635 width=114) |
| default@store_sales,ss1,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_item_sk","ss_store_sk","ss_net_profit"] |
| <-Reducer 9 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_143] |
| PartitionCols:_col1 |
| Select Operator [SEL_142] (rows=6951 width=8) |
| Output:["_col0","_col1"] |
| Filter Operator [FIL_141] (rows=6951 width=116) |
| predicate:(rank_window_0 < 11) |
| PTF Operator [PTF_140] (rows=20854 width=116) |
| Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col1 DESC NULLS LAST","partition by:":"0"}] |
| Select Operator [SEL_139] (rows=20854 width=116) |
| Output:["_col0","_col1"] |
| <-Reducer 3 [SIMPLE_EDGE] |
| SHUFFLE [RS_49] |
| PartitionCols:0 |
| Top N Key Operator [TNK_101] (rows=20854 width=228) |
| keys:_col1,top n:11 |
| Please refer to the previous Filter Operator [FIL_20] |
| |
