目录
宽表窄表的引入
--需求：统计今天每个小时访问量有多少？
--需要根据小时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;