网站建设方案书一定要有吗湖北专业网站建设维修电话

当前位置： 首页 > news >正文

网站建设方案书一定要有吗,湖北专业网站建设维修电话,软件开发流程图软件, 水多多dili在数据仓库中制作拉链表#xff0c;可以按照以下步骤进行#xff1a; 确定需求#xff1a;首先明确需要使用拉链表的场景和需求。例如#xff0c;可能需要记录历史数据的变化#xff0c;以便进行时间序列分析等。设计表结构#xff1a;在数据仓库中#xff0c;拉链表通…在数据仓库中制作拉链表可以按照以下步骤进行 确定需求首先明确需要使用拉链表的场景和需求。例如可能需要记录历史数据的变化以便进行时间序列分析等。设计表结构在数据仓库中拉链表通常由两个表组成当前表和历史表。当前表存储最新的数据历史表存储过去的数据。两个表的结构应该相同包含相同的字段。建立当前表创建一个当前表用于存储最新的数据。该表应包含需要记录的所有字段以及一个表示有效期的字段。有效期字段用于标识该条数据在何时失效。建立历史表创建一个历史表用于存储过去的数据。该表的结构与当前表相同但还应包含一个表示生效日期的字段。生效日期字段用于标识该条数据在何时生效。数据插入与更新当有新数据插入时将其插入到当前表中并设置相应的有效期。同时将过期的数据从当前表中移动到历史表中并更新它们的生效日期和有效期。数据查询在查询数据时同时查询当前表和历史表。根据查询的时间范围确定在哪个表中查找数据。定期维护定期对历史表进行维护例如删除过期的数据、优化索引等以确保拉链表的性能和存储空间的有效利用。 需要注意的是拉链表在数据仓库中的应用可能因具体需求和场景而有所不同。因此在实际制作拉链表时建议根据数据仓库的设计规范、业务需求和数据特点进行调整和优化。同时也要确保数据的完整性、一致性和准确性。 参考漫谈数据仓库之拉链表原理、设计以及在Hive中的实现_拉链表实现方式_小强签名设计的博客-CSDN博客 二、什么是拉链表 拉链表是针对数据仓库设计中表存储数据的方式而定义的顾名思义所谓拉链就是记录历史。记录一个事物从开始一直到当前状态的所有变化的信息。 我们先看一个示例这就是一张拉链表存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据 1. 拉链表的使用场景 在数据仓库的数据模型设计过程中经常会遇到下面这种表的设计 有一些表的数据量很大比如一张用户表大约10亿条记录50个字段这种表即使使用 ORC 压缩单张表的存储也会超过100G在 HDFS 使用双备份或者三备份的话就更大一些。表中的部分字段会被 update 更新操作如用户联系方式产品的描述信息订单的状态等等。需要查看某一个时间点或者时间段的历史快照信息比如查看某一个订单在历史某一个时间点的状态。表中的记录变化的比例和频率不是很大比如总共有10亿的用户每天新增和发生变化的有200万左右变化的比例占的很小。 那么对于这种表我该如何设计呢下面有几种方案可选 方案一每天只留最新的一份比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。方案二每天保留一份全量的切片数据。方案三使用拉链表。 拉链表 拉链表在使用上基本兼顾了我们的需求。   首先它在空间上做了一个取舍虽说不像方案一那样占用量那么小但是它每日的增量可能只有方案二的千分之一甚至是万分之一。   其实它能满足方案二所能满足的需求既能获取最新的数据也能添加筛选条件也获取历史的数据。 所以我们还是很有必要来使用拉链表的。 三、拉链表的设计和实现

