`
kt431128
  • 浏览: 39320 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

一般数据库增量数据处理和数据仓库增量数据处理的几种策略

 
阅读更多

一般增量数据处理的几种场景

通常在数据量较少的情况下,我们从一个数据源将全部数据加载到目标数据库的时候可以采取的策略可以是:先将目标数据库的数据全部清空掉,然后全部重新从数据源加载进来。这是一个最简单并且最直观的并且不容易出错的一种解决方案,但是在很多时候会带来性能上的问题。

如果我们的数据源来自于不同的业务系统,数据动辄百万,千万甚至亿级计算。第一次需要全部加载,如果在第二次周期或者第三次周期的时候仍然全部加载的话,耗费了极大的物理和时间资源。有可能部分数据源并未发生变化,而有的数据源可能只是增加了少量的数据。

我们要考虑的问题是,对于已经存在目标数据库中的数据都是历史数据,对于数据源中的数据我们只应该考虑新修改的记录和新插入的记录,只应该考虑这两种数据。所以增量处理实质上就是处理变化的数据。

下面我们一起看看这些表,忽略从数据仓库设计的角度,只考虑如何实现增量数据的检测和抽取。

第一类 - 具有时间戳或者自增长列的绝对历史数据表

这张表能够代表一部分数据源的特征 - 绝对历史事实数据。它指的是表中的数据是不可逆的,只有插入操作没有删除或者修改操作,表示在过去一段时间内完成的事实业务数据。比如这张表表示的某些产品的下载信息,用户什么时候下载了产品就会在数据库中记录一条数据。

这种数据表一般会提供一列能够记载这条记录生成的历史时间,或者说这个操作发生的时间,越早的操作时间越靠前,越晚的操作时间越靠后。

那么对于这类表的增量处理策略就是:

  1. 第一次加载动作完成之后,记录一下最大的时间点,保存到一个加载记录表中。
  2. 从第二次加载开始先比较上次操作保存的最后/最大的时间点,只加载这个时间点以后的数据。
  3. 当加载过程全部成功完成之后再更新加载记录表,更新这次最后的时间点。

另外,如果这类表有自增长列的话,那么也可以使用自增长列来实现这个标识特征。

第二类 - 有修改时间特征的数据表

这类表中的数据一般属于可以修改带有维护性质的数据,比如像会员信息表,创建会员的时候会生成一条记录,会在 CreateDate 标记一下,并且在 UpdateDate 中保存的也是 CreateDate 的值。当 CreateDate 和 UpdateDate 相同的时候说明这一条数据是插入操作,但是这个会员的信息是可以被编辑和修改的,于是每次更新的同时也更新了 UpdateDate 时间戳。

假设上面的这几条数据在第一次加载到目标数据库后,源表新加入了一条会员记录并同时修改了一条会员的信息。

那么像这种情况下增量数据处理的策略就可以是:

  1. 第一次加载动作完成以后,记录一下最大的 UpdateDate 时间戳,保存到一个加载记录表中。(第一次是 2010-10-23)
  2. 在第二次加载数据的时候,用加载记录表中的时间戳与源表里的 UpdateDate 相比较,比时间戳大的说明是新添加的或者修改的数据。(大于 2010-10-23 的是第一条 Update 的数据和第四条新增的数据)
  3. 当整个加载过程成功之后,更新最大的 UpdateDate到记录表中。(记录表中将 2010-10-26 记录下来)

但是要注意的是,不是每一个带有修改时间特征的数据表都会这么设计,有可能在插入数据的时候只会放入 CreateDate 但是并不会写入 UpdateDate。这样的话,在每次加载的过程中可能就需要同时比较 CreateDate 和 UpdateDate 了。

第三类 - 关联编辑信息的无时间特征数据表

 

这类表本身没有任何可以标识的自增长 ID 或者时间戳,只保留基本信息,所有的编辑操作等信息专门有一张表来记录。这样的设计可以是为了单独记载所有的编辑历史信息,但是同时又保留了主要信息的独立性,在查询主表的时候查询体积变小提供查询效率。类似于这样的设计可以参照第一类和第二类的设计方案,在这个示例中多出的就是要关联 Member Audit History 表并进行时间戳或者自增长ID 的判断。

第四类 - 无特征数据表

很少有人这样设计数据表,但是不代表不存在。我曾经碰到过一个文件表,由于部分数据的敏感性不能直接访问源数据库,因此是由客户从源数据库将数据抽取出来保存到一个文本文件中。很遗憾的是,抽取出来的数据中只保留了创建时间,但是并没有任何能够标识修改行为的列。与客户的沟通到最终客户接受意见修改,到最终修改完成这中间是没法停下来等客户的,因此只能暂时采用另外的一种方法 - 基于唯一列的数据对比。

很简单的概念 - 即每次加载数据源中的数据时,基于主键或者唯一列到目标表中查询是否存在,如果不存在就插入。如果存在就比较关键列数据是否相等,不相等就修改。

这种实现可以采用 SQL Merge 语句来完成 - 请参看- SQL Server - 使用 Merge 语句实现表数据之间的对比同步

或者通过 SSIS 中的 Lookup + Conditional Split 实现 - 请参看-SSIS 系列 - 数据仓库中实现 Slowly Changing Dimension 缓慢渐变维度的三种方式

那么对于前三类数据表,它们可以共同使用一个加载记录表来记录它们上一次的时间戳或者自增 ID。

对于 Table A -

SELECT 列1, 列2
FROM Table_A
WHERE ID > (SELECT LastSeqID FROM SourceLoadingAudit WHERE SourceTable = 'Table_A ')

对于 Table C -

SELECT 列1, 列2
FROM Table_C
WHERE UpdateDate > (SELECT LastModifiedDate FROM SourceLoadingAudit WHERE SourceTable = 'Table_C')

数据仓库增量数据处理

数据仓库增量数据处理一般发生在从 Source 到 Staging 的过程中,从 Staging 到DW 一般又分为维度 ETL 处理和事实 ETL 处理两个部分。那么实际上从 Source 到 Staging 的过程中,就已经有意识的对维度和事实进行了分类加载处理。通常情况下,作为维度的数据量较小,而作为业务事实数据量通常非常大。因此,着重要处理的是业务事实数据,要对这一部分数据采取合适的增量加载策略。

通常情况下,对数据仓库从 Source  到 Staging 增量数据的处理可以按照这种方式:

  1. 对于具有维度性质的数据表可以在 Staging 中采取全卸载,全重新加载的模式。即每次加载数据的时候,先将 Staging 表数据清空掉,然后再重新从数据源加载数据到 Staging 表中。
  2. 对于具有事实性质的数据表,需要考虑使用上面通用的集中增量数据处理的方案,选择一个合适的方式来处理数据。保证在 Staging 事实中的数据相对于后面的 DW 数据库来说就是新增的或者已修改过的数据。

但是也不排除大维度表的情况出现,即具有维度性质的数据表本身就非常庞大,像会员表有可能作为维度表,动辄百万甚至千万的数据。这种情况下,也可以考虑使用合适的增量数据加载策略来提高加载的性能。

至于从 Staging 到 DW 的这一过程,通常情况下包含了维度 SCD 过程和事实 Lookup 过程,这个在后面再陆续写。关于缓慢渐变维度 Slowly Change Dimension 的相关理论文章可以查看我的这篇博客 - 数据仓库系列 - 缓慢渐变维度 (Slowly Changing Dimension) 常见的三种类型及原型设计

在 SSIS 中的实现可以参看我的这篇博客 - SSIS 系列 - 数据仓库中实现 Slowly Changing Dimension 缓慢渐变维度的三种方式

其它的加载策略

增量加载的处理策略不是一成不变的,采取哪一种加载策略跟数据源的设计有很大的关系。良好的数据源设计可能直接就给后续的增量处理提供了最直接的判断依据,比如自增长列,时间日期戳等。还有的数据源设计可能加入了触发器,在数据新增,修改或者删除的过程中就做出了有效的日志记录。或者加入了一些审核表,在数据的增删改过程中记录并跟踪了数据的操作细节,那么这样也是可以变通的采用上面的几种增量加载策略来设计符合当前系统的流程。

如何在增量加载之上更进一步?

还有一个非常重要的问题就是:如何处理在增量加载过程中失败的情况? 比如从 Source 到 Staging 的过程总共需要将数据写入到10个不同的 Staging 表,但是在数据加载的过程中由于一些意外情况导致其中5个表加载失败,其它5个表成功。由于考虑到效率问题,不想每次都重新加载,因此可以考虑采用以下两种方式:

第一种方式 - SSIS Package 过程处理日志和错误日志模式

 

在每次 SSIS Package 执行的时候,写入一条记录到 ProcessLog 中,ExecutionStatus = 0 表示开始。执行成功的时候,更新 ExecutionStatus = 1 表示成功。执行失败的时候,更新 ExecutionStatus = -1 同时在 Event Handlers 中记录一条 Error Log 来记录一些错误信息。

下面这张表反映了在 ProcessLogID = 372 这一批次增量加载的 Audit 信息表,当然甚至可以添加加载的条数等等信息。Process Log ID = 372 的在 Process Log 表中反映出来的是一次成功的执行。

第二次执行的时候就会去检查是否执行失败的 Process Log ,如果没有的话就根据 LastSegID 或者 LastModifiedDate 完成增量加载。

第三次执行的时候,发现 Audit 表中第二次有两条没有执行成功,因此只会对上次没有成功的两个表再次加载数据。

第二种方式 - SSIS Package 中的检查点

具体内容可以参看 - SSIS 系列 - 通过设置 CheckPoints 检查点来增强 SSIS Package 流程的重用性

通过这两种方式,可以使我们的数据加载流程更加合理一些。通过增量数据的加载模式减少了一部分不必要的数据加载提升了性能,那么在这个基础之上通过日志和检查点模式在增量模式之上提高了加载过程的可重用性。已经加载过的,不再重复加载。加载失败了的,重新加载,这样对包的性能和健壮性又是一种提升。

不足之处就是第二次加载之后,由于有两个表加载成功,另外两张表加载失败。因此等失败的表重新加载之时数据源可能已经发生变化,这样造成成功与失败的表面对的数据源有所不一致,这一点在设计阶段需要考虑,这种变化是否在允许的范围内。


总结:增量数据加载的策略与数据源有莫大的关系,也与实际需求有莫大关系,因此在设计增量数据加载的过程中需要围绕实际需求以及数据源能够提供的增量变化特征仔细思考,甚至反复测试来达到加载机制的稳定和可靠性。 

分享到:
评论

相关推荐

    Oracle的增量数据处理方案

    Oracle CDC 增量数据处理方案 Oracle CDC(Change Data Capture)是一种数据增量处理技术,在构建数据仓库系统的 ETL 过程中,增量数据的抽取是一个非常关键的环节。CDC 能够帮助识别从上次提取之后发生变化的数据...

    SVN数据库增量备份脚本

    标题提到的“SVN数据库增量备份脚本”是为了定期或按需备份SVN服务器的数据,特别是针对数据库的变化部分。这种脚本通常基于shell、Python或其他编程语言编写,可以自动化执行备份任务,提高效率并减少人为错误。 ...

    ETL中的数据增量抽取机制研究

    根据不同的应用场景和技术要求,数据增量抽取机制有很多种实现方式,常见的包括但不限于以下几种: 1. **时间戳方法**:利用源数据中的时间戳字段来判断数据是否发生了变化,从而确定哪些数据需要被抽取。 2. **...

    数据仓库数据仓库和我们经常提到的数据库有哪些区别

    这主要集中在多维分析、数理统计和数据挖掘等领域。近年来,随着互联网技术的发展,多维分析工具和产品越来越注重提供基于Web的联机分析处理(OLAP)界面,以便用户能够方便地在线访问和分析数据。 #### 数据仓库与...

    基于oracle的增量数据采集实现总结

    增量数据采集是一种有效的方法,它只捕获自上次采集以来发生改变的数据,从而节省存储空间和提高处理效率。在Oracle数据库环境下,常见的增量数据采集方法有触发器、时间戳、日志解析(如LogMiner)以及Change Data ...

    CDC(Change Data Capture)增量抽取

    异步CDC模式根据实现机制的不同,又可以分为以下几种: - **异步HotLog模式** - **原理**: 通过解析源数据库的在线重做日志文件(onlineredologfile)来捕获数据变化。这种方式相比于同步CDC模式来说,虽然不是完全...

    MySQL数据库实时增量同步工具源代码.zip

    综上所述,MySQL数据库实时增量同步工具源代码的分析和利用对于数据库管理员和开发者来说,是一次深入学习和优化数据库管理策略的好机会,同时也为实现高效、可靠的分布式数据系统提供了可能。通过理解和改进源代码...

    数据仓库数据更新的研究及基于Oracle数据库的开发与应用.pdf

    论文"数据仓库数据更新的研究及基于Oracle数据库的开发与应用"探讨了数据增量更新的方法,特别是在确定数据源中新数据的问题上提出了几种解决方案。其中,Oracle数据库的数据更改捕获机制(CDC,Change Data Capture...

    ETL增量原理

    ETL增量处理是ETL流程中的一个重要部分,它能够在数据源发生更改时,仅处理新产生的或已更新的数据,从而提高数据处理的效率和资源利用率。 增量处理的原理主要分为全量处理和增量处理两种模式。全量处理是指每次...

    qq西游单机服务端数据库备份sql2005

    在本压缩包中,我们看到的文件主要有以下几种: 1. **HQXYDBCenter.bak** 和 **HQXYLogin.bak**:这是SQL Server数据库的备份文件,使用`BACKUP DATABASE`命令创建。`.bak`文件包含了数据库在特定时间点的完整结构...

    《数据库系统概念》数据库PPT

    8. 数据备份与恢复:为了防止数据丢失,数据库系统提供备份和恢复机制,包括日志记录、增量备份和完全备份等策略。 9. 性能优化:通过索引、查询优化、分区等手段提升数据库的读写速度和响应时间。 10. 数据仓库与...

    西工大数据库试题大全

    复习资料通常会提供更深入的解释、示例和练习,帮助考生巩固这些知识点,并可能包含历年真题、解答解析以及一些高级主题,如大数据、NoSQL数据库、数据仓库和数据挖掘等。 通过认真研究和练习这些试题,考生不仅...

    Flink CDC + OceanBase 全增量一体化数据集成方案.pdf

    2.数据仓库建设: Flink CDC + OceanBase 可以用于建设数据仓库,实现数据的实时同步和分析。 3.实时数据分析: Flink CDC + OceanBase 可以用于实时数据分析,实现数据的实时处理和分析。 Flink CDC OceanBase ...

    数据库系统实现 数据库系统实现 数据库系统实现

    首先,数据库系统是一个组织、存储和检索数据的软件系统,它提供了一种有效的方式来管理和处理大量结构化数据。在实现过程中,主要涉及以下几个核心组件: 1. **数据模型**:数据模型是数据库系统的基础,包括概念...

    2008年上半年全国软考数据库系统工程师试卷

    熟悉几种并发控制机制,如锁定协议(共享锁和排他锁)、多版本并发控制(MVCC)和时间戳排序算法。 7. 数据库性能优化:学习索引的类型和使用,以及如何通过索引来提升查询性能。理解查询优化器的工作原理,了解...

    数据仓库.pptx

    数据更新策略通常有增量和全量两种,结合实时和定期的调度策略,以满足不同场景的需求。存储方案则根据在线存储和离线存储的需求,选择关系型数据库、列式数据库或NoSQL数据库等。此外,ETL(提取、转换、加载)设计...

    华为数据仓库面试题

    4. **Cube计算**:根据数据仓库的设计,对增量数据执行Cube的计算逻辑。这通常包括聚合、分组和计算汇总值。计算过程中,可能需要考虑如何高效地合并现有Cube数据和新数据,以避免重复计算。 5. **Cube更新**:将...

    经典的数据仓库教程经典的数据仓库教程

    "# 数据库来说,合理的内存分配是提高数据处理速度的关键因素之一。内存分配主要包括以下几个方面: 1. **缓存池**:缓存池是数据库管理系统用来缓存数据页和索引页的内存区域。在小规模数据仓库环境下,适当增加...

    数据库之详细介绍.zip

    2. 非关系型数据库(NoSQL):如MongoDB、Cassandra、Redis等,适用于大数据处理和分布式系统,支持多种数据结构,如键值对、文档、列族和图形数据库。 3. 文档数据库:如MongoDB、CouchDB,以JSON、XML等文档格式...

    2007年下半年数据库系统工程师上下午试题和答案.doc

    4. **数据库管理**:存储管理、备份恢复策略、性能调优(索引、查询优化)、并发控制和死锁处理。 5. **数据库安全**:用户权限管理、角色、视图、访问控制列表(ACL)、加密技术等。 6. **数据库系统架构**:集中...

Global site tag (gtag.js) - Google Analytics