| Warning: Shuffle Join MERGEJOIN[112][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 8' 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 10 <- Reducer 8 (SIMPLE_EDGE) |
| Reducer 12 <- Map 11 (SIMPLE_EDGE) |
| Reducer 2 <- Map 1 (SIMPLE_EDGE), Reducer 9 (SIMPLE_EDGE) |
| Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE) |
| Reducer 4 <- Reducer 3 (SIMPLE_EDGE) |
| Reducer 5 <- Map 1 (SIMPLE_EDGE), Reducer 10 (SIMPLE_EDGE) |
| Reducer 7 <- Map 6 (SIMPLE_EDGE) |
| Reducer 8 <- Reducer 12 (CUSTOM_SIMPLE_EDGE), Reducer 7 (CUSTOM_SIMPLE_EDGE) |
| Reducer 9 <- Reducer 8 (SIMPLE_EDGE) |
| |
| Stage-0 |
| Fetch Operator |
| limit:100 |
| Stage-1 |
| Reducer 4 vectorized |
| File Output Operator [FS_150] |
| Limit [LIM_149] (rows=100 width=218) |
| Number of rows:100 |
| Select Operator [SEL_148] (rows=6951 width=218) |
| Output:["_col0","_col1","_col2"] |
| <-Reducer 3 [SIMPLE_EDGE] |
| SHUFFLE [RS_73] |
| Select Operator [SEL_72] (rows=6951 width=218) |
| Output:["_col0","_col1","_col2"] |
| Top N Key Operator [TNK_99] (rows=6951 width=218) |
| keys:_col3,top n:100 |
| Merge Join Operator [MERGEJOIN_116] (rows=6951 width=218) |
| Conds:RS_69._col3=RS_70._col3(Inner),Output:["_col1","_col3","_col5"] |
| <-Reducer 2 [SIMPLE_EDGE] |
| SHUFFLE [RS_69] |
| PartitionCols:_col3 |
| Merge Join Operator [MERGEJOIN_113] (rows=6951 width=111) |
| Conds:RS_119._col0=RS_142._col0(Inner),Output:["_col1","_col3"] |
| <-Map 1 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_119] |
| PartitionCols:_col0 |
| Select Operator [SEL_118] (rows=462000 width=111) |
| Output:["_col0","_col1"] |
| Filter Operator [FIL_117] (rows=462000 width=111) |
| predicate:i_item_sk is not null |
| TableScan [TS_0] (rows=462000 width=111) |
| default@item,i1,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_product_name"] |
| <-Reducer 9 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_142] |
| PartitionCols:_col0 |
| Select Operator [SEL_141] (rows=6951 width=8) |
| Output:["_col0","_col1"] |
| Filter Operator [FIL_140] (rows=6951 width=116) |
| predicate:((rank_window_0 < 11) and _col0 is not null) |
| PTF Operator [PTF_139] (rows=20854 width=116) |
| Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col1 ASC NULLS LAST","partition by:":"0"}] |
| Select Operator [SEL_138] (rows=20854 width=116) |
| Output:["_col0","_col1"] |
| <-Reducer 8 [SIMPLE_EDGE] |
| SHUFFLE [RS_24] |
| PartitionCols:0 |
| Top N Key Operator [TNK_100] (rows=20854 width=228) |
| keys:_col1,top n:11 |
| Filter Operator [FIL_23] (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 12 [CUSTOM_SIMPLE_EDGE] vectorized |
| PARTITION_ONLY_SHUFFLE [RS_137] |
| Select Operator [SEL_136] (rows=1 width=112) |
| Output:["_col0"] |
| Filter Operator [FIL_135] (rows=1 width=120) |
| predicate:(_col1 is not null and _col2 is not null) |
| Select Operator [SEL_134] (rows=1 width=120) |
| Output:["_col1","_col2"] |
| Group By Operator [GBY_133] (rows=1 width=124) |
| Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","count(VALUE._col1)"],keys:KEY._col0 |
| <-Map 11 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_132] |
| PartitionCols:_col0 |
| Group By Operator [GBY_131] (rows=258 width=124) |
| Output:["_col0","_col1","_col2"],aggregations:["sum(_col1)","count(_col1)"],keys:true |
| Select Operator [SEL_130] (rows=287946 width=114) |
| Output:["_col1"] |
| Filter Operator [FIL_129] (rows=287946 width=114) |
| predicate:(ss_hdemo_sk is null and (ss_store_sk = 410)) |
| TableScan [TS_11] (rows=575995635 width=114) |
| default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_hdemo_sk","ss_store_sk","ss_net_profit"] |
| <-Reducer 7 [CUSTOM_SIMPLE_EDGE] vectorized |
| PARTITION_ONLY_SHUFFLE [RS_128] |
| Select Operator [SEL_127] (rows=62562 width=116) |
| Output:["_col0","_col1"] |
| Filter Operator [FIL_126] (rows=62562 width=124) |
| predicate:(_col1 is not null and _col2 is not null) |
| Group By Operator [GBY_125] (rows=62562 width=124) |
| Output:["_col0","_col1","_col2"],aggregations:["sum(VALUE._col0)","count(VALUE._col1)"],keys:KEY._col0 |
| <-Map 6 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_124] |
| PartitionCols:_col0 |
| Group By Operator [GBY_123] (rows=3199976 width=124) |
| Output:["_col0","_col1","_col2"],aggregations:["sum(ss_net_profit)","count(ss_net_profit)"],keys:ss_item_sk |
| Select Operator [SEL_122] (rows=6399952 width=114) |
| Output:["ss_item_sk","ss_net_profit"] |
| Filter Operator [FIL_121] (rows=6399952 width=114) |
| predicate:(ss_store_sk = 410) |
| TableScan [TS_3] (rows=575995635 width=114) |
| default@store_sales,ss1,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_item_sk","ss_store_sk","ss_net_profit"] |
| <-Reducer 5 [SIMPLE_EDGE] |
| SHUFFLE [RS_70] |
| PartitionCols:_col3 |
| Merge Join Operator [MERGEJOIN_115] (rows=6951 width=111) |
| Conds:RS_120._col0=RS_147._col0(Inner),Output:["_col1","_col3"] |
| <-Map 1 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_120] |
| PartitionCols:_col0 |
| Please refer to the previous Select Operator [SEL_118] |
| <-Reducer 10 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_147] |
| PartitionCols:_col0 |
| Select Operator [SEL_146] (rows=6951 width=8) |
| Output:["_col0","_col1"] |
| Filter Operator [FIL_145] (rows=6951 width=116) |
| predicate:((rank_window_0 < 11) and _col0 is not null) |
| PTF Operator [PTF_144] (rows=20854 width=116) |
| Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col1 DESC NULLS LAST","partition by:":"0"}] |
| Select Operator [SEL_143] (rows=20854 width=116) |
| Output:["_col0","_col1"] |
| <-Reducer 8 [SIMPLE_EDGE] |
| SHUFFLE [RS_55] |
| 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_23] |
| |
