在前一篇文章中，我们探讨了数据应用如何做测试的问题。在数据测试中，ETL脚本的测试是个难题。一般而言，采用高集成度的测试方式（即运行ETL并比对结果，下文称集成测试）是更有效的做法。但是，这类测试的编写和维护却有较高的成本。如何降低ETL集成测试的成本呢？本文尝试从数据工具的角度分享一些我们的经验。

ETL集成测试的痛点

ETL集成测试的编写和维护成本高在哪里呢？

我们先来看如何编写一个ETL集成测试。

对于某一个ETL任务，其工作过程一般是三个步骤：读取数据、处理数据、将数据写入新表。为构建这样一个集成测试，我们需要完成这样几步：

为ETL测试构建一个运行环境；创建ETL需要读取的表，并将准备好的数据写入；创建ETL的正确输出结果表，并将准备好的数据写入；针对测试环境中的表运行ETL（可能需要做一定的修改，因为表名、数据库名可能不一样）；比对ETL任务生成的数据和第三步中构造的数据，如果两者一致，则测试通过。

一般而言，为了保证测试的有效性，我们希望构建一套类生产环境的ETL测试运行环境。如果额外搭建一套大数据平台环境，这带来的搭建和维护成本就比较高了。同时它还会带来一定的资源消耗，比如我们至少要准备3个节点的计算资源，且需要配置的CPU和内存还不能太低。

如果我们直接使用生产环境作为测试环境使用，这节省了集群的搭建和维护成本，但我们需要配置好运行ETL测试的用户权限，以防止在测试运行过程中错误的将数据写入了生产环境的数据库表。使用一套环境还有一个缺点，那就是在运行测试之前我们需要修改ETL脚本中的数据库名或数据表名，将它们指向测试对应的库或表。这也是一件麻烦事，而且极容易出错。

如果需要创建测试用的输入输出表，我们需要在准备测试用例时编写不少建表语句代码。同时，还需要注意数据表的Schema变化，比如某一天由于业务需要，我们将字段名字或类型做了修改，此时测试也不得不跟着修改。

比对数据这一步，也可能存在问题。由于并行计算的存在，ETL的输出结果中的数据很可能每次都不一样，是乱序的。此时我们在做比较的时候，需要注意将数据顺序对齐，这一般可以通过order by所有字段来实现。

ETL集成测试支持工具设计

从以上ETL集成测试痛点分析可以看出，如果不借助任何工具，只靠运维的手段来构建测试，将会非常复杂，且极易出错。如何解决这些痛点呢？我们可以考虑编写一个工具应用来做支持。

愿景

我们希望这个测试工具是轻量级的，简单可用。它不能直接产生价值，因此不能投入太多的精力。同时这个工具应当是易用的，尽量使得团队所有人都能一看就懂。这个工具还需要能辅助完成（或避免）前面痛点分析中的大部分复杂易错的操作。

需求及设计

要实现以上的愿景，第一个需要回答的问题是，使用者（开发人员或测试人员）应当如何和工具交互。

交互工具

在前面的文章数据仓库建模自动化中，我们使用Excel电子表格作为交互方式。这里可以参考前面的做法，依然使用电子表格来让用户构建测试。

电子表格不仅是大家常用的熟悉的工具，还是天生的编辑表格数据的工具，用来构建测试用例应当是非常合适的选择。而且现在很多可以协同编辑电子表格的工具，如Google Spreadsheet等，使得我们可以多人协作进行测试用例的设计和编写。

模板

电子表格可以非常灵活的编辑数据，为了使得用户可以创建测试用例，我们需要设计一个固定的模板。

测试文件和测试套件

模板应当包含哪些元素呢？

首先，我们需要能从电子表格测试用例中知道对应的被测试ETL文件是哪个。采用Convention over Configuration的原则，我们可以限制电子表格的文件名，要求其必须和被测试ETL文件名相同。

其次，在很多现有的测试框架中，多个测试用例可以被组织成测试套件来统一管理。我们也最好能支持测试套件和测试用例。如何在电子表格中定义测试套件和用例呢？可以利用电子表格的标签页Sheet。一个直观的想法就是，每一个标签页是一个测试套件，同时每个标签页内可以支持多个测试用例。

