1概述1.数据质量问题无处不在。基本上每个使用数据的同学都遇到过以下类似的问题。表没有按时输出，影响下游，严重的甚至会影响上线效果。点漏了，看了报告，发现数据不符。据统计，uv大于pv，尴尬。数据产量激增，原来的1000万条数据变成了3000万条。字段中的枚举值与评论中的不符，也没人解释。缺少一些维度，无法进行进一步的数据分析。做了分析后，发现结果很离谱。往前一点点分析，发现管理有问题。……以上都是数据质量问题。本文试图找到一种方法，尽可能地发现和解决数据质量问题。2数据标准说到数据质量，需要了解评价数据质量的维度。DAMAUK提出了数据质量的六个核心维度，见图1。注：DAMAInternational（国际数据管理协会）成立于1980年，是由技术和业务专业人士组成的国际数据管理专业协会。作为一个非营利组织，它独立于任何制造商，旨在在全球范围内推广它。并推广数据管理领域的理念和最佳实践，为数字经济奠定理论和实践基础。在全球拥有近万名会员，在全球48个国家设立了分支机构。图1数据质量维度完整性：完整性是指数据信息的缺失，如常见数据表中没有行、字段、编码值等。比如整体的pv虽然是正确的，但是在某个维度上，只管理了一部分，这就是完整性的问题。不完整的数据可以借鉴的价值会大大降低，也是数据质量最基本、最普遍的问题。常用统计sql：count(notnull)/count(*)有效性：有效性一般是指范围有效性、日期有效性、表单有效性等，主要体现在数据记录的规范性和数据是否符合逻辑。规范是指一条数据有其特定的格式，如：手机号码必须是11位数字；logic是指多个数据之间存在固定的逻辑关系，比如：PV必须大于等于UV。准确性准确性：准确性是指数据中记录的信息是否存在异常或错误。最常见的数据准确性错误是乱码。其次，异常大或小的数据也属于不合格数据。准确性可能存在于单个记录或整个数据集中，例如数量级的记录误差。可以使用最大值和最小值的统计信息来审计此类错误。Timeliness时效性：时效性是指从数据开始处理到可以查看的时间间隔。时效性对数据分析本身影响不大，但如果数据建立时间过长，数据分析不能及时进行，可能导致分析得出的结论失去参考价值。例如：实时的业务行情数据，关键业务指标的及时反映，业务指标异常波动的暴露，特殊突发事件的灵活应对，都需要及时的数据更新和输出。在某些情况下，数据并非纯粹用于分析，而是用于在线策略。如果数据没有及时产生，会影响上线效果。Consistency一致性：一致性是指相同含义的信息在多业务、多场景下是否一致。一般指多源数据的数据模型不一致，如命名不一致、数据结构不一致、约束规则不一致等。数据实体不一致，例如：数据编码不一致、命名和含义不一致、分类级别不一致、生命周期不一致等。唯一性唯一性：数据在数据集中不重复的程度。唯一数据项的数量，以及占数据项总数的百分比。比如count(distinctbusinesskey)/count(*)，一般用来验证主键的唯一性。3数据生命周期图2数据生命周期数据访问：访问来自上游表或其他数据源的数据输入。数据处理：编写sql生成目标数据表。数据输出：安排定时任务生成数据表。数据应用：下游数据分析、报表等应用数据。以上任何一个环节，都可能出现数据质量问题。提升数据质量需要从数据接入、数据处理、数据输出、数据应用、效果跟踪等全过程进行把控，大局观很重要，不分一点，看得更全面。2如何解决数据质量问题数据质量是数据的生命线。没有高质量的数据，一切数据分析、数据挖掘、数据应用的效果都会大打折扣，甚至会出现完全错误的结论，甚至造成资金损失。但数据质量问题普遍存在，治理难度很大，因为数据的生产、加工、流通、应用涉及业务运营、生产系统、数据系统、数据产品等上下游数十个环节。每个环节都可能引入数据质量问题。集团多个BU对数据质量都有系统的解决方案，集团也有很多解决数据质量问题的工具。本文不详细介绍此类工具的使用方法，主要针对数据开发同学在数据开发过程中由于经验不足导致的数据质量问题。图3数据质量解决方案如图3所示，我认为可以通过三种方式在一定程度上解决数据质量问题。数据探索发现完整性、一致性、有效性、准确性、相关性等解决问题的数据访问和数据输出阶段的问题制定规范发现数据时效性、数据一致性、数据准确性等解决数据生产问题生产阶段的问题数据监控避免一致性和准确性等问题解决数据生产阶段的问题1数据探测数据探测的定义一般是：数据探测是探索源数据以了解数据结构、数据内容和数据关系的过程以及识别数据工程可能存在的问题。数据剖析不仅应用于数据质量领域，数据仓库开发、数据迁移等都需要对源数据进行数据探查。数据仓库的所有数据库都是源数据（ODS）。在开发数据仓库之前，需要对源数据进行调查，以确保输出数据仓库的准确性。题库业务数据缺乏管理，数据构建主要基于业务结构的一些中间表和结果表。在发展初期，没有意识到数据探索的重要性，导致数据的准确性存在严重问题，数据研发出现大量返工。现象。Dataworks提供了数据探索的功能，可以统计基本信息、数据分布、topN、直方图等。但是我试了几次，还在检测中，易用性不是很好。图4数据剖析的基本方法上图展示了数据剖析的一些基本功能。本节介绍一些常用的数据探索方法，不系统，仅作为开发过程中遇到的问题，供参考。TableExploration1）TotalDataExplorationDataAggregateExploration是对ods的整体数据有一个初步的了解，可以通过datamap的分区信息来确认，也可以通过写sql来计算。在探测数据总量时，需要检测每日增量数据总量和全量数据总量（如果需要）。一般来说，总数据检查的结果应该与业务方或上游数据提供方确认，看是否符合预期。2）数据生产时间和生命周期检测在做数据检测时，需要检测数据生产时间和生命周期，有助于后续的任务调度和数据补充。列检测1）数据分布检测数据分布检测是数据检测中最重要的部分，可以检测数据在不同维度的分布情况。一般来说，它是这样写的。SELECTresult,COUNT(*)FROMxxx.table_nameWHEREdt='xxxxx'GROUPBYresult;2）枚??举值检测枚举值检测是上述数据分布检测的一个特例，检查某些维度的枚举值是否合理。一般来说，sql如下。SELECTDISTINCTresultFROMxxx.table_nameWHEREdt='xxxxx';这种检测可以检测到很多问题，比如上游生成的某个枚举值只有0和1，但是检测的时候发现是空的。3）唯一值检测有些情况下，上游生成的某些字段是唯一的（不一定是主键），这种情况也需要检测，否则做join时容易出现数据膨胀问题。sql的检测一般如下。SELECTCOUNT(item_id),COUNT(DISTINCTitem_id)FROMxxx.table_nameWHEREdt='xxxxx';4)Extremum&outlierdetection对于一些数值，必要时可以做极值检测，比如求最大值和最小值，平均值。这样可以尽快发现源数据中的脏数据。还要检查异常值，例如0、null、空字符串等。列间检测1）关联字段检测通常，表中的不同字段是直接相关的。例如，曝光场和曝光持续时间之间存在关系。如果有曝光，则必须有曝光持续时间，或者如果曝光持续时间大于0，则必须有曝光。或者uv必须大于pv，这个方法可以验证dws表。Inter-tableprobing1)joinconditionprobing这种情况属于跨表探测。不同表的join时，除了检查join条件是否成功，还需要检查join得到的数是否符合预期。在题库业务中，由于系统bug，导致下游表join条件中约3%的数据无法join。但是，由于前期没有这方面的数据探索，所以花了很长时间才发现这个问题。另一种情况是业务中的两张表必须join。例如，消费表中的所有用户都应该出现在用户表中，或者所有内容都应该出现在内容维度表中。一般的sql如下：SELECTcount(DISTINCTa.itemid)FROMxxx.yyy_logaLEFTJOIN(SELECTitemidFROMxxx.zzzzWHEREds='20210916')bONa.itemid=b.itemidWHEREa.dt='20210916'ANDb.itemidISNULL;业务检测1)某些情况下过滤条件错误在这种情况下，需要通过一定的过滤条件从海量数据中捞出需要的数据。例如，客户端管理规范一致，不同客户端的用户日志存储在一张表中。如果只分析某些数据，则需要对数据进行过滤。这个过滤条件一般是商科学生提供的。在数据探索阶段，先过滤条件，与业务同学沟通过滤后的数据是否符合预期。2）业务中数据重复的问题属于表的唯一性。这个问题类似于唯一值的现象，因为数据是重复的。不同的是，在某些情况下，虽然数据提供者声称某些列是唯一的，但在某些业务场景中，数据并不唯一。比如题库某业务，业务方开始说不同线索获取到的q_id不一致，但是q_id是从url中来的，业务中确实是url重复了，所以q_id是重复。但在另一种情况下，数据重复问题往往不是业务的情况，而是由系统的bug引起的。例如，在某项业务中，一本书理论上处理完后不应该再次处理，但系统的一个bug导致一本书被多次处理。对于第一种情况，我们在建模时需要考虑业务的复杂性；对于第二种情况，我们要做的就是找到有效数据，去除脏数据。3）数据漏斗问题数据环节中的数据漏斗是非常关键的数据，在做前期数据探索的时候也需要关注数据漏斗。每一层丢弃的数据量（比例）需要与业务方确认。比如某个入站流的处理数据量与入站数量，或者入站数量与索引数量的比例存在较大问题，需要上游业务方进行修正。4）业务数据分布不合理。“刷机用户”的发现是一种常见的不合理数据分布。比如一个用户的日pv超过5000，我们大概率会怀疑他是刷机用户。我们需要从Eliminate中统计这些用户，向上游找数据过滤掉相似的用户。一般的sql如下：SELECTuserid,count(*)AScntFROMxxx.yyyy_logWHEREdt='20210913'GROUPBYuseridHAVINGcnt>5000;2数据开发规范描述了许多数据探测问题。如果仔细进行数据探测，可以避免很多数据质量问题。本节介绍开发同学在数据开发过程中，由于经验等原因导致的数据质量问题。SQL编写问题1）笛卡尔积导致数据膨胀这个问题经常出现在join条件没有做唯一性检查的时候。因为右边的数据不唯一，会产生笛卡尔积，导致数据膨胀。如果是一些非常大的表，除了数据结果不正确之外，还会有计算和存储的浪费。还有一种情况，单个分区的数据是唯一的，但是join的时候没有写分区条件，导致多个分区同时计算，数据爆炸。很多同学在开发中多次遇到这个问题，一定要引起重视。2)where的连接顺序导致错误的结果。这个问题也是一个常见的问题，因为on和where的顺序写错了，导致出现意想不到的结果。错误情况如下。SELECTCOUNT(*)FROMxxxaLEFTJOINyyybONa.id=b.item_idWHEREa.dt='${bizdate}'ANDb.dt='${bizdate}';上面的sql中，因为b.dt是where条件，所以没有join的数据会被过滤掉。3)Innerjoin和outerjoin使用不当。偶尔会出现此问题。往往是因为开发同学对业务不了解或者错别字，导致出现意想不到的结果。写完sql一定要检查一下，有条件的请其他同学复习一下sql。4）时间分区加引号一般情况下分区都是字符串数据类型，但是写SQL的时候不在分区上写引号也能查询到正确的数据，这让一些同学不习惯在时间分区上加引号分区。但在某些情况下，如果没有引号，则查询到的数据是错误的。所以一定要在时间分区周围加上引号。5）表的循环依赖问题在开发过程中，偶尔会出现三个表相互依赖的问题。这种情况比较少见，在数据开发阶段不容易发现。只有提交任务后才会被发现。为了避免这种情况，需要明确一些开发规范。比如维度表和明细表必须从ods表中查，维度表和明细表不能直接相互依赖。对于一些复杂的逻辑，可以通过中间表的形式复用。6）枚举值问题在做etl的时候，有些枚举值需要转成字符串，比如1转成yes，0转成no等。常见的写法是在sql中写casewhen。但是对于某种不断增长的枚举值，这种方法就不适用了。不然加个代码，需要改一次sql，容易出现sql扩容的问题。建议通过加入码表来解决这个问题。性能问题1）joinonwhere的顺序性能问题上面提到了join的on和where的执行顺序问题，这也涉及到join的性能问题。因为是先on后where，建议在做join之前先减少数据量，这样也可以提高性能。(1)如果对左表的字段(a)进行数据过滤，可以直接写在where之后，此时的执行顺序是：先对a表的where条件进行数据过滤，然后然后加入b表；(2)if要过滤右表(b)字段中的数据，应该写在on条件之后或者嵌套在单独的子查询中，这样可以先过滤b表中的数据，然后进行join操作执行；如果b表的过滤条件直接放在where之后，执行顺序是：先过滤a表的数据，然后关联b表的所有数据，然后在reduce阶段对filter进行数据过滤表b中的条件。这时候如果b表的数据量很大，效率就会很低。所以在map阶段尽量对右表进行数据过滤。我一般在右表做子查询。2）小维表mapjoin在Hive中，如果所有表中只有一张小表，那么当最大的表经过Mapper时，小表可以完全放入内存，Hive可以对map进行join过程side，也就是所谓的map-sidejoin，这是因为Hive可以将内存中的小表一张一张匹配起来，从而省去了常规join所需要的reduce过程。即使对于小型数据集，此优化也比常规连接操作快得多。它不仅减少了reduce进程，有时还同时减少了Map进程的执行步骤。参考文末链接1。在MaxCompute中，mapjoin在Map阶段进行表连接，而不是等到Reduce阶段才进行表连接，可以缩短大量数据传输的时间，提高系统资源的利用率，优化作业。当对一张大表和一张或多张小表进行join操作时，mapjoin会将你指定的所有小表加载到执行join操作的程序内存中，在Map阶段完成表连接，加快执行速度加入的速度。文档中给出的例子如下：select/*+mapjoin(a)*/a.shop_name,a.total_price,b.total_pricefromsale_detail_sjajoinsale_detailbona.total_price
