建立宽表

文章目录

什么是宽表？为什么要创建宽表？创建宽表导入数据

什么是宽表？为什么要创建宽表？

所谓宽表，就是相对于窄表来说的，在原来表的基础上，把某些字段拓宽，就可以得到宽表。就像我这里的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;