目录
前言
首先说一下名字的由来,憨萌憨萌的考拉 🐨🐨🐨,这个名字还是我媳妇帮我起的。
考拉 | 考拉(验数)特点 | 憨萌小考拉特性 |
---|---|---|
提高业务、开发人员的工作效率,用了考拉(验数工具)提高了效率,有很多的时间可以用来休息喽 | 它每天18个小时处于睡眠状态,性情温顺,体态憨厚,清醒的时候,它们的大部分时间也用来吃东西,可谓是一个真正意义上的大懒虫。 | |
考拉(验数工具)能够快速的嗅探出数据的问题。 | 考拉大体归属为夜行性动物,考拉鼻子特别发达,能轻易地分辨出不同种类的桉树叶,并发觉哪些可以采食,哪些有毒而不能采食,也能嗅出别的考拉所遗留标记的警告性气味 |
一、 背景&现状
数据开发经常遇到切库切表、代码逻辑修改、表字段名称修改,修改前后需要进行表级数据验证;新增字段,查看字段的统计值和其它表相似字段比对,上下游字段比对校验;新表开发(数据开发常态),要更好的使用表,需要了解数据内容、研究指标分布等。在这些场景下,代码修改前后,数据到底差多少,差在哪儿?过去没有工具只能写一堆脚本,再去验证,效率极其低下,验证结果没有标准的评估方式极易出错。
基于以上现状,我们一直在思考如何去做数据验证?并且访问了数据PM、测试、数据RD工作过程中遇到验数的痛点,同时我们调研了行业内的阿里、腾讯等头部互联网企业,发现阿里巴巴OneData体系中的研发工具链成员“在彼岸”已经具备自动化数据校验的能力,(在彼岸如何实现,验数方法,只有简单描述,没找到相关文案,本文分享了个人的一些思考和尝试,希望能帮到大家),这应该是我们下一步需要努力的方向。我们也要搭建自动化数据验证平台,用于大数据系统的自动化测试,将通用性、重复性的操作沉淀在测试平台中,避免人肉,节省人力,提高测试效率。
二、 目标
搭建数据验证平台,勾勾选选实现数据的自动化验证,节省人力,提高测试效率。
三、 收益&效果衡量
数据团队的验数效率,提升50%左右,随着工具的完善、推广和使用,更多数据团队的验数效率会得到整体提升。
四、 技术方案
4.1 整体架构
如下图所示,自动化数据验证系统分为四个部分,主要是前端和后端建设,后端主要做验数逻辑控制;前端是可视化实现部分,主要提供友好的可视化界面,方便勾勾选选实现验数的基本配置。下文会分别对前后端功能做详细阐述。
4.2 前端模块
在前端模块中,我们的目的是提供友好的可视化界面,方便勾勾选选实现验数的基本配置。比如我们集成的三大功能:量级验证,一致性验证,差异case发现的规则,根据数据验证的需求,可以集成不同规则。三大规则和验数配置说明如下所示
序号 | 规则 | 详情 |
---|---|---|
1 | 量级比对 | 只需要输入基准表名,过滤条件分组条件都会自动推荐补全。点击立即执行按钮就可以运行,同时显示当前执行进度日志,最后输出验数结果。 |
2 | 一致性比对 | 输入基准表名,选择比对的字段和主键,过滤条件分组条件都会自动推荐补全。点击立即执行,执行完会输出表格,显示是否通过,如果没有通过就可以进一步点击差异发现。 |
3 | 差异case发现 | 在一致性验证的基础上,直接点击差异发现就能自动的查询差异最终输出。 |
4.3 后端逻辑控制模块
后端有三个子模块,元数据获取,验数逻辑生成,日志解析如下表所示。
模块 | 功能子模块 | 描述 |
---|---|---|
逻辑控制 | 元数据获取 | 获取需要验证表的元数据 |
验数逻辑生成 | 根据前端选择的规则和相关配置,智能生成数据验证的逻辑,并发送给大数据集群去执行。 | |
日志解析 | 解析验数结果报告,判断数据验证是否通过,整理格式方便前端展示 |
在如上个三个子模块中,验数逻辑生成是最核心的部分下面详细介绍。
功能 | 场景 | 功能 | 竞品(在彼岸)的功能 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
表级比对 (相同表结构数据验证) | 1.切库切表 2.代码逻辑修改 3.表字段名称修改(A表的A1字段名称修改成了A2,涉及到字段血缘关系) 4.相似指标统计值比对 | 表结构相同数据验证 和前端进行交互:只需要点击一个按钮,即可自动生成数据校验报表 实现如下功能:(相同字段的校验比对) (1)实现两个表数据量级比对 (2)实现表数据一致性校验。 (3)实现两个表差异数据自动发现。 | 对标在彼岸表级比对 表级对比规则: 主要包括数据量和全文对比; | ||||||||||
字段级比对(跨表比对相似字段) | 上下游:比如上游明细,下游groupBy(ID) 新增字段(有多层加工和最上游底层表实现对应逻辑进行比对) | 跨表比对相似字段 相似指标统计值比对: 主要包括字段的统计值(如sum、avg、max、min等)、枚举值、空值、去重数、长度值、0、负数等。展示、量级 | 对标在彼岸: 字段比对(可能大规模数据,比对大概,就通过一些统计值去做) | ||||||||||
数据探测 | 1.新开发的表 (1)准确性测试:数据符合预期(枚举值、空值、去重数、长度值) 2.遇到陌生的表,展示、量级、分布核心字段 | 如果开启数据探测功能,会探测数据的基本情况,比如:在上线前识别出异常值,提前告知业务 枚举值、空值(在线支付,没有支付时间;订单状态丢失,计算两个状态的时间差等,可能出现负数,在上线前告知业务),比如新增字段,看一下枚举值、空值、去重数、长度值,提前和业务沟通确。 比如:有一些比较重要的表需要分析,以往我们都是探查数据、写报告,基于这个报告去做分析,这样有些弊端:数据滞后性,不能及时反应当前情况;事后丢弃老旧报告,有新人或新需求,又需要花精力探测数据,重复工作,不利于数据探查经验汇总。 我们把数据探测标准化、系统化,不仅高效而且能积累经验。
| 数据分布 提取表和字段的一些特征值,并将这些特征值与预期值进行比对。 |
4.4 任务管理
模块 | 功能子模块 | 描述 |
---|---|---|
任务管理 | 调度管理 | 使用接口,复用原来的调度功能。 |
计算资源管理(CU监控) | 监控发起的任务所占资源,为调度管理和并发控制提供依据 | |
用户元数据 | 系统管理员、普通用户的信息管理 | |
衍生元数据 | 验数表的元数据需要最新的,平台不允许访问Hadoop元数据,需要和hive交互获取元数据。 |
4.5 大数据集群
模块 | 功能子模块 | 描述 |
---|---|---|
执行引擎 | 执行引擎 | 目前支持Presto引擎 后期会兼容:Spark、MR、Presto |
五、 技术实现
以三大规则为例,重点介绍验数的标准化SQL。之前在“上亿条数据,如何比对并发现两个表数据差异”文章中介绍过。
5.1 量级比对
两个表记录条数比对;两个表核心字段去重比对。
日期 | 是否通过 | 基准表PV | 对照表PV | PV_diff | 基准表UV | 对照表UV | UV_diff |
---|---|---|---|---|---|---|---|
20210101 | 是 | 100000 | 100000 | 0 | 100000 | 100000 | 0 |
select base.dt,
base.pv,
verify.pv as verify_pv,
base.pv - verify.pv as diff_pv,
base.uv,
verify.uv as verify_uv,
base.uv - verify.uv as diff_uv
from (
select dt,
count(1) as pv,
count(distinct id) as uv
from mart_online.fact_user_day
where dt=20210101
group by dt
)base
left outer join (
select dt,
count(1) as pv,
count(distinct id) as uv
from mart_verify.fact_user_day
where dt=20210101
group by dt
)verify
on base.dt=verify.dt
SELECT `t`.`dt` `dt`,
SUM(CASE WHEN `t`.`tb` = 'test' THEN `t`.`cnt` ELSE 0 END) `test_cnt`,
SUM(CASE WHEN `t`.`tb` = 'prod' THEN `t`.`cnt` ELSE 0 END) `prod_cnt`,
SUM(CASE WHEN `t`.`tb` = 'test' THEN `t`.`cnt` ELSE - `t`.`cnt` END) `quantity_diff`
FROM (
SELECT 'test' `tb`,
`dim_poi_dd`.`dt` `dt`,
COUNT(*) `cnt`
FROM `mart_test`.`dim_poi_dd` `dim_poi_dd`
WHERE `dim_poi_dd`.`dt` = '20230925'
GROUP BY `dim_poi_dd`.`dt`
UNION ALL SELECT 'prod' `tb`,
`dim_poi_dd`.`dt` `dt`,
COUNT(*) `cnt`
FROM `mart_online`.`dim_poi_dd` `dim_poi_dd`
WHERE `dim_poi_dd`.`dt` = '20230925'
GROUP BY `dim_poi_dd`.`dt`
) `t`
GROUP BY `t`.`dt`
ORDER BY `dt` DESC
5.2 一致性比对
勾稽验证+md5方法。如下是hive或Spark引擎语法:
日期 | 是否通过 | 基准表数据条数 | 对照表数据条数 | 一致数据条数 |
---|---|---|---|---|
20210101 | 是 | 100000 | 100000 | 100000 |
select Coalesce(base.dt, verify.dt) as dt,
sum(case when base.record_key is not null or base.record_key !='' then 1 else 0 end) as base_num,
sum(case when verify.record_key is not null or verify.record_key !='' then 1 else 0 end) as verify_num,
sum(case when base.record_key = verify.record_key then 1 else 0 end) as base_verify_equal_num
from (
select dt,
md5(concat(if(id is null, '-', id), if(user_name is null, '-', user_name), if(age is null, '-', age))) as record_key
from mart_online.fact_user_day
where dt=20210101
)base
full outer join (
select dt,
md5(concat(if(id is null, '-', id), if(user_name is null, '-', user_name), if(age is null, '-', age))) as record_key
from mart_verify.fact_user_day
where dt=20210101
)verify
on base.dt=verify.dt
and base.record_key=verify.record_key
group by Coalesce(base.dt, verify.dt)
5.3 差异case发现
发现两个表中不一致数据可视化展示并提示差异行列。
select base.dt as base_dt,
base.id as base_id,
base.age as base_age,
verify.age as verify_age,
case when if(base.age is null, '-',base.age) = if(verify.age is null, '-', verify.age) then '1'
else '0'
end as age_is_pass,
base.name as base_name,
verify.name as verify_name,
case when if(base.name is null, '-',base.name) = if(verify.name is null, '-', verify.name) then '1'
else '0'
end as name_is_pass,
base.sex as base_sex,
verify.sex as verify_sex,
case when if(base.sex is null, '-',base.sex) = if(verify.sex is null, '-', verify.sex) then '1'
else '0'
end as sex_is_pass
from (
select dt,
id,
age,
name,
sex
from mart_online.fact_user_day
where dt=20210101
)base
full outer join (
select dt,
id,
age,
name,
sex
from mart_verify.fact_user_day
where dt=20210101
)verify
on base.dt = verify.dt
and if(base.id is null, '-', base.id) = if(verify.id is null, '-', verify.id)
where if(base.age is null, '-', base.age) <> if(verify.age is null, '-', verify.age)
or if(base.name is null, '-', base.name) <> if(verify.name is null, '-', verify.name)
or if(base.sex is null, '-', base.sex) <> if(verify.sex is null, '-', verify.sex)
5.4 主键唯一性
SELECT `origin`.`poi_id`,
`origin`.`poi_name`,
`origin`.`city_id`,
`origin`.`city_name`,
`origin`.`dt`
FROM `mart_test`.`dim_poi_dd` `origin`
INNER JOIN (
SELECT concat_ws('#', COALESCE(obj_to_json(`dim_poi_dd`.`poi_id`), 'null'), COALESCE(obj_to_json(`dim_poi_dd`.`dt`), 'null')) `pks`
FROM `mart_test`.`dim_poi_dd` `dim_poi_dd`
WHERE `dim_poi_dd`.`dt` = '20230925'
GROUP BY concat_ws('#', COALESCE(obj_to_json(`dim_poi_dd`.`poi_id`), 'null'), COALESCE(obj_to_json(`dim_poi_dd`.`dt`), 'null'))
HAVING COUNT(*) > 1
) `tmp`
ON concat_ws('#', COALESCE(obj_to_json(`origin`.`poi_id`), 'null'), COALESCE(obj_to_json(`origin`.`dt`), 'null')) = `tmp`.`pks`
WHERE `origin`.`dt` = '20230925'
六、系统展示
量级比对
一致性比对
差异发现
七、作者简介
王先生,某大厂数据研发工程师,专注于大数据技术(Spark、Hadoop)、数据仓库技术、数据分析应用等领域。欢迎投递简历,联系邮箱: aijiudu@163.com