You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by "Mostafa Mokhtar (JIRA)" <ji...@apache.org> on 2015/01/14 01:41:34 UTC
[jira] [Updated] (HIVE-9368) Physical optimizer : Join order in
Explain is different from join order provided by Calcite
[ https://issues.apache.org/jira/browse/HIVE-9368?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mostafa Mokhtar updated HIVE-9368:
----------------------------------
Attachment: explain_fetch_column_stats_on.txt
explain_fetch_column_stats_off.txt
> 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
>
> Attachments: explain_fetch_column_stats_off.txt, explain_fetch_column_stats_on.txt
>
>
> 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)