首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >【SQL技能】SQL技能对于ETL开发人员的重要性

【SQL技能】SQL技能对于ETL开发人员的重要性

作者头像
陆勤_数据人网
发布2018-02-27 15:02:51
1.9K0
发布2018-02-27 15:02:51
举报

我最初是一个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 < 500 THEN ‘POOR’

WHEN sal between 500 and 1000 THEN ‘MID’

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_date

FROM (

SELECT Custid,

Trans_date,

Row_Number() OVER(PARTITION BY Custid ORDER BY custid, Trans_date desc) Rowno

FROM Cust_Trans)

WHERE 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项目成功。

作者:Shajesh Nair

译 者:Daniel Zhen

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2015-12-18,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 数据科学与人工智能 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档