优化含正则匹配的BigQuery LEFT JOIN慢查询问题

优化方案 #

核心思路 #

当前的LEFT JOIN是将223M行与600K行做动态正则匹配关联，本质是笛卡尔积级别的计算，BigQuery无法优化这类动态正则条件。优化方向是把替换逻辑从JOIN阶段移到单条数据处理阶段，用批量替换替代逐行关联，彻底避免大规模正则连接。

具体实现 #

1. 预处理映射表，生成批量替换规则

将错误-正确标签映射合并为可批量处理的正则模式和映射数组：

WITH src AS (SELECT * FROM `my_table`),
cat_src AS (
    SELECT DISTINCT cat.name
    FROM src, UNNEST(labels) cat
),
shortned_cat AS (
    SELECT 
        name,
        REPLACE(name, ' ', '') AS shortned
    FROM cat_src
    GROUP BY 1, 2
),
shortned_cat_map AS (
    SELECT 
        sc.name AS correct_name,
        sc.shortned AS wrong_str
    FROM cat_src cs
    JOIN shortned_cat sc ON LOWER(cs.name) = LOWER(sc.shortned)
    GROUP BY 1, 2
),
-- 生成批量替换用的正则和映射字典
replace_rules AS (
    SELECT 
        -- 生成匹配所有错误短串的正则（带单词边界、不区分大小写）
        r"(?i:\b(" || STRING_AGG(REGEXP_ESCAPE(wrong_str), "|") || r")\b" AS regex_pattern,
        -- 构建错误串到正确名称的映射数组
        ARRAY_AGG(STRUCT(wrong_str, correct_name)) AS replace_map
    FROM shortned_cat_map
)

2. 创建临时UDF实现批量替换

BigQuery原生REGEXP_REPLACE不支持动态多值映射，用JS UDF实现遍历替换：

CREATE TEMP FUNCTION replace_wrong_labels(label_str STRING, replace_map ARRAY<STRUCT<wrong_str STRING, correct_name STRING>>)
RETURNS STRING
LANGUAGE js AS """
    let result = label_str;
    // 按错误串长度倒序排序，避免短串优先匹配导致长串替换失效
    replace_map.sort((a, b) => b.wrong_str.length - a.wrong_str.length);
    replace_map.forEach(item => {
        const regex = new RegExp(`\\b${item.wrong_str}\\b`, 'gi');
        result = result.replace(regex, item.correct_name);
    });
    return result;
""";

3. 执行最终替换与聚合

直接在UNNEST后调用UDF完成替换，无需JOIN操作：

SELECT
    s.id,
    ARRAY_AGG(STRUCT(
        replace_wrong_labels(cat.name, rr.replace_map) AS name
    )) AS categories
FROM src s, UNNEST(s.labels) cat
CROSS JOIN replace_rules rr
GROUP BY s.id

额外优化建议 #

• 如果映射表中存在同一错误串对应多个正确名称的情况，需先去重或定义优先级（比如保留出现次数最多的正确名称），避免替换冲突。
• 若映射表规模超过10K条，可拆分映射表为多个批次分批替换，防止正则表达式过长导致性能下降。

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