为了使得标签页与测试套件能对应起来，我们可以限制标签页的名字格式必须为suit_xx。这还能获得一个额外的好处，那就是用户可以自由的添加新的标签页（只要名字不符合前面的规范格式），用于记录一些和该测试相关的信息。

测试用例

测试用例如何在电子表格中呈现呢？分析测试用例的必备元素，我们可以知道，一个测试用例应当包含：用例名、输入变量（用于支持ETL中的变量引用）、输入表、输出表。其中，输入表和输出表可以有多个。

在测试用例中，输入表无需填入原表的全部字段，只填入当前ETL需要用到的字段即可。输出表也可以只包括ETL输出表的部分关键的待验证的字段。

多个输出表的设计可以很好的支持对中间输出结果的验证。这一设计是更灵活的，它可以使得我们有能力测试到ETL的中间结果，而不只是对ETL的输出进行验证。

注意到用例中的各类信息的格式是不一样，用例名是一个字符串，输入变量可以是一个包含键值对的字典，输入表和输出表是表格。此时我们可以单独一列来标记信息类型。字符串可以直接置于信息类型旁边的单元格，字典可以置于信息类型旁边的两行表格区域（第一行表示键，第二行表示值）。

如下图示例，我们设计了一个用例用于测试一个统计空调销量的ETL。该ETL的输入表是DWD层的订单表和用户表，输出表为销量统计信息表。要运行此ETL，需要指定一个变量DATA_DATE，表示需要计算的时间。如果还需要支持其他变量，可以直接在旁边扩展添加。

表格

表格格式的支持非常简单，因为它可以和电子表格可以直接对应起来。在对应的输入类型后面，我们可以添加一列表示表名，如上图所示的订单表dwd.fact_order_h、用户表dwd.dim_user_h、输出表dm.order_sales_count。表名后面就可以是数据内容了。为了使得表格更容易理解和编辑，我们可以给列名所在的行标记一下特殊的颜色。如下图所示。

上图中，我们把测试数据也填充了进去。事实上，在输出表格的数据中，我们还可以利用电子表格的公式功能来计算数据。比如，输出表格中的province值四川是通过用户表中的province值得来的，于是我们可以使用公式=E10来填充其值。如下图所示。

使用公式来计算输出数据，可以帮助测试用例设计人员更好的理解输入和输出间的关系，大大加强了测试用例的可读性。在实际编写测试用例时，应该鼓励大家多使用这个功能。

业务注解

我们在前面的文章数据测试实践中提到，ETL代码更容易出错的一个地方在于开发人员对业务和数据的理解不足。是不是可以通过测试用例的设计促进大家去加强对业务和数据的理解呢？

注意到输入表格的表名下面的单元格都是空的，是不是可以利用这个地方来加入一些数据注解呢？

事实上，我们可以要求测试用例设计人员必须对每一行数据添加注解，以注明数据的来历。这可以当做测试工具强加的一个设计限制。

这一设计虽然表面上看增加了测试用例设计人员的工作量，但是它带来的价值是巨大的。比如：

可以很好的促进测试设计人员去思考数据来源，以便加强对业务和数据的理解；测试设计人员更加不容易设计出一些根本不存在的数据组合，从而保证测试的有效性；可以进一步增强测试的可理解性。

上述第二点非常重要，因为如果按照一般的软件测试用例设计方法去设计测试，则可能会设计出很多根本不存在的边界场景。比如示例中订单表的user_key字段是关联到用户表的外键，在业务上它不可能为空，但是如果没注意到这样的业务限制，则可能设计出为空的用例场景来。

由于输入数据是业务系统产生的，业务系统本身会对数据加入很多业务限制，所以很多数据组合是根本不会出现的，当然也没必要设计测试用例去覆盖了。如果设计出了很多类似上例这样的无效测试，则将对团队的测试构建和维护将带来非常多没必要的负担。

