基于HIVE数仓离线报表测试方法 | 微盟技术

本文首发自 微盟技术中心 微信公众平台~

一、引言

随着业务不断的发展和商户对数据可视化的诉求不断的增加，BI数据报表作为一个重要的可视化工具，已经得到了广泛的应用。由于BI报表的数据来自内部对接的多个业务团队以及外部不同的数据源，过程复杂的数据处理，因此对数据准确性的保障和测试效率面临着不小的挑战。

二、摘要

由于报表类的测试方法、测试工具与业务测试有一定的差异，本文结合在微盟 BI 报表测试的实践，梳理基于 HIVE 数仓离线报表的测试方法，包括 BI 报表制作流程、BI测试的特性、测试SQL的编写方法以及报表迭代影响范围的分析方法和测试使用数据质量工具的场景。

三、BI报表制作流程**

BI 研发根据需求按需配置好业务 MySQL 表同步到 HIVE 中，经过清洗后的结果数据同步到StarRocks存储，通过BiStudio关联StarRocks表制作符合报表控件的数据集，产品再根据数据集拖拉拽的方式完成报表页面的开发，然后由数据运营人员配置版本菜单发布WOS系统。

四、 BI测试的特性

4.1 测试对象

报表页面没有复杂的功能逻辑，更多的是维度指标数值，因此报表的测试对象是数据的核对。

4.2 测试方法

相比较业务测试，BI 测试方法通常是熟悉业务口径结合产品需求分析、梳理测试SQL 核对准确性。

五、离线报表测试方法**

BI 属于数据开发平台、数据应用的中间层，所以对数据的来源、去向需要有一定的了解并且对数据有较强的敏感度，需求评审能够判断现有数据能否满足以及应该用哪些表取数，报表测试的策略偏向于“内心功法”聚焦数据分析 SQL 核对，“外在功法”依赖数据开发平台能力来保障任务的及时性、稳定性、准确性。

5.1 需求分析阶段

根据产品提供的需求和指标口径抽象化分析报表需求，梳理涉及的指标和维度包含通用性维度（日期粒度、节点类型、全部节点、当前节点、下级汇总等）。

5.2 测试SQL编写

1、查询范围：查询同粒度数据（例如：订单级指标需要和订单商品级指标同时查询），如果测试 SQL 过于复杂可能会导致过程逻辑变动不能快速调整影响执行进度。2、查询指标：SELECT 聚合同粒度数据尽可能包含更多的指标，减少查询次数，提高核对效率。3、查询维度：分组中尽量包含更多的维度，在报表中维度下相同的指标场景比较多，一次查询覆盖更多的场景核对。

-- 聚合订单数据，计算总应收金额、总实收金额和总支付件数
SELECT
    o.source_channel,               -- 订单来源渠道
    o.access_channel,               -- 订单访问渠道
    SUM(o.receivable_amount) AS total_receivable,  -- 总应收金额
    SUM(o.actual_amount) AS total_actual,          -- 总实收金额
    SUM(od.payment_count) AS total_payment_count   -- 总支付件数
FROM
    orders o
JOIN (
  SELECT
  order_id,
  bos_id,
  SUM(payment_count) AS payment_count
  FROM
  order_details
  GROUP BY
  order_id, bos_id
) od ON o.order_id = od.order_id AND o.bos_id = od.bos_id
WHERE
    o.dd = "${dd}"                
    AND o.bos_id = "${bos_id}"     
    AND o.vid = ${vid}       
GROUP BY
    o.source_channel, o.access_channel;

5.3 影响范围分析

涉及迭代的报表根据数据平台的功能检查任务的上下游影响。

• 使用离线任务中chekout检查当前任务修改的SQL和指定版本差异。

• 根据任务产出表详情分析任务改动的内容对下游任务的影响，可以作为报表抽样回归的依据。

5.4 数仓之内复杂度降维

数仓模型表提供了业务公共的明细/维度指标，并且模型表逻辑通常不会频繁的改动。经过测试验证的 DWD、DWS 模型无需再从 ODS 编写复杂的 JOIN 和 WHERE 条件，更利于测试语句的编写效率和测试 SQL 的沉淀。下面是基于上文的查询 SQL 示例转变成从 DWD 查询，逻辑更清晰并且遇到数据错误的问题研发更好的判断问题所在。

SELECT
    source_channel,
    access_channel,
    SUM(receivable_amount) AS total_receivable,
    SUM(actual_amount) AS total_actual,
    SUM(payment_count) AS total_payment_count
FROM
    dwd.order_info
WHERE
    dd = "${dd}"
    AND bos_id = "${bos_id}"
    AND vid = ${vid}
GROUP BY
    source_channel, access_channel;

5.5 数据仓之外功能拆解

从报表制作的流程图中可见报表配置工作也是非常关键的一部分，既然是配置必然存在通用功能，在报表的测试中，对于工具自身的通用功能不需要额外投入太多的功能验证，例如：

• 表格控件排序、下载。
• 图表控件导出图片、导出文件。
• 节点筛选器正确性。
• 控件hover数据准确性、指标分组筛选展示。

六、数据质量工具使用

报表逻辑通常会在数仓中完成清洗，最后将计算好的 ADS 结果数据推送到 SR 中，因此工具重点关注任务清洗产生的表质量和结果表指标准确性。质量规则关联调度任务后，每天任务定时调度完成会触发配置规则检查产出表的数据质量。

6.1 测试可以应用的场景

• 表数据质量规则配置，能够感知到业务报表数据/任务自身异常
• 配置自定义规则后能够根据任务每次调度触发报表指标自动化回归

6.2 测试SQL的转变

6.2.1 数据范围

需求迭代中的测试 SQL 语句是基于店铺粒度查询修改成每天分区查询，这样包含的数据量、数据场景更多，更容易捕获生产的问题。

6.2.2 自定义对比 SQL 编写

工具设计自定义SQL最终需要聚合后的值，因此配置预期结果和实际结果的对比，使用两个 SQL 查询后 union 再聚合行数，如果聚合后的行数不等于1代表两个 SQL 语句数据不一致。