统计HIVE字段行数、NULL行数、字段重复行数

场景

源MySQL数据导入HIVE数据仓库ODS层后，统计主键行数、主键NULL行数、主键重复行数

统计HIVE表主键行数、主键NULL行数、主键重复行数的SQL

-- 统计HIVE表源MySQL主键行数、主键NULL行数、主键重复行数
WITH
w AS (
    SELECT
        `id`
        ,ROW_NUMBER()OVER(PARTITION BY `id` ORDER BY `id`)r
    FROM `{hive_tb}`
    WHERE ymd="{ymd}"
)
SELECT
    COUNT(1)                                       -- 行数
    ,COUNT(if(`id` IS NOT NULL AND `r`==1,1,NULL)) -- 非空主键去重后行数
    ,COUNT(if(`id` IS NULL,1,NULL))                -- 主键NULL行数
    ,COUNT(if(`id` IS NOT NULL AND `r`>1,1,NULL))  -- 主键重复行数
FROM w;
```### 联合主键的情况
`i1`和`i2`是联合主键
```sql
WITH
w AS (
    SELECT
        concat(`i1`,`i2`) AS `pk`
        ,ROW_NUMBER()OVER(PARTITION BY `i1`,`i2` ORDER BY `i1`,`i2`)r
    FROM `{hive_tb}`
    WHERE ymd="{ymd}"
)
SELECT
    COUNT(1) AS target_rows                                                    -- 行数
    ,COUNT(if(`pk` IS NOT NULL AND `r`==1,1,NULL)) AS target_distinct_pk_rows  -- 非空主键去重后行数
    ,COUNT(if(`pk` IS NOT NULL AND `r`>1,1,NULL)) AS target_pk_null_rows       -- 主键重复行数
    ,COUNT(if(`pk` IS NULL,1,NULL)) AS target_pk_repetition                    -- 主键NULL行数
FROM w;

测试

-- 建表测试
DROP TABLE IF EXISTS `temp_test`;
CREATE TABLE `temp_test`(
    `id`      STRING  COMMENT '主键'
) COMMENT '测试'
PARTITIONED BY (`ymd` STRING);
-- 插入数据
INSERT OVERWRITE TABLE `temp_test` PARTITION(ymd="2022-04-17")
VALUES ("1"),("2"),("3"),(NULL),("3"),("3"),(NULL),("4");
-- 查询数据
SELECT * FROM `temp_test`;

-- 统计HIVE表源MySQL主键行数、主键NULL行数、主键重复行数
WITH
w AS (
    SELECT
        `id`
        ,ROW_NUMBER()OVER(PARTITION BY `id` ORDER BY `id`)r
    FROM `temp_test`
    WHERE ymd="2022-04-17"
)
SELECT
    COUNT(1)                                       -- 行数
    ,COUNT(if(`id` IS NULL,1,NULL))                -- 主键NULL行数
    ,COUNT(if(`id` IS NOT NULL AND `r`>1,1,NULL))  -- 主键重复行数
    ,COUNT(if(`id` IS NOT NULL AND `r`==1,1,NULL)) -- 非空主键去重后行数
FROM w;

样本

+---------------+----------------+
| temp_test.id  | temp_test.ymd  |
+---------------+----------------+
| 1             | 2022-04-17     |
| 2             | 2022-04-17     |
| 3             | 2022-04-17     |
| NULL          | 2022-04-17     |
| 3             | 2022-04-17     |
| 3             | 2022-04-17     |
| NULL          | 2022-04-17     |
| 4             | 2022-04-17     |
+---------------+----------------+

样本计算结果

+------+------+------+------+
| _c0  | _c1  | _c2  | _c3  |
+------+------+------+------+
| 8    | 2    | 2    | 4    |
+------+------+------+------+

---

Appendix

持久化

统计HIVE主键的行数、NULL行数、重复行数，可用于数据同步校验
可用MySQL存储每天校验结果，DDL 如下：

CREATE TABLE checkout_synchronism(
 -- 基础信息
 source_host VARCHAR(255) NOT NULL COMMENT '源地址',
 source_type VARCHAR(255) NOT NULL COMMENT '源类型',
 source_database_name VARCHAR(255) NOT NULL COMMENT '源库名',
 source_table_name VARCHAR(255) NOT NULL COMMENT '源表名',
 source_primary_key VARCHAR(255) NOT NULL COMMENT '主键',
 source_increment_tag VARCHAR(255) NOT NULL COMMENT '源表增量标识',
 target_database VARCHAR(255) NOT NULL COMMENT '目标库',
 target_table VARCHAR(255) NOT NULL COMMENT '目标表',
 target_partition VARCHAR(255) NOT NULL COMMENT '分区键',
 target_partition_value VARCHAR(255) NOT NULL COMMENT '分区值',
 -- 度量值
 source_rows BIGINT NOT NULL COMMENT '源表行数',
 target_rows BIGINT NOT NULL COMMENT '目标表行数',
 target_distinct_pk_rows BIGINT NOT NULL COMMENT '目标表非空主键去重后行数',
 target_pk_null_rows INT NOT NULL COMMENT '目标表主键null',
 target_pk_repetition INT NOT NULL COMMENT '目标表主键重复',
 update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 -- 主键
 primary key (target_database,target_table)
)COMMENT='数据同步校验';

程序中的SQL

WITH
w AS (
    SELECT
        concat({source_primary_key}) AS `pk`
        ,ROW_NUMBER()OVER(PARTITION BY {source_primary_key} ORDER BY {source_primary_key})r
    FROM `{target_table}`
    WHERE {target_partition}="{target_partition_value}"
)
SELECT
    COUNT(1) AS target_rows                                                    -- 行数
    ,COUNT(if(`pk` IS NOT NULL AND `r`==1,1,NULL)) AS target_distinct_pk_rows  -- 非空主键去重后行数
    ,COUNT(if(`pk` IS NOT NULL AND `r`>1,1,NULL)) AS target_pk_null_rows       -- 主键重复行数
    ,COUNT(if(`pk` IS NULL,1,NULL)) AS target_pk_repetition                    -- 主键NULL行数
FROM w;

source_primary_key是源(MySQL)表主键，多个会用分隔，例如下面

`id`
`type`,`color`,`size`

MySQL主键不能为NULL

主键不能为NULL

-- 建表
CREATE TABLE t11(
 a INT(9) PRIMARY KEY,
 b CHAR(9) UNIQUE
);

-- 插入
INSERT INTO t11 VALUES (NULL,'AB');

插入报错

1048 - Column 'a' cannot be null

UNIQUE列可以为NULL且多个

INSERT INTO t11 VALUES (3,NULL);
INSERT INTO t11 VALUES (4,NULL);
SELECT * FROM t11;

联合主键中的任何列都不能为NULL

CREATE TABLE t13(
 a INT(9),
 b CHAR(9),
 c CHAR(9),
 PRIMARY KEY(a,b)
);

INSERT INTO t13 VALUES (2,NULL,'cc');

1048 - Column 'b' cannot be null

查看建表语句

SHOW CREATE TABLE t13;

最初的建表语句的主键是没写NOT NULL，但是查出来的建表语句有

CREATE TABLE `t13` (
  `a` int NOT NULL,
  `b` char(9) NOT NULL,
  `c` char(9) DEFAULT NULL,
  PRIMARY KEY (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci