hive on tez sql 优化

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

          1

         else

          0

       end as email_verfd_flg,

       case

         when t.valid_mobile_phone_num is not null then

          1

         else

          0

       end as mobl_bding_flg,

       case

         when t.end_user_name like '%@sina%' then

          1

         when t.end_user_name like '%@pingan%' then

          2

         when t.end_user_name like '%@alipay%' and

              not (t2.user_grade in (1, 2) and t2.user_grade_type = 1) then

          3

         when t.end_user_name like '%@alipay%' and t2.user_grade in (1, 2) and

              t2.user_grade_type = 1 then

          4

         when t.end_user_name like '%@163%' then

          5

         when t.end_user_name like '%@kaixin001%' then

          6

         when t.end_user_name like '%@139%' then

          7

         when t.end_user_name like '%@msn.com%' then

          8

         when t.end_user_name like '%@anyue%' then

          9

         when t.end_user_name like '%@qq%' then

          10

         else

          0

       end as commn_login_type,

       case

         when t.end_user_name like '%@b2b%' or t.end_user_name = 'yuxiaolan' then

          1

         else

          0

       end as b2b_flg,

       case

         when t3.send_to is not null then

          1

         else

          0

       end as email_ret_flg,

       case

         when t4.email is not null then

          1

         else

          0

       end as email_blacklist_flg,

       case

         when t5.car = '1' then

          1

         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;

运行速度慢

执行计划如下：

STAGE DEPENDENCIES:

  Stage-1 is a root stage

  Stage-0 depends on stages: Stage-1

