数据分析高级教程(二)

drop table if exists ods_weblog_origin;create table ods_weblog_origin(valid string,remote_addr string,remote_user string,time_local string,request string,status string,body_bytes_sent string,http_referer string,http_user_agent string)partitioned by (datestr string)row format delimitedfields terminated by '\001';

drop table if exists ods_click_pageviews;create table ods_click_pageviews(Session string,remote_addr string,time_local string,request string,visit_step string,page_staylong string,http_referer string,http_user_agent string,body_bytes_sent string,status string)partitioned by (datestr string)row format delimitedfields terminated by '\001';

drop table dim_time if exists ods_click_pageviews;create table dim_time(year string,month string,day string,hour string)row format delimitedfields terminated by ',';

导入清洗结果数据到贴源数据表ods_weblog_originload data inpath '/weblog/preprocessed/16-02-24-16/' overwrite into table ods_weblog_origin partition(datestr='2013-09-18'); 0: jdbc:hive2://localhost:10000> show partitions ods_weblog_origin;+-------------------+--+| partition |+-------------------+--+| timestr=20151203 |+-------------------+--+ 0: jdbc:hive2://localhost:10000> select count(*) from ods_origin_weblog;+--------+--+| _c0 |+--------+--+| 11347 |+--------+--+ 导入点击流模型pageviews数据到ods_click_pageviews表0: jdbc:hive2://hdp-node-01:10000> load data inpath '/weblog/clickstream/pageviews' overwrite into table ods_click_pageviews partition(datestr='2013-09-18'); 0: jdbc:hive2://hdp-node-01:10000> select count(1) from ods_click_pageviews;+------+--+| _c0 |+------+--+| 66 |+------+--+ 导入点击流模型visit数据到ods_click_visit表

drop table ods_weblog_detail;create table ods_weblog_detail(valid string, --有效标识remote_addr string, --来源IPremote_user string, --用户标识time_local string, --访问完整时间daystr string, --访问日期timestr string, --访问时间month string, --访问月day string, --访问日hour string, --访问时request string, --请求的urlstatus string, --响应码body_bytes_sent string, --传输字节数http_referer string, --来源url[dht1] ref_host string, --来源的hostref_path string, --来源的路径ref_query string, --来源参数queryref_query_id string, --来源参数query的值http_user_agent string --客户终端标识)partitioned by(datestr string);

drop table if exists t_ods_tmp_referurl;create table t_ ods _tmp_referurl asSELECT a.*,b.*FROM ods_origin_weblog a LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') b as host, path, query, query_id;

drop table if exists t_ods_tmp_detail;create table t_ods_tmp_detail as select b.*,substring(time_local,0,10) as daystr,substring(time_local,11) as tmstr,substring(time_local,5,2) as month,substring(time_local,8,2) as day,substring(time_local,11,2) as hourFrom t_ ods _tmp_referurl b;

