hive 对于join条件如何书写

select
       ${yyyymmdd} as stat_date,
       COALESCE(c.advertiser_id,' ') as advertiser_id,
       COALESCE(c.campaign_id,' ') as campaign_id,
       COALESCE(c.earner_id,' ') as earner_id,
       COALESCE(c.website_id,' ') as website_id,
       COALESCE(c.total_amount, 0) as total_amount,
       COALESCE(c.total_money, 0) as total_money,
       COALESCE(c.sys_total_commision, 0) as sys_total_commision,
       COALESCE(c.order_no,' ') as order_no,
       COALESCE(c.start_date,' ') as start_date,
       COALESCE(yc.time,0) as time,
       COALESCE(yc.ip,' ') as ip,
       COALESCE(yc.refer_url, ' ') as refer_url,
       COALESCE(yc.target_url, ' ') as target_url,
       yc.click_id as click_id
from yiqifa_db.cps_day_med c left outer join  adx_db.yqfclicklog yc

on c.feed_back_key=yc.click_id and yc.click_id <> '' and yc.click_id is not null  and yc.yyyymmdd <= ${yyyymmdd} and  yc.yyyymmdd > ${yyyymmdd2}  
 
where  c.yyyymmdd=${yyyymmdd} and c.feed_back_key <> '' and c.feed_back_key is not null;  

注意：

1.副表的约束条件要写到 on 后面这样在连接的时候才会用上，尤其是分区条件，要不会产生不必要的大量IO。

2.主表的约束条件一定要写到where后面这样才会起作用，要是放到的on后面就会白写了，尤其是分区条件，要不会产生不必要的大量IO。

3.对于多表连接的时候，要格外注意空字段，因为在reduce的时候会将空字段都分到一个reduce上面，这时候你看着reduce数据不多，就是执行不完。多半是空字段造成的。