数据仓库与数据湖相关总结

一、数据仓库分层：

DS:数据源层 (mysql,pg(增删改查日志),kafka(机器日志、点击日志)、接口)，同一类日志可以是多张表结构相似的表(日志表：网页日志表，抓拍日志表，手机信号日志表，h5日志表，小程序日志表)

staging:数据预处理层，存储每天(批次)的增量，表结构和ods层一样

ods:操作数据层，存储所有清洗(etl)后的基础数据，表结构基于DS但不完全同于DS（ods日志表 对应 DS 网页日志表，抓拍日志表，手机信号日志表，h5日志表，小程序日志表）。

dd:脏数据层，存储来自于DS中的脏数据(延迟过大、日志时间异常、字段值不合规)，表结构和ods层基本一样

dwd:数仓明细数据层：将部分维度退化到事实表中，减少与维度表的关联，生成大宽表，提高明细数据的易用性(存储空间足够,计算内存相对缺乏)
一般关联几乎不在变化的维表数据，如常住人口身份数据、地理位置数据，不关联经常变化的维度数据(需要做好同步更新机制)，如：昵称、个性签名、发型着装、工作场所

dws:数仓汇总数据层：在dwd基础上分主题，进行聚合和公共指标统计，比如人员轨迹主题，就需要按区域时间段去重，并统计在不同s2层级下的各个区域时间段的停留时长、抓拍次数，以及记录每次停留的开始结束时间

ads应用数据层：存储数据产品个性化统计指标，面向页面展示，需要关联出经常变化的维度数据(有展示需要的话)。分离出该层结果表的目的是为了简化查询sql提高页面响应速度，
缺点是业务较多情况下相关结果表也随之增多，统计指标相对固定不能支持灵活的即席查询(如结果表是按天统计的则不能满足统计每小时的活动范围的需求)。
例子--比如基于人员轨迹主题统计 每人每天的活动范围、每天所有区域总活跃情况、每天每个区域活跃情况 入结果表，然后应用查询展示在首页

二、关键表字段设计：
ETL增量记录表、日志产生时间、各个表入库时间、表分区方式、是否需要分库分表、冷热数据分离、索引、数据压缩格式等

三、ETL抽取过程注意的点：延迟数据处理、抽取性能能否跟上(数据积压)、漏数据、多数据、数据重复抽取、脏数据处理不完整、维表关联错误等等。

四、数据湖(hudi)
概念：数据湖是一个存储企业的各种各样原始数据的大型仓库，其中的数据可供存取、处理、分析及传输。
包括结构化、半结构化数据、非结构化数据，里面涉及多种数据库如MYSQL、GP、图数据库、ES、hbase、hive、ck、云等等。

与数仓的关系：是数仓的DS；一个数据仓库中的数据只占数据湖中的其中一部分，不同的数据仓库存储不同的业务数据，为企业提供不同的服务。
比如数据湖中既有空调运行数据又有生产空调的流水线数据，这两类数据的使用场景大多是不同的，显然我该构建2个数据仓库来分别提供这两类的服务。

数据湖与企业的关系：
能实现数据的集中式管理，分门别类，保证数据资产的完整性。
在宏观上，企业能挖掘出很多之前所不具备的能力，为企业提供了探索新模式、发现新问题的可能，比如把流水线数据和空调数据放到一起 看能否通过空调运行数据找到流水线设计的不足。
结合先进的数据科学与机器学习技术，能帮助企业构建更多优化后的运营模型、预测分析、推荐模型等

参考 ：https://blog.csdn.net/weixin_43508544/article/details/106249662

二. 数仓中的缓慢变化维度-处理策略

