在hive仓库中建贴源数据表

蔚落 2022-06-02 01:48 96阅读 0赞

该项目的数据分析过程在hadoop集群上实现，主要应用hive数据仓库工具，因此，采集并经过预处理后的数据，需要加载到hive数据仓库中，以进行后续的挖掘分析。

**创建原始数据表**

\--在hive仓库中建贴源数据表ods\_weblog\_origin

日志模型贴源数据表ods\_click\_pageviews

日志visit模型表click\_stream\_visit

时间维表创建

<table> 
 <tbody> 
 <tr> 
 <td style="padding: 0px 7px;border-width: 1px;border-color: windowtext;">drop&nbsp;table&nbsp;dim_time&nbsp;if&nbsp;exists&nbsp;ods_click_pageviews;create&nbsp;table&nbsp;dim_time(year&nbsp;string,month&nbsp;string,day&nbsp;string,hour&nbsp;string)row&nbsp;format&nbsp;delimitedfields&nbsp;terminated&nbsp;by&nbsp;',';</td> 
 </tr> 
 </tbody> 
</table>

**导入数据**

<table> 
 <tbody> 
 <tr> 
 <td style="padding: 0px 7px;border-width: 1px;border-color: windowtext;">导入清洗结果数据到贴源数据表ods_weblog_originload&nbsp;data&nbsp;inpath&nbsp;'/weblog/preprocessed/16-02-24-16/'&nbsp;overwrite&nbsp;into&nbsp;table&nbsp;ods_weblog_origin&nbsp;partition(datestr='2013-09-18');&nbsp;0:&nbsp;jdbc:hive2://localhost:10000&gt;&nbsp;show&nbsp;partitions&nbsp;ods_weblog_origin;+-------------------+--+|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;partition&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|+-------------------+--+|&nbsp;timestr=20151203&nbsp;&nbsp;|+-------------------+--+&nbsp;0:&nbsp;jdbc:hive2://localhost:10000&gt;&nbsp;select&nbsp;count(*)&nbsp;from&nbsp;ods_weblog_origin;+--------+--+|&nbsp;&nbsp;_c0&nbsp;&nbsp;&nbsp;|+--------+--+|&nbsp;11347&nbsp;&nbsp;|+--------+--+&nbsp;导入日志模型pageviews数据到ods_click_pageviews表0:&nbsp;jdbc:hive2://hdp-node-01:10000&gt;&nbsp;load&nbsp;data&nbsp;inpath&nbsp;'/weblog/clickstream/pageviews'&nbsp;overwrite&nbsp;into&nbsp;table&nbsp;ods_click_pageviews&nbsp;partition(datestr='2013-09-18');&nbsp;0:&nbsp;jdbc:hive2://hdp-node-01:10000&gt;&nbsp;select&nbsp;count(1)&nbsp;from&nbsp;ods_click_pageviews;+------+--+|&nbsp;_c0&nbsp;&nbsp;|+------+--+|&nbsp;66&nbsp;&nbsp;&nbsp;|+------+--+&nbsp;导入日志模型visit数据到click_stream_visit表0:&nbsp;jdbc:hive2://mini1:10000&gt;&nbsp;Load&nbsp;data&nbsp;local&nbsp;inpath&nbsp;'/weblog/clickstream/visits'&nbsp;overwrite&nbsp;into&nbsp;table&nbsp;click_stream_visit&nbsp;partition(datestr='2013-09-18');&nbsp;</td> 
 </tr> 
 </tbody> 
</table>

**ODS层明细宽表 **

**需求概述**

整个数据分析的过程是按照数据仓库的层次分层进行的，总体来说，是从ODS原始数据中整理出一些中间表（比如，为后续分析方便，将原始数据中的时间、url等非结构化数据作结构化抽取，将各种字段信息进行细化，形成明细表），然后再在中间表的基础之上统计出各种指标数据

**ETL实现**

建表——明细表ods\_weblog\_detail    (源：ods\_weblog\_origin)   （目标：ods\_weblog\_detail）

