不错的文章，已经收藏，谢谢分享

活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式


&gt; GBase 8a 没有外键约束，不支持事务，数据完整性的保障责任从数据库引擎转移到了应用层和数据管道。本文系统讲解在 MPP 分析数据库中如何建立数据质量检查体系，覆盖加载后校验、跨表一致性检查、数据漂移监控，以及异常数据的定位与修复流程。

---

## 一、为什么 MPP 数据库需要主动管理数据质量

在 MySQL 或 Oracle 这类事务型数据库中，数据质量的第一道防线由数据库引擎自动守护：外键约束拒绝不合法的关联关系、唯一约束防止重复写入、NOT NULL 约束拦截空值、CHECK 约束验证值域。应用层的代码写错了，数据库层面兜底。

GBase 8a 在这些约束机制上几乎是空白状态。建表语句里可以写 `PRIMARY KEY`，但 GBase 8a 不强制校验唯一性；可以写 `NOT NULL`，但加载时是否真的拒绝空值取决于配置；完全没有外键约束。这种设计是有意为之的权衡——去掉约束检查的开销换来了更高的写入吞吐量，对于每天处理亿级别数据的分析型数据库来说，这个权衡是合理的。

但权衡的代价是数据质量问题不会在写入时被自动发现和拒绝，而是悄悄存在于数据库中，直到某次报表数字对不上、某次业务质疑数据可信度时，才被人发现——而此时问题可能已经积累了很长时间，影响了大量历史数据。建立主动的数据质量检查体系，是 GBase 8a 运营必不可少的配套工作，不能依赖数据库引擎的约束机制来被动防御，而要通过定期主动检查来发现问题。

---

## 二、数据质量检查的四个维度

数据质量问题可以归纳为四个维度，每个维度对应不同的检查方法和修复策略。

​**完整性（Completeness）**​：检查关键字段是否存在空值或零值。比如 `order_id` 为空、`customer_id` 为 0、`amount` 为负数，这些情况在正常业务中不应该出现，一旦发现说明上游数据管道存在异常。

​**唯一性（Uniqueness）**​：检查应该唯一的键是否出现重复。由于 GBase 8a 不强制主键唯一性，重复写入的数据会安静地存在，直到聚合时产生多倍计算才被发现。定期检查重复记录，是保证聚合结果准确的基础。

​**一致性（Consistency）**​：检查跨表之间的数据关联关系是否合法。比如 `orders.dept_id` 是否都能在 `dim_dept.dept_id` 中找到对应记录，找不到的"孤儿记录"说明维度表和事实表之间出现了断裂。

​**及时性（Timeliness）**​：检查数据是否按时到达。在每日 ETL 体系中，每天的数据应该在规定时间窗口内加载完毕。通过检查特定日期的数据量是否符合预期、最大数据时间戳是否达到预期水位，可以发现数据延迟或缺失的问题。

---

## 三、加载完成后的即时校验

每次数据加载完成后，应该立即执行一套标准化的质量检查，把问题发现时间点从"用户看报表时"提前到"数据刚进库时"。检查内容根据业务特性定制，以下是通用的检查模板：

```sql
-- ============================================================
-- 数据加载质量检查报告（每次加载后执行，传入 p_date 业务日期）
-- ============================================================

-- 检查 1：当日数据行数是否在合理范围内
-- 正常范围通过历史均值 ± N 倍标准差确定
SELECT
    '行数检查'                                    AS check_name,
    COUNT(*)                                      AS actual_rows,
    (SELECT ROUND(AVG(daily_rows))
     FROM daily_stats_history
     WHERE stat_date &gt;= CURDATE() - INTERVAL 30 DAY) AS expected_avg,
    CASE
        WHEN COUNT(*) &lt; (SELECT AVG(daily_rows) * 0.5 FROM daily_stats_history
                         WHERE stat_date &gt;= CURDATE() - INTERVAL 30 DAY)
        THEN '异常：数据量偏少，可能存在数据缺失'
        WHEN COUNT(*) &gt; (SELECT AVG(daily_rows) * 2.0 FROM daily_stats_history
                         WHERE stat_date &gt;= CURDATE() - INTERVAL 30 DAY)
        THEN '异常：数据量偏多，可能存在重复加载'
        ELSE 'OK'
    END                                           AS check_result
FROM orders
WHERE order_date = CURDATE() - INTERVAL 1 DAY;

-- 检查 2：关键字段的空值率
SELECT
    '空值检查'                                    AS check_name,
    COUNT(*)                                      AS total_rows,
    SUM(CASE WHEN order_id    IS NULL THEN 1 ELSE 0 END) AS null_order_id,
    SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_id,
    SUM(CASE WHEN amount      IS NULL THEN 1 ELSE 0 END) AS null_amount,
    SUM(CASE WHEN order_date  IS NULL THEN 1 ELSE 0 END) AS null_order_date
FROM orders
WHERE order_date = CURDATE() - INTERVAL 1 DAY;

-- 检查 3：金额字段的值域合法性
SELECT
    '值域检查'                                    AS check_name,
    SUM(CASE WHEN amount &lt;= 0          THEN 1 ELSE 0 END) AS non_positive_amount,
    SUM(CASE WHEN amount &gt; 10000000    THEN 1 ELSE 0 END) AS suspicious_large_amount,
    SUM(CASE WHEN status NOT IN (0,1,2,3) THEN 1 ELSE 0 END) AS invalid_status,
    MIN(amount)                                   AS min_amount,
    MAX(amount)                                   AS max_amount
FROM orders
WHERE order_date = CURDATE() - INTERVAL 1 DAY;
```