insert into table zs.ods_weblog_detail partition(datestr='$day_01')select c.valid,c.remote_addr,c.remote_user,c.time_local,substring(c.time_local,0,10) as daystr,substring(c.time_local,12) as tmstr,substring(c.time_local,6,2) as month,substring(c.time_local,9,2) as day,substring(c.time_local,11,3) as hour,c.request,c.status,c.body_bytes_sent,c.http_referer,c.ref_host,c.ref_path,c.ref_query,c.ref_query_id,c.http_user_agentfrom(SELECT a.valid,a.remote_addr,a.remote_user,a.time_local,a.request,a.status,a.body_bytes_sent,a.http_referer,a.http_user_agent,b.ref_host,b.ref_path,b.ref_query,b.ref_query_id FROM zs.ods_weblog_origin a LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') b as ref_host, ref_path, ref_query, ref_query_id) c"0: jdbc:hive2://localhost:10000> show partitions ods_weblog_detail;+---------------------+--+| partition |+---------------------+--+| dd=18%2FSep%2F2013 |+---------------------+--+1 row selected (0.134 seconds)

create table v_time(year string,month string,day string,hour string)row format delimitedfields terminated by ','; load data local inpath '/home/hadoop/v_time.txt' into table v_time;

--计算指定的某个小时pvsselect count(*),month,day,hour from dw_click.ods_weblog_detail group by month,day,hour; --计算该处理批次（一天）中的各小时pvsdrop table dw_pvs_hour;create table dw_pvs_hour(month string,day string,hour string,pvs bigint) partitioned by(datestr string); insert into table dw_pvs_hour partition(datestr='2016-03-18')select a.month as month,a.day as day,a.hour as hour,count(1) as pvs from ods_weblog_detail awhere a.datestr='2016-03-18' group by a.month,a.day,a.hour; 或者用时间维表关联

drop table t_display_pv_month;create table t_display_pv_month (pvs bigint,month string);insert into table t_display_pv_monthselect count(*) as pvs,a.month from t_dim_time ajoin t_ods_detail_prt b on a.month=b.month group by a.month;

select distinct(http_user_agent) from ods_weblog_detail where http_user_agent like '%Mozilla%' limit 200;

drop table t_display_pv_terminal_uc;create table t_display_pv_ terminal_uc (pvs bigint,mm string,dd string,hh string);

drop table t_display_pv_terminal_chrome;create table t_display_pv_ terminal_ chrome (pvs bigint,mm string,dd string,hh string);

drop table t_display_pv_terminal_safari;create table t_display_pv_ terminal_ safari (pvs bigint,mm string,dd string,hh string);

drop table dw_pvs_referer_h;create table dw_pvs_referer_h(referer_url string,referer_host string,month string,day string,hour string,pv_referer_cnt bigint) partitioned by(datestr string); insert into table dw_pvs_referer_h partition(datestr='2016-03-18')select http_referer,ref_host,month,day,hour,count(1) as pv_referer_cntfrom ods_weblog_detail group by http_referer,ref_host,month,day,hour having ref_host is not nullorder by hour asc,day asc,month asc,pv_referer_cnt desc;

drop table dw_ref_host_visit_cnts_h;create table dw_ref_host_visit_cnts_h(ref_host string,month string,day string,hour string,ref_host_cnts bigint) partitioned by(datestr string); insert into table dw_ref_host_visit_cnts_h partition(datestr='2016-03-18')select ref_host,month,day,hour,count(1) as ref_host_cntsfrom ods_weblog_detail group by ref_host,month,day,hour having ref_host is not nullorder by hour asc,day asc,month asc,ref_host_cnts desc;

drop table dw_pvs_refhost_topn_h;create table dw_pvs_refhost_topn_h(hour string,toporder string,ref_host string,ref_host_cnts string) partitioned by(datestr string); insert into table zs.dw_pvs_refhost_topn_h partition(datestr='2016-03-18')select t.hour,t.od,t.ref_host,t.ref_host_cnts from (select ref_host,ref_host_cnts,concat(month,day,hour) as hour,row_number() over (partition by concat(month,day,hour) order by ref_host_cnts desc) as od from zs.dw_ref_host_visit_cnts_h) t where od<=3;

drop table dw_user_dstc_ip_h;create table dw_user_dstc_ip_h(remote_addr string,pvs bigint,hour string); insert into table dw_user_dstc_ip_h select remote_addr,count(1) as pvs,concat(month,day,hour) as hour from ods_weblog_detailWhere datestr='2013-09-18'group by concat(month,day,hour),remote_addr;

select count(1) as dstc_ip_cnts,hour from dw_user_dstc_ip_h group by hour;

练习：统计每小时请求次数topn的独立访客

select remote_addr,count(1) as counts,month from ods_weblog_detailgroup by month,remote_addr;

select remote_addr,count(1) as counts,concat(month,day) as dayfrom ods_weblog_detailWhere dd='18/Sep/2013'group by concat(month,day),remote_addr;

--历日去重访客累积表drop table dw_user_dsct_history;create table dw_user_dsct_history(day string,ip string) partitioned by(datestr string); --每日新用户追加到累计表drop table dw_user_dsct_history;create table dw_user_dsct_history(day string,ip string) partitioned by(datestr string); --每日新用户追加到累计表insert into table dw_user_dsct_history partition(datestr='2013-09-19')select tmp.day as day,tmp.today_addr as new_ip from(select today.day as day,today.remote_addr as today_addr,old.ip as old_addr from (select distinct remote_addr as remote_addr,"2013-09-19" as day from ods_weblog_detail where datestr="2013-09-19") todayleft outer join dw_user_dsct_history oldon today.remote_addr=old.ip) tmpwhere tmp.old_addr is null;

select count(distinct remote_addr) from ods_weblog_detail;-- 1005 select count(1) from dw_user_dsct_history where prtflag_day='18/Sep/2013';--845 select count(1) from dw_user_dsct_history where prtflag_day='19/Sep/2013';--160

类似日粒度算法

drop table dw_user_returning;create table dw_user_returning(day string,remote_addr string,acc_cnt string)partitioned by (datestr string); insert overwrite table dw_user_returning partition(datestr='2013-09-18') select tmp.day,tmp.remote_addr,tmp.acc_cntfrom(select '2013-09-18' as day,remote_addr,count(session) as acc_cnt from click_stream_visit group by remote_addr) tmpwhere tmp.acc_cnt>1;

select sum(pagevisits)/count(distinct remote_addr) from click_stream_visit partition(datestr='2013-09-18');

注：代码略长，见项目工程代码

[hadoop@hdp-node-01 ~]$ hadoop jar weblog.jar cn.itcast.bigdata.hive.mr.UserStayTime /weblog/input /weblog/stayout4--导入hive表("t_display_access_info")中drop table ods_access_info;create table ods_access_info(remote_addr string,firt_req_time string,last_req_time string,stay_long string)partitioned by(prtflag_day string)row format delimitedfields terminated by '\t'; load data inpath '/weblog/stayout4' into table ods_access_info partition(prtflag_day='18/Sep/2013');创建表时stay_long使用的string类型，但是在后续过程中发现还是用bigint更好，进行表修改alter table ods_access_info change column stay_long stay_long bigint;

drop table dw_access_info_dcip;create table dw_access_info_dcip(remote_addr string, firt_req_time string, last_req_time string, stay_long string,acc_counts string)partitioned by(prtflag_day string); insert into table dw_access_dcip partition(prtflag_day='18/Sep/2013')select b.remote_addr,b.firt_req_time,b.last_req_time,b.stay_long,a.acc_counts from (select remote_addr,count(remote_addr) as acc_counts from dw_access_info where prtflag_day='18/Sep/2013' group by remote_addr having acc_counts<2) ajoin dw_access_info bon a.remote_addr = b.remote_addr;

select avg(user_times.counts) as user_access_freqfrom(select remote_addr,counts from t_display_htip union allselect remote_addr,counts from t_display_access_dcip) user_times; --或直接从访问信息表 t_display_access_info 中查询select avg(a.acc_cts) from (select remote_addr,count(*) as acc_cts from dw_access_info group by remote_addr) a;

sqoop export \--connect jdbc:mysql://hdp-node-01:3306/webdb --username root --password root \--table click_stream_visit \--export-dir /user/hive/warehouse/dw_click.db/click_stream_visit/datestr=2013-09-18 \--input-fields-terminated-by '\001'

<workflow-app name="weblogpreprocess" xmlns="uri:oozie:workflow:0.4"><start to="firstjob"/><action name="firstjob"><map-reduce><job-tracker>${jobTracker}</job-tracker><name-node>${nameNode}</name-node><prepare><delete path="${nameNode}/${outpath}"/></prepare><configuration><property><name>mapreduce.job.map.class</name><value>cn.itcast.bigdata.hive.mr.WeblogPreProcess$WeblogPreProcessMapper</value></property> <property><name>mapreduce.job.output.key.class</name><value>org.apache.hadoop.io.Text</value></property><property><name>mapreduce.job.output.value.class</name><value>org.apache.hadoop.io.NullWritable</value></property> <property><name>mapreduce.input.fileinputformat.inputdir</name><value>${inpath}</value></property><property><name>mapreduce.output.fileoutputformat.outputdir</name><value>${outpath}</value></property><property><name>mapred.mapper.new-api</name><value>true</value></property><property><name>mapred.reducer.new-api</name><value>true</value></property> </configuration></map-reduce><ok to="end"/><error to="kill"/>

<workflow-app xmlns="uri:oozie:workflow:0.5" name="hive2-wf"><start to="hive2-node"/> <action name="hive2-node"><hive2 xmlns="uri:oozie:hive2-action:0.1"><job-tracker>${jobTracker}</job-tracker><name-node>${nameNode}</name-node><configuration><property><name>mapred.job.queue.name</name><value>${queueName}</value></property></configuration><jdbc-url>jdbc:hive2://hdp-node-01:10000</jdbc-url><script>script.q</script><param>input=/weblog/outpre2</param></hive2><ok to="end"/><error to="fail"/></action> <kill name="fail"><message>Hive2 (Beeline) action failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message></kill><end name="end"/></workflow-app>

create database if not exists dw_weblog;use dw_weblog;drop table if exists t_orgin_weblog;create table t_orgin_weblog(valid string,remote_addr string,remote_user string,time_local string,request string,status string,body_bytes_sent string,http_referer string,http_user_agent string)row format delimitedfields terminated by '\001';load data inpath '/weblog/preout' overwrite into table t_orgin_weblog; drop table if exists t_ods_detail_tmp_referurl;create table t_ods_detail_tmp_referurl asSELECT a.*,b.*FROM t_orgin_weblog a LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') b as host, path, query, query_id; drop table if exists t_ods_detail;create table t_ods_detail as select b.*,substring(time_local,0,11) as daystr,substring(time_local,13) as tmstr,substring(time_local,4,3) as month,substring(time_local,0,2) as day,substring(time_local,13,2) as hourfrom t_ods_detail_tmp_referurl b; drop table t_ods_detail_prt;create table t_ods_detail_prt(valid string,remote_addr string,remote_user string,time_local string,request string,status string,body_bytes_sent string,http_referer string,http_user_agent string,host string,path string,query string,query_id string,daystr string,tmstr string,month string,day string,hour string) partitioned by (mm string,dd string); insert into table t_ods_detail_prt partition(mm='Sep',dd='18')select * from t_ods_detail where daystr='18/Sep/2013';insert into table t_ods_detail_prt partition(mm='Sep',dd='19')select * from t_ods_detail where daystr='19/Sep/2013';