<table> 
 <tbody> 
 <tr> 
 <td style="padding: 0px 7px;border-width: 1px;border-color: windowtext;">drop&nbsp;table&nbsp;ods_weblog_detail;create&nbsp;table&nbsp;ods_weblog_detail(valid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--有效标识remote_addr&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--来源IPremote_user&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--用户标识time_local&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--访问完整时间daystr&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--访问日期timestr&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--访问时间month&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--访问月day&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--访问日hour&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--访问时request&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--请求的urlstatus&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--响应码body_bytes_sent&nbsp;string,&nbsp;--传输字节数http_referer&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--来源urlref_host&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--来源的hostref_path&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--来源的路径ref_query&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--来源参数queryref_query_id&nbsp;&nbsp;&nbsp;&nbsp;string,&nbsp;--来源参数query的值http_user_agent&nbsp;string&nbsp;--客户终端标识)partitioned&nbsp;by(datestr&nbsp;string);</td> 
 </tr> 
 </tbody> 
</table>

\--抽取refer\_url到中间表  "t\_ods\_tmp\_referurl"

\--将来访url分离出host  path  query  query id

\--抽取转换time\_local字段到中间表明细表 ”t\_ ods \_detail”

<table> 
 <tbody> 
 <tr> 
 <td style="padding: 0px 7px;border-width: 1px;border-color: windowtext;">drop&nbsp;table&nbsp;if&nbsp;exists&nbsp;t_ods_tmp_detail;create&nbsp;table&nbsp;t_ods_tmp_detail&nbsp;as&nbsp;select&nbsp;b.*,substring(time_local,0,10)&nbsp;as&nbsp;daystr,substring(time_local,11)&nbsp;as&nbsp;tmstr,substring(time_local,5,2)&nbsp;as&nbsp;month,substring(time_local,8,2)&nbsp;as&nbsp;day,substring(time_local,11,2)&nbsp;as&nbsp;hourFrom&nbsp;t_&nbsp;ods&nbsp;_tmp_referurl&nbsp;b;</td> 
 </tr> 
 </tbody> 
</table>

以上语句可以改写成：

<table> 
 <tbody> 
 <tr> 
 <td style="padding: 0px 7px;border-width: 1px;border-color: windowtext;">insert&nbsp;into&nbsp;table&nbsp;zs.ods_weblog_detail&nbsp;partition(datestr='2013-09-18')select&nbsp;c.valid,c.remote_addr,c.remote_user,c.time_local,substring&nbsp;(c.time_local,0,10)&nbsp;as&nbsp;daystr,substring(c.time_local,12)&nbsp;as&nbsp;tmstr,substring(c.time_local,6,2)&nbsp;as&nbsp;month,substring(c.time_local,9,2)&nbsp;as&nbsp;day,substring(c.time_local,11,3)&nbsp;as&nbsp;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&nbsp;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&nbsp;FROM&nbsp;zs.ods_weblog_origin&nbsp;a&nbsp;LATERAL&nbsp;VIEW&nbsp;parse_url_tuple&nbsp;(regexp_replace(http_referer,&nbsp;"\"",&nbsp;""),&nbsp;'HOST',&nbsp;'PATH','QUERY',&nbsp;'QUERY:id')&nbsp;b&nbsp;as&nbsp;ref_host,&nbsp;ref_path,&nbsp;ref_query,&nbsp;ref_query_id)&nbsp;c&nbsp;&nbsp;0:&nbsp;jdbc:hive2://localhost:10000&gt;&nbsp;show&nbsp;partitions&nbsp;ods_weblog_detail;+---------------------+--+|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;partition&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|+---------------------+--+|&nbsp;dd=18%2FSep%2F2013&nbsp;&nbsp;|+---------------------+--+1&nbsp;row&nbsp;selected&nbsp;(0.134&nbsp;seconds)&nbsp;</td> 
 </tr> 
 </tbody> 
</table>

**统计分析**

注：每一种统计指标都可以跟各维度表进行叉乘，从而得出各个维度的统计结果

篇幅限制，叉乘的代码及注释信息详见项目工程代码文件 。