STAGE PLANS:

  Stage: Stage-1

    Tez

      Edges:

        Reducer 2 <- Map 1 (SIMPLE_EDGE)

        Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE)

        Reducer 4 <- Map 11 (SIMPLE_EDGE), Map 12 (BROADCAST_EDGE), Reducer 3 (SIMPLE_EDGE)

        Reducer 6 <- Map 5 (SIMPLE_EDGE)

        Reducer 8 <- Map 10 (SIMPLE_EDGE), Map 13 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE)

      DagName: zhoushugang_20150505143131_1821ced5-3723-4094-b6a3-c2aae136cd32:2

      Vertices:

        Map 1 

            Map Operator Tree:

                TableScan

                  alias: yhd_send_message_blacklist

                  Statistics: Num rows: 870860 Data size: 46859629 Basic stats: COMPLETE Column stats: NONE

                  Select Operator

                    expressions: send_to (type: string)

                    outputColumnNames: send_to

                    Statistics: Num rows: 870860 Data size: 46859629 Basic stats: COMPLETE Column stats: NONE

                    Group By Operator

                      keys: lower(send_to) (type: string)

                      mode: hash

                      outputColumnNames: _col0

                      Statistics: Num rows: 870860 Data size: 46859629 Basic stats: COMPLETE Column stats: NONE

                      Reduce Output Operator

                        key expressions: _col0 (type: string)

                        sort order: +

                        Map-reduce partition columns: _col0 (type: string)

                        Statistics: Num rows: 870860 Data size: 46859629 Basic stats: COMPLETE Column stats: NONE

        Map 10 

            Map Operator Tree:

                TableScan

                  alias: t5

                  Statistics: Num rows: 4414357 Data size: 67807378 Basic stats: COMPLETE Column stats: NONE

                  Reduce Output Operator

                    key expressions: end_usr_id (type: bigint)

                    sort order: +

                    Map-reduce partition columns: end_usr_id (type: bigint)

                    Statistics: Num rows: 4414357 Data size: 67807378 Basic stats: COMPLETE Column stats: NONE

                    value expressions: car (type: string), shopping_habit (type: string)

        Map 11 

            Map Operator Tree:

                TableScan

                  alias: t6

                  Statistics: Num rows: 38930100 Data size: 4515891712 Basic stats: COMPLETE Column stats: NONE

                  Filter Operator

                    predicate: user_id is not null (type: boolean)

                    Statistics: Num rows: 19465050 Data size: 2257945856 Basic stats: COMPLETE Column stats: NONE

                    Reduce Output Operator

                      key expressions: email (type: string)

                      sort order: +

                      Map-reduce partition columns: email (type: string)

                      Statistics: Num rows: 19465050 Data size: 2257945856 Basic stats: COMPLETE Column stats: NONE

                      value expressions: mail_level (type: double)

        Map 12 

            Map Operator Tree:

                TableScan

                  alias: t7

                  Statistics: Num rows: 16 Data size: 1752 Basic stats: COMPLETE Column stats: NONE

                  Filter Operator

                    predicate: usr_union_logon_code is not null (type: boolean)

                    Statistics: Num rows: 8 Data size: 876 Basic stats: COMPLETE Column stats: NONE

                    Reduce Output Operator

                      key expressions: usr_union_logon_code (type: string)

                      sort order: +

                      Map-reduce partition columns: usr_union_logon_code (type: string)

                      Statistics: Num rows: 8 Data size: 876 Basic stats: COMPLETE Column stats: NONE

                      value expressions: usr_union_logon_id (type: bigint)

        Map 13 

            Map Operator Tree:

                TableScan

                  alias: t8

                  Statistics: Num rows: 25660729 Data size: 3178607508 Basic stats: COMPLETE Column stats: NONE

                  Filter Operator

                    predicate: (to_date(update_time) = '2015-05-04') (type: boolean)

                    Statistics: Num rows: 12830364 Data size: 1589303692 Basic stats: COMPLETE Column stats: NONE

                    Reduce Output Operator

                      key expressions: end_user_id (type: bigint)

                      sort order: +

                      Map-reduce partition columns: end_user_id (type: bigint)

                      Statistics: Num rows: 12830364 Data size: 1589303692 Basic stats: COMPLETE Column stats: NONE

                      value expressions: exp (type: int)

        Map 5 

            Map Operator Tree:

                TableScan

                  alias: edm_error_email

                  Statistics: Num rows: 5137907 Data size: 513790784 Basic stats: COMPLETE Column stats: NONE

                  Select Operator

                    expressions: email (type: string)

                    outputColumnNames: email

                    Statistics: Num rows: 5137907 Data size: 513790784 Basic stats: COMPLETE Column stats: NONE

                    Group By Operator

                      keys: lower(email) (type: string)

                      mode: hash

                      outputColumnNames: _col0

                      Statistics: Num rows: 5137907 Data size: 513790784 Basic stats: COMPLETE Column stats: NONE

                      Reduce Output Operator

                        key expressions: _col0 (type: string)

                        sort order: +

                        Map-reduce partition columns: _col0 (type: string)

                        Statistics: Num rows: 5137907 Data size: 513790784 Basic stats: COMPLETE Column stats: NONE

        Map 7 

            Map Operator Tree:

                TableScan

                  alias: t

                  Statistics: Num rows: 108659208 Data size: 49700768672 Basic stats: COMPLETE Column 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: COMPLETE Column 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)

        Map 9 

            Map Operator Tree:

                TableScan

                  alias: t2

                  Statistics: Num rows: 11871989 Data size: 422420127 Basic stats: COMPLETE Column stats: NONE

                  Reduce Output Operator

                    key expressions: end_user_id (type: bigint)

                    sort order: +

                    Map-reduce partition columns: end_user_id (type: bigint)

                    Statistics: Num rows: 11871989 Data size: 422420127 Basic stats: COMPLETE Column stats: NONE

                    value expressions: user_grade (type: double), user_grade_type (type: double)

        Reducer 2 

            Reduce Operator Tree:

              Group By Operator

                keys: KEY._col0 (type: string)

                mode: mergepartial

                outputColumnNames: _col0

                Statistics: Num rows: 435430 Data size: 23429814 Basic stats: COMPLETE Column stats: NONE

                Reduce Output Operator

                  key expressions: _col0 (type: string)

                  sort order: +

                  Map-reduce partition columns: _col0 (type: string)

                  Statistics: Num rows: 435430 Data size: 23429814 Basic stats: COMPLETE Column stats: NONE

        Reducer 3 

            Reduce Operator Tree:

              Merge Join Operator

                condition map:

                     Left Outer Join0 to 1

                     Left Outer Join0 to 2

                keys:

                  0 lower(_col107) (type: string)

                  1 _col0 (type: string)

                  2 _col0 (type: string)

                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col22, _col24, _col30, _col31, _col32, _col34, _col44, _col67, _col68, _col69, _col72, _col78, _col83, _col84, _col103, _col107, _col108, _col109, _col110, _col111, _col117, _col118, _col125, _col126, _col131, _col133, _col145, _col146

                Statistics: Num rows: 788865883 Data size: 360827596199 Basic stats: COMPLETE Column stats: NONE

                Reduce Output Operator

                  key expressions: _col107 (type: string)

                  sort order: +

                  Map-reduce partition columns: _col107 (type: string)

                  Statistics: Num rows: 788865883 Data size: 360827596199 Basic stats: COMPLETE Column stats: NONE

                  value expressions: _col0 (type: bigint), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col22 (type: string), _col24 (type: string), _col30 (type: double), _col31 (type: double), _col32 (type: double), _col34 (type: double), _col44 (type: string), _col67 (type: double), _col68 (type: string), _col69 (type: double), _col72 (type: string), _col78 (type: double), _col83 (type: bigint), _col84 (type: string), _col103 (type: double), _col108 (type: string), _col109 (type: string), _col110 (type: int), _col111 (type: string), _col117 (type: double), _col118 (type: double), _col125 (type: string), _col126 (type: string), _col131 (type: bigint), _col133 (type: int), _col145 (type: string), _col146 (type: string)

        Reducer 4 

            Reduce Operator Tree:

              Merge Join Operator

                condition map:

                     Left Outer Join0 to 1

                keys:

                  0 _col107 (type: string)

                  1 email (type: string)

                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col22, _col24, _col30, _col31, _col32, _col34, _col44, _col67, _col68, _col69, _col72, _col78, _col83, _col84, _col103, _col107, _col108, _col109, _col110, _col111, _col117, _col118, _col125, _col126, _col131, _col133, _col145, _col146, _col157

                Statistics: Num rows: 867752490 Data size: 396910364421 Basic stats: COMPLETE Column stats: NONE

                Map Join Operator

                  condition map:

                       Left Outer Join0 to 1

                  keys:

                    0 _col72 (type: string)

                    1 usr_union_logon_code (type: string)

                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col22, _col24, _col30, _col31, _col32, _col34, _col44, _col67, _col68, _col69, _col72, _col78, _col83, _col84, _col103, _col107, _col108, _col109, _col110, _col111, _col117, _col118, _col125, _col126, _col131, _col133, _col145, _col146, _col157, _col163

                  input vertices:

                    1 Map 12

                  Statistics: Num rows: 954527759 Data size: 436601410326 Basic stats: COMPLETE Column stats: NONE

                  Filter Operator

                    predicate: ((_col84 >= '2015-05-04') or (length(_col131) > 0)) (type: boolean)

                    Statistics: Num rows: 636351838 Data size: 291067606274 Basic stats: COMPLETE Column stats: NONE

                    Select Operator

                      expressions: _col0 (type: bigint), _col1 (type: string), _col3 (type: string), _col2 (type: string), _col4 (type: string), _col108 (type: string), _col109 (type: string), _col110 (type: int), _col107 (type: string), substr(_col107, instr(_col107'@'), (instr(_col107'.') - instr(_col107'@'))) (type: string), _col44 (type: string), _col22 (type: string), _col24 (type: string), _col30 (type: double), _col32 (type: double), _col31 (type: double), _col67 (type: double), _col68 (type: string), _col69 (type: double), _col111 (type: string), _col34 (type: double), _col83 (type: bigint), CASE WHEN ((_col78 = 1.0)) THEN (1) ELSE (0) END (type: int), CASE WHEN (_col110 is not null) THEN (1) ELSE (0) END (type: int), CASE WHEN ((_col1 like '%@sina%')) THEN (1) WHEN ((_col1 like '%@pingan%')) THEN (2) WHEN (((_col1 like '%@alipay%') and (not ((_col117) IN (1, 2) and (_col118 = 1.0))))) THEN (3) WHEN ((((_col1 like '%@alipay%') and (_col117) IN (1, 2)) and (_col118 = 1.0))) THEN (4) WHEN ((_col1 like '%@163%')) THEN (5) WHEN ((_col1 like '%@kaixin001%')) THEN (6) WHEN ((_col1 like '%@139%')) THEN (7) WHEN ((_col1 like '%@msn.com%')) THEN (8) WHEN ((_col1 like '%@anyue%')) THEN (9) WHEN ((_col1 like '%@qq%')) THEN (10) ELSE (0) END (type: int), CASE WHEN (((_col1 like '%@b2b%') or (_col1 = 'yuxiaolan'))) THEN (1) ELSE (0) END (type: int), CASE WHEN (_col145 is not null) THEN (1) ELSE (0) END (type: int), CASE WHEN (_col146 is not null) THEN (1) ELSE (0) END (type: int), CASE WHEN ((_col125 = '1')) THEN (1) ELSE (0) END (type: int), _col126 (type: string), _col157 (type: double), '' (type: string), '' (type: string), '' (type: string), '' (type: string), _col103 (type: double), CASE WHEN (_col72 is null) THEN (null) WHEN ((_col72 is not null and _col163 is null)) THEN (-999999) ELSE (_col163) END (type: bigint), _col72 (type: string), _col133 (type: int)

                      outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27, _col28, _col29, _col30, _col31, _col32, _col33, _col34, _col35, _col36, _col37, _col38

                      Statistics: Num rows: 636351838 Data size: 291067606274 Basic stats: COMPLETE Column stats: NONE

                      File Output Operator

                        compressed: false

                        Statistics: Num rows: 636351838 Data size: 291067606274 Basic stats: COMPLETE Column stats: NONE

                        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

        Reducer 6 

            Reduce Operator Tree:

              Group By Operator

                keys: KEY._col0 (type: string)

                mode: mergepartial

                outputColumnNames: _col0

                Statistics: Num rows: 2568953 Data size: 256895341 Basic stats: COMPLETE Column stats: NONE

                Reduce Output Operator

                  key expressions: _col0 (type: string)

                  sort order: +

                  Map-reduce partition columns: _col0 (type: string)

                  Statistics: Num rows: 2568953 Data size: 256895341 Basic stats: COMPLETE Column stats: NONE

        Reducer 8 

            Reduce Operator Tree:

              Merge Join Operator

                condition map:

                     Left Outer Join0 to 1

                     Left Outer Join0 to 2

                     Left Outer Join0 to 3

                keys:

                  0 id (type: bigint)

                  1 end_user_id (type: bigint)

                  2 end_usr_id (type: bigint)

                  3 end_user_id (type: bigint)

                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col22, _col24, _col30, _col31, _col32, _col34, _col44, _col67, _col68, _col69, _col72, _col78, _col83, _col84, _col103, _col107, _col108, _col109, _col110, _col111, _col117, _col118, _col125, _col126, _col131, _col133

                Statistics: Num rows: 358575394 Data size: 164012540172 Basic stats: COMPLETE Column stats: NONE

                Reduce Output Operator

                  key expressions: lower(_col107) (type: string)

                  sort order: +

                  Map-reduce partition columns: lower(_col107) (type: string)

                  Statistics: Num rows: 358575394 Data size: 164012540172 Basic stats: COMPLETE Column stats: NONE

                  value expressions: _col0 (type: bigint), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col22 (type: string), _col24 (type: string), _col30 (type: double), _col31 (type: double), _col32 (type: double), _col34 (type: double), _col44 (type: string), _col67 (type: double), _col68 (type: string), _col69 (type: double), _col72 (type: string), _col78 (type: double), _col83 (type: bigint), _col84 (type: string), _col103 (type: double), _col107 (type: string), _col108 (type: string), _col109 (type: string), _col110 (type: int), _col111 (type: string), _col117 (type: double), _col118 (type: double), _col125 (type: string), _col126 (type: string), _col131 (type: bigint), _col133 (type: int)

  Stage: Stage-0

    Fetch Operator

      limit: -1

      Processor Tree:

        ListSink

