如何为BigQuery数据创建空值、金额不匹配等自动邮件告警？

一、基础版告警（检测异常+邮件通知） #

1. 编写异常检测SQL #

把所有需要检测的异常场景用SQL实现，通过UNION ALL合并成一个查询，覆盖0行数据、空值、金额不匹配等场景：

-- 合并所有异常检测逻辑
SELECT * FROM (
  -- 场景1：发票表无数据
  SELECT 
    '发票表' AS table_name,
    '0行数据' AS issue_type,
    NULL AS root_cause,
    ['XX表', 'XY表'] AS related_tables
  FROM `your-project.your-dataset.invoice_table`
  HAVING COUNT(*) = 0
)
UNION ALL
SELECT * FROM (
  -- 场景2：发票ID字段空值/空内容
  SELECT 
    '发票表' AS table_name,
    '字段空值' AS issue_type,
    NULL AS root_cause,
    ['XX表'] AS related_tables
  FROM `your-project.your-dataset.invoice_table`
  WHERE invoice_id IS NULL OR TRIM(invoice_id) = ''
  HAVING COUNT(*) > 0
)
UNION ALL
SELECT * FROM (
  -- 场景3：发票总金额与明细金额不匹配
  SELECT 
    '发票表' AS table_name,
    '金额不匹配' AS issue_type,
    NULL AS root_cause,
    ['XY表'] AS related_tables
  FROM (
    SELECT 
      invoice_id,
      total_amount,
      SUM(detail_amount) AS detail_sum
    FROM `your-project.your-dataset.invoice_table`
    JOIN `your-project.your-dataset.invoice_detail` USING(invoice_id)
    GROUP BY invoice_id, total_amount
  )
  WHERE total_amount != detail_sum
)

2. 配置BigQuery调度查询 #

• 将上述SQL保存为BigQuery的查询脚本，或创建视图your-project.your-dataset.data_alerts。
• 进入查询页面，点击右上角调度按钮，设置执行频率（如每天凌晨2点）。
• 在调度设置的通知选项卡中添加收件人邮箱，勾选“当查询返回结果时发送通知”。
• 自定义邮件内容模板：

注意！{{table_name}}检测到{{issue_type}}，可查看{{related_tables}}。

当查询返回异常结果时，BigQuery会自动触发邮件通知。

二、进阶版告警（含根因分析） #

进阶版需要在检测异常的同时自动判断根因，将其加入告警内容，有两种实现方式：

方式1：增强SQL的根因判断逻辑 #

直接在检测SQL中加入CASE语句实现根因分析，让查询结果自带根因信息：

SELECT * FROM (
  -- 场景1：发票表无数据，判断上游表原因
  SELECT 
    '发票表' AS table_name,
    '0行数据' AS issue_type,
    CASE 
      WHEN (SELECT COUNT(*) FROM `your-project.your-dataset.XX_table`) = 0 THEN '上游XX表无数据导致发票表未同步'
      WHEN (SELECT COUNT(*) FROM `your-project.your-dataset.XY_table`) = 0 THEN '上游XY表无数据导致发票表未生成'
      ELSE '未知原因'
    END AS root_cause,
    ['XX表', 'XY表'] AS related_tables
  FROM `your-project.your-dataset.invoice_table`
  HAVING COUNT(*) = 0
)
UNION ALL
SELECT * FROM (
  -- 场景2：发票ID空值，判断根因
  SELECT 
    '发票表' AS table_name,
    '字段空值' AS issue_type,
    CONCAT('字段invoice_id存在', COUNT(*), '条空值，可能是上游XX表同步时未填充') AS root_cause,
    ['XX表'] AS related_tables
  FROM `your-project.your-dataset.invoice_table`
  WHERE invoice_id IS NULL OR TRIM(invoice_id) = ''
  HAVING COUNT(*) > 0
)
UNION ALL
SELECT * FROM (
  -- 场景3：金额不匹配，判断根因
  SELECT 
    '发票表' AS table_name,
    '金额不匹配' AS issue_type,
    CONCAT('发票ID ', invoice_id, '的总金额与明细总和差为', ABS(total_amount - detail_sum) ,'，可能是明细数据重复或计算错误') AS root_cause,
    ['XY表'] AS related_tables
  FROM (
    SELECT 
      invoice_id,
      total_amount,
      SUM(detail_amount) AS detail_sum
    FROM `your-project.your-dataset.invoice_table`
    JOIN `your-project.your-dataset.invoice_detail` USING(invoice_id)
    GROUP BY invoice_id, total_amount
  )
  WHERE total_amount != detail_sum
)

重复基础版的调度配置，将邮件内容模板修改为：

注意！{{table_name}}检测到{{issue_type}}，可查看{{related_tables}}。该问题由{{root_cause}}导致。

方式2：用Cloud Function实现自定义告警逻辑 #

如果需要更复杂的根因分析（如调用外部API、多表关联排查），可借助Cloud Function：

1. 在BigQuery配置调度查询，将异常结果写入临时表your-project.your-dataset.alert_logs。
2. 创建Cloud Function，触发条件设置为“BigQuery - 作业完成”，筛选目标调度查询的完成事件。
3. 在Cloud Function中读取alert_logs表的异常数据，编写逻辑生成根因描述，组装进阶版告警内容。
4. 通过GCP的gmail-api或第三方邮件服务（如SendGrid）发送邮件。

三、注意事项 #

• 确保BigQuery服务账号拥有读取相关表、创建调度、发送邮件的权限。
• 测试时可手动执行检测SQL，验证异常捕获是否准确。
• 根据业务需求调整调度频率，支持小时级、天级等周期。

内容的提问来源于stack exchange，提问作者Nadya