sql - 如何优化 Google BigQuery 中的重复正则表达式提取

Question

我的表有大约 1700 万行新闻文章，总计大约 40 GB 的数据。我有一个大约 120 个关键字的列表，并且想要提取它们的出现以及所有文章中的上下文。

所以我的正则表达式部分看起来像这样:

REGEXP_EXTRACT_ALL(LOWER(body), ".{1,50}\\bKEYWORD\\b.{1,50}"

其中 KEYWORD 替换为真正的关键字，body 是文章的全文。

对所有 120 个关键字执行这些查询并将结果聚合到一个目标表中的最佳策略是什么？

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
WITH keywords AS (
  SELECT LOWER(keyword) AS keyword
  FROM UNNEST(['Car', 'Vehicle', 'Motorcycle']) keyword
)
SELECT REGEXP_EXTRACT_ALL(LOWER(body), keywords_regexp) AS mentions, body 
FROM `project.dataset.table`, 
(SELECT CONCAT(r'.{1,50}\b(?:', STRING_AGG(keyword, '|'), r')\b.{1,50}') AS keywords_regexp FROM keywords)
WHERE REGEXP_CONTAINS(body, keywords_regexp)

您可以使用一些虚拟或公共(public)数据进行测试，使用上面的示例，如下例所示

#standardSQL
WITH `project.dataset.table` AS (
  SELECT text AS body
  FROM `bigquery-public-data.hacker_news.comments`
), keywords AS (
  SELECT LOWER(keyword) AS keyword
  FROM UNNEST(['Car', 'Vehicle', 'Motorcycle']) keyword
)
SELECT REGEXP_EXTRACT_ALL(LOWER(body), keywords_regexp) AS mentions, body 
FROM `project.dataset.table`, 
(SELECT CONCAT(r'.{1,50}\b(?:', STRING_AGG(keyword, '|'), r')\b.{1,50}') AS keywords_regexp FROM keywords)
WHERE REGEXP_CONTAINS(body, keywords_regexp)
-- LIMIT 100   

UPDATE: Optimized version - took just 17-20 sec vs. 440-460 sec for above version

#standardSQL
WITH `project.dataset.table` AS (
  SELECT text AS body
  FROM `bigquery-public-data.hacker_news.comments`
), keywords AS (
  SELECT 
    CONCAT(r'\b', LOWER(keyword), r'\b') AS keyword_test,
    CONCAT(r'.{1,50}\b', LOWER(keyword), r'\b.{1,50}') AS keyword
  FROM UNNEST(['Car', 'Vehicle', 'Motorcycle']) keyword
)
SELECT ARRAY_CONCAT_AGG(mention) AS mentions, body
FROM (
  SELECT body, REGEXP_EXTRACT_ALL(LOWER(body), keyword) AS mention
  FROM (
    SELECT keyword, body 
    FROM `project.dataset.table`, keywords 
    WHERE REGEXP_CONTAINS(body, keyword_test) 
  )
)
GROUP BY body   

Per OP's request - Some explanations on what makes the difference :o)

在初始版本中 - 有一种简单直接的方法，即使用所有关键字构建正则表达式并逐行应用提取所有内容，这显然非常昂贵(性能方面)

因此，优化版本首先为每个工作流程提取合格的关键字 - 仅使用关键字，每侧不包含 50 个字符。所以在第一个“回合”中，我们收集成对的关键字和正文包含关键字的行。因此，如果 body 有 N 个关键字 - 我们将获得 N 行关键字，body。
然后，每个这样的行仅由具有给定限定关键字的完整正则表达式处理。这最终会便宜得多!
因此，最后的操作只是将具有相同主体的行组合回(分组)并聚合初始提取 - 但因为初始提取本身可能是数组 - 我们不仅使用 ARRAY_AGG而是 ARRAY_CONCAT_AGG功能

希望这有助于理解上述优化版本的工作原理以及为什么它工作得更好:o)