Tez 合并了多个join给一个reduce，避免了启动过多的job（见粗体部分）已经有所优化，但是为啥此sql执行慢呢？

-------------------------------------------------------------------------------- 
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED 
-------------------------------------------------------------------------------- 
Map 1 .......... SUCCEEDED 1 1 0 0 0 0 
Map 10 ......... SUCCEEDED 3 3 0 0 0 0 
Map 11 ......... SUCCEEDED 151 151 0 0 0 0 
Map 12 ......... SUCCEEDED 1 1 0 0 0 0 
Map 13 ......... SUCCEEDED 76 76 0 0 0 0 
Map 5 .......... SUCCEEDED 11 11 0 0 0 0 
Map 7 .......... SUCCEEDED 156 156 0 0 0 0 
Map 9 .......... SUCCEEDED 10 10 0 0 0 0 
Reducer 2 ...... SUCCEEDED 1 1 0 0 0 0 
Reducer 3 ..... RUNNING 642 641 1 0 0 0 
Reducer 4 RUNNING 1009 0 89 920 0 0 
Reducer 6 ...... SUCCEEDED 3 3 0 0 0 0 
Reducer 8 ...... SUCCEEDED 203 203 0 0 0 0 
-------------------------------------------------------------------------------- 
VERTICES: 11/13 [==============>>------------] 55% ELAPSED TIME: 307.54 s   

