一、数仓建模
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');
8304
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
