如何处理ETL任务？

我应该执行ETL,其中源是一个庞大而设计糟糕的sql 2k数据库和一个设计更好的sql 2k5数据库.我认为SSIS是要走的路.任何人都可以提出待办事项列表或清单或要注意的事项,以便我不会忘记任何事情吗？我应该如何处理这个问题,以便以后不会在后面咬我.

一些一般的ETL提示

考虑按目的地组织它(例如,生成Customer维度的所有代码都存在于同一模块中,而不管源是什么).这有时被称为面向主题的ETL.它使查找内容变得更加容易,并且可以提高代码的可维护性.

如果SQL2000数据库很乱,您可能会发现SSIS数据流是一种处理数据的笨拙方式.作为一项规则,ETL工具的复杂性很差; 金融公司中所有数据仓库项目的一半都是用存储过程代码作为明确的架构决策来完成的 - 正是出于这个原因.如果必须在sprocs中放入大量代码,请考虑将所有代码放在sprocs中.

对于涉及大量复杂的清理或转换的系统,100%的sproc方法更易于维护,因为它是将所有转换和业务逻辑放在一个地方的唯一可行方法.使用混合ETL/sproc系统,您必须查看多个位置以跟踪,排除故障,调试或更改整个转换.

ETL工具的最佳位置是在您拥有大量数据源并且转换相对简单的系统上.

使代码可测试,因此您可以分离组件并单独测试.只能在ETL工具中的复杂数据流中间执行的代码要难以测试.

使数据提取没有业务逻辑,并复制到暂存区域.如果您在提取和转换层中分布了业务逻辑,那么您将无法单独测试转换,并且难以跟踪错误.如果转换从暂存区域运行,则可以减少对源系统的硬依赖性,从而再次提高可测试性.这是基于sproc架构的特别胜利,因为它允许几乎完全同质的代码库.

构建一个通用的缓慢变化的维度处理程序或使用现成的(如果可用).这样可以更轻松地对此功能进行单元测试.如果这可以进行单元测试,则系统测试不必测试所有极端情况,只需测试提供给它的数据是否正确.这并不像听起来那么复杂 - 我写的最后一篇是大约600或700行T-SQL代码.任何通用的擦洗功能都是如此.

尽可能逐步加载.

检测您的代码 - 让它生成日志条目,可能记录诊断,例如检查总计或计数.没有这个,几乎不可能进行故障排除.此外,断言检查是一种考虑错误处理的好方法(b中的行数相等,A:B关系实际为1:1).

使用合成键.使用源系统中的自然键将系统绑定到数据源,并且很难添加额外的源.系统中的键和关系应始终排列 - 没有空值.对于错误"未记录",在维度表中创建特定的"错误"或"未记录"条目并与之匹配.

如果您构建操作数据存储(许多宗教战争的主题),请不要回收星型模式中的ODS密钥.通过各种方式连接ODS键来构建维度,但匹配自然键.这允许您任意丢弃和重新创建ODS - 可能改变其结构 - 而不会干扰星型模式.拥有此功能是一项真正的维护成功,因为您可以随时更改ODS结构或对ODS进行暴力重新部署.

第1-2和4-5点意味着您可以构建一个系统,其中任何给定子系统的所有代码(例如,单个维度或事实表)都存在于系统中的一个且仅一个位置.这种类型的架构对于大量数据源也更好.

第3点是第2点的对应点.基本上,SQL和ETL工具之间的选择是转换复杂性和源系统数量的函数.数据越简单,数据源数量越多,基于工具的方法就越强.数据越复杂,基于存储过程迁移到体系结构的情况就越强.一般来说,最好是专门或几乎专门使用其中一种,但不能同时使用两者.

第6点使您的系统更容易测试.测试SCD或任何基于更改的功能是繁琐的,因为您必须能够向系统提供多个版本的源数据.如果将更改管理功能移动到基础结构代码中,则可以与测试数据集隔离地对其进行测试.这是测试的一个胜利,因为它降低了系统测试要求的复杂性.

第7点是一个通用性能提示,您需要观察大数据量.请注意,您可能只需要对系统的某些部分进行增量加载; 对于较小的参考表和尺寸,您可能不需要它.

第8点与任何无头过程密切相关.如果它在夜间出现问题,你需要一些战斗机会,看看第二天出了什么问题.如果代码没有正确记录正在发生的事情并捕获错误,那么对它进行故障排除将会更加困难.

第9点为数据仓库提供了自己的生命.当仓库有自己的密钥时,您可以轻松添加和删除源系统.仓库密钥也是实现缓慢变化的维度所必需的.

第10点是维护和部署胜利,因为如果您需要添加新系统或更改记录的基数,可以重新构建ODS.它还意味着可以从ODS中的多个位置加载维度(想一想:添加手动会计调整),而不依赖于ODS密钥.

我有ETL过程的经验,每天,每周,每月和每年将200多个分布式数据库中的数据提取到中央数据库.这是一个庞大的数据量,我们有许多问题特定于我们的情况.但正如我所看到的,无论情况如何,都有几个要考虑的事项:

确保在源端和目标端都考虑文件锁.确保其他进程没有锁定文件(并在必要时删除这些锁,这很有意义)非常重要.

为自己锁定文件.确保,尤其是在拔出数据时锁定文件的源,以便您不会获得中途更新的数据.

如果可能的话,拉取增量,而不是所有数据.获取数据的副本,然后仅提取已更改的行而不是所有内容.数据集越大,它就变得越重要.如果必须,请查看期刊和触发器,但是因为在某个基础上获得这些数据变得更加重要,这可能是我给你的第一个建议.即使它为项目增加了大量时间.

执行日志.确保你知道什么时候它工作,什么时候没有,并且在过程中抛出特定的错误可以真正帮助调试.

文件,文件,文件.如果你构建这个权利,你将构建它,然后很长一段时间不考虑它.但是你可以保证,你或其他人需要在某些时候回到它来增强它或修复错误.文档是这些情况的关键.

HTH,如果我想到其他任何事情,我会更新这个.