什么是宽表?为什么要创建宽表?
所谓宽表,就是相对于窄表来说的,在原来表的基础上,把某些字段拓宽,就可以得到宽表。就像我这里的ods_weblog_origin表,time_local字段的值:“2018-11-01 06:49:18”,当某个需求要统计某一天某个小时的访问量的时候,使用这个显然显得不方便,所以要建立宽表,方便后面的计算使用。我这里把ods_weblog_origin表进行拓宽,把time_local和http_referer两个字段进行拓宽,方便后面的计算使用。
创建宽表
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);
导入数据
这里导入数据有点复杂,大概的思路就是:先把原来的表的所有字段查出来,然后通过侧视图,把经过parse_url_tuple函数的字段关联起来,最后把关联起来的所有字段经过把时间字段拆分后,写入宽表。
insert into table 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_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 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;