Reduce 3 中有1个任务始终RUNNING

查看SQL中最后的where 条件发现

where t.update_time>= '2015-05-04'   or t8.end_user_id>0;  导致无法提前过滤掉t.update_time< '2015-05-04'的数据，导致后续join产生的reduce数据过大（见上面执行计划蓝色部分），可能产生了数据倾斜，导致任务时长拉长。

去掉or t8.end_user_id>0的时候，end_user   TS返回36219736 行

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),

没有去掉or t8.end_user_id>0的时候，end_user   TS返回108659208行

  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)

业务部门说可以去掉or t8.end_user_id>0 这样就大大减少了 reduce数据的结果集。

最后附上去掉or t8.end_user_id>0的执行计划：

STAGE DEPENDENCIES:

  Stage-1 is a root stage

  Stage-0 depends on stages: Stage-1

STAGE PLANS:

  Stage: Stage-1

    Tez

      Edges:

        Reducer 2 <- Map 1 (SIMPLE_EDGE)

        Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Reducer 6 (SIMPLE_EDGE), Reducer 8 (SIMPLE_EDGE)

        Reducer 4 <- Map 11 (SIMPLE_EDGE), Map 12 (BROADCAST_EDGE), Reducer 3 (SIMPLE_EDGE)

        Reducer 6 <- Map 5 (SIMPLE_EDGE)

        Reducer 8 <- Map 10 (SIMPLE_EDGE), Map 13 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE)

      DagName: zhoushugang_20150505143030_fda7333f-2147-4a65-a63f-7c08c8a3630b:1

      Vertices:

        Map 1 

            Map Operator Tree:

                TableScan

                  alias: yhd_send_message_blacklist

                  Statistics: Num rows: 870860 Data size: 46859629 Basic stats: COMPLETE Column stats: NONE

                  Select Operator

                    expressions: send_to (type: string)

                    outputColumnNames: send_to

                    Statistics: Num rows: 870860 Data size: 46859629 Basic stats: COMPLETE Column stats: NONE

                    Group By Operator

                      keys: lower(send_to) (type: string)

                      mode: hash

                      outputColumnNames: _col0

                      Statistics: Num rows: 870860 Data size: 46859629 Basic stats: COMPLETE Column stats: NONE

                      Reduce Output Operator

                        key expressions: _col0 (type: string)

                        sort order: +

                        Map-reduce partition columns: _col0 (type: string)

                        Statistics: Num rows: 870860 Data size: 46859629 Basic stats: COMPLETE Column stats: NONE

        Map 10 

            Map Operator Tree:

                TableScan

                  alias: t5

                  Statistics: Num rows: 4414357 Data size: 67807378 Basic stats: COMPLETE Column stats: NONE

                  Reduce Output Operator

                    key expressions: end_usr_id (type: bigint)

                    sort order: +

                    Map-reduce partition columns: end_usr_id (type: bigint)

                    Statistics: Num rows: 4414357 Data size: 67807378 Basic stats: COMPLETE Column stats: NONE

                    value expressions: car (type: string), shopping_habit (type: string)

        Map 11 

            Map Operator Tree:

                TableScan

                  alias: t6

                  Statistics: Num rows: 38930100 Data size: 4515891712 Basic stats: COMPLETE Column stats: NONE

                  Filter Operator

                    predicate: user_id is not null (type: boolean)

                    Statistics: Num rows: 19465050 Data size: 2257945856 Basic stats: COMPLETE Column stats: NONE

                    Reduce Output Operator

                      key expressions: email (type: string)

                      sort order: +

                      Map-reduce partition columns: email (type: string)

                      Statistics: Num rows: 19465050 Data size: 2257945856 Basic stats: COMPLETE Column stats: NONE

                      value expressions: mail_level (type: double)

        Map 12 

            Map Operator Tree:

                TableScan

                  alias: t7

                  Statistics: Num rows: 16 Data size: 1752 Basic stats: COMPLETE Column stats: NONE

                  Filter Operator

                    predicate: usr_union_logon_code is not null (type: boolean)

                    Statistics: Num rows: 8 Data size: 876 Basic stats: COMPLETE Column stats: NONE

                    Reduce Output Operator

                      key expressions: usr_union_logon_code (type: string)

                      sort order: +

                      Map-reduce partition columns: usr_union_logon_code (type: string)

                      Statistics: Num rows: 8 Data size: 876 Basic stats: COMPLETE Column stats: NONE

                      value expressions: usr_union_logon_id (type: bigint)

        Map 13 

            Map Operator Tree:

                TableScan

                  alias: t8

                  Statistics: Num rows: 25660729 Data size: 3178607508 Basic stats: COMPLETE Column stats: NONE

                  Filter Operator

                    predicate: (to_date(update_time) = '2015-05-04') (type: boolean)

                    Statistics: Num rows: 12830364 Data size: 1589303692 Basic stats: COMPLETE Column stats: NONE

                    Reduce Output Operator

                      key expressions: end_user_id (type: bigint)

                      sort order: +

                      Map-reduce partition columns: end_user_id (type: bigint)

                      Statistics: Num rows: 12830364 Data size: 1589303692 Basic stats: COMPLETE Column stats: NONE

                      value expressions: exp (type: int)

        Map 5 

            Map Operator Tree:

                TableScan

                  alias: edm_error_email

                  Statistics: Num rows: 5137907 Data size: 513790784 Basic stats: COMPLETE Column stats: NONE

                  Select Operator

                    expressions: email (type: string)

                    outputColumnNames: email

                    Statistics: Num rows: 5137907 Data size: 513790784 Basic stats: COMPLETE Column stats: NONE

                    Group By Operator

                      keys: lower(email) (type: string)

                      mode: hash

                      outputColumnNames: _col0

                      Statistics: Num rows: 5137907 Data size: 513790784 Basic stats: COMPLETE Column stats: NONE

                      Reduce Output Operator

                        key expressions: _col0 (type: string)

                        sort order: +

                        Map-reduce partition columns: _col0 (type: string)

                        Statistics: Num rows: 5137907 Data size: 513790784 Basic stats: COMPLETE Column stats: NONE

        Map 7 

            Map Operator Tree:

                TableScan

                  alias: t

                  Statistics: Num rows: 108659208 Data size: 49700768672 Basic stats: COMPLETE Column stats: NONE

                  Filter Operator

                    predicate: (update_time >= '2015-05-04') (type: boolean)

                    Statistics: Num rows: 36219736 Data size: 16566922890 Basic stats: COMPLETE Column 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: COMPLETE Column 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), 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)

        Map 9 

            Map Operator Tree:

                TableScan

                  alias: t2

                  Statistics: Num rows: 11871989 Data size: 422420127 Basic stats: COMPLETE Column stats: NONE

                  Reduce Output Operator

                    key expressions: end_user_id (type: bigint)

                    sort order: +

                    Map-reduce partition columns: end_user_id (type: bigint)

                    Statistics: Num rows: 11871989 Data size: 422420127 Basic stats: COMPLETE Column stats: NONE

                    value expressions: user_grade (type: double), user_grade_type (type: double)

        Reducer 2 

            Reduce Operator Tree:

              Group By Operator

                keys: KEY._col0 (type: string)

                mode: mergepartial

                outputColumnNames: _col0

                Statistics: Num rows: 435430 Data size: 23429814 Basic stats: COMPLETE Column stats: NONE

                Reduce Output Operator

                  key expressions: _col0 (type: string)

                  sort order: +

                  Map-reduce partition columns: _col0 (type: string)

                  Statistics: Num rows: 435430 Data size: 23429814 Basic stats: COMPLETE Column stats: NONE

        Reducer 3 

            Reduce Operator Tree:

              Merge Join Operator

                condition map:

                     Left Outer Join0 to 1

                     Left Outer Join0 to 2

                keys:

                  0 lower(_col107) (type: string)

                  1 _col0 (type: string)

                  2 _col0 (type: string)

                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col22, _col24, _col30, _col31, _col32, _col34, _col44, _col67, _col68, _col69, _col72, _col78, _col83, _col103, _col107, _col108, _col109, _col110, _col111, _col117, _col118, _col125, _col126, _col133, _col145, _col146

                Statistics: Num rows: 262955293 Data size: 120275865393 Basic stats: COMPLETE Column stats: NONE

                Reduce Output Operator

                  key expressions: _col107 (type: string)

                  sort order: +

                  Map-reduce partition columns: _col107 (type: string)

                  Statistics: Num rows: 262955293 Data size: 120275865393 Basic stats: COMPLETE Column stats: NONE

                  value expressions: _col0 (type: bigint), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col22 (type: string), _col24 (type: string), _col30 (type: double), _col31 (type: double), _col32 (type: double), _col34 (type: double), _col44 (type: string), _col67 (type: double), _col68 (type: string), _col69 (type: double), _col72 (type: string), _col78 (type: double), _col83 (type: bigint), _col103 (type: double), _col108 (type: string), _col109 (type: string), _col110 (type: int), _col111 (type: string), _col117 (type: double), _col118 (type: double), _col125 (type: string), _col126 (type: string), _col133 (type: int), _col145 (type: string), _col146 (type: string)

        Reducer 4 

            Reduce Operator Tree:

              Merge Join Operator

                condition map:

                     Left Outer Join0 to 1

                keys:

                  0 _col107 (type: string)

                  1 email (type: string)

                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col22, _col24, _col30, _col31, _col32, _col34, _col44, _col67, _col68, _col69, _col72, _col78, _col83, _col103, _col107, _col108, _col109, _col110, _col111, _col117, _col118, _col125, _col126, _col133, _col145, _col146, _col157

                Statistics: Num rows: 289250828 Data size: 132303454799 Basic stats: COMPLETE Column stats: NONE

                Map Join Operator

                  condition map:

                       Left Outer Join0 to 1

                  keys:

                    0 _col72 (type: string)

                    1 usr_union_logon_code (type: string)

                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col22, _col24, _col30, _col31, _col32, _col34, _col44, _col67, _col68, _col69, _col72, _col78, _col83, _col103, _col107, _col108, _col109, _col110, _col111, _col117, _col118, _col125, _col126, _col133, _col145, _col146, _col157, _col163

                  input vertices:

                    1 Map 12

                  Statistics: Num rows: 318175917 Data size: 145533803433 Basic stats: COMPLETE Column stats: NONE

                  Select Operator

                    expressions: _col0 (type: bigint), _col1 (type: string), _col3 (type: string), _col2 (type: string), _col4 (type: string), _col108 (type: string), _col109 (type: string), _col110 (type: int), _col107 (type: string), substr(_col107, instr(_col107'@'), (instr(_col107'.') - instr(_col107'@'))) (type: string), _col44 (type: string), _col22 (type: string), _col24 (type: string), _col30 (type: double), _col32 (type: double), _col31 (type: double), _col67 (type: double), _col68 (type: string), _col69 (type: double), _col111 (type: string), _col34 (type: double), _col83 (type: bigint), CASE WHEN ((_col78 = 1.0)) THEN (1) ELSE (0) END (type: int), CASE WHEN (_col110 is not null) THEN (1) ELSE (0) END (type: int), CASE WHEN ((_col1 like '%@sina%')) THEN (1) WHEN ((_col1 like '%@pingan%')) THEN (2) WHEN (((_col1 like '%@alipay%') and (not ((_col117) IN (1, 2) and (_col118 = 1.0))))) THEN (3) WHEN ((((_col1 like '%@alipay%') and (_col117) IN (1, 2)) and (_col118 = 1.0))) THEN (4) WHEN ((_col1 like '%@163%')) THEN (5) WHEN ((_col1 like '%@kaixin001%')) THEN (6) WHEN ((_col1 like '%@139%')) THEN (7) WHEN ((_col1 like '%@msn.com%')) THEN (8) WHEN ((_col1 like '%@anyue%')) THEN (9) WHEN ((_col1 like '%@qq%')) THEN (10) ELSE (0) END (type: int), CASE WHEN (((_col1 like '%@b2b%') or (_col1 = 'yuxiaolan'))) THEN (1) ELSE (0) END (type: int), CASE WHEN (_col145 is not null) THEN (1) ELSE (0) END (type: int), CASE WHEN (_col146 is not null) THEN (1) ELSE (0) END (type: int), CASE WHEN ((_col125 = '1')) THEN (1) ELSE (0) END (type: int), _col126 (type: string), _col157 (type: double), '' (type: string), '' (type: string), '' (type: string), '' (type: string), _col103 (type: double), CASE WHEN (_col72 is null) THEN (null) WHEN ((_col72 is not null and _col163 is null)) THEN (-999999) ELSE (_col163) END (type: bigint), _col72 (type: string), _col133 (type: int)

                    outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27, _col28, _col29, _col30, _col31, _col32, _col33, _col34, _col35, _col36, _col37, _col38

                    Statistics: Num rows: 318175917 Data size: 145533803433 Basic stats: COMPLETE Column stats: NONE

                    File Output Operator

                      compressed: false

                      Statistics: Num rows: 318175917 Data size: 145533803433 Basic stats: COMPLETE Column stats: NONE

                      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

        Reducer 6 

            Reduce Operator Tree:

              Group By Operator

                keys: KEY._col0 (type: string)

                mode: mergepartial

                outputColumnNames: _col0

                Statistics: Num rows: 2568953 Data size: 256895341 Basic stats: COMPLETE Column stats: NONE

                Reduce Output Operator

                  key expressions: _col0 (type: string)

                  sort order: +

                  Map-reduce partition columns: _col0 (type: string)

                  Statistics: Num rows: 2568953 Data size: 256895341 Basic stats: COMPLETE Column stats: NONE

        Reducer 8 

            Reduce Operator Tree:

              Merge Join Operator

                condition map:

                     Left Outer Join0 to 1

                     Left Outer Join0 to 2

                     Left Outer Join0 to 3

                keys:

                  0 id (type: bigint)

                  1 end_user_id (type: bigint)

                  2 end_usr_id (type: bigint)

                  3 end_user_id (type: bigint)

                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col22, _col24, _col30, _col31, _col32, _col34, _col44, _col67, _col68, _col69, _col72, _col78, _col83, _col103, _col107, _col108, _col109, _col110, _col111, _col117, _col118, _col125, _col126, _col133

                Statistics: Num rows: 119525131 Data size: 54670846721 Basic stats: COMPLETE Column stats: NONE

                Reduce Output Operator

                  key expressions: lower(_col107) (type: string)

                  sort order: +

                  Map-reduce partition columns: lower(_col107) (type: string)

                  Statistics: Num rows: 119525131 Data size: 54670846721 Basic stats: COMPLETE Column stats: NONE

                  value expressions: _col0 (type: bigint), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col22 (type: string), _col24 (type: string), _col30 (type: double), _col31 (type: double), _col32 (type: double), _col34 (type: double), _col44 (type: string), _col67 (type: double), _col68 (type: string), _col69 (type: double), _col72 (type: string), _col78 (type: double), _col83 (type: bigint), _col103 (type: double), _col107 (type: string), _col108 (type: string), _col109 (type: string), _col110 (type: int), _col111 (type: string), _col117 (type: double), _col118 (type: double), _col125 (type: string), _col126 (type: string), _col133 (type: int)

  Stage: Stage-0

    Fetch Operator

      limit: -1

      Processor Tree:

        ListSink