0: jdbc:hive2://localhost:10003/philz_1000> explain 0: jdbc:hive2://localhost:10003/philz_1000> select "Results:26" as sql_26,a.account_type, year(t.effective_date), sum(amount) 0: jdbc:hive2://localhost:10003/philz_1000> from accounts a, transactions t 0: jdbc:hive2://localhost:10003/philz_1000> where a.account_id = t.account_id 0: jdbc:hive2://localhost:10003/philz_1000> and year(t.effective_date) = year(a.effective_date) and month(t.effective_date) = month(a.effective_date) 0: jdbc:hive2://localhost:10003/philz_1000> and year(t.effective_date) = year('2014-04-26') and month(t.effective_date) between month('2014-04-26') and month('2015-04-26')+3 0: jdbc:hive2://localhost:10003/philz_1000> and a.account_type in (select at.account_type from account_types at where at.account_type = 'order ahead') 0: jdbc:hive2://localhost:10003/philz_1000> group by a.account_type, year(t.effective_date); +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ | Explain | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ | Plan optimized by CBO. | | | | Vertex dependency in root stage | | Map 1 <- Map 4 (BROADCAST_EDGE) | | Reducer 2 <- Map 1 (CUSTOM_SIMPLE_EDGE), Map 5 (CUSTOM_SIMPLE_EDGE) | | Reducer 3 <- Reducer 2 (SIMPLE_EDGE) | | | | Stage-0 | | Fetch Operator | | limit:-1 | | Stage-1 | | Reducer 3 vectorized, llap | | File Output Operator [FS_4232] | | compressed:false | | Statistics:Num rows: 83 Data size: 17015 Basic stats: COMPLETE Column stats: COMPLETE | | table:{"input format:":"org.apache.hadoop.mapred.TextInputFormat","output format:":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat","serde:":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"} | | Select Operator [OP_4231] | | outputColumnNames:["_col0","_col1","_col2","_col3"] | | Statistics:Num rows: 83 Data size: 17015 Basic stats: COMPLETE Column stats: COMPLETE | | Group By Operator [OP_4230] | | | aggregations:["sum(VALUE._col0)"] | | | keys:KEY._col0 (type: string), KEY._col1 (type: int) | | | outputColumnNames:["_col0","_col1","_col2"] | | | Statistics:Num rows: 83 Data size: 9213 Basic stats: COMPLETE Column stats: COMPLETE | | |<-Reducer 2 [SIMPLE_EDGE] vectorized, llap | | Reduce Output Operator [RS_4189] | | key expressions:_col0 (type: string), _col1 (type: int) | | Map-reduce partition columns:_col0 (type: string), _col1 (type: int) | | sort order:++ | | Statistics:Num rows: 83 Data size: 9213 Basic stats: COMPLETE Column stats: COMPLETE | | value expressions:_col2 (type: double) | | Group By Operator [OP_4229] | | aggregations:["sum(_col2)"] | | keys:_col0 (type: string), _col1 (type: int) | | outputColumnNames:["_col0","_col1","_col2"] | | Statistics:Num rows: 83 Data size: 9213 Basic stats: COMPLETE Column stats: COMPLETE | | Select Operator [OP_4228] | | outputColumnNames:["_col0","_col1","_col2"] | | Statistics:Num rows: 166 Data size: 26394 Basic stats: COMPLETE Column stats: COMPLETE | | Map Join Operator [MAPJOIN_4227] | | | condition map:[{"":"Inner Join 0 to 1"}] | | | keys:{"Reducer 2":"KEY.reducesinkkey0 (type: bigint), KEY.reducesinkkey1 (type: int), KEY.reducesinkkey2 (type: int)","Map 5":"KEY.reducesinkkey0 (type: bigint), KEY.reducesinkkey1 (type: int), KEY.reducesinkkey2 (type: int)"} | | | outputColumnNames:["_col1","_col3","_col5"] | | | Statistics:Num rows: 166 Data size: 26394 Basic stats: COMPLETE Column stats: COMPLETE | | |<-Map 5 [CUSTOM_SIMPLE_EDGE] vectorized, llap | | | Reduce Output Operator [RS_4226] | | | key expressions:_col1 (type: bigint), year(_col2) (type: int), month(_col2) (type: int) | | | Map-reduce partition columns:_col1 (type: bigint), year(_col2) (type: int), month(_col2) (type: int) | | | sort order:+++ | | | Statistics:Num rows: 74973886 Data size: 5098224248 Basic stats: COMPLETE Column stats: COMPLETE | | | value expressions:_col0 (type: float), _col2 (type: date) | | | Select Operator [OP_4225] | | | outputColumnNames:["_col0","_col1","_col2"] | | | Statistics:Num rows: 74973886 Data size: 5098224248 Basic stats: COMPLETE Column stats: COMPLETE | | | Filter Operator [FIL_4224] | | | predicate:((account_id is not null and month(effective_date) BETWEEN 4 AND 7) and month(effective_date) is not null) (type: boolean) | | | Statistics:Num rows: 74973886 Data size: 5098224248 Basic stats: COMPLETE Column stats: COMPLETE | | | TableScan [TS_4171] | | | alias:t | | | Statistics:Num rows: 149947772 Data size: 10196448496 Basic stats: COMPLETE Column stats: COMPLETE | | |<-Map 1 [CUSTOM_SIMPLE_EDGE] vectorized, llap | | Reduce Output Operator [RS_4223] | | key expressions:_col0 (type: bigint), year(_col2) (type: int), month(_col2) (type: int) | | Map-reduce partition columns:_col0 (type: bigint), year(_col2) (type: int), month(_col2) (type: int) | | sort order:+++ | | Statistics:Num rows: 50289673 Data size: 8197216699 Basic stats: COMPLETE Column stats: COMPLETE | | value expressions:_col1 (type: string) | | Map Join Operator [MAPJOIN_4222] | | | condition map:[{"":"Left Semi Join 0 to 1"}] | | | keys:{"Map 1":"_col1 (type: string)","Map 4":"_col0 (type: string)"} | | | outputColumnNames:["_col0","_col1","_col2"] | | | Statistics:Num rows: 50289673 Data size: 8197216699 Basic stats: COMPLETE Column stats: COMPLETE | | |<-Map 4 [BROADCAST_EDGE] vectorized, llap | | | Reduce Output Operator [RS_4179] | | | key expressions:_col0 (type: string) | | | Map-reduce partition columns:_col0 (type: string) | | | sort order:+ | | | Statistics:Num rows: 1 Data size: 99 Basic stats: COMPLETE Column stats: COMPLETE | | | Group By Operator [OP_4219] | | | keys:_col0 (type: string) | | | outputColumnNames:["_col0"] | | | Statistics:Num rows: 1 Data size: 99 Basic stats: COMPLETE Column stats: COMPLETE | | | Select Operator [OP_4218] | | | outputColumnNames:["_col0"] | | | Statistics:Num rows: 3 Data size: 297 Basic stats: COMPLETE Column stats: COMPLETE | | | Filter Operator [FIL_4217] | | | predicate:(account_type = 'order ahead') (type: boolean) | | | Statistics:Num rows: 3 Data size: 294 Basic stats: COMPLETE Column stats: COMPLETE | | | TableScan [TS_4168] | | | alias:at | | | Statistics:Num rows: 13 Data size: 1274 Basic stats: COMPLETE Column stats: COMPLETE | | |<-Select Operator [OP_4221] | | outputColumnNames:["_col0","_col1","_col2"] | | Statistics:Num rows: 50289673 Data size: 8197216699 Basic stats: COMPLETE Column stats: COMPLETE | | Filter Operator [FIL_4220] | | predicate:(((account_id is not null and (account_type = 'order ahead')) and year(effective_date) is not null) and month(effective_date) is not null) (type: boolean) | | Statistics:Num rows: 50289673 Data size: 8197216699 Basic stats: COMPLETE Column stats: COMPLETE | | TableScan [TS_4165] | | alias:a | | Statistics:Num rows: 201158695 Data size: 32788867285 Basic stats: COMPLETE Column stats: COMPLETE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ | Explain | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ | | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ 0: jdbc:hive2://localhost:10003/philz_1000>