网站流量日志分析（扩展—明细表）

目录

• 宽表窄表的引入
• 宽表的实现
  • 宽表的数据由何而来
  • 宽表需要扩宽哪些字段
  • 使用什么技术进行字段的扩宽
• 明细表的创建与数据导入
  • 建明细表dw_weblog_detail
  • 抽取refer_url到中间表 t_ods_tmp_referurl
  • 抽取转换time_local字段到中间表明细表 t_ods_tmp_detail
  • 语句可以合成一个总的语句，插入表数据

宽表窄表的引入

--需求：统计今天每个小时访问量有多少？
--需要根据小时hour进行分组 group by  分组之后统计每个组内的个数count 
--当下：group by(substring(time_local,12,2))
--缺点：每一条记录在分组之前 都需要进行所谓的截取操作

--原因：表中的某些字段看似一个字段 实则糅杂了多个属性在一起
--解决：把糅合在一起的属性拆分出来变成单独独立的新字段   hour
		group by(hour)
--结果：因为表的字段相比较之前变多了,称之为宽表。原来对应的表称之为窄表。又因为变宽之后信息更加详细具体，所以也可以称之为明细表。

宽表的实现

宽表的数据由何而来

由窄表数据得到,所谓窄表就是原始数据表
insert into  宽 + select  from 窄

宽表需要扩宽哪些字段

```
跟业务需求相关，本项目中进行两个字段的扩宽
时间字段：time_local
来访字段：http_referer
```

使用什么技术进行字段的扩宽

```
insert into  宽 + select  from 窄
至于插入什么样的数据完全取决于查询语句返回的结果。
因此在查询的时候就需要使用hive的函数进行字段的扩宽操作。
```
```
时间字段的拓宽：substring（time_local）
来源url字段拓宽：hive内置的解析url函数  是一个标准的udtf函数 parse_url_tuple
```

明细表的创建与数据导入

建明细表dw_weblog_detail

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

通过查询插入数据到明细宽表,dw_weblog_detail中

抽取refer_url到中间表 t_ods_tmp_referurl

也就是将来访url分离出host path query query id

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

注：lateral view用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据。
UDTF(User-Defined Table-Generating Functions) 用来解决输入一行输出多行(On-to-many maping) 的需求。Explode也是拆列函数，比如Explode (ARRAY) ，array中的每个元素生成一行。

抽取转换time_local字段到中间表明细表 t_ods_tmp_detail

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,12) as tmstr,
substring(time_local,6,2) as month,
substring(time_local,9,2) as day,
substring(time_local,12,2) as hour
from t_ods_tmp_referurl b;

语句可以合成一个总的语句，插入表数据

insert into table weblog.dw_weblog_detail partition(datestr='20181101')
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,12,2) 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_agent
from
(SELECT 
a.valid,a.remote_ip,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 weblog.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;