BQ存储过程无UPDATE语句却触发表更新速率超限错误求助

问题根源 #

你遇到的错误是因为BigQuery对单表的更新操作（包括CREATE OR REPLACE TABLE、INSERT、UPDATE等）有频率限制，短时间内多次调用存储过程执行CREATE OR REPLACE TABLE，会不断替换同一张表，触发了这个限制——和是否使用CTE无关，所以移除CTE后问题依然存在。

针对性解决方案 #

方案1：改为追加数据而非覆盖表 #

如果你的需求是每次调用存储过程向结果表追加数据，而非覆盖全表，把CREATE OR REPLACE TABLE改成INSERT INTO即可：

CREATE OR REPLACE PROCEDURE mydataset.my_procedure()
BEGIN
    -- 先确保results_table已存在，结构与查询输出一致
    INSERT INTO myproject.mydataset.results_table (col1, col2, ...)
    WITH cte_statement AS (
        SELECT 
            col1, col2, ...
            LAG(col1) OVER (PARTITION BY t ORDER BY d) AS val1,
            LAG(col2) OVER (PARTITION BY t ORDER BY d) AS val2
        FROM myproject.mydataset.sourcetable
        ORDER BY col2
    )
    SELECT 
        col1, col2, ...
        CASE WHEN val1 ... END AS new_val1,
        CASE WHEN val2 ... END AS new_val2
    FROM cte_statement
    ORDER BY col1;
END;

如果需要避免插入重复数据，可以在查询末尾加上去重逻辑：

SELECT ...
FROM cte_statement
WHERE NOT EXISTS (
    SELECT 1 FROM myproject.mydataset.results_table rt
    WHERE rt.col1 = cte_statement.col1 -- 用主键或唯一标识判断重复
)
ORDER BY col1;

方案2：合并多次调用为单次执行 #

如果你的需求是重复执行相同逻辑生成全量数据，完全不需要多次调用存储过程——单次调用就能生成完整结果。如果是因为需要处理不同参数（比如不同时间范围、不同分区），应该给存储过程添加参数，一次性处理所有场景，而非多次调用无参数的存储过程：

CREATE OR REPLACE PROCEDURE mydataset.my_procedure(p_filter_value STRING)
BEGIN
    INSERT INTO myproject.mydataset.results_table (col1, col2, ...)
    WITH cte_statement AS (
        SELECT 
            col1, col2, ...
            LAG(col1) OVER (PARTITION BY t ORDER BY d) AS val1,
            LAG(col2) OVER (PARTITION BY t ORDER BY d) AS val2
        FROM myproject.mydataset.sourcetable
        WHERE filter_col = p_filter_value -- 根据参数过滤数据
        ORDER BY col2
    )
    SELECT 
        col1, col2, ...
        CASE WHEN val1 ... END AS new_val1,
        CASE WHEN val2 ... END AS new_val2
    FROM cte_statement
    ORDER BY col1;
END;

然后通过一次脚本处理所有参数，比如用循环或UNION ALL合并所有参数的查询结果，减少对目标表的更新次数。

方案3：使用分区表分散更新压力 #

如果结果表可以按字段（比如日期、用户ID）分区，每次调用只更新指定分区，这样每个分区的更新频率不会触发限制：

1. 先创建分区表：

CREATE OR REPLACE TABLE myproject.mydataset.results_table
PARTITION BY DATE(partition_col) -- 按日期分区，替换为你的分区字段
AS
-- 初始化空表或初始数据
SELECT col1, col2, ..., partition_col FROM myproject.mydataset.sourcetable LIMIT 0;

2. 修改存储过程，只更新目标分区：

CREATE OR REPLACE PROCEDURE mydataset.my_procedure(p_partition_date DATE)
BEGIN
    MERGE INTO myproject.mydataset.results_table rt
    USING (
        WITH cte_statement AS (
            SELECT 
                col1, col2, ..., p_partition_date AS partition_col
                LAG(col1) OVER (PARTITION BY t ORDER BY d) AS val1,
                LAG(col2) OVER (PARTITION BY t ORDER BY d) AS val2
            FROM myproject.mydataset.sourcetable
            WHERE DATE(partition_col) = p_partition_date
            ORDER BY col2
        )
        SELECT 
            col1, col2, ..., partition_col
            CASE WHEN val1 ... END AS new_val1,
            CASE WHEN val2 ... END AS new_val2
        FROM cte_statement
    ) AS src
    ON rt.partition_col = src.partition_col AND rt.col1 = src.col1
    WHEN MATCHED THEN UPDATE SET ... -- 更新已有数据
    WHEN NOT MATCHED THEN INSERT (col1, col2, ..., partition_col) VALUES (...); -- 插入新数据
END;

不推荐的方案：控制调用频率 #

如果必须多次替换全表，可以在调用之间添加延迟（比如用WAIT FOR语句），但这会大幅降低执行效率，仅作为临时应急方案：

CALL mydataset.my_procedure();
WAIT FOR 30 SECOND; -- 等待30秒再执行下一次
CALL mydataset.my_procedure();

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