ETL测试框架

最近写了一个针对数据仓库ETL的测试框架，baidu google了一下发现还没有非常靠谱的同类型框架或解决方案，就忍不住提前分享一下（其实是因为周五下午不想干活）。

首先分享一下我们过去测试ETL的方法：很简单，就是写两段SQL分别query上下两层数据，然后通过数据库的minus方法来得到不符合预期的数据，进而进行分析。例如

-- Source

select
	src1.pk
	, case
	when src1.lkp_ky is null then
		-2 -- not available
	else
		case
			when src2.some_ky is null then
				0 -- not found
			else
				src2.some_ky
		end
	end some_ky
from
	(select * from source_table_1 where lgcl_del_fg = 'n') src1
left join
	source_table_2 src2 on src1.lkp_ky = src2.lkp_ky

minus

-- Target

select
	pk
	, some_ky
from
	target_table

用SQL写测试用例是可行的的，但是有很多我认为不够好的问题：
1. SQL可读性非常差。
一个ETL的mapping有十几二十个字段很正常，写出来的SQL最后一看超过二百行也很正常，但是如果能让别人一眼看懂就不正常了：对于数据集之间的连接，是在from下面进行，而对于连接好的数据的操作，是在select下面，from上面进行（即上例中的case when语句），这种憋屈的语法结构会让review的人很头疼，导致后期维护也会痛不欲生。

2. SQL写ETL逻辑的时候有点捉急……
SQL毕竟不能完全算是编程语言，虽然提供了很多数据的操作方法，但是比起正儿八经的编程语言还是略逊一筹……相信用SQL测过ETL的人都有过力不从心的感觉。我认为这也是ORM出现的根本原因！

3. SQL一点都不灵活！
这点一时不知道怎么说才好，因为我根本不知道SQL跟灵活有什么关系。不过看完我们提供的方案后，希望读者能感受到：这TMD才是灵活！

总之篇幅关系，我就不继续埋汰SQL了，重点还是介绍我们的方案：提供一套取代SQL的方法来编写测试用例。

先看一下我们实现上面sql的方式：

mapping("test_etl") do

	declare_target_table 'target_table', :t
	declare_source_table "select * from source_table_1 where lgcl_del_fg='n'", :src1

	m t.pk, src1.pk

	m t.some_ky do
		declare_source_table 'source_table_2', :src2
		left_join src2, "src1.lkp_ky = sr2.lkp_ky"
		if src1.lkp_ky.nil?
			-2 # not available
		else
			if src2.some_ky.nil?
				0 # not found
			else
				src2.some_ky
			end
		end
	end

end

这里说明一下，框架用ruby开发，用例就是ruby代码，如果看官您不懂ruby，请不要转身离开，我们做了最够多的工作在框架本身，暴露给编写用例的测试人员的都是最基础的，既普通的if else一类的控制语句以及字符串数字的操作，所以完全不用为了使用此框架来额外学习很多ruby知识。

开始解释我们的用例：
mapping表示着我们用例的开始，后面的“test_etl”相当于这个用例的名字，后面生成报告时会用到这个名字。mapping后面的do ... end中编写我们一个mapping的所有逻辑。

