数据质量是数据的生命线,没有高质量的数据,一切数据分析、数据挖掘、数据应用的效果都会大打折扣,甚至出现完全错误的结论,或者导致资损。然而数据质量问题却是广泛存在的,且治理的难度很大,因为数据的生产、加工、流转、应用涉及到业务运营、生产系统、数据系统、数据产品等上下游链路几十个环节,每个环节都可能引入数据质量问题。
集团很多BU都有成体系的解决数据质量的方案,集团也有很多工具来解决数据质量问题。本文不详细介绍此类工具的使用,主要聚焦在数据开发过程中因为数据研发同学经验不足而导致的数据质量问题。
数据探查
数据探查的定义一般为:数据探查是探索源数据的过程,用来理解数据结构、数据内容、数据关系以及为数据工程识别可能存在的问题。
数据探查不止用在数据质量领域,数仓开发、数据迁移等都需要对源数据进行数据探查。数据仓库的所有数据基础都是源数据(ODS),在开发数仓之前,需要对源数据进行探查,才能保证产出的数据仓库的准确性。
题库业务的数据缺少打点,数据建设主要基于业务架构的一些中间表和结果表,在开发前期,没有意识到数据探查的重要性,导致数据的准确性有严重问题,数据研发出现了大量的返工现象。
dataworks提供了数据探查的功能,可以统计基本信息、数据分布、topN、直方图等。但我试了几次一直是探查中,易用性还不是太好。
表探查
2.1.1.1. 数据总量探查
数据总量探索是对ods的总体数据有初步认知,可以通过数据地图的分区信息确认,也可以通过写sql计算。
数据总量探查时要探查每日增量数据总量、全量数据总量(如有需要)。
一般情况下,数据总量探查结果要与业务方或者上游数据提供方确认是否符合预期。
2.1.1.2. 数据产出时间和生命周期探查
在做数据探查时,需要探查数据产出时间和生命周期,对后续的任务调度和补数据有一定的帮助。
2.1.2. 列探查
2.1.2.1. 数据分布探查
数据分布探查是数据探查中最重要的部分,可以探测不同维度下数据的分布情况。一般情况下,有如下写法。
SELECT result
,COUNT(*)
FROM xxx.table_name
WHERE dt = 'xxxxx'
GROUP BY result ;
2.1.2.2. 枚举值探查
枚举值探查是上面数据分布探查的一种特例,探查某些维度的枚举值是否合理。一般情况下sql如下。
SELECT DISTINCT result
FROM xxx.table_name
WHERE dt = 'xxxxx' ;
这种探查,可以探查出很多问题,比如上游生成某枚举值只有0和1,但探查的时候探查出为空等。
2.1.2.3. 唯一值探查
某些情况下,上游生成某些字段唯一(不一定是主键),也需要对此类情况探查,不然做join时容易出现数据膨胀问题。探查sql一般如下。
SELECT COUNT(item_id)
,COUNT(DISTINCT item_id)
FROM xxx.table_name
WHERE dt = 'xxxxx' ;
2.1.2.4. 极值&异常值探查
对于某些数值类的值,必要情况下可以做一下极值探查,比如求最大值、最小值、平均值。这样可以尽快发现源数据中的脏数据。
对于异常值也要探查一下,比如0、null、空字符串等。
2.1.3. 列间探查
2.1.3.1. 关联字段探查
通常情况下,一张表中不同字段直接有关联关系。比如曝光字段和曝光时长之间有关联关系,有曝光的一定有曝光时长,或者曝光时长大于0的情况下一定有曝光。
或者uv一定大于pv,这种方法可以对dws表进行验证。
2.1.4. 表间探查
2.1.4.1. join条件探查
此种情况属于跨表探查。不同的表在做join时,除了探查join条件是否成功,还需要探查join得到的数量是否符合预期。
在题库业务中,出现过因为系统bug,下游表的join条件中,有3%左右的数据join不上,但因为前期没有做此方面的数据探查,导致用了很久才发现此问题。
还有一种情况是业务上两张表必须join上,比如消费表所有的用户都应该出现在用户表,或者所有内容都应该出现在内容维表等。
一般sql如下:
SELECT count(DISTINCT a.itemid)
FROM xxx.yyy_log a
LEFT JOIN (
SELECT itemid
FROM xxx.zzzz
WHERE ds = '20210916'
) b
ON a.itemid = b.itemid
WHERE a.dt = '20210916'
AND b.itemid IS NULL ;
2.1.5. 业务探查
2.1.5.1. 过滤条件不对
在某些情况下,需要从海量数据中,通过某些过滤条件捞出所需数据。比如客户端打点的规范是一致的,不同的端的用户日志都在一张表中,如果只分析某种数据,需要对数据进行过滤。
此过滤条件一般由业务方同学提供,在数据探查阶段要先做条件过滤,与业务方同学沟通过滤之后的数据是否符合预期。
2.1.5.2. 业务上数据重复问题
属于表唯一性探查。此问题与唯一值的现象类似,都是数据有重复。
不同之后在于,某些情况下,虽然数据提供方称了某些列唯一,但在某些业务场景下,数据就是不唯一的。比如题库的某业务中,业务方开始说不同线索得到的q_id不一致,然而q_id来自url,在业务上url确实存在重复的情况,所以q_id有重复的情况。
但在另一种数据重复的问题往往不是业务如此,而是系统bug导致的。比如某种业务中,一本书理论上处理完之后不应该再次处理,但系统的bug导致出现一本书被处理多次的情况。
对于第一种情况,我们在建模时要考虑业务复杂性;而第二种情况,我们要做的是找到有效的数据,去掉脏数据。
2.1.5.3. 数据漏斗问题
数据链路中数据漏斗是很关键的数据,在做初步数据探查时,也需要关注数据漏斗。每一层数据丢弃的数量(比例)都要和业务方确认。
比如某一个入库流的处理数据数量和入库数量对比,或者入库数量和入索引数量等,如果比例出现了很大的问题,需要找上游业务方修正。
2.1.5.4. 业务上数据分布不合理
“刷子用户”的发现就是一种常见的数据分布不合理,比如某个user的一天的pv在5000以上,我们大概率怀疑是刷子用户,要把这些用户从统计中剔除,并要找到数据上游过滤掉类似用户。
一般sql如下:
SELECT userid
,count(*) AS cnt
FROM xxx.yyyy_log
WHERE dt = '20210913'
GROUP BY userid
HAVING cnt > 5000 ;
2.2. 数据开发规范
上面描述了很多数据探查问题,如果认真的做了数据探查,可以避免很多数据质量问题。本部分描述在数据开发环节中开发同学因为经验等原因导致的数据质量问题。
2.2.1. SQL编写问题
2.2.1.1. 笛卡尔积导致数据膨胀
此问题往往发生在没有对join条件进行唯一性检查的情况下。因为右边数据不唯一,发生笛卡尔积,导致数据膨胀。如果是某些超大表,除了数据结果不对之外,会产生计算和存储的浪费。
还有一种情况,在单一分区中数据是唯一的,但join时没有写分区条件,导致多个分区同时计算,出现数据爆炸。
这个问题很多同学在开发中遇到了多次,一定要注意。
2.2.1.2. join on where顺序导致结果错误
此问题也是常见问题,因为写错了on和where的顺序,导致结果不符合预期。错误case如下。
SELECT COUNT(*)
FROM xxx a
LEFT JOIN yyy b
ON a.id = b.item_id
WHERE a.dt = '${bizdate}'
AND b.dt = '${bizdate}' ;
在上面的sql中,因为b.dt在where条件中,那么没有join上的数据会被过滤掉。
2.2.1.3. inner join和outer join用错问题
此问题偶发,往往是开发同学没有理解业务或者typo,导致结果不符合预期。
写完sql一定要检查,如果有可能请别的同学review sql。
2.2.1.4. 时间分区加引号
一般情况下,分区都是string数据类型,但在写sql时,分区不写引号也可以查询出正确的数据,导致有些同学不习惯在分区上加引号。
但某些情况下,如果没有加引号,查询的数据是错误的。所以一定要在时间分区上加引号。
2.2.1.6. 表循环依赖问题
在开发时,偶尔会出现三个表相互依赖的问题,这种情况比较少见,而且在数据开发阶段不容易发现,只有再提交任务之后才会发现。
要避免这种情况,需要明确一些开发规范。比如维表和明细表都要从ods表中查得,不能维表和明细表直接互相依赖。对于某些复杂的逻辑,可以通过中间表的形式实现重用。
2.2.1.7. 枚举值问题
在做etl时,需要把某些枚举值转化成字符串,比如1转成是、0转成否等。
常见的写法是在sql中写case when。
但对于某种一直增长的枚举值,这种方法不合适,否则增加一种编码就要改一次sql,而且容易出现sql膨胀的问题。
推荐通过与码表join的方法解决此问题。
2.2.2. 性能问题
2.2.2.1. join on where顺序的性能问题
上面提到过join的on和where执行顺序的问题,这也关系到join的性能问题。因为是先on后where,建议先把数据量缩小再做join,这也可以提升性能。
(1) 如果是对左表(a)字段过滤数据,则可以直接写在where后面,此时执行的顺序是:先对a表的where条件过滤数据然后再join b 表
(2) 如果是对右表(b)字段过滤数据,则应该写在on 条件后面或者单独写个子查询嵌套进去,这样才能实现先过滤b表数据再进行join 操作;
如果直接把b表过滤条件放在where后面,执行顺序是:先对a表数据过滤,然后和b表全部数据关联之后,在reduce 阶段才会对b表过滤条件进行过滤数据,此时如果b表数据量很大的话,效率就会很低。因此对于应该在map 阶段尽可能对右表进行数据过滤。
我一般对右表做一个子查询。
2.2.2.2. 小维表 map join
在Hive中
若所有表中只有一张小表,那可在最大的表通过Mapper的时候将小表完全放到内存中,Hive可以在map端执行连接过程,称为map-side join,这是因为Hive可以和内存的小表逐一匹配,从而省略掉常规连接所需的reduce过程。即使对于很小的数据集,这个优化也明显地要快于常规的连接操作。其不仅减少了reduce过程,而且有时还可以同时减少Map过程的执行步骤。
参考:https://developer.aliyun.com/article/67300
在MaxCompute中
mapjoin在Map阶段执行表连接,而非等到Reduce阶段才执行表连接,可以缩短大量数据传输时间,提升系统资源利用率,从而起到优化作业的作用。
在对大表和一个或多个小表执行join操作时,mapjoin会将您指定的小表全部加载到执行join操作的程序的内存中,在Map阶段完成表连接从而加快join的执行速度。
文档中给的例子如下:
select /*+ mapjoin(a) */
a.shop_name,
a.total_price,
b.total_price
from sale_detail_sj a join sale_detail b
on a.total_price < b.total_price or a.total_price + b.total_price < 500;
参考:https://help.aliyun.com/document_detail/73785.html
2.2.2.3. 超大维表 hash clustering
在互联网大数据场景中,一致性维表的数据量都比较大,有的甚至到几亿甚至十亿的量级,在这个数据量级下做join,会这种任务往往耗时非常长,有些任务甚至需要耗费一天的时间才能产出。
在这种情况下,为了缩短执行时间,通常可以调大join阶段的instance数目,增加join阶段的内存减少spill等,但是instance的数目不能无限增长,否则会由于shuffle规模太大造成集群压力过大,另外内存的资源也是有限的,所以调整参数也只是牺牲资源换取时间,治标不治本。
Hash clustering,简而言之,就是将数据提前进行shuffle和排序,在使用数据的过程中,读取数据后直接参与计算。这种模式非常适合产出后后续节点多次按照相同key进行join或者聚合的场景。
Hash clustering是内置在MaxCompute中,不用显示的指定,很方便。