| PREHOOK: query: explain |
| select * |
| from (select i_category |
| ,i_class |
| ,i_brand |
| ,i_product_name |
| ,d_year |
| ,d_qoy |
| ,d_moy |
| ,s_store_id |
| ,sumsales |
| ,rank() over (partition by i_category order by sumsales desc) rk |
| from (select i_category |
| ,i_class |
| ,i_brand |
| ,i_product_name |
| ,d_year |
| ,d_qoy |
| ,d_moy |
| ,s_store_id |
| ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales |
| from store_sales |
| ,date_dim |
| ,store |
| ,item |
| where ss_sold_date_sk=d_date_sk |
| and ss_item_sk=i_item_sk |
| and ss_store_sk = s_store_sk |
| and d_month_seq between 1212 and 1212+11 |
| group by rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2 |
| where rk <= 100 |
| order by i_category |
| ,i_class |
| ,i_brand |
| ,i_product_name |
| ,d_year |
| ,d_qoy |
| ,d_moy |
| ,s_store_id |
| ,sumsales |
| ,rk |
| limit 100 |
| PREHOOK: type: QUERY |
| PREHOOK: Input: default@date_dim |
| PREHOOK: Input: default@item |
| PREHOOK: Input: default@store |
| PREHOOK: Input: default@store_sales |
| PREHOOK: Output: hdfs://### HDFS PATH ### |
| POSTHOOK: query: explain |
| select * |
| from (select i_category |
| ,i_class |
| ,i_brand |
| ,i_product_name |
| ,d_year |
| ,d_qoy |
| ,d_moy |
| ,s_store_id |
| ,sumsales |
| ,rank() over (partition by i_category order by sumsales desc) rk |
| from (select i_category |
| ,i_class |
| ,i_brand |
| ,i_product_name |
| ,d_year |
| ,d_qoy |
| ,d_moy |
| ,s_store_id |
| ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales |
| from store_sales |
| ,date_dim |
| ,store |
| ,item |
| where ss_sold_date_sk=d_date_sk |
| and ss_item_sk=i_item_sk |
| and ss_store_sk = s_store_sk |
| and d_month_seq between 1212 and 1212+11 |
| group by rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2 |
| where rk <= 100 |
| order by i_category |
| ,i_class |
| ,i_brand |
| ,i_product_name |
| ,d_year |
| ,d_qoy |
| ,d_moy |
| ,s_store_id |
| ,sumsales |
| ,rk |
| limit 100 |
| POSTHOOK: type: QUERY |
| POSTHOOK: Input: default@date_dim |
| POSTHOOK: Input: default@item |
| POSTHOOK: Input: default@store |
| POSTHOOK: Input: default@store_sales |
| POSTHOOK: Output: hdfs://### HDFS PATH ### |
| Plan optimized by CBO. |
| |
| Vertex dependency in root stage |
| Map 1 <- Reducer 9 (BROADCAST_EDGE) |
| Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) |
| Reducer 3 <- Map 10 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) |
| Reducer 4 <- Map 11 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) |
| Reducer 5 <- Reducer 4 (SIMPLE_EDGE) |
| Reducer 6 <- Reducer 5 (SIMPLE_EDGE) |
| Reducer 7 <- Reducer 6 (SIMPLE_EDGE) |
| Reducer 9 <- Map 8 (CUSTOM_SIMPLE_EDGE) |
| |
| Stage-0 |
| Fetch Operator |
| limit:100 |
| Stage-1 |
| Reducer 7 vectorized |
| File Output Operator [FS_115] |
| Limit [LIM_114] (rows=100 width=617) |
| Number of rows:100 |
| Select Operator [SEL_113] (rows=1575989691 width=617) |
| Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"] |
| <-Reducer 6 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_112] |
| Select Operator [SEL_111] (rows=1575989691 width=617) |
| Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"] |
| Top N Key Operator [TNK_110] (rows=1575989691 width=613) |
| keys:_col2, _col1, _col0, _col3, _col4, _col6, _col5, _col7, _col8, rank_window_0,top n:100 |
| Filter Operator [FIL_109] (rows=1575989691 width=613) |
| predicate:(rank_window_0 <= 100) |
| PTF Operator [PTF_108] (rows=4727969073 width=613) |
| Function definitions:[{},{"name:":"windowingtablefunction","order by:":"_col8 DESC NULLS LAST","partition by:":"_col2"}] |
| Select Operator [SEL_107] (rows=4727969073 width=613) |
| Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"] |
| <-Reducer 5 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_106] |
| PartitionCols:_col2 |
| Select Operator [SEL_105] (rows=4727969073 width=613) |
| Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8"] |
| Top N Key Operator [TNK_104] (rows=4727969073 width=621) |
| PartitionCols:_col2,keys:_col2, _col9,top n:101 |
| Group By Operator [GBY_103] (rows=4727969073 width=621) |
| Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col9"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, KEY._col1, KEY._col2, KEY._col3, KEY._col4, KEY._col5, KEY._col6, KEY._col7, KEY._col8 |
| <-Reducer 4 [SIMPLE_EDGE] |
| SHUFFLE [RS_23] |
| PartitionCols:_col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8 |
| Group By Operator [GBY_22] (rows=4727969073 width=621) |
| Output:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"],aggregations:["sum(_col3)"],keys:_col11, _col12, _col13, _col14, _col5, _col6, _col7, _col9, 0L |
| Merge Join Operator [MERGEJOIN_85] (rows=525329897 width=613) |
| Conds:RS_18._col1=RS_102._col0(Inner),Output:["_col3","_col5","_col6","_col7","_col9","_col11","_col12","_col13","_col14"] |
| <-Map 11 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_102] |
| PartitionCols:_col0 |
| Select Operator [SEL_101] (rows=462000 width=393) |
| Output:["_col0","_col1","_col2","_col3","_col4"] |
| Filter Operator [FIL_100] (rows=462000 width=393) |
| predicate:i_item_sk is not null |
| TableScan [TS_9] (rows=462000 width=393) |
| default@item,item,Tbl:COMPLETE,Col:COMPLETE,Output:["i_item_sk","i_brand","i_class","i_category","i_product_name"] |
| <-Reducer 3 [SIMPLE_EDGE] |
| SHUFFLE [RS_18] |
| PartitionCols:_col1 |
| Merge Join Operator [MERGEJOIN_84] (rows=525329897 width=228) |
| Conds:RS_15._col2=RS_99._col0(Inner),Output:["_col1","_col3","_col5","_col6","_col7","_col9"] |
| <-Map 10 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_99] |
| PartitionCols:_col0 |
| Select Operator [SEL_98] (rows=1704 width=104) |
| Output:["_col0","_col1"] |
| Filter Operator [FIL_97] (rows=1704 width=104) |
| predicate:s_store_sk is not null |
| TableScan [TS_6] (rows=1704 width=104) |
| default@store,store,Tbl:COMPLETE,Col:COMPLETE,Output:["s_store_sk","s_store_id"] |
| <-Reducer 2 [SIMPLE_EDGE] |
| SHUFFLE [RS_15] |
| PartitionCols:_col2 |
| Merge Join Operator [MERGEJOIN_83] (rows=525329897 width=131) |
| Conds:RS_96._col0=RS_88._col0(Inner),Output:["_col1","_col2","_col3","_col5","_col6","_col7"] |
| <-Map 8 [SIMPLE_EDGE] vectorized |
| PARTITION_ONLY_SHUFFLE [RS_88] |
| PartitionCols:_col0 |
| Select Operator [SEL_87] (rows=317 width=16) |
| Output:["_col0","_col1","_col2","_col3"] |
| Filter Operator [FIL_86] (rows=317 width=20) |
| predicate:(d_month_seq BETWEEN 1212 AND 1223 and d_date_sk is not null) |
| TableScan [TS_3] (rows=73049 width=20) |
| default@date_dim,date_dim,Tbl:COMPLETE,Col:COMPLETE,Output:["d_date_sk","d_month_seq","d_year","d_moy","d_qoy"] |
| <-Map 1 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_96] |
| PartitionCols:_col0 |
| Select Operator [SEL_95] (rows=525329897 width=123) |
| Output:["_col0","_col1","_col2","_col3"] |
| Filter Operator [FIL_94] (rows=525329897 width=122) |
| predicate:(ss_sold_date_sk is not null and ss_store_sk is not null and ss_item_sk is not null and ss_sold_date_sk BETWEEN DynamicValue(RS_13_date_dim_d_date_sk_min) AND DynamicValue(RS_13_date_dim_d_date_sk_max) and in_bloom_filter(ss_sold_date_sk, DynamicValue(RS_13_date_dim_d_date_sk_bloom_filter))) |
| TableScan [TS_0] (rows=575995635 width=122) |
| default@store_sales,store_sales,Tbl:COMPLETE,Col:COMPLETE,Output:["ss_sold_date_sk","ss_item_sk","ss_store_sk","ss_quantity","ss_sales_price"] |
| <-Reducer 9 [BROADCAST_EDGE] vectorized |
| BROADCAST [RS_93] |
| Group By Operator [GBY_92] (rows=1 width=12) |
| Output:["_col0","_col1","_col2"],aggregations:["min(VALUE._col0)","max(VALUE._col1)","bloom_filter(VALUE._col2, expectedEntries=1000000)"] |
| <-Map 8 [CUSTOM_SIMPLE_EDGE] vectorized |
| PARTITION_ONLY_SHUFFLE [RS_91] |
| Group By Operator [GBY_90] (rows=1 width=12) |
| Output:["_col0","_col1","_col2"],aggregations:["min(_col0)","max(_col0)","bloom_filter(_col0, expectedEntries=1000000)"] |
| Select Operator [SEL_89] (rows=317 width=4) |
| Output:["_col0"] |
| Please refer to the previous Select Operator [SEL_87] |
| |