---

## 四、唯一性检查与重复数据处理

由于 GBase 8a 不强制主键唯一，重复数据是最常见的数据质量问题，也是危害最大的。重复的订单记录会导致销售额被重复计算，重复的用户记录会导致用户数虚高，影响所有依赖这些数据的报表和决策。

```sql
-- 检查 orders 表中重复的 order_id
SELECT
    order_id,
    COUNT(*) AS duplicate_count
FROM orders
WHERE order_date = CURDATE() - INTERVAL 1 DAY
GROUP BY order_id
HAVING COUNT(*) &gt; 1
ORDER BY duplicate_count DESC
LIMIT 100;

-- 统计重复数据的规模（影响评估）
SELECT
    COUNT(*)                     AS total_rows,
    COUNT(DISTINCT order_id)     AS unique_order_ids,
    COUNT(*) - COUNT(DISTINCT order_id) AS duplicate_rows,
    ROUND((COUNT(*) - COUNT(DISTINCT order_id)) * 100.0 / COUNT(*), 4)
                                 AS duplicate_pct
FROM orders
WHERE order_date = CURDATE() - INTERVAL 1 DAY;
```

发现重复数据后，需要判断重复的根因：是 ETL 脚本的幂等设计有缺陷（先删后写但删除不彻底）、上游数据源本身就有重复（Kafka 消息重投、数据库 binlog 重放）、还是两次加载任务重叠执行了同一日期的数据。根因不同，修复策略也不同。

对于已经进入 GBase 8a 的重复数据，修复的标准做法是"建临时表去重后替换"：

```sql
-- 步骤 1：建去重临时表（每个 order_id 只保留一条）
CREATE TABLE orders_dedup_tmp AS
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY create_time DESC) AS rn
    FROM orders
    WHERE order_date = '2024-06-01'
) t
WHERE rn = 1
DISTRIBUTED BY HASH(customer_id);

-- 步骤 2：删除原始数据（该日期的全部数据）
DELETE FROM orders WHERE order_date = '2024-06-01';

-- 步骤 3：回写去重后的数据
INSERT INTO orders
SELECT order_id, customer_id, dept_id, amount, status, order_date, create_time
FROM orders_dedup_tmp;

-- 步骤 4：清理临时表
DROP TABLE orders_dedup_tmp;
```

---

## 五、跨表一致性校验

数据仓库中，事实表和维度表之间的关联完整性是数据可信度的基础。"孤儿事实记录"（fact record without a matching dimension）会导致报表中出现无法归类的数据，通常表现为报表里出现"其他"分类的数据量异常大，或者维度下钻时数据对不上汇总。

```sql
-- 检查 orders 中是否有 dim_dept 中不存在的 dept_id
SELECT
    o.dept_id,
    COUNT(*) AS orphan_count
FROM orders o
LEFT JOIN dim_dept d ON o.dept_id = d.dept_id
WHERE d.dept_id IS NULL
  AND o.order_date &gt;= CURDATE() - INTERVAL 7 DAY
GROUP BY o.dept_id
ORDER BY orphan_count DESC;

-- 检查 orders 中是否有 dim_product 中不存在的 product_id
SELECT
    o.product_id,
    COUNT(*) AS orphan_count
FROM orders o
LEFT JOIN dim_product p ON o.product_id = p.product_id
WHERE p.product_id IS NULL
  AND o.order_date &gt;= CURDATE() - INTERVAL 7 DAY
GROUP BY o.product_id
ORDER BY orphan_count DESC;
```

跨表一致性问题的根因通常是维度表和事实表的更新时序不对齐——维度表的新记录还没有同步到 GBase 8a，但事实表里已经有了引用这些新维度 ID 的记录。解决方法是在 ETL 流程中强制"先同步维度，后同步事实"，并在事实表写入前检查关键外键的有效性。

---

## 六、数据漂移监控

数据漂移（Data Drift）是指某个业务指标的值出现了无法用正常业务增长解释的突变。最典型的漂移有两种：某天的订单金额突然是前一天的 5 倍（很可能是重复加载），或者某天的数据量突然降到正常水平的 10%（很可能是数据丢失或 ETL 未完成）。

建立漂移监控的核心是​**历史基准**​：记录每天关键指标的值，并与近期历史均值做对比，超出阈值时告警。这个逻辑实现起来并不复杂，只需要一张统计历史表和一个每日运行的 SQL 脚本。

