hive> create table lpx_mapjoin as
> SELECT '2012-04-17' as stat_date
> ,b.admin_member_id
> ,a.category_level2_id
> ,b.keywords
> ,sum(shownum) as sum_prod_show_cnt
> ,sum(clicknum) as sum_prod_click_cnt
> ,sysdate('yyyy-MM-dd hh:mm:ss')as dw_ins_date
> FROM (SELECT category_id
> ,category_level2_id
> FROM pub_en_category_dimt0_h
> WHERE dw_begin_date <= '2012-04-17'
> AND hp_dw_end_date > '2012-04-17'
> AND category_type = '02'
> ) a
> JOIN (SELECT admin_member_id
> ,lower(trim(regexp_replace(keyword,'[\\\\t|\\\\s]+', ' '))) as keywords
> ,coalesce(shownum,cast(0 as bigint)) as shownum
> ,coalesce(clicknum,cast(0 as bigint)) as clicknum
> ,post_category_id
> FROM idl_en_ctr_click_sdt0
> WHERE hp_stat_date = '2012-04-17'
> AND keyword IS NOT NULL
> ) b
> ON (a.category_id = b.post_category_id)
> GROUP BY b.admin_member_id
> ,a.category_level2_id
> ,b.keywords ;
Total MapReduce jobs = 3
Launching Job 1 out of 3
Number of reduce tasks not specified.Estimated from input data size: 3
In order to change the average loadfor a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum numberof reducers:
set hive.exec.reducers.max=<number>
In order to set a constant number ofreducers:
set mapred.reduce.tasks=<number>
Cannot run job locally: InputSize (= 2511131447) is larger than hive.exec.mode.local.auto.inputbytes.max (= -1)
Warning:Can't find tianshu info:markid missing!
Starting Job = job_201205151343_1062053,Tracking URL = http://hdpjt:50030/jobdetails.jsp?jobid=job_201205151343_1062053
Kill Command = /dhwdata/hadoop/bin/../bin/hadoopjob -Dmapred.job.tracker=hdpjt:9001 -killjob_201205151343_1062053
Hadoop job information for Stage-1: number of mappers: 111; number of reducers: 3
2012-05-25 13:24:05,753 Stage-1 map= 26%, reduce = 0%
2012-05-25 13:24:23,957 Stage-1 map= 77%, reduce = 0%
2012-05-25 13:24:41,265 Stage-1 map= 92%, reduce = 0%
2012-05-25 13:25:00,561 Stage-1 map= 100%, reduce = 14%
2012-05-25 13:25:11,320 Stage-1 map= 100%, reduce = 23%
2012-05-25 13:25:29,255 Stage-1 map= 100%, reduce = 32%
2012-05-25 13:25:48,604 Stage-1 map= 100%, reduce = 57%
2012-05-25 13:26:07,861 Stage-1 map= 100%, reduce = 70%
2012-05-25 13:26:31,523 Stage-1 map= 100%, reduce = 73%
2012-05-25 13:26:54,908 Stage-1 map= 100%, reduce = 77%
20