开发者学堂课程【大数据分析之企业级网站流量运营分析系统开发实战(第三阶段):网站流量日志分析—数据入库—宽表具体表现1—时间拓宽】学习笔记,与课程紧密联系,让用户快速学习知识。
课程地址:https://developer.aliyun.com/learning/course/695/detail/12206
网站流量日志分析—数据入库—宽表具体表现1—时间拓宽
为了得到它,需要搞清楚几个问题,第一个宽表的数据由何而来;第二个宽表需要拓宽哪些字段,有宽表就有窄表,需要增加那些字断;第三个使用什么技术进行字段的扩宽。把这三个问题搞明白就可以得到最后的宽表。
首先看一下第一个问题,宽表的数据由何而来,想想既然有宽表与之对应的是窄表就是原始日志数据表,所以自然而然想到宽表由窄表数据得到,那么所谓的窄表就是原始日志表,没有经过处理的叫做窄表,这时候可以想到当去创建一个宽表之后,大致语法结构是什么,可以搞个伪代码叫做 insert inton 宽=select from 窄
,这个语法非常重要,从侧面告诉宽表的数据来自于窄表的查询;
第二个宽表扩宽需要哪些字段,这个就跟项目息息相关,来到项目当中,这时候需要扩宽两个字段,根据数据来看,第一个字段是时间字段 time local 当中包含年月日时分秒,为此把这些属性提出来变成一个新的字段,首先原来的字段还叫 time local 提取出来一个日期的,时间的 month day hour 这五个字段就相当于从 time local 当中提取出来,第一个需要的字段跟业务需求相关,接下来叫做本项目中进行两个字段的互换,第一个就是时间的字段 time local 由这个字段当中一区相关的属性变成演变时间表,第二个字段,在表中还有一个字段叫做 http referer,也就是说数据当中有一个字段表明从哪里来,哪个页面来,这个字段 referrer ,这个字段需要互换什么,说过标准的 url 有哪些属性,看 http 叫协议,后面 host.name 叫做主机, host 叫做IP 请求的具体资源还可以拼接参数,referer 当中通常是一个 url ,那么就可以统计网站的域名、资源、路径等等来进行相关的分析,所以说第二个需要拓宽资源的叫做 http referer ,第二个字段叫做来访的字段,这个表(如下图)中的字段有一个来源、
来访的 host ,主机、一个来源的路径 path,查询的所有参数 query,指定参数对应的值,这四个字段相当于这个宽表新增了九个字段,这五个字段来自于对时间的扩宽,而这四个字段来源于来访的扩宽,这样就得到了宽表。当然其他的字段如果有 url 也可以进行扩宽,就完成了扩宽字段的创建。
接下来把宽表的建表语句创建, create table 叫做宽表,叫做 dw 表明它来自数据仓库,从 ods 窄表经过 detail 把它导入到宽表,当中的这些字段是它之前本来就存在的字段,而要把新建的字段创建出来,在项目资料当中有数据入库当中有一个叫做 dw-detail.sql,打开它,这里面指定了创建宽表的语句,后面这个表是注释,这里面有有效标识 IP referrer,当中再次强调从日期开始这五个字段是新增的(如下图),下面来访的号 ,总共扩宽了九个字段。接下来把建表语句创建下来,因为窄表是分区表,宽表也是分区表,一一对应的,给你1号的窄表数据肯定不能得到2号的宽表,这个一一对应的过程,一天一分析,一周一分析,把表创建一下,复制,打开服务器,粘贴,因为后面是注释
接下来 show tables
这样就完成了宽表的 dw 建表语句,当然建表完成之后并没有数据,
接下来涉及到第三个问题,使用什么样的技术进行字段的扩宽,但是核心思路是把宽表数据进行插入 ,插入的数据来自于查询返回的结果,至于插入什么样的数据完全取决于查询语句返回的结果,宽表实现的重担就落在查询,在查询的时候因此就需要使用 hive 的函数进行字段的扩宽操作,以项目来说,需要扩宽两个字段,
第一个字段叫做 time local 这个字段看一下表数据就是一个非常标准的字幕上的时间,里面有年月日时分秒,需要日期时间的,针对它的操作没有太多的难度,第一个针对时间维度扩宽很方便,需要 substring 截取出来。
举个例子来看,提取这个06,从第十二开始截取为两个,要获取月份,比如说十一月份012356,从第六个开始提取两个,只要数的清楚,数的准确,就可以解析出来。
演示示例,打开 hive 数据库,首先 select*from 表叫做 origin ods ,为了方便加关键字 limit1 ,这时候不需要其他字段,只需要 time local ,执行语句,运行结果如下:
返回的是时间,接下来想要 month 几月份,截取出来 substring ,把 time local 作为字段,首先里面 time/local,接下来怎么截取,就是数,从第一位,第二位,第三位,第四位,第五位,从第六位开始截取两个,写个六,提取完之后,as month, 这是第一个,接下来再截取一个字段叫做 as day,只要数的准确再执行,
针对第一个字段采用 substring 截取就可以,要截取直接完成使用。