如何优化BigQuery中使用交叉连接实现的海量文本正则匹配打标任务

你的原始SQL是交叉连接生成笛卡尔积，2000万行文本*10万关键词会产生2e12次计算，必然无法正常运行，可按以下方案优化：

方案1：SQL层改写（无需额外工具，适配绝大多数数仓/计算引擎） #

把逐行逐关键词匹配的逻辑，改为先合并所有关键词为单个正则表达式，一次性提取每行文本的所有命中关键词，完全避免笛卡尔积，计算量直接降到2000万量级。
示例代码（以BigQuery语法为例，其他引擎替换对应正则函数即可）：

WITH raw_text AS(
  select 'my car was broken' as text
  union all
  select 'nobody knows'
  union all
  select 'the fish is so beautiful'
),
raw_keywords AS(
  select 'car' as keyword
  union all
  select 'beautiful'
  union all
  select 'know'
  union all
  select 'journey'
),
-- 合并所有关键词为单个正则串
combined_regex AS (
  SELECT CONCAT('(', STRING_AGG(REGEXP_ESCAPE(keyword), '|'), ')') AS regex_str
  FROM raw_keywords
)
SELECT 
  text,
  matched_keyword
FROM raw_text, combined_regex,
-- 炸开所有命中的关键词为多行
UNNEST(REGEXP_EXTRACT_ALL(text, regex_str)) AS matched_keyword

• 如果需要严格匹配完整单词，避免"know"误命中"knowledge"这类场景，可把正则拼接逻辑改为CONCAT(r'\b(', STRING_AGG(REGEXP_ESCAPE(keyword), '|'), r')\b')
• 关键词拼接前必须加REGEXP_ESCAPE转义，避免关键词里的.、*等特殊字符破坏正则逻辑

方案2：分布式场景优化（适配Spark/Flink等计算框架，关键词量>15万时推荐） #

• 把10万关键词表作为广播变量分发到所有计算节点，避免shuffle开销
• 用Aho-Corasick多模式匹配算法替换正则匹配，仅需遍历文本1次即可命中所有关键词，性能比正则高3~10倍，Java/Python均有成熟的开源实现
• 可提前对文本做分词预处理，过滤掉不在关键词集合里的分词后再做匹配，进一步降低计算量

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