--业务系统中的设备管理表(维度表),该表中的管理人员会随时间缓慢变化,只保留最新记录
CREATE table device_management (
  "device_id" int4 NOT NULL,
  "owner_people" varchar(255) ,
  "update_time" timestamp(6) default CURRENT_TIMESTAMP
)
--业务系统表变化日志
INSERT INTO device_management values (1,'张三','2020-06-14 21:29:13');
INSERT INTO device_management values (2,'张三','2020-06-14 21:29:13');
UPDATE device_management set owner_people='李四' , update_time='2020-07-20 21:29:13' WHERE device_id=1;
UPDATE device_management set owner_people='李四' , update_time='2020-07-20 21:29:13' WHERE device_id=2;
UPDATE device_management set owner_people='王五' , update_time='2020-08-17 21:29:13' WHERE device_id=1;
UPDATE device_management set owner_people='王五' , update_time='2020-08-17 21:29:13' WHERE device_id=2;
UPDATE device_management set owner_people='李四' , update_time='2020-07-20 21:29:13' WHERE device_id=1;
UPDATE device_management set owner_people='李四' , update_time='2020-07-20 21:29:13' WHERE device_id=2;
UPDATE device_management set owner_people='张三' , update_time='2020-08-18 21:29:13' WHERE device_id=1;
UPDATE device_management set owner_people='张三' , update_time='2020-08-18 21:29:13' WHERE device_id=2;
UPDATE device_management set owner_people='李明' , update_time='2020-08-20 21:29:15' WHERE device_id=2;
UPDATE device_management set owner_people='小明' , update_time=CURRENT_TIMESTAMP(0) WHERE device_id=2;

------------------------
若数仓中的设备管理表(维度表),该表中的管理人员会随时间缓慢变化，且保留所有变更记录
CREATE table dw_device_management (
  "device_id" int4 NOT NULL,
  "owner_people" varchar(255) ,
  "update_time" timestamp(6) NOT NULL,
  "latest_record" INT4 default 0
)
--全量抽取设备管理表历史记录到数仓维度表
insert INTO dw_device_management SELECT * FROM device_management ;
--增量抽取最近30天变更记录到数仓维度表
insert INTO dw_device_management SELECT * FROM device_management a WHERE date(a.update_time)>=CURRENT_DATE-30 and not exists (
SELECT 1 from dw_device_management where device_id=a.device_id and update_time=a.update_time );
--更新数仓维度表的latest_record标识
UPDATE dw_device_management  SET latest_record=(case when (device_id,update_time ) IN (SELECT device_id,max(update_time) from dw_device_management GROUP BY device_id) THEN 1 ELSE 0 end);

--查询是否更新成功
SELECT * FROM dw_device_management;

--SELECT *,case when (device_id,update_time ) IN (SELECT device_id,max(update_time) from device_management GROUP BY device_id)
THEN 1 ELSE 0 END latest_record FROM device_management ORDER BY device_id,latest_record;

--------------------
若数仓中的设备管理表(维度表),该表中的管理人员会随时间缓慢变化，且只保留最新记录
CREATE table dw_device_management_1 (
	"device_id" int4 NOT NULL,
  "owner_people" varchar(255) ,
  "update_time" timestamp(6) NOT NULL
)
--全量抽取设备管理表历史记录到数仓维度表
insert INTO dw_device_management_1 SELECT * FROM device_management ;
--增量抽取最近30天变更记录到增量表
create table dw_device_management_incre (like dw_device_management_1);
insert INTO dw_device_management_incre SELECT * FROM device_management a WHERE date(a.update_time)>=CURRENT_DATE-30 ;
--增量更新到数仓维度表(有则更新无则插入)
UPDATE dw_device_management_1 a set owner_people=b.owner_people,update_time=b.update_time from dw_device_management_incre b  where a.device_id=b.device_id ;
insert INTO dw_device_management_1 SELECT * FROM dw_device_management_incre b 
WHERE NOT exists (SELECT 1 from dw_device_management_1 a where a.device_id=b.device_id);

注意：如果业务系统中的维度表本身没有时间字段，则数仓中对应的维度表需定时全量更新，不采取增量更新的策略，因为增量更新需解析日志，流程非常复杂容易出错，得不偿失。

三. 维度表在数仓中的定位

维度表应独立于数仓中的所有层之外，即作为一个公共层存在。

四. 维度退化

4.1在所有业务中，当某个维度很少关联事实表，或者关联后只用到了该维度的很少的几个字段，且那几个字段值几乎不会更新只会新增，则可以把那几个字段直接迁移到事实表中，然后删除该维度。

4.2 如果有多个维度互相有关联关系，尽量退化为1个维度，以减少和事实表的关联，提高查询性能。