一、数仓建模
1.事实表:一般指的就是分析主题所对应的表,每一条数据用于描述一个具体的的事实信息,这些表一般都是一坨主键(外键)和描述事实字段的聚集
例如:统计2020年度订单销售情况
进行统计分析的时候,可以结合商品维度,用户维度,商家维度,地区维度进行统计分析,在进行统计分析的时候,可能需要关联到其他的表(维度表)。
注意:一般需要计算的指标字段所在表,都是事实表。
事实表的分类:
事务事实表:保存的是最原子的数据,也称“原子事实表”或“交易事实表”。沟通中常说的事实表,大多指的是事务事实表。
周期快照事实表:以具有规律性的,可预见的时间间隔来记录事实,时间间隔如每天,每月,每年等等。周期表由事务表加工产生(按照某一个可预见周期进行提前聚合形成的事实表)。
累计快照事实表:完全覆盖一个事务或产品的生命周期的时间跨度,它通常具有多个日期字段,用来记录整个生命周期中的关键时间点。
2.维度表:指的是在对事实表进行统计分析的时候,基于某一个维度,而这个维度信息可能在其他表中,而这些表就是维度表。
维度表不一定存在,但维度一定存在:
比如:根据用户维度进行统计,如果在事实表只存储了用户id,此时需要关联用户表,这个时候就是维度表
再比如:根据用户维度进行统计,如果在事实表中不仅仅存储了用户id,还存储了用户姓名,这个时候有用户维度,但是不需要用户表的参与,意味着没有这个维度表。
维度表的分类:
高基数维度表:指的表中的数据量是比较庞大的,而且数据也在发生变化
例如:商品表,用户表
低基数维度表:指的表中的数据量不是特别多,一般在几十条到几千条左右,而且数据相对比较稳定
例如:日期表,配置表,区域表
3.维度建模的三种模型
(1)星型模型:
a.特点:只有一个事实表,那么也就意味着只有一个分析的主题,在事实表的周围围绕了多个维度表,维度表与维度表之间没有任何的依赖。
b.反映了数仓发展初期最容易产生的模型。
(2)雪花模型:
a.特点:只有一个事实表,那么也就意味着只有一个分析的主题,在事实表的周围围绕了多个维度表,维度表可以接着关联其他的维度表。
b.反映数仓发展出现了畸形产生的模型,这种模型一旦大量出现,对后期维护时非常繁琐,同时如果依赖层次越多,SQL分析的难度也会加大。
c.此种模型在实际生产中,建议尽量减少这种模型产生
(3)星座模型:
a.特点:有多个事实表,那么也就意味着有了多个分析的主题,在事实表的周围围绕了多个维度表,多个事实表在条件符合的情况下,可以共享维度表。
b.反映数仓发展中后期最容易产生的模型。
4.缓慢渐变维
解决问题:解决历史变更数据是否需要维护的情况 。
SCD1:直接覆盖,不维护历史变化数据。
主要适用于:对错误数据处理。
SCD2:不删除,不修改已存在的数据,当数据发生变更后,会添加一条新的版本记录的数据,在建表的时候会多加两个字段(起始时间,结束时间),通过这两个字段来标记每条数据的起止时间,一般称为拉链表。
好处:适用于保存多个历史版本,方便维护实现。
弊端:会造成数据的冗余情况,导致磁盘占用率提升。
SCD3:通过在增加列的方式来维护历史变化数据。
好处:减少数据冗余,适用于少量历史版本的记录以及磁盘空间不是特别充足情况。
弊端:无法记录更多的历史版本,以及维护比较繁琐。
二、教育项目的数仓分层
1.原有的基础分层:
ODS层:源数据层
作用:对接数据源,和数据源的数据保持相同的粒度(将数据源的数据完整的拷贝到ODS层中)
注意:如果数据来源于文本文件中,可能会需要先对这些文本文件进行预处理(Spark)操作,将其中不规则的数据,不完整的数据,脏乱差的数据先过滤掉,将其转换为一份结构化的数据,然后灌入到ODS层
DW层:数据仓库层
作用:进行数据分析的操作
DA层:数据应用层
作用:存储DW层分析的结果,用于对接后续的应用(图标,推荐系统...)
2.教育数仓中:
ODS层:源数据层
作用:对接数据源,和数据源的数据保持相同的粒度(将数据源的数据完整的拷贝到ODS层中)
注意:如果数据来源于文本文件中,可能会需要先对这些文本文件进行预处理(Spark)操作,将其中不规则的数据,不完整的数据,脏乱差的数据先过滤掉,将其转换为一份结构化的数据,然后灌入到ODS层
一般放置事实表数据和少量维度表数据。
DW层:数据仓库层
-->DWD层:(detail) 明细层
作用:用于对ODS层数据进行清洗转换工作,以及进行少量的维度退化操作
(少量:a.将多个事实表的数据合并为一个事实表操作 b.如果维度表放置在ODS层,一般也是在DWD层完成维度退化)
-->DWM层:(middle) 中间层
作用:(1)用于进行维度退化操作(2)用于进行提前聚合操作(周期快照事实表)
-->DWS层:(service) 业务层
作用:进行细化维度统计分析操作
DA层:数据应用层
作用:存储基于DWS层再次分析的结果,用于对接后续的应用(图标,推荐系统...)
例如:比如DWS层的数据表已经完成了基于订单表各项统计结果信息,但是图标只需要其中销售额,此时从DWS层将销售额的数据提取出来存储在DA层。
DIM层:维度层
作用:用于存储维度表数据
3.维度退化:是为了减少维度表的关联工作
做法:将数据分析中可能在维度表中需要使用的字段,将这些字段退化到事实表中,这样后续在基于维度统计的时候,就不需要再关联维度表,事实表中已经涵盖了维度数据了
例如:订单表,原有订单表中只有用户id,当我们需要根据用户维度进行分析的时候,此时需要关联用户表,找到用户的名称,那么如果我们提前将用户的名称放置在订单表中,那么就不需要关联用户表,而这就是维度退化。
好处:减少后续分析的表关联情况
弊端:造成数据冗余
三、数仓工具的使用
1.HUE相关使用
(1)HUE:hadoop用户体验
(2)出现目的:提升使用hadoop生态圈中相关软件便利性
(3)核心:是将各类hadoop生态圈的软件的操作界面集成在一个软件中(大集成者)
2.HUE操作OOZIE
(1)OOZIE:用于管理Apache Hadoop作业的工作流调度程序系统,其本质是将工作流翻译为MR程序来运行
(2)大数据工作流程:确定数据源->数据存储->数据预处理->数据分析->数据应用->报告产出
(3)使用软件来实现工作流:
(单独使用)
azkaban:来源于领英公司,配置工作流的方式是通过类似于properties文件的方式来配置,只需要简单的几行即可配置,提供了一个非常好的可视化界面,通过界面可以对工作流进行监控管理,号称只要能够被shell所执行,azkaban都可以进行调度,所以azkaban就是一个shell客户端软件
oozie:来源于apache,出现时间较早的一款工作流调度工具,整个工作流的配置主要采用XML方式进行配置,整个XML配置时非常繁琐的,如果配置一个MR,相当于将MR重写一遍,而且虽然提供了一个管理界面,但是这个界面仅能查看,无法进行操作,界面异常卡顿
3.sqoop相关的操作
(1)sqoop是隶属于Apache旗下的,是一个用户进行数据的导入导出的工具,主要是将关系型数据库(MySQL,oracle...)导入到hadoop生态圈(HDFS,HIVE,Hbase...),以及将hadoop生态圈数据导出到关系型数据库中
(2)sqoop将导入导出命令翻译成MR程序来实现。
(3)通过sqoop将数据导入到HIVE主要有两种方式:原生API 和 hcatalog API
数据格式支持:原生API仅支持textFile格式;hcatalog API支持多种hive的存储格式(textFile,ORC,sequenceFile,parquet...)
数据覆盖:原生API支持数据覆盖操作;hcatalog API不支持数据覆盖,每一次都是追加操作
字段名:原生API字段名比较随意,更多关注字段的顺序,会将关系型数据库的第一个字段给hive表的第一个字段;hcatalog API:按照字段名进行导入操作,不关心顺序。建议在导入的时候不管是顺序还是名字都保持一致,目前主要采用hcatalog的方式。
(4)sqoop的基本操作
sqoop help:查看命令帮助文档
sqoop list-databases --help :查看某一个命令的帮助文档
查看mysql中的库:
sqoop list-databases --connect jdbc:mysql://192.168.52.150:3306 --username root --password 123456
查看mysql中hue数据库下的所有表:(\表示当前命令没有写完,换行书写)
sqoop list-tables \
--connect jdbc:mysql://192.168.52.150:3306/hue \
--username root \
--password 123456
(5)sqoop的数据导入操作
第一:如何将数据从mysql中导入到hdfs中(全量)
以emp表为例:
命令一:
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/test \
--username root \
--password 123456 \
--table emp
说明:默认情况下, 会将数据导入到操作sqoop用户的HDFS的家目录下,在此目录下会创建一个以导入表的表名为名称的文件夹, 在此文件夹下面的每一条数据都会运行一个mapTask, 数据的默认分隔符号为 逗号
更改其默认的位置:命令二:
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/test \
--username root \
--password 123456 \
--table emp \
--delete-target-dir \
--target-dir '/sqoop_works/emp_1'
调整map的数量:命令三:当-m不为1时,需要用--split-by指定分片字段进行并行导入,尽量指定int型
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/test \
--username root \
--password 123456 \
--table emp \
--delete-target-dir \
--target-dir '/sqoop_works/emp_2' \
--split-by id \
-m 2
调整默认的分隔符号:命令四:
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/test \
--username root \
--password 123456 \
--table emp \
--fields-terminated-by '\001' \
--delete-target-dir \
--target-dir '/sqoop_works/emp_3' \
--split-by id \
-m 1
第二:全量导入数据到Hive中(以emp_add表为例)
步骤一:在HIVE中创建一个目标表
create database hivesqoop;
use hivesqoop;
create table hivesqoop.emp_add_hive(
id int,
hno string,
street string,
city string
)
row format delimited fields terminated by '\t'
stored as orc;
步骤二:通过sqoop完成数据导入操作
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/test \
--username root \
--password 123456 \
--table emp_add \
--hcatalog-database hivesqoop \
--hcatalog-table emp_add_hive \
-m 1
第三:如何进行条件导入到HDFS中 (以emp表为例)
方式一:通过where的方式:下面这个方法会对原来的文件进行覆盖
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/test \
--username root \
--password 123456 \
--table emp \
--where 'id>1205' \
--delete-target-dir \
--target-dir '/sqoop_works/emp_2' \
--split-by id \
-m 2
方式二:通过SQL的方式
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/test \
--username root \
--password 123456 \
--query 'select deg from emp where 1=1 and $CONDITIONS' \
--delete-target-dir \
--target-dir '/sqoop_works/emp_4' \
--split-by id \
-m 1
第四:如何通过条件的方式导入到hive中(以emp_add表为例) :后期模拟增量导入数据
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/test \
--username root \
--password 123456 \
--table emp_add \
--where 'id>1205' \
--hcatalog-database hivesqoop \
--hcatalog-table emp_add_hive \
-m 1
或者
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/test \
--username root \
--password 123456 \
--query 'select * from emp_add where id>1205 where 1=1 and $CONDITIONS'
--hcatalog-database hivesqoop \
--hcatalog-table emp_add_hive \
-m 1
注意:如果sql使用双引号包裹,$CONDITIONS前面需要加一个\进行转义,单引号不需要
(6)sqoop的数据导出操作
需求:将hive中emp_add_hive表数据导出到mysql中
第一步:在mysql中创建目标表(必须创建)
create table test.emp_add_mysql(
id int,
hno varchar(32) NULL,
street varchar(32) NULL,
city varchar(32) NULL
);
第二步:执行sqoop命令导出数据
sqoop export \
--connect jdbc:mysql://192.168.52.150:3306/test \
--username root \
--password 123456 \
--table emp_add_mysql \
--hcatalog-database hivesqoop \
--hcatalog-table emp_add_hive \
-m 1
存在问题:如果hive中表数据存在中文,通过上述sqoop命令会出现中文乱码的问题
(7)sqoop中相关常用参数
--hcatalog-partition-keys --hcatalog-partition-values: keys和values必须同时存在,相当于指定静态分区。允许将多个键和值提供为静态分区键。多个选项值之间用,(逗号)分隔。
比如: --hcatalog-partition-keys year,month,day --hcatalog-partition-values 1999,12,31
--null-string '\N' --null-non-string '\N':指定mysql数据为空值时用什么符号存储,null-string针对string类型的NULL值处理,--null-non-string针对非string类型的NULL值处理
--hive-drop-import-delims:设置无视字符串中的分割符(hcatalog默认开启)
四、访问咨询主题看板_全量流程
1.需求分析
将调研需求转化为开发需求—>如何转化?--->将每一个需求中涉及到维度以及涉及到指标从需求中分析出来,同时找到涉及到哪些表,以及哪些字段。
目的:涉及的维度,涉及的指标,涉及的表,涉及的字段
在此基础上,还需要找到需要清洗哪些数据,需要转换哪些数据,如果有多个表,表与表关联的条件是什么....
(1)需求一:统计指定时间段内,访问客户的总数量。能够下钻到小时数据。
涉及维度:时间维度:年 季度 月 天 小时
涉及指标:访问量
涉及的表:web_chat_ems_2019_12(事实表)
涉及的字段:
时间维度:create_time-->转换操作:将create_time后期转化为yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo
注意:当发现一个字段中涵盖了多个字段的数据时候,可以尝试将其进行拆分出来
指标字段:sid--->先去重再做统计操作
(2)需求二:统计指定时间段内,访问客户中各区域人数热力图。能够下钻到小时数据。
涉及维度:时间维度:年 季度 月 天 小时,区域维度
涉及指标:访问量
涉及表:web_chat_ems_2019_12(事实表)
涉及字段:时间维度:create_time(需要转换操作);区域维度:area;指标字段:sid(先去重再统计)
(3)需求三:统计指定时间段内,不同地区(省、市)访问的客户中发起咨询的人数占比;
咨询率=发起咨询的人数/访问客户量;客户与网咨有说一句话的称为有效咨询。
涉及维度:时间维度:年 季度 月 天;地区维度:
涉及指标:咨询人数,访问量(在需求二中已经计算完成了,此处可以省略)
注意:当遇到指标需要计算比率问题的时候,一般的处理方案是只需要计算其分子和分母的指标,在最后DWS以及DA层进行统计计算
涉及表:web_chat_ems_2019_12
涉及字段:时间维度:create_time;区域维度:area;指标字段:sid;区分咨询人数:msg_count 必须 >=1
(4)需求四:统计指定时间段内,每日客户访问量/咨询率双轴趋势图。能够下钻到小时数据。
涉及维度:时间维度:年 季度 月 天 小时
涉及指标:访问量(需求一已经算过了),咨询人数
涉及表:web_chat_ems_2019_12
涉及字段:时间维度:create_time;指标字段:sid;区分咨询人数:msg_count 必须 >=1
(5)需求五:统计指定时间段内,1-24h之间,每个时间段的访问客户量。横轴:1-24h,间隔为一小时,纵轴:指定时间段内同一小时内的总访问客户量。
涉及维度:时间维度:天 小时
涉及指标:访问量(需求一已实现)
(6)需求六:统计指定时间段内,不同来源渠道的访问客户量占比。能够下钻到小时数据。
占比:各个渠道下 咨询量/访问量 占比
涉及维度:时间维度:年 季度 月 天 小时 ;各个渠道
涉及指标:咨询量,访问量
涉及表:web_chat_ems_2019_12
涉及字段:各个渠道字段:origin_channel;时间维度:create_time;指标:sid;访问量与咨询量的划分:msg_count>=1
(7)需求七:统计指定时间段内,不同搜索来源的访问客户量占比。能够下钻到小时数据。
占比:各个搜索来源下 访问量/总访问量
涉及维度:时间维度:年 季度 月 天 小时;不同的搜索来源
涉及指标:访问量
涉及表:web_chat_ems_2019_12
涉及字段:搜索来源:seo_source;时间维度:create_time;指标字段:sid
(8)需求八:统计指定时间段内,产生访问客户量最多的页面排行榜TOPN。能够下钻到小时数据。
涉及维度:时间维度:年 季度 月 天 小时;各个页面
涉及指标:访问量
涉及表:web_chat_text_ems_2019_11(事实表)
涉及字段:各个页面:from_url;指标字段:count(1)
缺失:时间维度的字段
-->解决方案:查看这个表中是否有时间字段;如果没有,这个表是否和另一个表有关联;如果都解决不了,找需求方
(9)汇总:
涉及维度:
固有维度:时间维度:年 季度 月 天 小时
产品属性维度:地区维度;来源渠道;搜索来源;受访页面
涉及指标:访问量,咨询量
涉及表:事实表:web_chat_ems_2019_12,web_chat_text_ems_2019_11;没有维度表,说明在数仓建模时不需要DIM层
涉及字段:
时间维度:web_chat_ems:create_time
地区维度:web_chat_ems:area
来源渠道:web_chat_ems:origin_channel
搜索来源:web_chat_ems:seo_source
受访页面:web_chat_text_ems:from_url
指标字段:访问量:sid;咨询量:sid;
区分访问和咨询:web_chat_ems:msg_count>=1即为咨询数据
需要清洗的数据:没有清洗
需要转化的字段:时间字段:需要将create_time转化为yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo
一对一关系:id=id:本质上就是一张表
2.业务数据准备
(1)两个表的关系图:
(2)第一步:在hadoop01的mysql中建立一个数据库
create database nev default character set utf8mb4 collate utf8mb4_unicode_ci;
第二步:将表导入仅数据库中
3.建模分析
建模:如何在hive中构建各个层次的表
(1)ODS层:源数据层
作用:对接数据源,一般和数据源保持相同的粒度(将数据源数据完整的拷贝到ODS层)
建表比较简单:业务库中对应表有哪些字段,需要在ODS层建一个与之相同字段的表即可,额外在建表的时候,需要构建为分区表,分区字段为时间字段,用于标记在何年何月何日将数据抽取到ODS层
注:此层会有两个表
(2)DIM层:维度层
作用:存储维度表数据
注:此时不需要,因为当前主题没有维度表
(3)DWD层:明细层
作用:清洗转化操作;少量维度退化
思考一:当前需要做什么清洗操作:不需要进行清洗
思考二:当前需要做什么转换操作:对时间字段进行转换,需转化为yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo
思考三:当前需要做什么维度退化操作:将两个事实表合并在一起
建表字段:原有表的字段+转换后的字段+清洗后的字段
sid,session_id,ip,create_time,area,origin_channel,seo_source,from_url,msg_count,yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo,referrer,landing_page_url,url_title,platform_description,other_params,history
注意:当合并多个表的时候,或者抽取数据的时候,处理的方案有两种:a.当表中字段比较多的时候,只需要抽取需要用的字段 b.当无法确定需要用哪些字段时,采用全部抽取 c.如果表中字段比较少,不管用的上还是用不上都抽取
(4)DWM层:中间层(省略)
作用:维度退化操作;提前聚合操作
思考一:当前需要进行什么退化操作:没有任何维度退化操作,因为没有DIM层
思考二:当前需要进行什么提前聚合操作:可以尝试先对小时进行提前聚合的操作,以便于后续的统计方便
思考三:当前主题是否可以按照小时提前聚合:目前不可以,因为数据存在重复的问题,无法提前聚合,一旦聚合后会导致后续的统计(上卷统计)出现不精确的问题
(5)DWS层:业务层
作用:细化维度统计操作
一般是一个指标会对应一个统计结果表
访问量:涉及维度:
固有维度:时间维度:年 季度 月 天 小时
产品属性维度:地区维度;来源渠道;搜索来源;受访页面
建表字段:指标统计字段+各个维度字段+三个经验字段(time_type,group_time,time_str)sid_total,sessionid_total,ip_total,yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo,area,origin_channel,seo_source,from_url,time_type,group_time,time_str
咨询量:涉及维度:
固有维度:时间维度:年 季度 月 天 小时
产品属性维度:地区维度;来源渠道
建表字段:指标统计字段+各个维度字段+三个经验字段(time_type,group_time,time_str)sid_total,sessionid_total,ip_total,yearinfo,quarterinfo,monthinfo,dayinfo,hourinfo,area,origin_channel,time_type,group_time,time_str
注意:如果不存在维度字段的值,设置为-1(业务指定,表示没有这个维度)
(6)DA层:
作用:对接应用,应用需要什么数据,从DWS层获取什么数据即可
注:此层目前不做任何处理,全部需要都已经细化统计完成了,后续具体用什么,看图表支持了。
4.建模操作
建表时需要考虑的问题:
1)表需要采用什么存储格式
2)表需要采用什么压缩格式
3)表需要构建什么类型表
4.1数据存储格式以及压缩方案
(1)存储格式选择:
情况一:如果数据不是来源于普通文本文件的数据,一般存储格式选择为列式的ORC存储
情况二:如果数据来源于普通文本文件的数据,一般存储格式选择为行式的textFile格式
当前项目:数据是存储在mysql中,选择为orc存储格式
(2)压缩方案选择:
写多读少:优先考虑压缩比,建议选择zlib,gz
写多读多:优先考虑压缩性能,建议选择snappy,lzo
注:如果空间比较充足,建议各个层次都选择snappy压缩方案
一般情况下:hive中ODS层选择zlib压缩方案;在hive的其他层次,选择snappy。
当前项目:ODS:zlib;其他层次:snappy
最终:ODS层:orc+zlib;其他层次:orc+snappy
4.2全量和增量
在进行数据统计分析时,一般来说,第一次统计分析都是全量统计分析,而后续的操作,都是在结果的基础上进行增量化的统计操作
(1)全量统计:需要面对公司所有的数据进行统计分析,数据体量一般比较大--->解决方案:采用分批次执行(比如按年)
(2)增量统计:一般时T+1模式统计,当天的数据,在第二天才会进行统计操作,4.3每一天都是统计上一天的数据。
4.3hive分区
后续的hive中构建表大部分的表都是分区表
(1)分区表作用:当查询的时候,指定分区字段,可以减少查询表数据扫描量,从而提升效率
(2)内部表和外部表如何选择:判断当前这份数据是否具有绝对的控制权
(3)向分区表添加数据:
静态分区:
格式:load data local inpath '路径' into table 表名 partition(分区字段=值...)
insert into table 表名 partition(分区字段=值....) + select语句
动态分区:
格式:insert into table 表名 partition(分区字段1,分区字段2....) +select语句
注意事项:1)必须开启hive对动态分区的支持 2)必须开启hive的非严格模式 3)保证select语句的最后的字段必须是分区字段数据(且要与分区字段的顺序保证一致)
动静混合:
格式:insert into table 表名 partition(分区字段1=值1,分区字段2,分区字段3....) +select语句
注意事项:1)必须开启hive对动态分区的支持 2)必须开启hive的非严格模式 3)保证select语句的最后的字段必须是动态分区字段数据(且要与分区字段的顺序保证一致)
(4)动态分区的优化点:有序动态分区
什么时候需要优化:有时候表中动态分区比较多,hive为提升写入效率,会启动多个reduce程序进行并行写入操作,此时对内存消耗比较大,有可能会出现内存溢出问题
解决方案:开启有序动态分区,开启后,reduce不会再并行运行了,只会运行一个,大大降低了内存的消耗,从而能够正常的运行完成,但是效率会降低。
该操作需要再CM的hive的配置窗口下开启此配置,但是目前不改,后续出现动态分区问题的时候再尝试开启,并且通过CM更改是全局有效的,相当于在hive-site.xml中修改。
4.4建模操作
ODS层:
create database if not exists itcast_ods;
-- 写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
-- 访问咨询表
CREATE EXTERNAL TABLE IF NOT EXISTS itcast_ods.web_chat_ems (
id INT comment '主键',
create_date_time STRING comment '数据创建时间',
session_id STRING comment '七陌sessionId',
sid STRING comment '访客id',
create_time STRING comment '会话创建时间',
seo_source STRING comment '搜索来源',
seo_keywords STRING comment '关键字',
ip STRING comment 'IP地址',
area STRING comment '地域',
country STRING comment '所在国家',
province STRING comment '省',
city STRING comment '城市',
origin_channel STRING comment '投放渠道',
user_match STRING comment '所属坐席',
manual_time STRING comment '人工开始时间',
begin_time STRING comment '坐席领取时间 ',
end_time STRING comment '会话结束时间',
last_customer_msg_time_stamp STRING comment '客户最后一条消息的时间',
last_agent_msg_time_stamp STRING comment '坐席最后一下回复的时间',
reply_msg_count INT comment '客服回复消息数',
msg_count INT comment '客户发送消息数',
browser_name STRING comment '浏览器名称',
os_info STRING comment '系统名称')
comment '访问会话信息表'
PARTITIONED BY(starts_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
location '/user/hive/warehouse/itcast_ods.db/web_chat_ems_ods'
TBLPROPERTIES ('orc.compress'='ZLIB');
-- 访问咨询附属表
CREATE EXTERNAL TABLE IF NOT EXISTS itcast_ods.web_chat_text_ems (
id INT COMMENT '主键来自MySQL',
referrer STRING comment '上级来源页面',
from_url STRING comment '会话来源页面',
landing_page_url STRING comment '访客着陆页面',
url_title STRING comment '咨询页面title',
platform_description STRING comment '客户平台信息',
other_params STRING comment '扩展字段中数据',
history STRING comment '历史访问记录'
) comment 'EMS-PV测试表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
location '/user/hive/warehouse/itcast_ods.db/web_chat_text_ems_ods'
TBLPROPERTIES ('orc.compress'='ZLIB');
DWD层:
CREATE DATABASE IF NOT EXISTS `itcast_dwd`;
create table if not exists itcast_dwd.visit_consult_dwd(
session_id STRING comment '七陌sessionId',
sid STRING comment '访客id',
create_time bigint comment '会话创建时间',
seo_source STRING comment '搜索来源',
ip STRING comment 'IP地址',
area STRING comment '地域',
msg_count int comment '客户发送消息数',
origin_channel STRING COMMENT '来源渠道',
referrer STRING comment '上级来源页面',
from_url STRING comment '会话来源页面',
landing_page_url STRING comment '访客着陆页面',
url_title STRING comment '咨询页面title',
platform_description STRING comment '客户平台信息',
other_params STRING comment '扩展字段中数据',
history STRING comment '历史访问记录',
hourinfo string comment '小时'
)
comment '访问咨询DWD表'
partitioned by(yearinfo String,quarterinfo string, monthinfo String, dayinfo string)
row format delimited fields terminated by '\t'
stored as orc
location '/user/hive/warehouse/itcast_dwd.db/visit_consult_dwd'
tblproperties ('orc.compress'='SNAPPY');
DWS层:
CREATE DATABASE IF NOT EXISTS `itcast_dws`;
-- 访问量统计结果表
CREATE TABLE IF NOT EXISTS itcast_dws.visit_dws (
sid_total INT COMMENT '根据sid去重求count',
sessionid_total INT COMMENT '根据sessionid去重求count',
ip_total INT COMMENT '根据IP去重求count',
area STRING COMMENT '区域信息',
seo_source STRING COMMENT '搜索来源',
origin_channel STRING COMMENT '来源渠道',
hourinfo STRING COMMENT '创建时间,统计至小时',
time_str STRING COMMENT '时间明细',
from_url STRING comment '会话来源页面',
groupType STRING COMMENT '产品属性类型:1.地区;2.搜索来源;3.来源渠道;4.会话来源页面;5.总访问量',
time_type STRING COMMENT '时间聚合类型:1、按小时聚合;2、按天聚合;3、按月聚合;4、按季度聚合;5、按年聚合;')
comment 'EMS访客日志dws表'
PARTITIONED BY(yearinfo STRING,quarterinfo STRING,monthinfo STRING,dayinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
location '/user/hive/warehouse/itcast_dws.db/visit_dws'
TBLPROPERTIES ('orc.compress'='SNAPPY');
-- 咨询量统计结果表
CREATE TABLE IF NOT EXISTS itcast_dws.consult_dws
(
sid_total INT COMMENT '根据sid去重求count',
sessionid_total INT COMMENT '根据sessionid去重求count',
ip_total INT COMMENT '根据IP去重求count',
area STRING COMMENT '区域信息',
origin_channel STRING COMMENT '来源渠道',
hourinfo STRING COMMENT '创建时间,统计至小时',
time_str STRING COMMENT '时间明细',
groupType STRING COMMENT '产品属性类型:1.地区;2.来源渠道;3.总访问量',
time_type STRING COMMENT '时间聚合类型:1、按小时聚合;2、按天聚合;3、按月聚合;4、按季度聚合;5、按年聚合;'
)
COMMENT '咨询量DWS宽表'
PARTITIONED BY (yearinfo string,quarterinfo STRING, monthinfo STRING, dayinfo string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
LOCATION '/user/hive/warehouse/itcast_dws.db/consult_dws'
TBLPROPERTIES ('orc.compress'='SNAPPY');