20120815

`````````````````????

insert overwrite table t_calc_sms_ex_base partition(stat_time='20120807')
select /*+mapjoin(b)*/a.user_name,b.camp_id,b.project_id,b.camp_type,
a.send_time,a.mobile,a.sms_bill_count,a.uid from s_sms a join con_task_info b on(a.user_name=b.user_name and a.task_id=b.task_id and b.stat_time='{CURRENT_DATE}') where a.stat_time='{CURRENT_DATE}';
insert overwrite table t_calc_edm_ex_base partition(stat_time='{CURRENT_DATE}')

a b必须指定马sgfs

````````

explain create table gv_test_tmp

````````````````

hive> desc s_sms;

xxxxxxxxxxxxx11:33

hive> insert  overwrite  table js_sms_uid partition(stat_time='20120807')

    > select DISTINCT user_name,task_id,send_time,uid
    > from s_sms
    > where stat_time='20120807'
    > group by user_name,task_id,send_time;

FAILED: Error in semantic analysis: 5:9 SELECT DISTINCT and GROUP BY can not be in the same query. Error encountered near token 'send_time'

用group by 就可以去重。

xxxxxxxxxxxxxxxx3:09

hive> select user_name,task_id,send_time,count(1),sum(sms_bill_count),uid
    > from s_sms
    > where stat_time='20120807'
    > group by send_time,task_id;
FAILED: Error in semantic analysis: Line 1:7 Expression not in GROUP BY key 'user_name'

xxxxxxxxxxxxxxxxx3:23

hive 中不要出想tab 

select aa.*,bb.uid
from (select a.user_name,a.send_time,sum(a.send_sum),sum(a.bill_sum)
from js_sms_byTaskid_exUid a
where stat_time='20120807'
group by user_name,send_time)aa
join (select b.user_name,b.send_time,count(distinct b.uid) as uid
from js_sms_uid 
where stat_time='20120807'
group by user_name,send_time)bb
on (aa.user_name=bb.user_name and aa.send_time=bb.send_time);