BigQuery存储过程遇ingestDate空值抛错退出实现方法

需求落地逻辑 #

针对提出的核心要求与约束，逻辑调整遵循两个原则：

1. 空值校验完全前置：任何写入操作执行前，先全量扫描源表ingestDate列的空值情况，只要存在空值直接抛出错误终止流程，不会执行任何数据写入
2. 增量逻辑兼容边界场景，同时减少不必要的全表扫描开销，直接返回本次插入行数

修正后完整代码 #

CREATE OR REPLACE PROCEDURE dataset.sample1()
BEGIN
    -- 变量声明
    DECLARE null_ingest_count INT64;
    DECLARE latestIngestDate DATETIME;
    DECLARE inserted_row_count INT64;

    -- 1. 前置校验：检查源表是否存在ingestDate为空的记录
    SET null_ingest_count = (
        SELECT COUNT(*) 
        FROM dataset.sourceTable 
        WHERE ingestDate IS NULL
    );
    
    IF null_ingest_count > 0 THEN
        RAISE USING 
            MESSAGE = '同步终止：源表ingestDate列存在' || CAST(null_ingest_count AS STRING) || '条空值记录',
            ERROR_CODE = 'NULL_INGEST_DATE_FOUND';
    END IF;

    -- 2. 获取目标表当前最大摄入时间，兼容目标表为空的首次同步场景
    SET latestIngestDate = IFNULL(
        (SELECT MAX(ingestDate) FROM dataset.destTable),
        DATETIME '1900-01-01 00:00:00' -- 兜底极小值，保证首次同步能拉取所有源表数据
    );

    -- 3. 执行增量插入
    INSERT INTO dataset.destTable (ID, ingestDate, Column1)
    SELECT ID, ingestDate, Column1
    FROM dataset.sourceTable
    WHERE ingestDate > latestIngestDate;

    -- 4. 获取本次实际插入行数，无需二次全表统计目标表
    SET inserted_row_count = @@row_count;

    -- 返回结果
    SELECT inserted_row_count AS new_inserted_rows;
END;

关键优化点说明 #

• 空值校验优先级最高：校验逻辑放在所有写入操作之前，一旦发现空值直接抛出错误退出，完全不会触发后续插入逻辑，符合要求
• 移除冗余全表计数：原逻辑中两次对目标表执行COUNT(*)属于大表场景下的不必要开销，直接使用BigQuery内置系统变量@@row_count即可拿到INSERT语句实际写入的行数，性能提升明显
• 修复首次同步失效问题：原逻辑中如果目标表为空，MAX(ingestDate)返回NULL，ingestDate > NULL的判断结果永远为假，会导致首次同步无法写入任何数据，新增的IFNULL兜底逻辑解决了这个边界问题
• 移除冗余类型转换：源表和目标表结构完全一致的前提下，不需要额外对字段做CAST转换，减少不必要的计算消耗；如果CSV导入时源表字段类型确实不匹配，可以按需保留CAST逻辑
• 错误信息明确：抛错时会直接返回源表中空值记录的数量，方便排查问题

注意：当前空值校验是扫描全量源表的ingestDate空值，即只要源表存在任何一条空值记录（哪怕该记录的时间早于目标表最新时间，不属于本次增量范围）都会触发报错，完全匹配需求描述。如果后续需要调整为仅校验本次增量范围内的空值，只需要把空值检查的查询条件加上ingestDate > latestIngestDate即可。

内容的提问来源于stack exchange，提问作者Echo-Victor58