Mostafa Mokhtar created HIVE-9368:
-------------------------------------
Summary: Physical optimizer : Join order in Explain is different
from join order provided by Calcite
Key: HIVE-9368
URL: https://issues.apache.org/jira/browse/HIVE-9368
Project: Hive
Issue Type: Bug
Components: Physical Optimizer
Affects Versions: 0.14.0
Reporter: Mostafa Mokhtar
Assignee: Vikram Dixit K
Fix For: 0.15.0
Join order in explain is different from that provided by Calcite, this was
observed during the Fidelity POC.
Logical plan from Calcite :
{code}
2015-01-13 18:54:42,892 DEBUG [main]: parse.CalcitePlanner
(CalcitePlanner.java:apply(743)) - Plan After Join Reordering:
HiveProject(scale=[$0], time_key_num=[$1], dataset_code=[$2],
cost_center_lvl1_id=[$3], cost_pool_lvl6_id=[$4], lvl5_id=[$5],
view_lvl1_id=[$6], from_lvl1_id=[$7], plan_id=[$8], client_id=[$9],
lob_id=[$10], product_id=[$11], fprs_lvl5_id=[$12], ssn_id=[$13],
account_id=[$14], mtd_balance=[$15]): rowcount = 2.53152774E8, cumulative cost
= {3.057177094767754E9 rows, 0.0 cpu, 0.0 io}, id = 636
HiveAggregate(group=[{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14}],
agg#0=[SUM($15)]): rowcount = 2.53152774E8, cumulative cost =
{3.057177094767754E9 rows, 0.0 cpu, 0.0 io}, id = 634
HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$24],
$f6=[$6], $f7=[$7], $f8=[$8], $f9=[$9], $f10=[$10], $f11=[$11], $f12=[$21],
$f13=[$18], $f14=[$19], $f15=[*($13, $20)]): rowcount = 3.401053197411791E11,
cumulative cost = {3.057177094767754E9 rows, 0.0 cpu, 0.0 io}, id = 632
HiveProject(scale=[$7], time_key_num=[$8], dataset_code=[$9],
cost_center_lvl1_id=[$10], cost_pool_lvl6_id=[$11], activity_id=[$12],
view_lvl1_id=[$13], from_lvl1_id=[$14], plan_id=[$15], client_id=[$16],
lob_id=[$17], product_id=[$18], fprs_id=[$19], mtd_balance=[$20],
time_key_num0=[$0], activity_id0=[$1], plan_id0=[$2], fprs_id0=[$3],
ssn_id=[$4], account_id=[$5], driver_pct=[$6], lvl5_id=[$25],
current_ind=[$26], fprs_id1=[$27], lvl5_id0=[$21], rollup_key=[$22],
current_ind0=[$23], activity_id1=[$24]): rowcount = 3.401053197411791E11,
cumulative cost = {3.057177094767754E9 rows, 0.0 cpu, 0.0 io}, id = 692
HiveJoin(condition=[AND(AND(AND(=($8, $0), =($15, $2)), =($19, $3)),
=($12, $1))], joinType=[inner]): rowcount = 3.401053197411791E11, cumulative
cost = {3.057177094767754E9 rows, 0.0 cpu, 0.0 io}, id = 690
HiveProject(time_key_num=[$0], activity_id=[$1], plan_id=[$2],
fprs_id=[$3], ssn_id=[$4], account_id=[$5], driver_pct=[$6]): rowcount =
2.926396239E9, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 590
HiveTableScan(table=[[fidelity.fcap_drivers_part_exp_inter_bucket_256]]):
rowcount = 2.926396239E9, cumulative cost = {0}, id = 465
HiveJoin(condition=[=($12, $20)], joinType=[inner]): rowcount =
1.0871372980143067E8, cumulative cost = {2.2067125966323376E7 rows, 0.0 cpu,
0.0 io}, id = 688
HiveJoin(condition=[=($5, $17)], joinType=[inner]): rowcount =
1.4392118216323378E7, cumulative cost = {6880237.75 rows, 0.0 cpu, 0.0 io}, id
= 653
HiveProject(scale=[$0], time_key_num=[$1], dataset_code=[$2],
cost_center_lvl1_id=[$3], cost_pool_lvl6_id=[$4], activity_id=[$5],
view_lvl1_id=[$6], from_lvl1_id=[$7], plan_id=[$8], client_id=[$9],
lob_id=[$10], product_id=[$11], fprs_id=[$12], mtd_balance=[$14]): rowcount =
6870067.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 587
HiveTableScan(table=[[fidelity.fcap_agg_prod_exp_nofund_decimal]]): rowcount =
6870067.0, cumulative cost = {0}, id = 464
HiveProject(lvl5_id=[$36], rollup_key=[$48], current_ind=[$51],
activity_id=[$60]): rowcount = 10170.75, cumulative cost = {0.0 rows, 0.0 cpu,
0.0 io}, id = 628
HiveFilter(condition=[AND(=($51, 'Y'), =($48, 'TOTACT'))]):
rowcount = 10170.75, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 626
HiveTableScan(table=[[fidelity.fobi_activity_dim_mv]]):
rowcount = 40683.0, cumulative cost = {0}, id = 467
HiveProject(lvl5_id=[$36], current_ind=[$51], fprs_id=[$58]):
rowcount = 794770.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 622
HiveFilter(condition=[=($51, 'Y')]): rowcount = 794770.0,
cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 620
HiveTableScan(table=[[fidelity.fobi_fprs_dim_mv_orc]]):
rowcount = 1589540.0, cumulative cost = {0}, id = 466
{code}
Plan #1 with Fetch column stats on
{code}
OK
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-2 depends on stages: Stage-1
Stage-0 depends on stages: Stage-2
Stage-3 depends on stages: Stage-0
STAGE PLANS:
Stage: Stage-1
Tez
Edges:
Map 4 <- Map 5 (BROADCAST_EDGE), Map 6 (BROADCAST_EDGE)
Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 4 (SIMPLE_EDGE)
Reducer 3 <- Reducer 2 (SIMPLE_EDGE)
DagName: mmokhtar_20150113185454_d7ce6ecf-2d50-45ed-8a88-6283bb091b0e:3
Vertices:
Map 1
Map Operator Tree:
TableScan
alias: driver
filterExpr: (((time_key_num is not null and plan_id is not
null) and fprs_id is not null) and activity_id is not null) (type: boolean)
Statistics: Num rows: 2926396239 Data size: 468223398240
Basic stats: COMPLETE Column stats: COMPLETE
Filter Operator
predicate: (((time_key_num is not null and plan_id is not
null) and fprs_id is not null) and activity_id is not null) (type: boolean)
Statistics: Num rows: 2926396239 Data size: 468223398240
Basic stats: COMPLETE Column stats: COMPLETE
Select Operator
expressions: time_key_num (type: bigint), activity_id
(type: bigint), plan_id (type: bigint), fprs_id (type: bigint), ssn_id (type:
bigint), account_id (type: bigint), driver_pct (type: decimal(28,12))
outputColumnNames: _col0, _col1, _col2, _col3, _col4,
_col5, _col6
Statistics: Num rows: 2926396239 Data size: 468223398240
Basic stats: COMPLETE Column stats: COMPLETE
Reduce Output Operator
key expressions: _col0 (type: bigint), _col2 (type:
bigint), _col3 (type: bigint), _col1 (type: bigint)
sort order: ++++
Map-reduce partition columns: _col0 (type: bigint),
_col2 (type: bigint), _col3 (type: bigint), _col1 (type: bigint)
Statistics: Num rows: 2926396239 Data size:
468223398240 Basic stats: COMPLETE Column stats: COMPLETE
value expressions: _col4 (type: bigint), _col5 (type:
bigint), _col6 (type: decimal(28,12))
Execution mode: vectorized
Map 4
Map Operator Tree:
TableScan
alias: balance
filterExpr: (((activity_id is not null and fprs_id is not
null) and time_key_num is not null) and plan_id is not null) (type: boolean)
Statistics: Num rows: 6870067 Data size: 2102240502 Basic
stats: COMPLETE Column stats: COMPLETE
Filter Operator
predicate: (((activity_id is not null and fprs_id is not
null) and time_key_num is not null) and plan_id is not null) (type: boolean)
Statistics: Num rows: 6870067 Data size: 1483934472 Basic
stats: COMPLETE Column stats: COMPLETE
Select Operator
expressions: scale (type: bigint), time_key_num (type:
bigint), dataset_code (type: bigint), cost_center_lvl1_id (type: bigint),
cost_pool_lvl6_id (type: bigint), activity_id (type: bigint), view_lvl1_id
(type: bigint), from_lvl1_id (type: bigint), plan_id (type: bigint), client_id
(type: bigint), lob_id (type: bigint), product_id (type: bigint), fprs_id
(type: bigint), mtd_balance (type: decimal(28,12))
outputColumnNames: _col0, _col1, _col2, _col3, _col4,
_col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13
Statistics: Num rows: 6870067 Data size: 1483934472 Basic
stats: COMPLETE Column stats: COMPLETE
Map Join Operator
condition map:
Inner Join 0 to 1
keys:
0 _col5 (type: bigint)
1 _col3 (type: bigint)
outputColumnNames: _col0, _col1, _col2, _col3, _col4,
_col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14
input vertices:
1 Map 5
Statistics: Num rows: 57555202 Data size: 17151450196
Basic stats: COMPLETE Column stats: COMPLETE
Map Join Operator
condition map:
Inner Join 0 to 1
keys:
0 _col12 (type: bigint)
1 _col2 (type: bigint)
outputColumnNames: _col0, _col1, _col2, _col3, _col4,
_col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14,
_col18
input vertices:
1 Map 6
Statistics: Num rows: 869509350 Data size:
266069861100 Basic stats: COMPLETE Column stats: COMPLETE
Select Operator
expressions: _col0 (type: bigint), _col1 (type:
bigint), _col10 (type: bigint), _col11 (type: bigint), _col12 (type: bigint),
_col13 (type: decimal(28,12)), _col14 (type: string), _col18 (type: bigint),
_col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type:
bigint), _col6 (type: bigint), _col7 (type: bigint), _col8 (type: bigint),
_col9 (type: bigint)
outputColumnNames: _col0, _col1, _col10, _col11,
_col12, _col13, _col14, _col18, _col2, _col3, _col4, _col5, _col6, _col7,
_col8, _col9
Statistics: Num rows: 869509350 Data size:
266069861100 Basic stats: COMPLETE Column stats: COMPLETE
Reduce Output Operator
key expressions: _col1 (type: bigint), _col8
(type: bigint), _col12 (type: bigint), _col5 (type: bigint)
sort order: ++++
Map-reduce partition columns: _col1 (type:
bigint), _col8 (type: bigint), _col12 (type: bigint), _col5 (type: bigint)
Statistics: Num rows: 869509350 Data size:
266069861100 Basic stats: COMPLETE Column stats: COMPLETE
value expressions: _col0 (type: bigint), _col2
(type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col6 (type:
bigint), _col7 (type: bigint), _col9 (type: bigint), _col10 (type: bigint),
_col11 (type: bigint), _col13 (type: decimal(28,12)), _col14 (type: string),
_col18 (type: bigint)
Execution mode: vectorized
Map 5
Map Operator Tree:
TableScan
alias: act
filterExpr: (((current_ind = 'Y') and (rollup_key =
'TOTACT')) and activity_id is not null) (type: boolean)
Statistics: Num rows: 40683 Data size: 271025472 Basic stats:
COMPLETE Column stats: COMPLETE
Filter Operator
predicate: (((current_ind = 'Y') and (rollup_key =
'TOTACT')) and activity_id is not null) (type: boolean)
Statistics: Num rows: 20341 Data size: 5553093 Basic stats:
COMPLETE Column stats: COMPLETE
Select Operator
expressions: lvl5_id (type: string), activity_id (type:
bigint)
outputColumnNames: _col0, _col3
Statistics: Num rows: 20341 Data size: 1993418 Basic
stats: COMPLETE Column stats: COMPLETE
Reduce Output Operator
key expressions: _col3 (type: bigint)
sort order: +
Map-reduce partition columns: _col3 (type: bigint)
Statistics: Num rows: 20341 Data size: 1993418 Basic
stats: COMPLETE Column stats: COMPLETE
value expressions: _col0 (type: string)
Execution mode: vectorized
Map 6
Map Operator Tree:
TableScan
alias: fprs
filterExpr: ((current_ind = 'Y') and fprs_id is not null)
(type: boolean)
Statistics: Num rows: 1589540 Data size: 6862044180 Basic
stats: COMPLETE Column stats: COMPLETE
Filter Operator
predicate: ((current_ind = 'Y') and fprs_id is not null)
(type: boolean)
Statistics: Num rows: 794770 Data size: 80271770 Basic
stats: COMPLETE Column stats: COMPLETE
Select Operator
expressions: lvl5_id (type: bigint), fprs_id (type:
bigint)
outputColumnNames: _col0, _col2
Statistics: Num rows: 794770 Data size: 12716320 Basic
stats: COMPLETE Column stats: COMPLETE
Reduce Output Operator
key expressions: _col2 (type: bigint)
sort order: +
Map-reduce partition columns: _col2 (type: bigint)
Statistics: Num rows: 794770 Data size: 12716320 Basic
stats: COMPLETE Column stats: COMPLETE
value expressions: _col0 (type: bigint)
Execution mode: vectorized
Reducer 2
Reduce Operator Tree:
Merge Join Operator
condition map:
Inner Join 0 to 1
keys:
0 _col0 (type: bigint), _col2 (type: bigint), _col3 (type:
bigint), _col1 (type: bigint)
1 _col1 (type: bigint), _col8 (type: bigint), _col12 (type:
bigint), _col5 (type: bigint)
outputColumnNames: _col4, _col5, _col6, _col7, _col8, _col9,
_col10, _col11, _col13, _col14, _col15, _col16, _col17, _col18, _col20, _col21,
_col25
Statistics: Num rows: 28151027141 Data size: 8389006088018
Basic stats: COMPLETE Column stats: COMPLETE
Select Operator
expressions: _col7 (type: bigint), _col8 (type: bigint),
_col9 (type: bigint), _col10 (type: bigint), _col11 (type: bigint), _col21
(type: string), _col13 (type: bigint), _col14 (type: bigint), _col15 (type:
bigint), _col16 (type: bigint), _col17 (type: bigint), _col18 (type: bigint),
_col25 (type: bigint), _col4 (type: bigint), _col5 (type: bigint), (_col20 *
_col6) (type: decimal(38,24))
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5,
_col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15
Statistics: Num rows: 28151027141 Data size: 8389006088018
Basic stats: COMPLETE Column stats: COMPLETE
Group By Operator
aggregations: sum(_col15)
keys: _col0 (type: bigint), _col1 (type: bigint), _col2
(type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type:
string), _col6 (type: bigint), _col7 (type: bigint), _col8 (type: bigint),
_col9 (type: bigint), _col10 (type: bigint), _col11 (type: bigint), _col12
(type: bigint), _col13 (type: bigint), _col14 (type: bigint)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3, _col4,
_col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14,
_col15
Statistics: Num rows: 28151027141 Data size: 8389006088018
Basic stats: COMPLETE Column stats: COMPLETE
Reduce Output Operator
key expressions: _col0 (type: bigint), _col1 (type:
bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint),
_col5 (type: string), _col6 (type: bigint), _col7 (type: bigint), _col8 (type:
bigint), _col9 (type: bigint), _col10 (type: bigint), _col11 (type: bigint),
_col12 (type: bigint), _col13 (type: bigint), _col14 (type: bigint)
sort order: +++++++++++++++
Map-reduce partition columns: _col0 (type: bigint), _col1
(type: bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type:
bigint), _col5 (type: string), _col6 (type: bigint), _col7 (type: bigint),
_col8 (type: bigint), _col9 (type: bigint), _col10 (type: bigint), _col11
(type: bigint), _col12 (type: bigint), _col13 (type: bigint), _col14 (type:
bigint)
Statistics: Num rows: 28151027141 Data size:
8389006088018 Basic stats: COMPLETE Column stats: COMPLETE
value expressions: _col15 (type: decimal(38,24))
Reducer 3
Reduce Operator Tree:
Group By Operator
aggregations: sum(VALUE._col0)
keys: KEY._col0 (type: bigint), KEY._col1 (type: bigint),
KEY._col2 (type: bigint), KEY._col3 (type: bigint), KEY._col4 (type: bigint),
KEY._col5 (type: string), KEY._col6 (type: bigint), KEY._col7 (type: bigint),
KEY._col8 (type: bigint), KEY._col9 (type: bigint), KEY._col10 (type: bigint),
KEY._col11 (type: bigint), KEY._col12 (type: bigint), KEY._col13 (type:
bigint), KEY._col14 (type: bigint)
mode: mergepartial
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5,
_col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15
Statistics: Num rows: 28151027141 Data size: 8389006088018
Basic stats: COMPLETE Column stats: COMPLETE
Select Operator
expressions: _col0 (type: bigint), _col1 (type: bigint),
_col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint),
UDFToLong(_col5) (type: bigint), _col6 (type: bigint), _col7 (type: bigint),
_col8 (type: bigint), _col9 (type: bigint), _col10 (type: bigint), _col11
(type: bigint), _col12 (type: bigint), _col13 (type: bigint),
UDFToString(_col14) (type: string), CAST( _col15 AS decimal(28,12)) (type:
decimal(28,12))
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5,
_col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15
Statistics: Num rows: 28151027141 Data size: 11260410856400
Basic stats: COMPLETE Column stats: COMPLETE
File Output Operator
compressed: false
Statistics: Num rows: 28151027141 Data size: 11260410856400
Basic stats: COMPLETE Column stats: COMPLETE
table:
input format:
org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
output format:
org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde
name: fidelity.fcap_agg_part_exp_hive_decimal_decimal
Stage: Stage-2
Dependency Collection
Stage: Stage-0
Move Operator
tables:
replace: false
table:
input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
output format: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde
name: fidelity.fcap_agg_part_exp_hive_decimal_decimal
Stage: Stage-3
Stats-Aggr Operator
{code}
Plan #2 with fetch column stats off
{code}STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-2 depends on stages: Stage-1
Stage-0 depends on stages: Stage-2
Stage-3 depends on stages: Stage-0
STAGE PLANS:
Stage: Stage-1
Tez
Edges:
Map 1 <- Map 5 (BROADCAST_EDGE)
Map 3 <- Map 4 (BROADCAST_EDGE)
Map 5 <- Map 3 (BROADCAST_EDGE)
Reducer 2 <- Map 1 (SIMPLE_EDGE)
DagName: mmokhtar_20150113185454_7c350b7b-53fa-4bbb-a747-24ec0c2a6ab7:4
Vertices:
Map 1
Map Operator Tree:
TableScan
alias: driver
filterExpr: (((time_key_num is not null and plan_id is not
null) and fprs_id is not null) and activity_id is not null) (type: boolean)
Statistics: Num rows: 2926396239 Data size: 468223398240
Basic stats: COMPLETE Column stats: NONE
Filter Operator
predicate: (((time_key_num is not null and plan_id is not
null) and fprs_id is not null) and activity_id is not null) (type: boolean)
Statistics: Num rows: 182899765 Data size: 29263962400
Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: time_key_num (type: bigint), activity_id
(type: bigint), plan_id (type: bigint), fprs_id (type: bigint), ssn_id (type:
bigint), account_id (type: bigint), driver_pct (type: decimal(28,12))
outputColumnNames: _col0, _col1, _col2, _col3, _col4,
_col5, _col6
Statistics: Num rows: 182899765 Data size: 29263962400
Basic stats: COMPLETE Column stats: NONE
Map Join Operator
condition map:
Inner Join 0 to 1
keys:
0 _col0 (type: bigint), _col2 (type: bigint), _col3
(type: bigint), _col1 (type: bigint)
1 _col1 (type: bigint), _col8 (type: bigint), _col12
(type: bigint), _col5 (type: bigint)
outputColumnNames: _col4, _col5, _col6, _col7, _col8,
_col9, _col10, _col11, _col13, _col14, _col15, _col16, _col17, _col18, _col20,
_col21, _col25
input vertices:
1 Map 5
Statistics: Num rows: 201189745 Data size: 32190359337
Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: _col7 (type: bigint), _col8 (type:
bigint), _col9 (type: bigint), _col10 (type: bigint), _col11 (type: bigint),
_col21 (type: string), _col13 (type: bigint), _col14 (type: bigint), _col15
(type: bigint), _col16 (type: bigint), _col17 (type: bigint), _col18 (type:
bigint), _col25 (type: bigint), _col4 (type: bigint), _col5 (type: bigint),
(_col20 * _col6) (type: decimal(38,24))
outputColumnNames: _col0, _col1, _col2, _col3, _col4,
_col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14,
_col15
Statistics: Num rows: 201189745 Data size:
32190359337 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: sum(_col15)
keys: _col0 (type: bigint), _col1 (type: bigint),
_col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type:
string), _col6 (type: bigint), _col7 (type: bigint), _col8 (type: bigint),
_col9 (type: bigint), _col10 (type: bigint), _col11 (type: bigint), _col12
(type: bigint), _col13 (type: bigint), _col14 (type: bigint)
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3,
_col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13,
_col14, _col15
Statistics: Num rows: 201189745 Data size:
32190359337 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
key expressions: _col0 (type: bigint), _col1
(type: bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type:
bigint), _col5 (type: string), _col6 (type: bigint), _col7 (type: bigint),
_col8 (type: bigint), _col9 (type: bigint), _col10 (type: bigint), _col11
(type: bigint), _col12 (type: bigint), _col13 (type: bigint), _col14 (type:
bigint)
sort order: +++++++++++++++
Map-reduce partition columns: _col0 (type:
bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: bigint),
_col4 (type: bigint), _col5 (type: string), _col6 (type: bigint), _col7 (type:
bigint), _col8 (type: bigint), _col9 (type: bigint), _col10 (type: bigint),
_col11 (type: bigint), _col12 (type: bigint), _col13 (type: bigint), _col14
(type: bigint)
Statistics: Num rows: 201189745 Data size:
32190359337 Basic stats: COMPLETE Column stats: NONE
value expressions: _col15 (type: decimal(38,24))
Execution mode: vectorized
Map 3
Map Operator Tree:
TableScan
alias: balance
filterExpr: (((activity_id is not null and fprs_id is not
null) and time_key_num is not null) and plan_id is not null) (type: boolean)
Statistics: Num rows: 6870067 Data size: 2102240502 Basic
stats: COMPLETE Column stats: NONE
Filter Operator
predicate: (((activity_id is not null and fprs_id is not
null) and time_key_num is not null) and plan_id is not null) (type: boolean)
Statistics: Num rows: 429380 Data size: 131390279 Basic
stats: COMPLETE Column stats: NONE
Select Operator
expressions: scale (type: bigint), time_key_num (type:
bigint), dataset_code (type: bigint), cost_center_lvl1_id (type: bigint),
cost_pool_lvl6_id (type: bigint), activity_id (type: bigint), view_lvl1_id
(type: bigint), from_lvl1_id (type: bigint), plan_id (type: bigint), client_id
(type: bigint), lob_id (type: bigint), product_id (type: bigint), fprs_id
(type: bigint), mtd_balance (type: decimal(28,12))
outputColumnNames: _col0, _col1, _col2, _col3, _col4,
_col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13
Statistics: Num rows: 429380 Data size: 131390279 Basic
stats: COMPLETE Column stats: NONE
Map Join Operator
condition map:
Inner Join 0 to 1
keys:
0 _col5 (type: bigint)
1 _col3 (type: bigint)
outputColumnNames: _col0, _col1, _col2, _col3, _col4,
_col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14
input vertices:
1 Map 4
Statistics: Num rows: 472318 Data size: 144529310 Basic
stats: COMPLETE Column stats: NONE
Reduce Output Operator
key expressions: _col12 (type: bigint)
sort order: +
Map-reduce partition columns: _col12 (type: bigint)
Statistics: Num rows: 472318 Data size: 144529310
Basic stats: COMPLETE Column stats: NONE
value expressions: _col0 (type: bigint), _col1 (type:
bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint),
_col5 (type: bigint), _col6 (type: bigint), _col7 (type: bigint), _col8 (type:
bigint), _col9 (type: bigint), _col10 (type: bigint), _col11 (type: bigint),
_col13 (type: decimal(28,12)), _col14 (type: string)
Execution mode: vectorized
Map 4
Map Operator Tree:
TableScan
alias: act
filterExpr: (((current_ind = 'Y') and (rollup_key =
'TOTACT')) and activity_id is not null) (type: boolean)
Statistics: Num rows: 40683 Data size: 271025472 Basic stats:
COMPLETE Column stats: NONE
Filter Operator
predicate: (((current_ind = 'Y') and (rollup_key =
'TOTACT')) and activity_id is not null) (type: boolean)
Statistics: Num rows: 5085 Data size: 33875685 Basic stats:
COMPLETE Column stats: NONE
Select Operator
expressions: lvl5_id (type: string), activity_id (type:
bigint)
outputColumnNames: _col0, _col3
Statistics: Num rows: 5085 Data size: 33875685 Basic
stats: COMPLETE Column stats: NONE
Reduce Output Operator
key expressions: _col3 (type: bigint)
sort order: +
Map-reduce partition columns: _col3 (type: bigint)
Statistics: Num rows: 5085 Data size: 33875685 Basic
stats: COMPLETE Column stats: NONE
value expressions: _col0 (type: string)
Execution mode: vectorized
Map 5
Map Operator Tree:
TableScan
alias: fprs
filterExpr: ((current_ind = 'Y') and fprs_id is not null)
(type: boolean)
Statistics: Num rows: 1589540 Data size: 6862044180 Basic
stats: COMPLETE Column stats: NONE
Filter Operator
predicate: ((current_ind = 'Y') and fprs_id is not null)
(type: boolean)
Statistics: Num rows: 397385 Data size: 1715511045 Basic
stats: COMPLETE Column stats: NONE
Select Operator
expressions: lvl5_id (type: bigint), fprs_id (type:
bigint)
outputColumnNames: _col0, _col2
Statistics: Num rows: 397385 Data size: 1715511045 Basic
stats: COMPLETE Column stats: NONE
Map Join Operator
condition map:
Inner Join 0 to 1
keys:
0 _col12 (type: bigint)
1 _col2 (type: bigint)
outputColumnNames: _col0, _col1, _col2, _col3, _col4,
_col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14,
_col18
input vertices:
0 Map 3
Statistics: Num rows: 519549 Data size: 158982244 Basic
stats: COMPLETE Column stats: NONE
Select Operator
expressions: _col0 (type: bigint), _col1 (type:
bigint), _col10 (type: bigint), _col11 (type: bigint), _col12 (type: bigint),
_col13 (type: decimal(28,12)), _col14 (type: string), _col18 (type: bigint),
_col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type:
bigint), _col6 (type: bigint), _col7 (type: bigint), _col8 (type: bigint),
_col9 (type: bigint)
outputColumnNames: _col0, _col1, _col10, _col11,
_col12, _col13, _col14, _col18, _col2, _col3, _col4, _col5, _col6, _col7,
_col8, _col9
Statistics: Num rows: 519549 Data size: 158982244
Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
key expressions: _col1 (type: bigint), _col8 (type:
bigint), _col12 (type: bigint), _col5 (type: bigint)
sort order: ++++
Map-reduce partition columns: _col1 (type: bigint),
_col8 (type: bigint), _col12 (type: bigint), _col5 (type: bigint)
Statistics: Num rows: 519549 Data size: 158982244
Basic stats: COMPLETE Column stats: NONE
value expressions: _col0 (type: bigint), _col2
(type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col6 (type:
bigint), _col7 (type: bigint), _col9 (type: bigint), _col10 (type: bigint),
_col11 (type: bigint), _col13 (type: decimal(28,12)), _col14 (type: string),
_col18 (type: bigint)
Execution mode: vectorized
Reducer 2
Reduce Operator Tree:
Group By Operator
aggregations: sum(VALUE._col0)
keys: KEY._col0 (type: bigint), KEY._col1 (type: bigint),
KEY._col2 (type: bigint), KEY._col3 (type: bigint), KEY._col4 (type: bigint),
KEY._col5 (type: string), KEY._col6 (type: bigint), KEY._col7 (type: bigint),
KEY._col8 (type: bigint), KEY._col9 (type: bigint), KEY._col10 (type: bigint),
KEY._col11 (type: bigint), KEY._col12 (type: bigint), KEY._col13 (type:
bigint), KEY._col14 (type: bigint)
mode: mergepartial
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5,
_col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15
Statistics: Num rows: 100594872 Data size: 16095179588 Basic
stats: COMPLETE Column stats: NONE
Select Operator
expressions: _col0 (type: bigint), _col1 (type: bigint),
_col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint),
UDFToLong(_col5) (type: bigint), _col6 (type: bigint), _col7 (type: bigint),
_col8 (type: bigint), _col9 (type: bigint), _col10 (type: bigint), _col11
(type: bigint), _col12 (type: bigint), _col13 (type: bigint),
UDFToString(_col14) (type: string), CAST( _col15 AS decimal(28,12)) (type:
decimal(28,12))
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5,
_col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15
Statistics: Num rows: 100594872 Data size: 16095179588 Basic
stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 100594872 Data size: 16095179588
Basic stats: COMPLETE Column stats: NONE
table:
input format:
org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
output format:
org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde
name: fidelity.fcap_agg_part_exp_hive_decimal_decimal
Stage: Stage-2
Dependency Collection
Stage: Stage-0
Move Operator
tables:
replace: false
table:
input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
output format: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde
name: fidelity.fcap_agg_part_exp_hive_decimal_decimal
Stage: Stage-3
Stats-Aggr Operator
{code}
Query used
{code}
explain insert INTO table fidelity.FCAP_AGG_PART_EXP_hive_decimal_decimal
SELECT BALANCE.SCALE, BALANCE.TIME_KEY_NUM, BALANCE.DATASET_CODE,
BALANCE.Cost_Center_LVL1_ID, BALANCE.Cost_Pool_LVL6_ID, act.lvl5_ID,
BALANCE.VIEW_LVL1_ID, BALANCE.FROM_LVL1_ID, BALANCE.PLAN_ID, BALANCE.CLIENT_ID,
BALANCE.LOB_ID, BALANCE.PRODUCT_ID, fprs.lvl5_ID fprs_lvl5_id, DRIVER.SSN_ID,
DRIVER.ACCOUNT_ID, SUM ( BALANCE.MTD_BALANCE * DRIVER.DRIVER_PCT) AS
MTD_BALANCE FROM fidelity.FCAP_AGG_PROD_EXP_NOFUND_decimal BALANCE JOIN
fidelity.fcap_drivers_part_exp_inter_bucket_256 DRIVER ON BALANCE.TIME_KEY_NUM
= DRIVER.TIME_KEY_NUM AND BALANCE.PLAN_ID = DRIVER.PLAN_ID AND BALANCE.FPRS_ID
= DRIVER.FPRS_ID AND BALANCE.ACTIVITY_ID = DRIVER.ACTIVITY_ID INNER JOIN
fidelity.fobi_fprs_dim_mv_orc fprs ON balance.FPRS_ID = fprs.fprs_id AND
fprs.current_ind = 'Y' inner join fidelity.fobi_activity_dim_mv act ON
BALANCE.activity_id = act.activity_id AND act.current_ind = 'Y' AND
act.ROLLUP_KEY = 'TOTACT' GROUP BY BALANCE.SCALE, BALANCE.TIME_KEY_NUM,
BALANCE.DATASET_CODE, BALANCE.Cost_center_LVL1_ID, BALANCE.Cost_Pool_LVL6_ID,
act.lvl5_ID, BALANCE.VIEW_LVL1_ID, BALANCE.FROM_LVL1_ID, BALANCE.PLAN_ID,
BALANCE.CLIENT_ID, BALANCE.LOB_ID, BALANCE.PRODUCT_ID, fprs.lvl5_ID,
DRIVER.SSN_ID, DRIVER.ACCOUNT_ID;
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