1. 如何设计一张拉链表 我们先看一下在 Mysql 关系型数据库里的 user 表中信息变化。 在 2017-01-01 这一天表中的数据是 在 2017-01-02 这一天表中的数据是用户 002 和 004 资料进行了修改005 是新增用户 在 2017-01-03 这一天表中的数据是用户 004 和 005 资料进行了修改006 是新增用户 如果在数据仓库中设计成历史拉链表保存该表则会有下面这样一张表这是最新一天即 2017-01-03 的数据 说明 t_start_date 表示该条记录的生命周期开始时间t_end_date 表示该条记录的生命周期结束时间。t_end_date ‘9999-12-31’ 表示该条记录目前处于有效状态。如果查询当前所有有效的记录则 select * from user where t_end_date ‘9999-12-31’。如果查询 2017-01-02 的历史快照则 select * from user where t_start_date ‘2017-01-02’ and t_end_date ‘2017-01-02’。此处要好好理解是拉链表比较重要的一块。 where条件筛选当前有效数据开始日期小于等于当前日期并且结束日期大于等于当前日期则为有效。
2. 在Hive中实现拉链表 在现在的大数据场景下大部分的公司都会选择以 Hdfs 和 Hive 为主的数据仓库架构。目前的 Hdfs 版本来讲其文件系统中的文件是不能做改变的也就是说 Hive 的表只能进行删除和添加操作而不能进行 update。基于这个前提我们来实现拉链表。 还是以上面的用户表为例我们要实现用户的拉链表。在实现它之前我们需要先确定一下我们有哪些数据源可以用。 我们需要一张ODS层的用户全量表。至少需要用它来初始化。每日的用户更新表。 而且我们要确定拉链表的时间粒度比如说拉链表每天只取一个状态也就是说如果一天有3个状态变更我们只取最后一个状态这种天粒度的表其实已经能解决大部分的问题了。 另外补充一下每日的用户更新表该怎么获取据笔者的经验有3种方式拿到或者间接拿到每日的用户增量因为它比较重要所以详细说明 我们可以监听 Mysql 数据的变化比如说用 Canal最后合并每日的变化获取到最后的一个状态。假设我们每天都会获得一份切片数据我们可以通过取两天切片数据的不同来作为每日更新表这种情况下我们可以对所有的字段先进行 concat再取 md5这样就 ok 了。流水表有每日的变更流水表。通过 etl 工具对操作型数据库按照时间字段增量抽取到 ods 或者数据仓库每天抽取前一天的数据形成每天的增量数据实际中使用最多的情形。 1拉链表实现方式一 ods 层的 user 表 现在我们来看一下我们 ods 层的用户资料切片表的结构 CREATE EXTERNAL TABLE ods.user (user_num STRING COMMENT 用户编号,mobile STRING COMMENT 手机号码,reg_date STRING COMMENT 注册日期 COMMENT 用户资料表 PARTITIONED BY (dt string) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t LINES TERMINATED BY \n STORED AS ORC LOCATION /ods/user; )ods 层的 user_update 表 然后我们还需要一张用户每日更新表前面已经分析过该如果得到这张表现在我们假设它已经存在。 CREATE EXTERNAL TABLE ods.user_update (user_num STRING COMMENT 用户编号,mobile STRING COMMENT 手机号码,reg_date STRING COMMENT 注册日期 COMMENT 每日用户资料更新表 PARTITIONED BY (dt string) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t LINES TERMINATED BY \n STORED AS ORC LOCATION /ods/user_update; )拉链表 现在我们创建一张拉链表 CREATE EXTERNAL TABLE dws.user_his (user_num STRING COMMENT 用户编号,mobile STRING COMMENT 手机号码,reg_date STRING COMMENT 用户编号,t_start_date ,t_end_date COMMENT 用户资料拉链表 ROW FORMAT DELIMITED FIELDS TERMINATED BY \t LINES TERMINATED BY \n STORED AS ORC LOCATION /dws/user_his; ) 现在我们假设我们已经已经初始化了 2017-01-01 的日期然后需要更新 2017-01-02 那一天的数据我们有了下面的 Sql。 然后把两个日期设置为变量就可以了。 INSERT OVERWRITE TABLE dws.user_his SELECT * FROM (SELECT A.user_num,A.mobile,A.reg_date,A.t_start_time,CASEWHEN A.t_end_time 9999-12-31 AND B.user_num IS NOT NULL THEN 2017-01-01ELSE A.t_end_timeEND AS t_end_timeFROM dws.user_his AS ALEFT JOIN ods.user_update AS BON A.user_num B.user_num UNIONSELECT C.user_num,C.mobile,C.reg_date,2017-01-02 AS t_start_time,9999-12-31 AS t_end_timeFROM ods.user_update AS C ) AS T2拉链表实现方式二 操作型数据库的用户表结构 CREATE EXTERNAL TABLE ods.user ( user_num STRING COMMENT 用户编号, mobile STRING COMMENT 手机号码, reg_date STRING COMMENT 注册日期 ,last_modify_date STRING COMMENT ‘最后修改时间’ COMMENT 用户资料表 PARTITIONED BY (dt string) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t LINES TERMINATED BY \n STORED AS ORC LOCATION /ods/user;每天增量抽取的用户表结构和抽取条件 1表结构和上面的表结构保持一致我们取表名为 ods.user_update 2增量抽取条件select * from ods.user where last_modify_date \(date 拉链表 现在我们创建一张拉链表 CREATE EXTERNAL TABLE dws.user_his ( user_num STRING COMMENT 用户编号, mobile STRING COMMENT 手机号码, reg_date STRING COMMENT 用户编号,last_modify_date STRING COMMENT ‘最后修改时间’ t_start_date , t_end_date COMMENT 用户资料拉链表 ROW FORMAT DELIMITED FIELDS TERMINATED BY \t LINES TERMINATED BY \n STORED AS ORC LOCATION /dws/user_his; )实现 sql 1 merge into dws.user_his tar using (select user_num,mobile from ods.user_update ) sou on tar.user_numsou.user_num and tar.mobilesou.mobile and tar.t_start_date \)date and tar.t_end_date $date when matched then update set tar.t_end_date9999-12-31;按照主键筛选在 dws.user_his 表中出现过的并且现在为有效数据的全部更新为闭链数据。 INSERT TABLE dws.user_his SELECT C.user_num, C.mobile, C.reg_date, c.last_modify_date2017-01-02 AS t_start_time, 9999-12-31 AS t_end_time FROM ods.user_update AS C; 比如我们要1月2号的数据取出来的数据为 select from user where t_start_date ‘2017-01-02’ and t_end_date ‘2017-01-02’ 与1月2号数据完全一致。
3. 拉链表和流水表 流水表存放的是一个用户的变更记录比如在一张流水表中一天的数据中会存放一个用户的每条修改记录但是在拉链表中只有一条记录。 这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些一般按天就足够。
4. 查询性能 拉链表当然也会遇到查询性能的问题比如说我们存放了5年的拉链数据那么这张表势必会比较大当查询的时候性能就比较低了个人认为两个思路来解决 在一些查询引擎中我们对start_date和end_date做索引这样能提高不少性能。保留部分历史数据比如说我们一张表里面存放全量的拉链表数据然后再对外暴露一张只提供近3个月数据的拉链表。
5. 备用方案 可以采用备份的方案保证无误和可行。保存增量数据并对 t_dw_orders_his 表每个月备份一次全量数据。如需回滚最多重跑30天数据即可 六、总结 在后面的使用中又有了一些心得补充进来 使用拉链表的时候可以不加 t_end_date即失效日期但是加上之后能优化很多查询。可以加上当前行状态标识能快速定位到当前状态。在拉链表的设计中可以加一些内容因为我们每天保存一个状态如果我们在这个状态里面加一个字段比如当天修改次数那么拉链表的作用就会更大。