通过标注数据来历，可以较好的解决这个问题，因为它要求测试数据设计人员必须思考数据来历，从而促进测试设计人员去弄清当前数据是通过什么样的业务流程产生的。

下面是一个添加了注解的测试用例。

ETL集成测试支持工具实现

到这里，一个ETL集成测试支持工具的雏形已经显现出来，下面就是如何实现这一工具了。

考虑到电子表格不方便进行版本管理，我们可以参考之前的做法，尝试定义一个中间测试文件格式来存储电子表格中的测试用例。于是我们的工具的工作流程就变成：

我们需要开发两个工具，一个进行格式转换，另一个执行测试。

格式转换工具

格式转换工具只需要读取电子表格，然后将其内容保存为另一个格式即可。

选择什么格式呢？常用的保存数据的文件格式可以是json yaml等。这里我们考虑选择json格式。

在进行数据存储时，需要注意中间文件的可读性。一是可以考虑存储格式化之后的数据。二是需要关心格式化后的数据是否易读。

由于测试用例中有很多表格数据，如果我们把数据存储为一个二维列表（或对象的列表），文件内容将会非常长，可读性会较差，也不便于进行版本比较。一种更好的方式是将表格数据中的一行存储为文件中的一行，可以考虑使用嵌套的json来存储表格数据。比如以下示例：

使用Python，我们可以很容易的读取电子表格的内容。这里将会碰到的一个问题是表格中的数据类型的处理。在电子表格中，我们并没有设计一个地方让用户填入数据类型，这可以提升用户体验，但是在工具实现上就会更复杂一些了。

我们需要想办法找出数据的类型，还需要将电子表格中的数据转换为真实的数据类型。怎么做呢？

在前面的文章数据仓库建模自动化中，我们提到了使用电子表格来做建模，电子表格会记录建模得到的所有表的字段的类型。这可以作为字段类型信息提取的入口。另一方面，如果是中间表，或者DWB层的表，此时我们可以将建表语句放到代码仓库中进行管理，于是可以从这些建表语句中提取字段类型。

有了字段类型，还需要实现一系列的转换规则，以便将电子表格中的数据转换为真正的类型进行存储。我们还可以把这些附加的信息写入到中间的测试用例文件中。一个较为完整的测试用例中间文件可以为：

[ { "name": "正确统计空调销量", "etl_sql_name": "dm.order_sales_count.sql", "vars": { "DATA_DATE": "2021-04-25" }, "inputs": [ { "name": "dwd.order_h", "columns": "[\"order_key\", \"order_id\", ... ]", "column_types": "[\"string\", \"string\"]", "value_descriptions": [ "用户u1创建订单", "用户u1付款", "用户创建u2订单" ], "values": [ "[\"oa\", \"o1\", ...]", "[\"ob\", \"o1\", ...]", "[\"oc\", \"o2\", ...]" ] }, ... ], "outputs": [ { "name": "dm.order_sales_count", "columns": "[\"province\", \"is_reward\", ...]", "column_types": "[\"string\", \"boolean\", ...]", "value_descriptions": [], "values": [ "[\"四川\", false, ..." ] } ] }, ... ]

测试执行工具

测试执行工具的功能是读取格式转换工具的输出，并执行测试。

首先我们要确定执行的环境。上面分析了如何选择执行环境，我们发现无论使用生产环境还是一个独立的测试环境代价都比较高。怎么办呢？这里可以考虑降低一些集成度，选择直接使用本地的Spark环境来执行测试。虽然和真实的环境会有一些差别，但是考虑到它可以节省很大的工作量并降低风险（无需操作生产环境），这样的取舍是值得的。

如果使用本地Spark环境，我们需要先为测试用例创建数据表，并写入数据。此时Spark会创建一个临时的数据库用于存储元数据，我们需要指定配置spark.sql.warehouse.dir以便可以设置一个用于存储数据的位置，比如可以设置为一个临时目录/tmp/spark-warehouse-sqltest。（此处如果设置为一个静态的临时目录，可能无法同时运行多个测试，如果有并行运行测试的需求，可以考虑随机生成一个临时目录。）