为了在前端展示时速度更快，每一个指标都事先算出各维度结果存入mysql。

提前准备好维表数据，在hive仓库中创建相应维表，如：

时间维表：

<table> 
 <tbody> 
 <tr> 
 <td style="padding: 0px 7px;border-width: 1px;border-color: windowtext;">create&nbsp;table&nbsp;v_time(year&nbsp;string,month&nbsp;string,day&nbsp;string,hour&nbsp;string)row&nbsp;format&nbsp;delimitedfields&nbsp;terminated&nbsp;by&nbsp;',';&nbsp;load&nbsp;data&nbsp;local&nbsp;inpath&nbsp;'/home/hadoop/v_time.txt'&nbsp;into&nbsp;table&nbsp;v_time;</td> 
 </tr> 
 </tbody> 
</table>

在实际生产中，究竟需要哪些统计指标通常由相关数据需求部门人员提出，而且会不断有新的统计需求产生，以下为网站流量分析中的一些典型指标示例。

**PV统计**

**多维统计PV总量**

**1. 时间维度**

<table> 
 <tbody> 
 <tr> 
 <td style="padding: 0px 7px;border-width: 1px;border-color: windowtext;">--计算指定的某个小时pvsselect&nbsp;count(*),month,day,hour&nbsp;from&nbsp;dw_click.ods_weblog_detail&nbsp;group&nbsp;by&nbsp;month,day,hour;&nbsp;&nbsp;--计算该处理批次（一天）中的各小时pvsdrop&nbsp;table&nbsp;dw_pvs_hour;create&nbsp;table&nbsp;dw_pvs_hour(month&nbsp;string,day&nbsp;string,hour&nbsp;string,pvs&nbsp;bigint)&nbsp;partitioned&nbsp;by(datestr&nbsp;string);&nbsp;insert&nbsp;into&nbsp;table&nbsp;dw_pvs_hour&nbsp;partition(datestr='2016-03-18')select&nbsp;a.month&nbsp;as&nbsp;month,a.day&nbsp;as&nbsp;day,a.hour&nbsp;as&nbsp;hour,count(1)&nbsp;as&nbsp;pvs&nbsp;from&nbsp;ods_weblog_detail&nbsp;awhere&nbsp;&nbsp;a.datestr='2016-03-18'&nbsp;&nbsp;group&nbsp;by&nbsp;a.month,a.day,a.hour;&nbsp;或者用时间维表关联&nbsp;</td> 
 </tr> 
 </tbody> 
</table>

维度：日

维度：月

<table> 
 <tbody> 
 <tr> 
 <td style="padding: 0px 7px;border-width: 1px;border-color: windowtext;">drop&nbsp;table&nbsp;t_display_pv_month;create&nbsp;table&nbsp;t_display_pv_month&nbsp;(pvs&nbsp;bigint,month&nbsp;string);insert&nbsp;into&nbsp;table&nbsp;t_display_pv_monthselect&nbsp;count(*)&nbsp;as&nbsp;pvs,a.month&nbsp;from&nbsp;t_dim_time&nbsp;ajoin&nbsp;t_ods_detail_prt&nbsp;b&nbsp;on&nbsp;a.month=b.month&nbsp;group&nbsp;by&nbsp;a.month;</td> 
 </tr> 
 </tbody> 
</table>

** **

**2. 按终端维度统计pv总量**

注：探索数据中的终端类型

<table> 
 <tbody> 
 <tr> 
 <td style="padding: 0px 7px;border-width: 1px;border-color: windowtext;">select&nbsp;distinct(http_user_agent)&nbsp;from&nbsp;ods_weblog_detail&nbsp;where&nbsp;http_user_agent&nbsp;like&nbsp;'%Mozilla%'&nbsp;limit&nbsp;200;</td> 
 </tr> 
 </tbody> 
</table>

终端维度：uc

终端维度：chrome

终端维度：safari

![6e8b6c624b6448a4ebd266c6d81987d1.png][]

[6e8b6c624b6448a4ebd266c6d81987d1.png]: /images/20220602/f63e74697608404bbcf57fdaddaf94ce.png