宇说建筑网站,wordpress微信 缩略图不显示,潍坊模板建站定制网站,台州seo外链推广代理1、数仓
数据仓库是一个为数据分析而设计的企业级数据管理系统#xff0c;它是一个系统#xff0c;不是一个框架。可以独立运行的#xff0c;不需要你参与#xff0c;只要运行起来就可以自己运行。
数据仓库不是为了存储#xff08;但是能存#xff09;#xff0c;而是…1、数仓
数据仓库是一个为数据分析而设计的企业级数据管理系统它是一个系统不是一个框架。可以独立运行的不需要你参与只要运行起来就可以自己运行。
数据仓库不是为了存储但是能存而是为了统计分析
数据仓库可集中、整合多个信息源的大量数据借助数据仓库的分析能力企业可从数据中获得宝贵的信息进而改进决策。
1、核心架构 其中维度层就是用不同的角度去研究需求任务也可以用同一个维度或角度研究不同需求比如研究不同性别下登录量的区别或者是和订单喜好的关系
另外这个数据仓库系统其实就是涉及不同的层每一层会有一些表这样避免之后会重复工作直接用之前可能处理好的表所以数仓的任务其实不是说就写一个sql任务出一个结果是写一系列的sql得出一系列中间过程的表然后去处理任务需求最后得到结果 但是需要注意的是不同公司的数仓会有不同的层数但是基本都有上述的几层
2、建模方法论
// 主要见第四个数据仓库系统讲义的第2-6页1ER模型
1实体关系模型
实体关系模型将复杂的数据抽象为两个概念——实体和关系。实体表示一个对象例如学生、班级关系是指两个实体之间的关系例如学生和班级之间的从属关系。
这个是说的比如一对多、多对一、一对一、多对多关系
其中强调一下一对一关系 2 数据库规范化
使用一系列范式设计数据库通常是关系型数据库的过程其目的是减少数据冗余增强数据的一致性。
这一系列范式就是指在设计关系型数据库时需要遵从的不同的规范。关系型数据库的范式一共有六种分别是第一范式1NF、第二范式2NF、第三范式3NF、巴斯-科德范式BCNF、第四范式(4NF和第五范式5NF。
遵循的范式级别越高数据冗余性就越低。也不能冗余性太低太低的话性能也低因为有时候重复性工作就是前部分工作可能需要一些重复的表这个工作用到了这个中间表另外的工作也用到了这个中间表所以还是需要一定的冗余性的
基本上达到三范式即可
1、函数依赖 举几个例子 2、范式
第一范式属性不可切割。【属性划分到最小划分到不能再分割的地步】第二范式不能存在部分函数依赖第三范式不能存在传递函数依赖
3、分析优缺点ER模型不适合用于数据仓库的建模和分析
较为松散、零碎物理表数量多。
这种建模方法的出发点是整合数据其目的是将整个企业的数据进行组合和合并并进行规范处理减少数据冗余性保证数据的一致性。这种模型并不适合直接用于分析统计。
2维度模型 维度分析数据的角度、一个角度一个表、还包括用于统计的数据汇总起来就是一个表 维度模型将复杂的业务通过事实和维度两个概念进行呈现。事实通常对应业务过程而维度通常对应业务过程发生时所处的环境。
eg站在省份的角度研究订单销量、站在性别的角度研究订单销量
3、环境搭建
1运行环境
Hive环境搭建
Hive on SparkHive既作为存储元数据又负责SQL的解析优化语法是HQL语法执行引擎变成了SparkSpark负责采用RDD执行。
Spark on Hive : Hive只作为存储元数据Spark负责SQL解析优化语法是Spark SQL语法Spark负责采用RDD执行。
1. Hive on Spark Hive解析SQL Spark RDD Jar Yarn
2. Spark on Hive SparkSQLSpark解析SQL Spark RDD Jar Yarn //这是在Spark解析SQL后会有一些表会存放在hive中所以hive也可以当数据源 我们这里用的是Hive on Spark
// 具体安装流程见第四个数仓系统讲义第六节也就是第21页往后但是这里由于版本匹配问题为了可以用到最新的spark所以用到的hive为了兼容新一点的spark是修改过的需要在hive所在节点部署Spark纯净版
这是因为yarn里面也有Hadoop而spark如果不是纯净版里面也配置有Hadoop就会引起版本冲突
向HDFS****上传Spark纯净版jar包**
说明1采用Spark纯净版jar包不包含hadoop和hive相关依赖能避免依赖冲突。说明2Hive任务最终由Spark来执行Spark任务资源分配由Yarn来调度该任务有可能被分配到集群的任何一个节点。所以需要将Spark的依赖上传到HDFS集群路径这样集群中任何一个节点都能获取到
2开发环境
先启动Hadoop
启动hiveserver2
打开datagrip连接hive
4、模拟数据
这部分和采集不太一样因为采集将数据模拟出来最后都传到了hdfs
而我们这部分只要保证hdfs有需要的数据即可
这部分其实就是利用之前的采集部分将数据模拟好几天的数据然后利用采集通道传送到hdfs
通常企业在开始搭建数仓时业务系统中会存在历史数据一般是业务数据库存在历史数据而用户行为日志无历史数据。假定数仓上线的日期为2022-06-08为模拟真实场景需准备以下数据。注在执行以下操作之前先将HDFS上/origin_data路径下之前的数据删除。//所以总结就是先关掉maxwell通过模拟生成数据的程序生成了2022-06-04到2022-06-07的数据和行为日志但是删除了这几天已经通过采集通道流转到hdfs的行为日志数据但是业务数据还在数据库gmall中的表里面之后又通过程序生成了2022-06-08上线这个日期的数据日志数据通过kafka流转到hdfs中然后打开maxwell通过脚本【是增值数据中首日的那部分】将2022-06-08的业务数据从mysql流转到kafka再流转到hdfs中也就是说我们现在有8号的行为日志和业务数据中增量表的首日8号增值数据还有8号的全量表的全部数据数据库gmall中有前几日的业务数据不过没有传到hdfs中数据库中存有历史数据但是数仓只有上线这天的业务首日增值数据应该这么说hdfs中的inc文件中存有所有历史数据从2022-06-04到2022-06-08只不过我们在导到数仓ods层时候统一将业务数据的首日增值当作06-08的分区数据之后只要maxwell开启着gmall数据库一有变化就可以监听到然后加到kafka中的增量数据中而模拟程序可以模拟出来insertupdate等操作如果我们用脚本就可以记录到增量表的首日数据如果不用脚本只是开着maxwell运行一次lg.sh 模拟数据的程序那就可以记录到增值表每日的增值数据日志同样也是会记录到新的行为日志全量数据通过脚本也可以记录到全量表数据在从mysql导入到hdfs的时候需要经过datax这里会用脚本去执行命令会输入需要同步的表的名字以及日期增量表的数据需要打开Maxwell然后在Maxwell中设置mock_date日期因为我们是模拟别的日期所以需要这样但是实际开发中不用这样他就会记录实际的日期。更改模拟生成数据程序中的application中的日期【和mock_date日期一样】数据生成到gmall数据库中后我们Maxwell就会记录到然后加载到kafka中我们通过脚本经过flume将kafka中的数据加载到hdfs中这时候用到了拦截器就是将mock date中的日期当作时间文件名放到hdfs中同时加上inc的文件名结尾全量表
全量表每次更新都会记录全量数据包括原全量数据和本次新增数据即每个分区内的数据都是截至分区时间的全量总数据。注意全量表中每个分区内都是截至分区时间的全量数据原先分区的数据依然存在于表中只是每次更新会在最新分区内再更新一遍全量数据。
增量表
首日也是一样包括之前的历史数据一起同步
这是时间戳的问题就是mock date 5、ODS层operate data store
1、数据内容及格式
-- 1、存储从mysql业务数据库和日志服务器的日志文件中采集到的数据--日志数据-- 格式JSON--业务数据-- 历史数据-- 格式-- 全量DataX: TSV (Tab分割的)-- 增量Maxewell: JSON
-- 2、汇总数据--希望用最少的资源存储最多的数据-- 压缩 选择压缩率空间的概念高的而压缩效率时间的概念相对没那么重要压缩效率是影响计算速率-- 数据格式尽可能不变-- 压缩格式尽可能不变
-- 3、命名规范--数据仓库中表其实都是放置在一起的从逻辑上进行区分进行分层--表从名称上区分每一层-- 分层标记ods_ 同步数据的表名称一般全量是状态增量是行为但是有些表既可能是全量也可能是增量 全量/增量 标识full/inc补充一个知识点动态分区
就是通过查询到的结果用作插入数据和分区需要的字段值。
这个需要设置允许动态分区并且查询的时候需要设置一下严格查询要不然select * 还是可以把所有分区的数据都查出来 -- 日志数据格式--1、页面浏览日志--JSON中含有JSON--JSON表中的JSON数据如果存在嵌套的情况-- 一般会将最外层的json对象的属性名作为json表的字段-- 表的字段类型应该用特殊类型因为这个每一个字段都是相当于一个对象一个json对象--2、APP启动日志--JSON中含有JSON-- 但是上面两类日志数据会有字段名一样的情况。-- 这样就保留最多的情况即可比如页面浏览日志有a b c d 四个字段APP启动日志有a b 两个字段-- 创建表的时候就创建a b c d 四个字段在插入APP启动日志数据的时候没有c d 数据就会默认是空了// 补充一下在电商数仓6.0视频中第102-104节是讲解建表特殊类型。分别是array map struct结构体那里如果直接struct(a,b,c)这样是直接赋值的属性值属性名默认就是col1,col2,col3如果是named_struct(a,b,c,d)那就是成对的键值对a就是属性名b就是属性值所以括号内的数据应该是成对出现map和struct的区别-- 泛型-- struct中的属性名称是固定的只要约束好不能发生变化-- map中的key不是固定的只要约束好不能发生变化可以动态变化
因为map在定义的时候只用定义泛型不需要定义属性名字但是map定义好泛型后插入的数据要不都是数据类型和泛型一直要不就是都不是泛型的值所有属性值的类型一致即可
struct就没有这方面的要求可以不同属性值有不同的属性类型2、流程 创建日志表 加载行为日志数据 编写每日日志数据装载脚本 hdfs_to_ods_log.sh 2022-06-08创建业务表 -- 业务表--全量表datax-- TSV--增量表Maxwell-- JSON这里需要注意TSV就是用 /t序列化而json就是用json的序列化加载业务数据也是用的脚本这里是 hdfs_to_ods_db.sh all 2022-06-08有两个参数一个是表的名字可以是all表示所有业务表全量和增量同时更新第二个参数就是时间日期
6、DIM层Dimension
1、需要注意的点及框架
-- DIM层--Dimension 维度--维度层保存的表其实就是分析数据的角度表-- 维度层保存维度表所以建模理论应该遵循维度建模理论--维度层中的维度表主要用于统计分析--数据存储方式应该为列式存储 : orc--压缩格式为了统计分析这里就需要时间越少越好所以重视压缩效率而不是压缩率和ods反过来了就是snappy--数据源--ODS层的数据为整个数据仓库做准备--DIM层数据源就是ODS层--命名规范--分层标记dim__维度名称_全量/拉链标记-- 全量维度表的全部数据-- 状态数据为了避免数据出现问题最好的方式就是每一天都保存全部数据-- 绝大多数的维度表都是全量表特殊情况采用拉链的方式--拉链--建模理论--ER模型--ODS因为相当于直接把mysql中的表拿过来-- 维度模型【事实表用来做统计[计算]的维度表用来分析[属性]的】--维度状态表--事实行为表
2、维度表说明 -- 维度表--表-- 维度角度一个维度就是一张表--t_order, t_sex , t_age-- 从实践来讲一般会将有关联性的维度设置为一张表不同的维度就是这张表的维度字段--t_order, t_user(sex , age)--t_order, t_sku(tm , category)-- 如果维度特别简单特别独立只在特殊场合用。其实这个表可以不用创建可以在事实表中直接使用而不用单独创建-- t_payment_type : 微信支付支付宝支付银联支付--字段--维度只要等用来进行分析【重点是分析两字】的维度都是字段-- 就是只有分析价值的属性才可以用作维度表的字段-- 数据字段来源参考业务数据库的表字段--主维表业务数据库中主要用于分析维度字段的表--相关维表业务数据库中相关用于分析维度字段的表--维度字段的确定-- 尽可能生成丰富的维度属性 字段越多越好选择性越多-- 编码和字段共存【因为只有别的属性id的话不知道是什么意思需要添加配置id相对应的名字】-- 沉淀出计算通用的维度属性3、创建表思路 划分需要的维度表然后去业务数据库找到相关的表 找到主维表和相关维表之后从主维表看起一行一行的分析需要别的表的信息就找出来**但是只留下相关联且有分析价值的属性** 另外如果是一对多关系的那个关联上的表的字段用array struct 数组嵌套结构体方式 比如 4、数据装载 这里有两个问题 一个是需要注意我们这里的数据源不是mysql而是ods中的表另外一个是新创建的表是经过整合的并不能由单独的ods中一个表数据通过查询语句加载到新创建的表。并且查询的时候是 ods.表名字
所以需要补全数据:
1) 一对一的情况就是主维表一对一相关维表中的属性或者类似上图再分析主维表中有些属性id需要找到相关联的表中的id对应的name 补全行union 补全列join。 有left join、join、right join等等 考虑到出现丢数据的情况实际开发中在left join和join结果一致的情况用left join与主维表看齐
2一对多这种复杂情况 先要将查询结果变成需要的结果就此例子来说需要先变成结构体 用的是named_struct(“名字”属性值“名字“…) 然后再将N个结构体变成数组array 可以用collect_list(col)用作聚合函数返回值是数组array这个是去重的 3新关联的表增加的属性需要加名字就是主维表中所需要的名字
5、几个需要注意的表信息 补充一下一些需要注意的表信息 1) 活动表
与其他的不一样的点是
-- 活动维度表
-- 这里主维表不是activity_info而是activity_rule,因为我们主要分析的是规则而不是活动本身2) 日期维度表
通常情况下时间维度表的数据并不是来自于业务系统而是手动写入并且由于时间维度表数据的可预见性无须每日导入一般可一次性导入一年的数据。
没有分区、并且表名最后没有加上full的关键字 date_id STRING COMMENT 日期ID,week_id STRING COMMENT 周ID,一年中的第几周,week_day STRING COMMENT 周几,day STRING COMMENT 每月的第几天,month STRING COMMENT 一年中的第几月,quarter STRING COMMENT 一年中的第几季度,year STRING COMMENT 年份,is_workday STRING COMMENT 是否是工作日,holiday_id STRING COMMENT 节假日-- 除了最后两行需要上传特定的信息文件但是这里需要注意-- 文件的数据格式是行式存储还是列式因为我们现在表的结构是列式存储所以可以建一个中间表中间表是行式存储的数据是行式存储的也没事了最后再将中间表数据insert插入目标表中-- 剩下就是前面所有行这些都是可以看日期直接知道的3) 用户表 [拉链表]
用户注册人数基数会很大我们又需要每日统计表全量数据
所以数据会很庞大需要换种方式
-- 所以我们不去每天同步一份全部的数据而是将数据状态的变化记录下来而不是全部数据而是指定时间范围start,end-- 在表的设计中需要增加两个额外的字段用于表示时间范围start,end-- 表设计为拉链表zip)-- 一般情况下拉链表中截止到今天依然有效的状态的结束时间为了不修改所以设置为时间极大值9999-12-31由于表的设计多了时间范围的两个字段start,end 所以单纯的insert通过查询语句插入是不可以的
1、并且我们的数据源**不是ods层的全量表而是ods层的增量表**但是增量表中的数据是json同步过来的所以ods层中的增量表里面属性其实都是复杂数据类型需要注意
2、同时那两个新增字段需要分别讨论两种情况是首日增量和每日增量
--拉链表的数据源应该是ods层增量表--增量表--Maxwell--数据格式json--同步方式-- 首日bootstrapselect-- 每日insertupdatedeletebinlog-- 所以首日全量表和每日增量表也应该不一样-- 首日全量--2022-06-08 数仓上线首日user_info all--首日获取的全量数据只要用户的最新状态数据不存在历史数据所以无法判断状态的开始-- 所以折中的认为首日就是当前最新状态的开始日期-- 将最新状态的结束时间设为时间极大值9999-12-31--拉链表的分区策略--将数据存储到哪一个分区更方便我们的查询--假设将用户数据保存到用户的开始日期的分区中可能出现当天状态改变就会和别的用户不统一不知道这个日期是新还是旧--假设将用户数据保存到用户的结束日期的分区中--所以最后分区应该将数据存储到结束时间所在分区注意上面查询中是type为bootstrap-insert而不是insert
--每日增量--获取的数据-- 增加的用户信息type : insertstart : 当天2022-06-09end : 9999-12-31partition : 9999-12-31-- 修改的用户信息--修改后type : updatestart : 当天2022-06-09end : 9999-12-31partition : 9999-12-31--修改前dim_user_zip(dt9999-12-31)start : 同原表的startend : 9999-12-31 - 2022-06-08partition : 9999-12-31 - 2022-06-08这里修改不用update效率太低了更改数据的时候通过insertinsert overwrite table xxxselect2022-06-08 dtfrom select 9999-12-31 dt) t思路 -- 如果不明白可以重新看一下视频126、127最后这一步涉及到的是动态分区但是默认是关的
需要打开一下:set hive.exec.dynamic.partition.modenistrict;-- 另外需要注意我们这里因为是按天统计的所以我们只要获取同一用户当天最晚的一次操作就可以获取其最新状态。
-- 也就是说我们在写sql的时候需要只留下前一天最后修改的数据每一个用户只留下一条数据即可通过开窗函数row_number即可做到from (select data.id,data.name,data.phone_num,data.email,data.user_level,data.birthday,data.gender,data.create_time,data.operate_time,row_number() over (partition by data.id order by ts desc) rnfrom ods_user_info_incwhere dt 2022-06-09) t1
where rn 16、数据装载脚本 需要区分首日装载脚本和每日装载脚本 首日人工
每日自动化
1首日装载脚本
ods_to_init.sh
需要两个参数表名/all 时间日期
2每日装载脚本
ods_to_dim.sh
需要两个参数表名/all 时间日期
但是如果没有日期参数就自动去计算前一天日期将此日期放入
7、DWD层data warehouse detail
1、主要内容及框架
// 如果不知道如何区分理解事实表状态表。也就是DWD层和DIM层的区别// 可以看电商数仓6.0的第140节课程讲解--DWD层--data warehouse detail--detail:详细明细--加工数据为统计分析做准备--对ODS层的数据进行加工为后续的统计分析做准备--DIM层主要功能其实是分析数据面向状态所以大部分都是全量表--DWD层主要功能其实是统计数据面向行为大部分是增值表--DWD层的表主要保存的就是业务行为数据表的设计需要遵循建模理论 -维度建模 -事实行为表-- 数据的存储格式列式存储【因为是为了统计分析】-- 压缩方式snappy--命名规范-- 分层标记dwd_) 数据域分类比如交易域、流量域、用户域等等 行为 全量 / 增量-- 原则上来讲所有的行为都应该是增量数据-- 特殊情况下会采用全量方式实现行为统计【周期快照事实表】2、事实表 事实表分类:事务事实表、周期快照事实表累计快照事实表 事实类型【度量值类型】 1、可加事实度量值可加
2、半可加事实度量值在某些场景可加在某些场景不可加
3、不可加事实比率 --事实表--包含维度、--维度越多行为越详细维度越少行为越简单--包含度量值-- 所有的行为必须可以用于统计这里用于统计的值字段就是度量值!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!--事实表分类--事务事实表-- 绝大多数的事实表都是事务型事实表--事务原子性--原子操作独立行为不能再被切分了。-- 比如登录行为就不是因为登录失败和登录成功是两种流程所以就不能将登录行为当作一种行为--所以行为可以不定义说登录而是登录成功这就具备原子性了--下单这这种行为是只可能是系统或者网络等原因的不是由于用户本身失败的所以也可以理解成是独立行为--粒度行为描述的详细程序称之为粒度--描述的越详细粒度越细称之为细粒度--维度越多粒度越细--描述的越简单粒度越粗称之为细粒度--维度越少粒度越粗--周期快照事实表--累计快照事实表1事务事实表
1、举个例子
创建步骤-- 选择业务过程 确定创建什么表
-- 声明粒度 确定行确定一行数据的描述了什么
-- 确认维度 确定列
-- 确认事实 确定度量值必须有字段可以用于统计举个例子--交易域加购事务事实表--交易域--加购--购物车中没有这个商品新增商品--购物车中有这个商品增加购买商品的数量--事务事实表--表 dwd_trade_add_cart_inc--行 用户 时间 商品 商品数量--列 user date sku num--度量值 num 加购次数-- 建表
DROP TABLE IF EXISTS dwd_trade_cart_add_inc;
CREATE EXTERNAL TABLE dwd_trade_cart_add_inc
(id STRING COMMENT 编号,user_id STRING COMMENT 用户ID,sku_id STRING COMMENT SKU_ID,date_id STRING COMMENT 日期ID, -- 这个时间是和维度相关的所以加上的如果需要和时间分析需要时间维度表create_time STRING COMMENT 加购时间, -- 这个时间是和行为相关的sku_num BIGINT COMMENT 加购物车件数
) COMMENT 交易域加购事务事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dwd/dwd_trade_cart_add_inc/TBLPROPERTIES (orc.compress snappy); 数据装载 -- 数据装载
-- 数据源还是ods层的增量数据中获取需要的数据 -- 数据格式json。所以获取属性值的时候需要data.id等等加一个data -- 首日 全量 --包括历史数据4、5、6、7好几天的数据--无法判断行为》 比如加购的商品数量根本不知道是加购的数量还是减量后的数量--折中地认为当前数据全部都是新增购物加购-- 每日 增量--当天新增及变化-- 分区策略-- 哪一天的行为数据就存放到哪一天的分区中-- 首日的数据需要动态分区因为首日数据是全量数据包括历史数据包括历史的那些create_time,所以只能用create—_time作为分区字段-- 每日的数据就只有一天的就不需要动态分区了所以在装载的时候需要分清楚首日和每日在通过select查询insert的时候where语句中的type需要时bootstrap-insert还是insert等等 在每日的数据加载时候如果是insert那时间应该是create时间如果是update操作那时间应该是对应的operate时间2、需要注意的事项 创建表的时候要保证行为的原子性比如支付行为我们单独写支付成功而不是支付 创建表在确定字段的时候要保证有可以用于统计的度量值类似交易金额之类 由于我们在创建表的时候是关于行为的所以基本都是基于行为数据也就是增量表所以在装载数据的时候需要区分首日装载和每日装载这两类行为的type需要注意是不一样的在where条件里面 并且由于增量表都是json数据所以在select的时候属性应该是data.属性名字 另外首日装载因为ods首日数据的inc里面包括历史数据所以我们需要动态分区。首日数据是ods保存到了6-08但是里面包括4号到8号的所有历史数据很多的create_time所以需要动态分区哪一天create的就分区到哪一天的分区。但是每日装载就不需要动态分区了 在装载数据的时候join需要注意因为我们需要的是原子性行为有时候还是left的话就会忽略还有一些购物行为会具有零点漂移问题比如十二点前加入的订单但是12点后才支付成功这就是两个时间了没法直接join需要更改一下where条件。这个都在交易域支付成功事务事实表例子中可以看到 由于事务事实表create的时候必须要度量值字段但是有些时候也可能有隐藏的统计度量值就是没有可以写在表的创建过程中但是也可以统计并且日志数据没有历史数据而且不是maxwell获取的所以不会需要划分首日和每日。比如 -- 工具域优惠券使用(支付)事务事实表--用户 时间 订单 优惠券 次数隐藏的度量值并不会在建表语句中在每日装载的时候需考虑数据库gmall原本数据的逻辑有的数据只有insert【比如收藏表】有的数据只有update【比如支付表】明确这些后再去写每日装载的sql语句 有时候数据装载的时候不一定都是从业务数据库中找也可能是行为日志中去获取。同时数据加载时也需要考虑拿页面浏览日志还是APP启动日志这个是通过where筛选列的属性名字区分的。因为在生成ods中的log表时候是将两个日志的列属性一起放进去的而app启动日志是没有page属性值的也就是说page属性值是空的那一行数据就是属于APP启动日志的 where dt‘2022-06-08’ and page is not null; 这么筛选就可以 -- 流量域页面浏览事务事实表--流量域--页面浏览-- 用户 时间 上一个页面 当前页面 次数是隐藏度量值一行数据代表一次 停留时间也可以当作度量值/* 由于页面不属于电商的业务所以不能在业务数据中找到要去日志数据中找 */-- 数据装载--日志的数据分为两大类--页面浏览日志--APP启动日志--只需要页面浏览日志// 会话的概念可以看看视频的第150节一个用户很久不用会话会中断再次使用的时候相当于连接了一个新的会话
而计算机就是针对会话进行统计的
哪怕是同一个用户但是由于一些原因分成两个会话也会被计算机看作是两个操作两个人注册一个概念 --页面浏览日志离开页面的时候--怎么才算注册成功必须浏览了注册页面并且生成了uid并且在业务数据库中的业务表中增加了用户信息
-- page.page_idregister
-- and common.uid is not null登录和注册不一样登录可能不需要每次都登陆但是默认登录了【比如是十天免登录这种选项但是注册不会这样】 会话内第一个uid不为null的页面就对应一次登录操作【但是不同公司不同处理方式】。 其实就是找每一个会话中的第一个uid【通过开窗函数】
2周期快照表
一些图书的需求不需要特殊的计算或者关联表因为效率太低可以直接从业务数据中获取特殊字段存量[比如商品库存账户余额等]
特殊字段不需要进行多张表的关联计算直接从业务数据库中周期性地获取即可
比如购物车商品数量不用非得加购 - 减购直接就有sku_num 表示当前购物车中商品剩余数量
可以直接
DROP TABLE IF EXISTS dwd_trade_cart_full;
CREATE EXTERNAL TABLE dwd_trade_cart_full
(id STRING COMMENT 编号,user_id STRING COMMENT 用户ID,sku_id STRING COMMENT SKU_ID,sku_name STRING COMMENT 商品名称,sku_num BIGINT COMMENT 现存商品件数
) COMMENT 交易域购物车周期快照事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dwd/dwd_trade_cart_full/TBLPROPERTIES (orc.compress snappy);数据加载的时候应该用全量表的数据因为需要定期 完整的去记录需要的数据
3累积快照事实表
特殊的需求下单到支付时间间隔的平均值
把多个行为状态数据累积在一张表中但是这些行为状态数据最好是有关系的。
将一个流程中的多个行为累积到一个表中
举个例子
DROP TABLE IF EXISTS dwd_trade_trade_flow_acc;
CREATE EXTERNAL TABLE dwd_trade_trade_flow_acc
(order_id STRING COMMENT 订单ID,user_id STRING COMMENT 用户ID,province_id STRING COMMENT 省份ID,order_date_id STRING COMMENT 下单日期ID,order_time STRING COMMENT 下单时间,payment_date_id STRING COMMENT 支付日期ID,payment_time STRING COMMENT 支付时间,finish_date_id STRING COMMENT 确认收货日期ID,finish_time STRING COMMENT 确认收货时间,order_original_amount DECIMAL(16, 2) COMMENT 下单原始价格,order_activity_amount DECIMAL(16, 2) COMMENT 下单活动优惠分摊,order_coupon_amount DECIMAL(16, 2) COMMENT 下单优惠券优惠分摊,order_total_amount DECIMAL(16, 2) COMMENT 下单最终价格分摊,payment_amount DECIMAL(16, 2) COMMENT 支付金额
) COMMENT 交易域交易流程累积快照事实表PARTITIONED BY (dt STRING)STORED AS ORCLOCATION /warehouse/gmall/dwd/dwd_trade_trade_flow_acc/
TBLPROPERTIES (orc.compress snappy);需要注意这里的数据加载 -- 分区策略因为这里涉及到了三个时间所以需要好好考虑用什么时间进行分区--哪天的数据就存放到哪天分区--如果表中存在多个和业务相关的时间字段一般会选择其中的一个业务时间作为分区字段-- 一般选择时间靠后的字段作为分区字段-- 存在一个问题最后一个时间字段可能没值一般不会采用null,所以采用极大值9999-12-31-- 分区策略--查询的数据如何存储到表的分区中--查询数据的效率数据有效--ODS层--一天采集到的数据就存储到表的一天的分区--DIM层--全量每天一份全量数据存放到一天的分区中--拉链会采用结束时间作为分区字段--DWD层--事务性事实表一天的业务数据就存储到表的一天的分区--周期性快照事实表将每一天的全部状态数据保存到一天的分区--累积型快照事实表将业务流程中最后的那个时间字段作为分区字段交易流程累积快照表中的首日加载和每日加载是不一样的 因为每日加载的时候支付表和收货信息需要对比新订单和旧订单 由于我们在下订单的时候会自动增加支付信息的而且如果没有收货的话会被统一划分到极大值9999-12-31分区这就不知道到底支付没有只能说确认收货的一定是支付了的但是没有收货的不知道有没有支付 然后每日新增数据这时候的支付表中可能含有历史订单中在今日支付的所以需要统一union起来一起对比判断 3、装载脚本
1、首日装载
// ods_to_dwd_init.sh 两个参数第一个是表名字/all第二个是日期2、每日装载
// ods_to_dwd.sh 两个参数第一个是表名字/all第二个是日期// 不过每日装载的数据可以提供日期也可以不提供不提供的话就是取前一天的日期8、数据上线日期偏差问题
// 主要看视频的第154节之所以在8号会划分两段**是因为数据通道打通需要一定的时间**所以肯定没办法0点整准时打通
9、DWS层Data Warehouse Summary 这一层可以看出数仓设计能力强不强 这一层没有理论能想到就设计出来还会有一些问题容易注意不到所以很看重能力 1、主要内容及框架
主要是用于最后计算中用到的中间表
提前把中间计算结果记录下来进行预聚合多个需求中都可以用到
1DWS层的设计参考指标体系。
2DWS层的数据存储格式为orc列式存储snappy压缩。
3DWS层表名的命名规范为dws_数据域_统计粒度_业务过程_统计周期1d/nd/td。
注1d表示最近1日nd表示最近n日td表示历史至今。需要注意的 DWS层表的字段再预统计时如果字段可以跨越天【比如统计近七天每天下单数和下单人数可能近七天都是同一个人下的订单那下单人数就应该是1而不是每天的这个下单人数1进行求和】那么就不能再每天中统计
因为最终统计需要指定的字段但是提前聚合不能对这个字段做统计所以为了避免数据丢失
需要在表中增加这个字段而不是统计这个字段【就比如上面那下单人数的问题解决办法就是把user_id放到表中】 主要内容 --DWS--Data Warehouse Summary-- Data Warehouse : 数据仓库-- Summary : 汇总预聚合-- 用于讲DIMDWD的数据进行提前统计将统计结果保存到当前的表中--所以当前的表不是最终的统计结果表-- 数据量就可能有点多表的设计中应该添加分区--当前表需要进一步的聚合处理所以表设计中应该是列式存储snappy压缩-- 表的分类根据数据范围进行分类--1d : 1天的数据的统计--数据来源为DIM、DWD--Nd : N天的数据的统计--数据来源为1d表--td : 所有数据的统计--首日每日最好是这样用于统计比较快不要把之前所有天一点点累加--数据来源可以为1d表也可以是DIM、DWD--表的设计-- 参考ADS层表的设计--指标体系-- 原子指标拆分指标--行为统计字段统计逻辑-- 派生指标增加指标--统计周期范围 业务限定筛选条件 统计粒度分组维度-- 衍生指标比例关系--表名--分层标记dws_数据域_统计粒度_业务过程_统计周期1d/nd/td--指标客户想要的一个统计结果数值--业务过程相同数据来源相同--统计周期相同数据范围相同--统计粒度相同一行数据表示的含义相同
2、具体实现思路【重要】 确定需要的表由于交易域用户商品粒度订单最近1日汇总表涉及的行为是下单行为【dwd_trade_order_detail_inc】并且需要用到商品的信息【dim_sku_full】 进行表之间的连接 思考是先统计还是先补全如果需要统计的属性不需要补全就可以那就先统计反之先补全 确定需要统计的派生指标从上面确定的表中进行挑选数据 然后去写where筛选数据并且确定统计时间范围 最后加上group by以及具体的统计逻辑【就是一些分组函数】去实现统计粒度的实现 最后需要检查分组使用的属性是否会出现问题不要冲突。另外就是看看避免统计重复因为如果不是一天的话而是近几天的情况基于1d的表统计时可能会出现重复统计的问题。比如每一天就一个用户下单还都是同一个用户那统计近7天的时候就会每一天都是这个用户就会当作是7个不同的但实际只是同一个人这样就会出现问题 另外就是分区问题有些数据统计的时候因为全量表的首日和增量的首日中都是包括历史数据所以我们不能单纯的直接就写一个日期用作分区。而需要动态分区 但是加的属性要么是常量要么就是聚合函数要么就是分组字段因为不是这些的话使用group by就会报错 上面是解决思路
下面是代码思路【很重要】
-- 一般都是由浅入深
--1、先补全数据就是补全我们建表语句中用到的属性然后再去进行写聚合函数这些进行统计【效率低】 逻辑简单缺什么补什么
--2、先统计数据然后再去优化连接之前减少数据 【效率高】 之所以可以进行优化是因为统计的结果和补全数据没有任何的关系但是如果统计的结果和补全数据有关系则需要先补全再统计一般1d的表由于首日涉及到之前的数据所以需要分为首日数据加载和每日数据加载。首日数据加载是动态分区而每日的就直接用业务日期分区即可 而td表也需要分为首日和每日数据加载 -- 需要注意的是td表就是历史至今表的数据装载一般为了考虑效率会分成首日装载和每日装载-- 首日装载 : 直接获取所有的数据做聚合-- 每日装载 : 装载的数据只比前一天多了一天的数据而前一天的数据已经统计过了所以就存在重复计算--改善装载的思路获取昨天的统计结果 今天的新数据 》 做进一步的聚合上面说的那种就是利用union不过在最外层可能还需要重新根据统计粒度用一次 group by
3、具体表举个例子
交易域用户商品粒度订单最近1日汇总表
1d表的数据装载分为首日装载和每日装载-- 首日装载包含历史数据-- 每日装载包含当前数据代码 --首日
set hive.exec.dynamic.partition.modenonstrict;
-- Hive的bug对某些类型数据的处理可能会导致报错关闭矢量化查询优化解决
set hive.vectorized.execution.enabled false;
insert overwrite table dws_trade_user_sku_order_1d partition(dt)
select......dt
from
(selectdt,user_id,sku_id,count(*) order_count_1d,sum(sku_num) order_num_1d,sum(split_original_amount) order_original_amount_1d,sum(nvl(split_activity_amount,0.0)) activity_reduce_amount_1d,sum(nvl(split_coupon_amount,0.0)) coupon_reduce_amount_1d,sum(split_total_amount) order_total_amount_1dfrom dwd_trade_order_detail_incgroup by dt,user_id,sku_id
)od
left join
(selectid,sku_name,category1_id,category1_name,category2_id,category2_name,category3_id,category3_name,tm_id,tm_namefrom dim_sku_fullwhere dt2022-06-08
)sku
on od.sku_idsku.id;
-- 矢量化查询优化可以一定程度上提升执行效率不会触发前述Bug时应打开
set hive.vectorized.execution.enabled true;4、统计粒度的变化
1如果统计粒度减少然后需求分别是针对减少的统计粒度进行统计或者是针对其他字段进行统计
2当需求的统计粒度发生变化不属于表中任何一个统计粒度此时必须判重 对其他字段进行进一步统计需要聚合数据
3粒度没有变化可以直接将中间表的数据获取后使用
4粒度变粗数据需要进一步聚合
-- 在ads中看讲义的商品主题中的最近30日各品牌复购率-- 涉及到了粒度转换的应用需要赚了两次粒度5、数据装载脚本 nd表的只有每日装载脚本1d和td都是首日和每日两类脚本 不过这里有个顺序问题因为1d和nd还有td三类都有脚本 先执行1d表然后再执行nd和td表 1、首日装载
1d表 dwd_to_dws_1d_init.sh
nd表 无
td表 dws_1d_to_dws_td_init.sh
同样是all 日期
2、每日装载
1d表 dwd_to_dws_1d.sh
nd表 dws_1d_to_dws_nd.sh
td表 dws_1d_to_dws_td.sh
同样是all 日期
10、ADS层Application data service
1、主要内容及框架
--ADS层--Application : (数据仓库)应用--Data : 用户需求的统计结果数据--service : 对外服务--1、ADS层保存的数据是最终的统计结果无需做进一步的计算-- 因为不需要再计算了所以不需要列式存储也不需要snappy压缩--2、统计结果的目的是对外提供服务所以表不是最终数据的存储位置-- 需要将表中的数据同步发哦第三方存储MySQL--由于MySQL是行式存储所以ADS层的表最好是行市存储不选json,orc,最好选tsv(DataX)--压缩格式采用gzip--3、统计结果的数据量不会很多因为这是想要展示给用户看的。-- ADS层的表无需分区设计【数据比较少没必要分区】--4、表的设计--ODS层 : 表的结构依托于数据源的数据结构ER模型--DIM层 : 遵循维度模型的维度表的设计理念维度越丰富越好--DWD层 : 遵循维度模型的事实表的设计理念(粒度越细越好)--ADS层 : 客户要啥你加啥不要额外添加
--基础概念--维度分析数据的角度--粒度描述数据的详细程度-- 统计周期 : 统计的时候数据统计时间范围--【统计日期以获取数据的那一天为准包含数据的那一天比如我要统计8号最近一周但是程序可能是9号开始跑的】--2022-06-08统计最近一周02 03 04 05 06 07 08--2022-06-09统计最近一周03 04 05 06 07 08 09-- 统计粒度 : 分析数据的具体角度根据需求具体的分析角度称之为统计粒度站在哪一个角度分析这个需要看看统计粒度和数据源粒度是不是一样的不一样的话根据之前的笔记《统计粒度的变化》进行sql的编写-- 指标 : 客户想要的一个结果数值比如下面举个例子2、举个例子
-- 各品牌商品下单统计-- 统计的行为下单-- 分析的角度品牌-- 指标 下单数量下单人数--统计日期以获取数据的那一天为准包含数据的那一天比如我要统计8号最近一周但是程序可能是9号开始跑的DROP TABLE IF EXISTS ads_order_stats_by_tm;
CREATE EXTERNAL TABLE ads_order_stats_by_tm
(dt STRING COMMENT 统计日期,recent_days BIGINT COMMENT 最近天数,1:最近1天,7:最近7天,30:最近30天,tm_id STRING COMMENT 品牌ID,tm_name STRING COMMENT 品牌名称,order_count BIGINT COMMENT 下单数,order_user_count BIGINT COMMENT 下单人数
) COMMENT 各品牌商品下单统计ROW FORMAT DELIMITED FIELDS TERMINATED BY \tLOCATION /warehouse/gmall/ads/ads_order_stats_by_tm/;数据加载
首先举个例子然后再给出上面建表语句实际的数据加载语句
--数据加载--统计指标分析--将复杂的指标分解成小的简单的指标-- 1、原子指标最基础的统计指 --业务过程行为 度量值 聚合逻辑求和还是求平均值之类的--下单 次数 count()
selectcount(distinct order_id)
from dwd_trade_order_detail_inc-- 2、派生指标--基于原子指标增加其他的条件角度之类--派生指标 原子指标 统计周期(最近7天) 业务限定 统计粒度--统计周期和业务限定都是数据的筛选条件但是不一样--业务限定一般指的是数据约束条件数据字段的过滤过滤文件--统计周期一般指的是数据时间范围分区字段的过滤过滤文件夹selectcount(distinct order_id) --原子指标
from dwd_trade_order_detail_inc
where dt date_sub(2022-06-08,6) and dt 2022-06-08 --统计周期
and user.sex 男 --业务限定
group by 品牌 --统计粒度-- 3、衍生指标--将多个派生指标进行计算实际加载的时候本来需要考虑到首日每日装载由于不分区每日装载如果还用insert overwrite就会覆盖之前的数据
所以这里有一个技巧就是不用写两份首日和每日装载sql先查询一下这个表的数据然后和本次的sql语句进行union不是union all就还可以overwrite
还有取全量表数据时候尽管是需要2号到8号但是只取8号就够了【这样就避免重复取数据】因为是全量表没有删除操作【具体情况具体分析电商设计的是没有直接删除的就是标记一下这个数据被删了】只有逻辑删除就是标记一下但是如果计算数量的话那个数据还是存在的
具体的解释可以看第158节视频的最后
insert overwrite table ads_order_stats_by_tm
select * from ads_order_stats_by_tm -- 关键步骤 多union了一个表的自己
union
select
... ...3、需要中间表
td表就是历史至今表dws_first_order_td
创建表
首日装载
每日装载用到了union避免数据重复相当于在首日的基础上进行去重union
nd表就是最近n日汇总表
// 视频的159-161,dws层中也会讲4、数据装载脚本
dws_to_ads.sh
两个参数表名命令执行日期
11、【总结】数仓构建流程 其中业务总线矩阵就像是一个分析的笔记.后面还会有各个属性值和度量值的统计等 // 见讲义中第四个讲义数仓的第5章12、报表数据导出
为方便报表应用使用数据需将ads各指标的统计结果导出到MySQL数据库中。
1、新建数据库
使用mysql创建数据库gmall_report用来存放记录数仓输出数据。
2、数据导出
数据导出工具选用DataX选用HDFSReader和MySQLWriter
这里导出ads数据的dataX
也是用的生成器去生成DataX的配置文件
修改生成器需要的配置文件 执行命令
java -jar datax-config-generator-1.0-SNAPSHOT-jar-with-dependencies.jar
3、编写每日导出脚本
hdfs_to_mysql.sh all
其实all表示的含义是命令 配置文件 需要导出保存到的地址
13、数据仓库工作流调度 【任务调度器DolphinScheduler】 Apache DolphinScheduler是一个分布式、易扩展的可视化DAG工作流任务调度平台。致力于解决数据处理流程中错综复杂的依赖关系使调度系统在数据处理流程中开箱即用。 解压安装包后并不像其他工具一样可以直接使用
而是还需要安装才能使用
1、安装配置 更改配置文件install_config 初始化数据库拷贝MySQL驱动到DolphinScheduler的解压目录下的lib中要求使用 MySQL JDBC Driver 8.0.31。 命令行输入初始化脚本。数据库初始化脚本位于DolphinScheduler解压目录下的script目录中script/create-dolphinscheduler.sh 一键部署【安装】先启动zookeeper然后在文件夹目录下执行命令行./install.sh 访问UI DolphinScheduler UI地址为http://hadoop106:12345/dolphinscheduler 初始用户的用户名为admin密码为why20000615 在页面的安全中心创建租户【因为脚本执行需要权限而admin是没有权限的只是管理账户】,执行这个的时候需要启动hdfs而租户其实就是why_hadoop 点击用户管理创建用户名字why_hadoop 密码why20000615。admin用户是做管理的why_hadoop用户是做业务的。这样就可以退出页面重新登录这个创建的用户即可 平常的DolphinScheduler启停命令 DolphinScheduler的启停脚本均位于其安装目录的bin目录下 -- 1一键启停所有服务
./bin/start-all.sh
./bin/stop-all.sh
注意同Hadoop的启停脚本进行区分。-- 这里需要注意 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
可能在一键启停所有服务的之后还需要单独启停 Master !!!!!!!!!!2启停 Master
./bin/dolphinscheduler-daemon.sh start master-server
./bin/dolphinscheduler-daemon.sh stop master-server3启停 Worker
./bin/dolphinscheduler-daemon.sh start worker-server
./bin/dolphinscheduler-daemon.sh stop worker-server4启停 Api
./bin/dolphinscheduler-daemon.sh start api-server
./bin/dolphinscheduler-daemon.sh stop api-server5启停 Logger
./bin/dolphinscheduler-daemon.sh start logger-server
./bin/dolphinscheduler-daemon.sh stop logger-server6启停 Alert
./bin/dolphinscheduler-daemon.sh start alert-server
./bin/dolphinscheduler-daemon.sh stop alert-server//DolphinScheduler 界面使用可以看视频197、198节//平常工作流也都需要先点击上线然后再运行// 主要看怎么设置定时需要点击任务的上线以及定时的上线
// 还有传参
//DolphinScheduler 参数的优先级从低到高为上游任务传递的参数全局参数本地参数。2、任务调度2、任务调度
课程中由于内存问题最后用到的是单机模式
首先需要理清楚脚本的先后顺序 然后在admin管理员账户里面在安全中心中的环境管理里面创建环境将需要用到的组件环境配置复制进去
之后登录用户账户在资源中心创建文件夹里面存放脚本文件
在项目管理中点击创建项目创建工作流按照脚本先后顺序创建shell先后顺序并且设置好脚本及参数
最后保存的时候需要注意选择租户【【因为脚本执行需要权限而admin是没有权限的只是管理账户】,执行这个的时候需要启动hdfs而租户其实就是之前创建的why_hadoop】
并且设置全局参数【其中这个2022-06-09也可以设置成内置参数就是自动取前一天的日期等等】 设置工作流这个的定时上线定时然后上线工作流并且运行
14、可视化用的是superset
// 可以看最后一份讲义
