Hive on tez sql optimization
Select t.id end_usr_id
T.end_user_name end_usr_name
T.end_user_real_name end_usr_nknm
T.end_user_password end_usr_pswd
T.end_user_birthday end_usr_bthd
T.mobile mobl_num
T.phone phone_num
T.valid_mobile_phone_num bindg_mobl_num
T.end_user_email end_usr_email
Substr (t.end_user_email
Instr (t.end_user_email,'@')
Instr (t.end_user_email,'.')-instr (t.end_user_email,'@') email_class
T.end_user_create_time creat_time
T.end_user_last_login_date end_usr_last_login_date
T.end_user_last_bought_date end_usr_last_buy_date
T.end_user_login_times end_usr_login_times
T.end_user_bought_times end_usr_buy_times
T.end_user_bought_amount end_usr_buy_amt
T.end_user_type end_usr_type
T.ip ip
T.end_user_points end_usr_intgl
T.id_card id_card
T.end_user_sex sex
T.mc_site_id mc_site_id
Case
When t.is_email_activate = 1 then
one
Else
0
End as email_verfd_flg
Case
When t.valid_mobile_phone_num is not null then
one
Else
0
End as mobl_bding_flg
Case
When t.end_user_name like'% @ sina%' then
one
When t.end_user_name like'% @ pingan%' then
two
When t.end_user_name like'% @ alipay%' and
Not (t2.user_grade in (1,2) and t2.user_grade_type = 1) then
three
When t.end_user_name like'% @ alipay%' and t2.user_grade in (1,2) and
T2.user_grade_type = 1 then
four
When t.end_user_name like'% @ 163% 'then
five
When t.end_user_name like'% @ kaixin001%' then
six
When t.end_user_name like'% @ 139% 'then
seven
When t.end_user_name like'% @ msn.com%' then
eight
When t.end_user_name like'% @ anyue%' then
nine
When t.end_user_name like'% @ qq%' then
ten
Else
0
End as commn_login_type
Case
When t.end_user_name like'% @ b2B%'or t.end_user_name = 'yuxiaolan' then
one
Else
0
End as b2b_flg
Case
When t3.send_to is not null then
one
Else
0
End as email_ret_flg
Case
When t4.email is not null then
one
Else
0
End as email_blacklist_flg
Case
When t5.car ='1' then
one
Else
0
End as car_flg
T5.shopping_habit shppg_prefr
T6.mail_level email_lvl
'' whsl_flg
'' xh_flg
'' exptn_flg
'' b2b_vip_flg
T.member_grade end_usr_grd_id
Case
When t.co_code is null then
Null
When t.co_code is not null and t7.usr_union_logon_id is null then
-999999
Else
T7.usr_union_logon_id
End usr_union_logon_id
T.co_code usr_union_logon_code
T8.exp end_usr_grow_val
From tandem.end_user t
Left outer join tandem.alipay_user t2
On t.id = t2.end_user_id
Left outer join (select distinct lower (send_to) as send_to
From tandem.yhd_send_message_blacklist) T3
On lower (t.end_user_email) = t3.send_to
Left outer join (select distinct lower (email) as email
From tandem.edm_error_email) T4
On lower (t.end_user_email) = t4.email
Left outer join tmp_end_usr_info t5
On t.id = t5.end_usr_id
Left outer join tandem.crm_user_quality t6
On t.end_user_email = t6.email
And t6.user_id is not null
Left outer join dw.usr_union_logon t7
On t.co_code = t7.usr_union_logon_code
And t7.usr_union_logon_code is not null
Left outer join tandem.member t8
On t.id = t8.end_user_id
And to_date (t8.update_time) = '2015-05-04'
Where t.update_time > = '2015-05-04'
Or length (t8.end_user_id) > 0
Slow running speed
The implementation plan is as follows:
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE PLANS:
Stage: Stage-1
Tez
Edges:
Reducer 2 = '2015-05-04' or t8.end_user_id > 0; t.update_time cannot be filtered out in advance
< '2015-05-04'的数据，导致后续join产生的reduce数据过大（见上面执行计划蓝色部分），可能产生了数据倾斜，导致任务时长拉长。 去掉or t8.end_user_id>At 0, end_user TS returns 36219736 rows
Map 7
MapOperator Tree:
TableScan
Alias: t
Statistics: Num rows: 108659208 Data size: 49700768672 Basic stats: COMPLETEColumn stats: NONE
Filter Operator
Predicate: (update_time > = '2015-05-04') (type: boolean)
Statistics: Num rows: 36219736 Data size: 16566922890 Basic stats: COMPLETEColumn stats: NONE
Reduce Output Operator
Key expressions: id (type: bigint)
Sort order: +
Map-reduce partition columns: id (type: bigint)
Statistics: Num rows: 36219736 Data size: 16566922890 Basic stats: COMPLETEColumn stats: NONE
Value expressions: end_user_name (type: string)
When or t8.end_user_id > 0 is not removed, end_user TS returns 108659208 rows
Map 7
MapOperator Tree:
TableScan
Alias: t
Statistics: Num rows: 108659208 Data size: 49700768672 Basic stats: COMPLETEColumn stats: NONE
Reduce Output Operator
Key expressions: id (type: bigint)
Sort order: +
Map-reduce partition columns: id (type: bigint)
Statistics: Num rows: 108659208 Data size: 49700768672 Basic stats: COMPLETEColumn stats: NONE
Value expressions: end_user_name (type: string), end_user_password (type: string), end_user_real_name (type: string), end_user_birthday (type: string), end_user_last_login_date (type: string), end_user_last_bought_date (type: string), end_user_login_times (type: double), end_user_bought_amount (type: double), end_user_bought_times (type: double), end_user_sex (type: double) End_user_create_time (type:string), end_user_type (type: double), ip (type:string), end_user_points (type: double), co_code (type:string), is_email_activate (type: double), mc_site_id (type: bigint), update_time (type:string), member_grade (type: double), end_user_email (type:string), mobile (type:string), phone (type:string), valid_mobile_phone_num (type: int) Id_card (type: string)
The business unit says that or t8.end_user_id > 0 can be removed, which greatly reduces the result set of reduce data.
Finally, an implementation plan for removing or t8.end_user_id > 0 is attached:
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE PLANS:
Stage: Stage-1
Tez
Edges:
Reducer 2
