关于大数据:Maxcompute-数据上云一致性比对
我写过很多如何去对数、如何批量对数的技术文档,最近我的项目遇到这个问题,我才发现在官网博客上还没有公布过这个课题的文章。这就像灯下黑,太长用到的知识点,反而没有意识到其重要性。 注:这里对数的场景就是指在阿里云平台应用dataworks等大数据开发工具集成业务零碎数据库(oracle等)数据上云到maxcompute的场景,所以,示例的SQL也是针对maxcompute。 先说说个别业务上怎么对数的,咱们做了一个报表,出了一个数据“某个产品卖了30个”。这个不只是在大数据平台上有这个数据,在业务零碎也有这个数据,这些统计动作在业务零碎通过程序和人工也会有一份,个别做好报表后会先对这个数据。 所以,第一线反馈回来的数据就是这个汇总数据不统一的问题。然而这个后果是十分概括的,因为就像我感觉这个月工资少发了5毛一样,如果我不看我的工资条我其实不晓得本人是不是少发了。工资条不只是一个汇总数据,外面有我税前工资、奖金(浮动)、社保、扣税等一系列的明细数据,这些数据让我去判断我是不是少了5毛,而加工过的数据是简单的。 说到这里,我其实就像表白一个事件,对数是要对明细数据。这是所有计算后事实的根底,能够拿进去作证的。 所以,两边都查一下这个汇总值应用的表的对应的记录,比如说查问“明天这个产品ID的售卖记录”。后果就发现业务零碎有31笔,而大数据平台有30笔。 即使到了这里,其实咱们依然不晓得期间产生了什么,为什么会失落数据。另外咱们还不晓得其余商品ID的数据是不是也有失落的,还有其余的表的数据是不是也会产生相似的状况。 1.明细数据比对既然最终都是对明细数据,那么我是不是能够间接比对明细数据呢?答复是:正确。 个别产生这种状况,首先要比对业务零碎和大数据平台两个表的数据。 1.再利用全量集成工具,从业务零碎的数据库全量抽取一遍数据到大数据平台。比对数据肯定要把数据放到一起,隔空比对是不存在的。因为大数据平台的容量是数百倍于业务零碎的,所以,个别都在大数据平台比对。(这里有一个悖论,如果集成工具自身就有缺点,导致抽取过程中就丢数据,岂不是永远没方法比对了。所以,如果对这个工具也不确定就得从数据库导出数据到文件,而后再加载到某个数据库下来比对。在这里,通过我对离线集成这个产品的长年应用教训,这个工具是十分牢靠的,还未遇到过这个问题。) 2.依据主键关联,比对2个表中的主键的差别。如果是下面提到的记录失落的问题,这一步做完就很容易比对进去了。这里还会发现一个问题,就是业务零碎的表是一直变动的,所以,这时与大数据平台的表比照会有差别。这个差别的外围起因是:大数据平台的表是业务零碎表在每日的日末(00:00:00)的一个时点数据,而业务零碎的数据是始终在变动的。所以,即使有差别超出预期也不要惊恐。如果是应用实时同步能够从归档日志中获取到这期间数据的每一条变动,能够追溯变动起因。如果没有实时同步,也能够通过表中与工夫相干字段去判断数据是否被更新掉。要是什么都没有(这种状况也是存在的),那就去骂骂设计表的业务零碎开发(没错,是他们的锅),也能够跟业务去具体理解一下,这行记录是不是明天做的,而不是昨天。 3.还有一种状况,就是主键统一,数据内容(主键之外的字段)不统一。这种状况,还是须要思考数据变动的状况,能够从日志、工夫字段、业务等几个角度去比对。如果发现数据的确不合乎预期,就须要查问同步工具的问题。 2.比对SQL剖析在下面的章节,我形容了比对明天新抽取的全量表和上日在maxcompute上应用前日全量和上日增量合并的上日全量的环节。比对两张表汇合是否统一的SQL办法其实比较简单,大家第一工夫就会想到汇合操作。在oracle外面有Minus、except,同样在maxcompute外面也有。然而为了便于剖析问题,我还是本人写了一个SQL。示例SQL(maxcompute sql)如下: --限定日期分区,比对上日select count(t1.BATCH_NUMBER) as cnt_left,count(t2.BATCH_NUMBER) as cnt_right,count(concat(t1.BATCH_NUMBER,t2.BATCH_NUMBER)) as pk_inner,count(case when t1.BATCH_NUMBER is not null and t2.BATCH_NUMBER is null then 1 end) as pk_left,count(case when t2.BATCH_NUMBER is not null and t1.BATCH_NUMBER is null then 1 end) as pk_right,count(case when nvl(t1.rec_id ,'') = nvl(t2.rec_id ,'') then 1 end) as col_diff_rec_id,count(case when nvl(t2.rec_creator ,'') = nvl(t1.rec_creator ,'') then 1 end) as col_diff_rec_creator,count(case when nvl(t2.rec_create_time,'') = nvl(t1.rec_create_time,'') then 1 end) as col_diff_rec_create_timefrom ods_dev.o_rz_lms_im_timck01 t1 -- 开发环境从新初始化的明天数据full join ods.o_rz_lms_im_timck01 t2 -- 生产环节昨日长期增量合并的数据on t1.BATCH_NUMBER =t2.BATCH_NUMBER and t1.IN_STOCK_TIME =t2.IN_STOCK_TIMEand t1.OP_NO =t2.OP_NO and t1.STOCK_CODE =t2.STOCK_CODE and t1.YP_ID =t2.YP_ID and t2.ds='20230426'where t1.ds='20230426';--cnt_left 9205131 阐明:左表有记录数 9205131--cnt_right 9203971 阐明:右表有记录数 9203971--pk_inner 9203971 阐明:主键关联统一记录数 9203971--pk_left 1160 阐明:左表比右表多记录数 1160--pk_right 0 阐明:右表比左表多有记录数 0--col_diff_rec_id 9203971 阐明:字段统一记录数与主键统一雷同,阐明关联上的两个表该字段统一--col_diff_rec_creator 9203971 阐明:同上--col_diff_rec_create_time 9203971 阐明:同上在下面的例子中,左表是明天从新初始化的数据,右表是在maxcompute上merge的上日全量数据。在比对之前,咱们其实就应该理解这两个表的数据必然是不统一的。尽管是同一张表,然而时点是不统一的。 ...