在运行ETL之后比较结果时，可以采用前面提到的策略，即将所有字段进行排序，然后依次比较每一行数据。

生成标准测试用例

测试执行工具本身可以是一个小的应用程序，我们可以通过它来直接运行测试。但是这对开发人员还不够友好，因为它与开发人员常用的测试方式不一样，很多现有的工具无法使用。比如，PyCharm可以识别用Python的测试框架unittest编写的测试，点击代码旁边的运行按钮就可以直接运行。

如何支持这个特性，以便提升开发人员的体验呢？

我们可以考虑在生成中间用例文件的时候，再生成一个Python版本的测试用例源代码文件。在实现上，这也不难，使用Jinja模板，可以很容易生成这个文件。

其他

到这里，我们的数据集成测试工具就差不多实现了。工具本身还有哪些可扩展的地方呢？在使用这个工具时，有没有什么需要注意的问题呢？下面有几个点值得一提。

在测试用例中同时运行多个ETL

有时候，我们的ETL可能会被拆分为多个更小的ETL。比如，当某个ETL比较复杂的时候，或者，当某个ETL的一部分代码可以被复用的时候等。

我们可能会有需求想要在一个测试用例里面运行多个ETL脚本，此时可以考虑扩展上面的ETL测试工具，使之可以支持选择需要包含的ETL文件。

在电子表格设计上，可以考虑新建一个标签页，然后在这一页中存储需要包含的ETL文件列表。事实上，类似ETL文件列表这样的信息可以抽象为测试用例的元信息。在将来我们还可能加入更多的类似元信息。于是，我们可以将这个标签页命名为meta_info。

什么时候编写ETL集成测试

本文讨论的ETL测试工具固然可以提高ETL测试的构建效率，但在使用过程中，需要特别注意的是，我们需要弄清楚何时应该用它来编写测试用例。

需要记住，每添加一个测试用例，都会增加一定的维护成本。这些成本包括：

此类测试由于集成度比较高，运行起来并不快。当我们有成百上千个此类测试时，跑一遍所有的测试可能需要数十分钟到数小时。当我们需要修改ETL代码时，需要同时修改测试用例。

在这里，我想强调在文章数据测试实践中提到的内容。

加深对业务和数据的理解，可能比编写ETL测试更重要；通过SQL自定义函数来降低ETL中的复杂度，可能比编写ETL测试更重要；通过代码评审和结对编程来保证ETL质量，可能比编写ETL测试更重要。

为测试人员创建ETL测试环境

除了开发人员需要编写测试，测试人员也需要编写测试。ETL测试工具基于电子表格和用户进行交互，所以即便只有较少的编程背景，测试人员也可以很方便进行测试用例的编写。

但是，如果测试人员还需要构建一套本地的执行环境，这就不太友好了。我们可以考虑为测试人员搭建一套测试用的本地执行环境，然后将此环境部署成为一个Web服务。一旦可以将测试工具服务化，测试人员只需要打开网页便可以运行测试了，这将大大提高团队的工作效率。

有一个非常简单的可以实现测试工具服务化的办法，就是利用CI工具。比如在Jenkins中，每一个任务都可以设置一个参数，也包括一个文件参数。那么我们可以在Jenkins中创建一个用于运行测试的任务，然后只需要用户上传一个电子表格即可触发测试运行。

有了这样服务化之后的测试工具支持，相信团队内部的测试小伙伴们也可以愉快的工作了。

总结

本文讨论了如何构建一个ETL集成测试工具，以便用于解决我们编写及运行ETL测试中的痛点。

利用Excel电子表格，我们构建了一个轻量级的工具。它可以方便的支持测试用例编写和运行，能有效提高团队工作效率。

ETL测试工具虽然好用，但我们还需要谨慎对待构建测试这件事，因为过多的测试可能会带来更高的维护成本。

基于在多家企业多个项目的经验总结，我们沉淀出一套企业数据开发工作台，可以帮助团队高效交付数据需求，帮助企业快速构建数据能力，工作台地址：https://data-workbench.com/

我们开源了其中的核心模块--ETL开发语言，开源项目地址：https://github.com/easysql/easy_sql