SQL技能对于ETL开发人员的重要性.doc

SQL技能对于ETL开发人员的重要性 (2009-12-11 00:18:41)转载标签： sql etl it分类： DataBase作者：Shajesh Nair译 者：Daniel Zhen我最初是一个Oracle开发者，我喜欢它的结构化查询语言，一年后，我意识到SQL并非Oracle的专有。 作为70年代Sequel标准的一个分支，SQL走向成熟并且成为全世界数据库用户广泛应用的语言。其一是因为SQL简单（基于英语词汇），同 时它又能解决很多复杂的问题。SQL是当代最容易学习和使用的语言之一。ANSI-SQL标准几乎被所有主流关系型数据库所接受，如Oracle,DB2 和SQL Server，当客户决定从一个数据库迁移到另一个时，它极大地提高了可移植性。 在接触ETL工具前，将近五年的时间我都在创建复杂的Oracle程序语言/SQL代码。作为SQL专家，我最初的ETL路线图是：源结构-复 杂SQL-目标，所有复杂的转换逻辑都是用SQL写的。 我很少使用ETL工具所提供的很多优秀特性。别误会我，我不是SQL取代ETL工具的鼓吹者，因为ETL工具会比SQL提供更多的功能。实际上，我 是个ETL工具的支持者，因为它的非常易用，有图形化用户接口，有连接到不同环境中进行数据抽取和装载的能力。当今很多ETL工具都有从不同源系统读取数据的能力，如COBOL文件，平面文件，XML， 数据库和对实时数据捕捉的扩展支持。ETL的一些缺点虽然，ETL工具比较健壮，但它也是有缺点的，它有时候不能写出高效的SQL查询并能在数据库外得到最佳性能。我对这些在数据仓库项目中出现的问题 深有体会,有些表中的数据数以十亿计,也许百万亿。ETL工具会使用通用的方法来满足所有类型数据库，而不能使用数据库的特性来提升ETL处理性能。显 然，SQL的威力在于提升性能和降低ETL映射的复杂度。 数据库命令类型标准的数据库有三种类型的命令，DDL，DML和DCL。数据定义语言（DDL）-用来定义数据库结构（比如create,alter和drop等）数据操作语言（DML）-用来管理数据（比如select，insert，update和delete等）数据控制语言（DCL）-用来管理安全（比如Grant和Revoke等） 数据库内置ETL使用Select语句插入是最好的和最基础的ETL实例，使用select语句来进 行抽取和转换并使用insert语句完成装载。在下面的用于计算红利的语句中，应用SQL查询来进行数据转换是个非常好的例子。应用数据库端的SQL特性可以完成很多复杂的转换，比如“CASE”语句，它可以完成“If ,Else if, 和 Else ”的逻辑。我使用该特性在数据库端创建了很多复杂的转换逻辑。 比如： Select Empid, CASE WHEN sal 1000 THEN RICH ELSE N.A END As Status From EMP; 一些真实经验 我很愿意分享一些经验，当我受雇于一个大客户碰到很多问题时，在所提供的解决放案中 SQL占有很重要的位置。在一个ETL对应表中，我们需要基于时间戳查找特定ID的最后一条记录。这一逻辑可以通过复用组件共享给多个任务。在进入测试阶 段的UAT测试（用户接受度测试）之前，我们在开发阶段要进行逻辑测试和验证。然而，我们发现工具有些异常，对于某些ID值工具会产生出错误的记录，虽然 这在之前的开发阶段还没问题。这距离客户团队开始进行UAT就差两天了，花了一整天时间在工具中调试，无果。在数个小时的头脑风暴后，我最后建议“让我们 使用SQL查询的Analytical函数特性来修正记录吧”。“Analytics万岁！”我意识到Oracle天才Thomas Kyte的这条语句是多么有效。团队开始在数据库端使用SQL查询和Analytical函数特性重写整个逻辑。这一查询输出了正确的答案，我们为UAT 做好了准备。我重现了这一简单逻辑，这为我们展示了SQL的威力。SELECT a.Custid, a.Trans_dateFROM (SELECT Custid, Trans_date, Row_Number() OVER(PARTITION BY Custid ORDER BY custid, Trans_date desc) RownoFROM Cust_Trans) aWHERE a. Rowno = 1;在本例中使用了Row_Number函数，还有很多其它非常有用的analytic函数可以用作查询，比如rank和dense rank,lead和lag等。我并未深入某个函数的细节，因为你可以在网上根据例子中的函数检索出相关信息。 大 逃亡我使用工作中另一个例子来示例SQL知识的重要性。当我、 受雇于一家大药厂客户时，我们的团队创建的ETL任务在开发和测试阶段表现良好。它已通过了所有的压力测试，并接受了客户的UAT测试。移植入生产环境 后，在第一次运行时，ETL任务崩溃了。团队并不知道错在哪里，经过一阵抢修，团队意识到在产品环境中优化器被设置为基于规则，在开发和测试环境被设置为 基于损耗。就因为这个原因，优化器不能够使用索引，索引在理论上可以加速查询访问速度。没有时间去修正产品环境中的优化器设置了，这样做也会改变数据库的 其它设置。我们采用的解决方案是在select语句中用hint创建视图（译者注：hint会起到加速查询的效果），这样查询就会使用相应的索引了。团队 中出这个主意的人具有良好的SQL知识并知道如何在SQL中使用hint。虽然具有如上好处，我仍然要提出在ETL中使用复杂SQL 所带来的问题。根据公司元数据管理的需求，在ETL中开发清晰的具有端到端逻辑的数据流不是一件易事。在我的职业生涯中，我遇到过很多ETL开发人员都曾处于这 样的境地，ETL工具无法完成任务，当他们转到后台操作数据库时，主要用到的是SQL。我的经验是一个ETL开发者需要掌握数据库的基础。开发者 需要了解数据库的基本概念，比如数据类型，视图，索引，分区等。这些概念可以在ETL工具性能不佳以及工具的每种技巧都不起作用的时候。我建议开发展人员不但要掌握ETL工具，还要掌握SQL， 作为在ETL工具不能达成目的的条件下备用选择。我论 证的关键是SQL知识对于ETL开发人员的重要性。我的目的是，阐释SQL和ETL工具配合使用的方法，最终促使ETL项目成功。 译者介绍： 甄浩