```sql
-- 每日写入关键统计指标
INSERT INTO daily_data_quality_stats
    (stat_date, table_name, metric_name, metric_value)
SELECT
    CURDATE() - INTERVAL 1 DAY                        AS stat_date,
    'orders'                                           AS table_name,
    'total_rows'                                       AS metric_name,
    COUNT(*)                                           AS metric_value
FROM orders
WHERE order_date = CURDATE() - INTERVAL 1 DAY

UNION ALL

SELECT CURDATE() - INTERVAL 1 DAY, 'orders', 'total_amount', SUM(amount)
FROM orders WHERE order_date = CURDATE() - INTERVAL 1 DAY

UNION ALL

SELECT CURDATE() - INTERVAL 1 DAY, 'orders', 'unique_customers', COUNT(DISTINCT customer_id)
FROM orders WHERE order_date = CURDATE() - INTERVAL 1 DAY;
```

```sql
-- 漂移检测：与过去 30 天均值比较，偏差超过 50% 时告警
SELECT
    s.stat_date,
    s.metric_name,
    s.metric_value                               AS today_value,
    ROUND(AVG(h.metric_value), 0)               AS hist_avg_30d,
    ROUND((s.metric_value - AVG(h.metric_value))
          / AVG(h.metric_value) * 100, 2)       AS pct_deviation,
    CASE
        WHEN ABS(s.metric_value - AVG(h.metric_value))
             / AVG(h.metric_value) &gt; 0.5
        THEN '⚠ 漂移告警'
        ELSE 'OK'
    END                                         AS drift_flag
FROM daily_data_quality_stats s
JOIN daily_data_quality_stats h
  ON s.table_name   = h.table_name
 AND s.metric_name  = h.metric_name
 AND h.stat_date BETWEEN s.stat_date - INTERVAL 30 DAY
                     AND s.stat_date - INTERVAL 1 DAY
WHERE s.stat_date = CURDATE() - INTERVAL 1 DAY
GROUP BY s.stat_date, s.metric_name, s.metric_value
HAVING pct_deviation IS NOT NULL
ORDER BY ABS(pct_deviation) DESC;
```

漂移监控的阈值（上面例子中的 50%）需要根据业务的实际波动规律来设定。周末和节假日的业务量通常与工作日差别很大，如果不做区分，会产生大量误报。更精细的漂移检测可以按"同类日期"（上周同一天、去年同月同周）做对比，而不是简单的 30 天均值，这样能有效降低误报率，提高告警的可信度。

---

## 七、数据质量检查的自动化

以上所有检查 SQL 都应该整合到 ETL 流程中，作为每次数据加载的必要后置步骤自动运行，而不是等到有问题时才人工查询。

推荐的自动化方式是把所有检查结果写入一张质量报告表，每次 ETL 结束后发送汇总邮件或推送告警，让 DBA 和数据工程师不需要主动去查，而是在有问题时被动接收通知。

```bash
#!/bin/bash
# run_quality_checks.sh：ETL 完成后调用，执行质量检查并推送结果

GCCLI="gccli -u dba -pdba_password sales_db"
BIZDATE=$(date -d yesterday '+%Y-%m-%d')
WEBHOOK="https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=YOUR_KEY"

# 执行唯一性检查
DUPS=$($GCCLI -e "
    SELECT COUNT(*) FROM (
        SELECT order_id FROM orders
        WHERE order_date='$BIZDATE'
        GROUP BY order_id HAVING COUNT(*)&gt;1
    ) t" 2&gt;/dev/null | tail -1 | tr -d ' ')

# 执行孤儿记录检查
ORPHANS=$($GCCLI -e "
    SELECT COUNT(*) FROM orders o
    LEFT JOIN dim_dept d ON o.dept_id=d.dept_id
    WHERE d.dept_id IS NULL AND o.order_date='$BIZDATE'" \
    2&gt;/dev/null | tail -1 | tr -d ' ')

# 汇总结果并推送
MSG="【数据质量报告】$BIZDATE\n重复记录: ${DUPS} 条\n孤儿记录: ${ORPHANS} 条"
if [ "$DUPS" -gt 0 ] || [ "$ORPHANS" -gt 0 ]; then
    MSG="⚠ ${MSG}\n请及时排查！"
fi

curl -s -X POST "$WEBHOOK" \
    -H "Content-Type: application/json" \
    -d "{\"msgtype\":\"text\",\"text\":{\"content\":\"$MSG\"}}"
```

好的数据质量管理体系最终呈现的效果，是数据问题在影响到业务用户之前就被内部发现和修复，报表数字的可信度持续提升，DBA 和数据工程师的被动救火时间减少，转而投入到更有价值的主动优化工作中。这个目标的实现不依赖任何高端工具，只需要把本文中的检查 SQL 系统化地整合到日常 ETL 流程中，坚持执行。



运维管理

GBase 8a 数据质量管理与一致性校验实战

GBase 8a 数据质量管理与一致性校验实战

评论

热门帖子