declare_source(target)_table 方法用来定义我们使用到的表（或者sql），第一个参数是表名，第二个参数是别名，定义好别明后，下面的代码即可应用别名来代替包。
比如declare_source_table "wo_qu_nian_mai_le_ge_biao", :biao
之后就可以用biao.id, biao.name 来表示表的id跟name字段。(顺便一提，我们还提供了CTE的定义。）

m方法用来表示target表的column跟source是怎么对应的。如果没有任何处理直接照搬过去可以用“m t.pk, src1.pk”来表示。
如果要经过转换才能得到目标column，可以把转换逻辑写在m后面的 do end里，如

	m t.some_ky do
		declare_source_table 'source_table_2', :src2
		left_join src2, "src1.lkp_ky = sr2.lkp_ky"
		if src1.lkp_ky.nil?
			-2 # not available
		else
			if src2.some_ky.nil?
				0 # not found
			else
				src2.some_ky
			end
		end
	end

do end里是纯的ruby代码（其中的left_join是框架自己的方法），可以非常清晰的表达转换逻辑。

看到这里可能有人会说：这不是换汤不换药么？看不出比上面那段SQL强在哪里。
首先我们的用例可以直接把source target的mapping关系非常直观的表现出来，其次，我们能做到join表跟使用表写在一起，即我能清楚的知道我join这张表是干什么用的。如果用例需要join非常多的表，这种设计对于用例可读性的提升是巨大的！同时如我之前说的，do end里面可以用ruby代码写逻辑，而ruby比SQL操作数据轻松，比如ruby可以轻松实现字符串的split功能，据我所知oracle目前还没有提供split功能。‘

当然这还不是全部，我刚才还说我们的方案非常灵活。

1. 首先可以非常方便添加参数：比如再etl实际运行中可能会用到sysdate（系统时间），但是测试运行时sysdate很可能跟之前sysdate不一样，我们测试时要赋一个值给sysdate，这时我们就可以把sysdate作为一个参数。

2. 可以添加动态的变量：比如我们每次测试时需要找到最新的数据，即每次都要得到一个max(date)，这个过程可以非常方便得定义在用例里。

3. 可以引用外部数据：测试过程中有可能需要读取一个文件的数据，或者访问webservice得到数据，这个过程也可以定义到用例中！只要ruby能解决的问题，框架都能解决……

4. 引用之前的计算结果：写sql时，经常发现之前得到的计算结果，后面居然没有办法直接用。如我的source有一个学生的各科成绩，首先我要得到学生的总分，然后要根据学生的总分来判断学生是优等生还是差生。如果用sql的话我要这样写：

select
	std_id
	, (score1 + score2 + score3 .... + scoren) total_score
	, case
		when (score1 + score2 + score3 .... + scoren) >= 600 then
			'NB'
		when (score1 + score2 + score3 .... + scoren) >= 400 (score1 + score2 + score3 .... + scoren) < 600 then
			'Normal'
		else
			'SB'
	end grade
from
	score

当然也可以用CTE

with t as(
	select
		std_id
		, (score1 + score2 + score3 .... + scoren) total_score
	from
		score)
select
	std_id
	, t.total_score total_score
	, case
		when t.total_score >= 600 then
			'NB'
		when t.total_score >= 400 t.total_score < 600 then
			'Normal'
		else
			'SB'
	end grade
from
	score inner join t on score.std_id = t.std_id

嗯，再看看我们怎么做的

mapping("score_grade") do

	declare_target_table 'target', :t
	declare_source_table "score", :score

	m t.std_id, score.std_id

	m t.total_score do
		score.score1 + score.score2 + score.score3 ... + scoren
	end

	m t.grade do
		case
		when row[:total_score] >= 600
			'NB'
		when row[:total_score] < 600 && row[:total_score] >= 400
			'Normal'
		else
			'SB'
		end
	end

end

请容我先自我陶醉一会……

现在简单介绍一下我认为最牛逼的功能：测试覆盖率
我们的方案可以检测到数据是否能覆盖所有分支！！！从而能彻底杜绝很多传统测试方法难以检查到到的隐患。

请让我再陶醉一会……

现在我们提供了一套完全替代sql的书写用例方法，这种用例书写方法不仅灵活，而且可读性极强。我做这个框架的灵感就来源于看到设计文档时，想如果文档能当用例运行起来就好了，现在我做到了……我们的用例跟ETL的设计文档相似度已经很高了。

用例管理/运行/数据验证/报告生成方面，包括给user提供扩展的接口我们也做了，但是感觉没有太大的新意，就不详细介绍了。

我们的框架现在还在测试中，如果稳定了，我会把gem（ruby的代码包）pull到rubygems.org，还会有更详细的文档介绍上面提到或者没提到的功能，如果大家有兴趣可以联系我一起讨论。部分代码可以在https://github.com/piecehealth/ETLTester 得到

评论

3 楼 hbbbs 2013-05-30  

看了楼上的你那段关于 Ruby扩展基本的数据类型的例子，感觉ruby很是灵活！难怪Ruby有这么多的Fans

2 楼 piecehealth 2013-05-27  

hbbbs 写道

   不错~
复杂的SQL阅读起来确实很不直观。

博主的这种方式在编写复杂SQL和阅读方面都比SQL强很多。
如果编写时能更简洁点的话，那就更好了！

其实写得复杂程度是跟逻辑的复杂程度有关，这种写法可以保证跟需求文档的复杂度差不多。不过要想更简洁也有办法，ruby可以扩展基本的数据类型，比如按照分数分A, B, C, D, E代码是

case
	when score >= 90
		'A'
	when score >= 80 && score < 90
		'B'
	...
	else
		'E'
end

但是可以把这段代码作为数字类型自己的方法

class Numeric
		
		def to_grade
			case
				when self >= 90
					'A'
				when self >= 80 && self < 90
					'B'
				...
				else
					'E'
			end
		end
end

然后写逻辑的时候直接用score.to_grade就好了，达到简洁目的。这也是借助ruby的灵活做到框架的灵活。

1 楼 hbbbs 2013-05-27  

   不错~
复杂的SQL阅读起来确实很不直观。

博主的这种方式在编写复杂SQL和阅读方面都比SQL强很多。
如果编写时能更简洁点的话，那就更好了！