数据逻辑校验机制

概述：

数据开发不同于后端开发之处在于：
后端开发可是有测试👧帮忙进行功能测试的噢~
而数据开发工程师却没有😰😂😭

数据逻辑错误不像功能bug那么明显，计算结果错误并不会使程序报错
对此建立数据逻辑校验机制，包括👇

1. 左联前后数量校验（联表后数据量=左表数据量）
2. 度量值是否可加
3. 每日分区数据量统计
4. 数值类型校验，是否越界，是否损失精度
5. 重复值和NULL检测
6. 时间数据类型校验，注意时区

1、度量值是否可加

• 统计UV时，当天总UV 不能通过 当天各页面UV求和 得到，7日总UV 不能通过 7日的UV求和 得到
• 统计库存时，昨天库存 不能和 前天库存 相加，佛山库存 可与 广州库存 相加
• 统计增长率时，省的增长率 不能通过 市的增长率直接求和或求平均 得到

指标命名规范

2、左联前后数量校验

1. A表 LEFT JOIN B表 得到 C表，应满足$C表行数=A表行数$
2. 如果$C表行数>A表行数$，就说明发生笛卡儿积
3. 此时应当 检查B表的联表键是否有重复

3、数值类型校验，是否越界，是否损失精度，是否计算出错

• 不同数据库的数值数据类型不同，转换时可能出错
• 在SUM一列INT类型数据后，如果结果超出INT范围，就要写到BIGINT类型的列
• 10元运费分摊到3个商品时，金额只保留2位小数
  错误分法：3.33+3.33+3.33=9.99
  正确分法：3.33+3.33+3.34=10.00
• STRING列和INT列匹配时，要把INT转为STRING，因为STRING转INT后，前面的0会被吃掉
• 对于含有除法的指标计算时，须考虑除以0的情况，addK可防止0除错误

4、每日分区数据量监控

在HIVE上查询

DESC FORMATTED 表名 PARTITION(分区名="分区值");
1

在元数据库(MySQL)上查询

SELECT
  table_name
  ,PART_NAME AS partition_name
  ,create_time
  ,num_files
  ,num_rows
  ,raw_data_size
  ,total_size
  -- ,num_files_erasure_coded
  ,transient_last_ddl_time
  ,column_stats_accurate
FROM ( -- 昨天分区
  SELECT PART_NAME,PART_ID,TBL_ID,FROM_UNIXTIME(CREATE_TIME,"%Y-%m-%d %h:%i:%s") AS create_time FROM PARTITIONS
  -- 按分区键值对筛选
  WHERE PART_NAME=CONCAT("ymd=",DATE_FORMAT(DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY),"%Y-%m-%d"))
)t1
INNER JOIN ( -- 库名.表名（筛选外部表）
  SELECT CONCAT(DBS.NAME,TBLS.TBL_NAME) AS table_name,TBL_ID
  FROM DBS INNER JOIN TBLS ON DBS.DB_ID=TBLS.DB_ID
  WHERE TBLS.TBL_TYPE="EXTERNAL_TABLE"
  -- OR TBLS.TBL_TYPE="MANAGED_TABLE"
)t0 ON t1.TBL_ID=t0.TBL_ID
INNER JOIN (
  SELECT
    PART_ID
    ,MAX(IF(PARAM_KEY="numFiles",PARAM_VALUE+0,NULL)) AS num_files
    ,MAX(IF(PARAM_KEY="numRows",PARAM_VALUE+0,NULL)) AS num_rows
    ,MAX(IF(PARAM_KEY="rawDataSize",PARAM_VALUE+0,NULL)) AS raw_data_size
    ,MAX(IF(PARAM_KEY="totalSize",PARAM_VALUE+0,NULL)) AS total_size
    ,MAX(IF(PARAM_KEY="numFilesErasureCoded",PARAM_VALUE+0,NULL)) AS num_files_erasure_coded
    ,MAX(IF(PARAM_KEY="transient_lastDdlTime",FROM_UNIXTIME(PARAM_VALUE,"%Y-%m-%d %h:%i:%s"),NULL)) AS transient_last_ddl_time
    ,MAX(IF(PARAM_KEY="COLUMN_STATS_ACCURATE",PARAM_VALUE,NULL)) AS column_stats_accurate
  FROM PARTITION_PARAMS
  GROUP BY PART_ID
  -- HAVING column_stats_accurate IS NOT NULL
)t2 ON t1.PART_ID=t2.PART_ID;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

5、重复值和NULL检测

HIVE没有主键，但是 HIVE表某些列在业务上是 唯一且非空的
例如：消费者表的唯一标识是用户ID，用户ID不允许重复，用户ID不允许有NULL

WITH
t AS (
  SELECT 1 AS user_id UNION ALL
  SELECT 2 AS user_id UNION ALL
  SELECT 2 AS user_id UNION ALL
  SELECT NULL AS user_id
),
w AS (SELECT user_id,ROW_NUMBER()OVER(PARTITION BY user_id) AS r FROM t)
SELECT
  COUNT(1) AS total_rows                                            -- 总行数
  ,COUNT(if(user_id IS NOT NULL AND r==1,1,NULL)) AS distinct_rows  -- 非空去重的唯一标识列的行数
  ,COUNT(if(user_id IS NOT NULL AND r>1,1,NULL)) AS null_rows       -- 重复数
  ,COUNT(if(user_id IS NULL,1,NULL)) AS repetition                  -- NULL行数
FROM w;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

SQL计算均值遇到NULL

当SQL计算均值遇到NULL时，需要和需求人员沟通好逻辑，看要不要把NULL算上

6、时间数据校验

• 注意时区
• 时间戳相关计算

7、数据格式规范性

• 日期的字符串格式为yyyy-MM-dd，如2022-02-02
• 时间的字符串格式为yyyy-MM-dd HH:mm:ss，如2022-02-02 02:22:22

---