| | Time taken: 0.912 seconds, Fetched: 246 row(s) | hive> explain select i_product_name | > ,i_brand | > ,i_class | > ,i_category | > ,avg(inv_quantity_on_hand) qoh | > from inventory | > ,date_dim | > ,item | > ,warehouse | > where inventory.inv_date_sk=date_dim.d_date_sk | > and inventory.inv_item_sk=item.i_item_sk | > and inventory.inv_warehouse_sk = warehouse.w_warehouse_sk | > and date_dim.d_month_seq between 1193 and 1193 + 11 | > group by i_product_name | > ,i_brand | > ,i_class | > ,i_category with rollup | > order by qoh, i_product_name, i_brand, i_class, i_category | > limit 100; | OK | STAGE DEPENDENCIES: | Stage-1 is a root stage | Stage-0 depends on stages: Stage-1 | | STAGE PLANS: | Stage: Stage-1 | Tez | DagId: rbalamohan_20170216071005_9d054197-9e9e-4bd5-9705-2d8eb2e0595d:4 | Edges: | Map 1 <- Map 4 (BROADCAST_EDGE), Map 5 (BROADCAST_EDGE), Map 7 (BROADCAST_EDGE), Reducer 6 (BROADCAST_EDGE), Reducer 8 (BROADCAST_EDGE) | Reducer 2 <- Map 1 (SIMPLE_EDGE) | Reducer 3 <- Reducer 2 (SIMPLE_EDGE) | Reducer 6 <- Map 5 (CUSTOM_SIMPLE_EDGE) | Reducer 8 <- Map 7 (CUSTOM_SIMPLE_EDGE) | DagName: | Vertices: | Map 1 | Map Operator Tree: | TableScan | alias: inventory | filterExpr: (inv_item_sk is not null and inv_warehouse_sk is not null and inv_warehouse_sk BETWEEN DynamicValue(RS_16_warehouse_inv_warehouse_sk_min) AND DynamicValue(RS_16_warehouse_inv_warehouse_sk_max) and inv_item_sk BETWEEN DynamicValue(RS_19_item_inv_item_sk_min) AND DynamicValue(RS_19_item_inv_item_sk_max) and in_bloom_filter(inv_warehouse_sk, DynamicValue(RS_16_warehouse_inv_warehouse_sk_bloom_filter)) and in_bloom_filter(inv_item_sk, DynamicValue(RS_19_item_inv_item_sk_bloom_filter))) (type: boolean) | Statistics: Num rows: 1311525000 Data size: 36460386728 Basic stats: COMPLETE Column stats: COMPLETE | Filter Operator | predicate: (inv_item_sk is not null and inv_warehouse_sk is not null and inv_warehouse_sk BETWEEN DynamicValue(RS_16_warehouse_inv_warehouse_sk_min) AND DynamicValue(RS_16_warehouse_inv_warehouse_sk_max) and inv_item_sk BETWEEN DynamicValue(RS_19_item_inv_item_sk_min) AND DynamicValue(RS_19_item_inv_item_sk_max) and in_bloom_filter(inv_warehouse_sk, DynamicValue(RS_16_warehouse_inv_warehouse_sk_bloom_filter)) and in_bloom_filter(inv_item_sk, DynamicValue(RS_19_item_inv_item_sk_bloom_filter))) (type: boolean) | Statistics: Num rows: 1311525000 Data size: 36460386728 Basic stats: COMPLETE Column stats: COMPLETE | Select Operator | expressions: inv_item_sk (type: bigint), inv_warehouse_sk (type: bigint), inv_quantity_on_hand (type: int), inv_date_sk (type: bigint) | outputColumnNames: _col0, _col1, _col2, _col3 | Statistics: Num rows: 1311525000 Data size: 36460386728 Basic stats: COMPLETE Column stats: COMPLETE | Map Join Operator | condition map: | Inner Join 0 to 1 | keys: | 0 _col3 (type: bigint) | 1 _col0 (type: bigint) | outputColumnNames: _col0, _col1, _col2 | input vertices: | 1 Map 4 | Statistics: Num rows: 145715025 Data size: 2914300500 Basic stats: COMPLETE Column stats: COMPLETE | Map Join Operator | condition map: | Inner Join 0 to 1 | keys: | 0 _col1 (type: bigint) | 1 _col0 (type: bigint) | outputColumnNames: _col0, _col2 | input vertices: | 1 Map 5 | Statistics: Num rows: 145715025 Data size: 1748580300 Basic stats: COMPLETE Column stats: COMPLETE | Map Join Operator | condition map: | Inner Join 0 to 1 | keys: | 0 _col0 (type: bigint) | 1 UDFToLong(_col0) (type: bigint) | outputColumnNames: _col2, _col8, _col9, _col10, _col11 | input vertices: | 1 Map 7 | Statistics: Num rows: 145715025 Data size: 57266004825 Basic stats: COMPLETE Column stats: COMPLETE | Select Operator | expressions: _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col2 (type: int) | outputColumnNames: _col8, _col9, _col10, _col11, _col2 | Statistics: Num rows: 145715025 Data size: 57266004825 Basic stats: COMPLETE Column stats: COMPLETE | Group By Operator | aggregations: avg(_col2) | keys: _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), 0 (type: int) | mode: hash | outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5 | Statistics: Num rows: 728575125 Data size: 341701733625 Basic stats: COMPLETE Column stats: COMPLETE | Reduce Output Operator | key expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: int) | sort order: +++++ | Map-reduce partition columns: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: int) | Statistics: Num rows: 728575125 Data size: 341701733625 Basic stats: COMPLETE Column stats: COMPLETE | value expressions: _col5 (type: struct) | Execution mode: llap | LLAP IO: all inputs | Map 4 | Map Operator Tree: | TableScan | alias: date_dim | filterExpr: (d_month_seq BETWEEN 1193 AND 1204 and d_date_sk is not null) (type: boolean) | Statistics: Num rows: 73049 Data size: 876588 Basic stats: COMPLETE Column stats: COMPLETE | Filter Operator | predicate: (d_month_seq BETWEEN 1193 AND 1204 and d_date_sk is not null) (type: boolean) | Statistics: Num rows: 8116 Data size: 97392 Basic stats: COMPLETE Column stats: COMPLETE | Select Operator | expressions: d_date_sk (type: bigint) | outputColumnNames: _col0 | Statistics: Num rows: 8116 Data size: 97392 Basic stats: COMPLETE Column stats: COMPLETE | Reduce Output Operator | key expressions: _col0 (type: bigint) | sort order: + | Map-reduce partition columns: _col0 (type: bigint) | Statistics: Num rows: 8116 Data size: 97392 Basic stats: COMPLETE Column stats: COMPLETE | Select Operator | expressions: _col0 (type: bigint) | outputColumnNames: _col0 | Statistics: Num rows: 8116 Data size: 97392 Basic stats: COMPLETE Column stats: COMPLETE | Group By Operator | keys: _col0 (type: bigint) | mode: hash | outputColumnNames: _col0 | Statistics: Num rows: 4058 Data size: 48696 Basic stats: COMPLETE Column stats: COMPLETE | Dynamic Partitioning Event Operator | Target column: inv_date_sk (bigint) | Target Input: inventory | Partition key expr: inv_date_sk | Statistics: Num rows: 4058 Data size: 48696 Basic stats: COMPLETE Column stats: COMPLETE | Target Vertex: Map 1 | Execution mode: vectorized, llap | LLAP IO: all inputs | Map 5 | Map Operator Tree: | TableScan | alias: warehouse | filterExpr: w_warehouse_sk is not null (type: boolean) | Statistics: Num rows: 25 Data size: 200 Basic stats: COMPLETE Column stats: COMPLETE | Filter Operator | predicate: w_warehouse_sk is not null (type: boolean) | Statistics: Num rows: 25 Data size: 200 Basic stats: COMPLETE Column stats: COMPLETE | Select Operator | expressions: w_warehouse_sk (type: bigint) | outputColumnNames: _col0 | Statistics: Num rows: 25 Data size: 200 Basic stats: COMPLETE Column stats: COMPLETE | Reduce Output Operator | key expressions: _col0 (type: bigint) | sort order: + | Map-reduce partition columns: _col0 (type: bigint) | Statistics: Num rows: 25 Data size: 200 Basic stats: COMPLETE Column stats: COMPLETE | Select Operator | expressions: _col0 (type: bigint) | outputColumnNames: _col0 | Statistics: Num rows: 25 Data size: 200 Basic stats: COMPLETE Column stats: COMPLETE | Group By Operator | aggregations: min(_col0), max(_col0), bloom_filter(_col0, expectedEntries=19) | mode: hash | outputColumnNames: _col0, _col1, _col2 | Statistics: Num rows: 1 Data size: 24 Basic stats: COMPLETE Column stats: COMPLETE | Reduce Output Operator | sort order: | Statistics: Num rows: 1 Data size: 24 Basic stats: COMPLETE Column stats: COMPLETE | value expressions: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: binary) | Execution mode: vectorized, llap | LLAP IO: all inputs | Map 7 | Map Operator Tree: | TableScan | alias: item | filterExpr: i_item_sk is not null (type: boolean) | Statistics: Num rows: 402000 Data size: 157986000 Basic stats: COMPLETE Column stats: COMPLETE | Filter Operator | predicate: i_item_sk is not null (type: boolean) | Statistics: Num rows: 402000 Data size: 157986000 Basic stats: COMPLETE Column stats: COMPLETE | Select Operator | expressions: i_item_sk (type: int), i_brand (type: string), i_class (type: string), i_category (type: string), i_product_name (type: string) | outputColumnNames: _col0, _col1, _col2, _col3, _col4 | Statistics: Num rows: 402000 Data size: 157986000 Basic stats: COMPLETE Column stats: COMPLETE | Reduce Output Operator | key expressions: UDFToLong(_col0) (type: bigint) | sort order: + | Map-reduce partition columns: UDFToLong(_col0) (type: bigint) | Statistics: Num rows: 402000 Data size: 157986000 Basic stats: COMPLETE Column stats: COMPLETE | value expressions: _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string) | Select Operator | expressions: UDFToLong(_col0) (type: bigint) | outputColumnNames: _col0 | Statistics: Num rows: 402000 Data size: 157986000 Basic stats: COMPLETE Column stats: COMPLETE | Group By Operator | aggregations: min(_col0), max(_col0), bloom_filter(_col0, expectedEntries=402000) | mode: hash | outputColumnNames: _col0, _col1, _col2 | Statistics: Num rows: 1 Data size: 24 Basic stats: COMPLETE Column stats: COMPLETE | Reduce Output Operator | sort order: | Statistics: Num rows: 1 Data size: 24 Basic stats: COMPLETE Column stats: COMPLETE | value expressions: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: binary) | Execution mode: vectorized, llap | LLAP IO: all inputs | Reducer 2 | Execution mode: llap | Reduce Operator Tree: | Group By Operator | aggregations: avg(VALUE._col0) | keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string), KEY._col3 (type: string), KEY._col4 (type: int) | mode: mergepartial | outputColumnNames: _col0, _col1, _col2, _col3, _col5 | Statistics: Num rows: 728575125 Data size: 292158625125 Basic stats: COMPLETE Column stats: COMPLETE | pruneGroupingSetId: true | Select Operator | expressions: _col3 (type: string), _col0 (type: string), _col1 (type: string), _col2 (type: string), _col5 (type: double) | outputColumnNames: _col0, _col1, _col2, _col3, _col4 | Statistics: Num rows: 728575125 Data size: 289244324625 Basic stats: COMPLETE Column stats: COMPLETE | Reduce Output Operator | key expressions: _col4 (type: double), _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string) | sort order: +++++ | Statistics: Num rows: 728575125 Data size: 289244324625 Basic stats: COMPLETE Column stats: COMPLETE | TopN Hash Memory Usage: 0.04 | Reducer 3 | Execution mode: vectorized, llap | Reduce Operator Tree: | Select Operator | expressions: KEY.reducesinkkey1 (type: string), KEY.reducesinkkey2 (type: string), KEY.reducesinkkey3 (type: string), KEY.reducesinkkey4 (type: string), KEY.reducesinkkey0 (type: double) | outputColumnNames: _col0, _col1, _col2, _col3, _col4 | Statistics: Num rows: 728575125 Data size: 289244324625 Basic stats: COMPLETE Column stats: COMPLETE | Limit | Number of rows: 100 | Statistics: Num rows: 100 Data size: 39700 Basic stats: COMPLETE Column stats: COMPLETE | File Output Operator | compressed: false | Statistics: Num rows: 100 Data size: 39700 Basic stats: COMPLETE Column stats: COMPLETE | 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 | Reducer 6 | Execution mode: vectorized, llap | Reduce Operator Tree: | Group By Operator | aggregations: min(VALUE._col0), max(VALUE._col1), bloom_filter(VALUE._col2, expectedEntries=19) | mode: final | outputColumnNames: _col0, _col1, _col2 | Statistics: Num rows: 1 Data size: 24 Basic stats: COMPLETE Column stats: COMPLETE | Reduce Output Operator | sort order: | Statistics: Num rows: 1 Data size: 24 Basic stats: COMPLETE Column stats: COMPLETE | value expressions: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: binary) | Reducer 8 | Execution mode: vectorized, llap | Reduce Operator Tree: | Group By Operator | aggregations: min(VALUE._col0), max(VALUE._col1), bloom_filter(VALUE._col2, expectedEntries=402000) | mode: final | outputColumnNames: _col0, _col1, _col2 | Statistics: Num rows: 1 Data size: 24 Basic stats: COMPLETE Column stats: COMPLETE | Reduce Output Operator | sort order: | Statistics: Num rows: 1 Data size: 24 Basic stats: COMPLETE Column stats: COMPLETE | value expressions: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: binary) | | Stage: Stage-0 | Fetch Operator | limit: 100 | Processor Tree: | ListSink | | Time taken: 0.79 seconds, Fetched: 246 row(s)