title: How to efficiently match regular expressions in a large dataset of Google BigQuery
body: <p>I have almost 750 regular expressions to match against the <a href="https://cloud.google.com/blog/topics/public-datasets/github-on-bigquery-analyze-all-the-open-source-code" rel="nofollow noreferrer">Google BigQuery GitHub Public Dataset</a>. At first, I was writing the query as below:</p>
  <pre><code>SELECT * FROM
  Sample_Table
  WHERE
  (REGEXP_CONTAINS(content, r&quot;[a-z0-9A-Z]{40}&quot;) OR -- expression 1
  REGEXP_CONTAINS(content, r&quot;[0-9a-z]{32}&quot;) OR -- expression 2
  ..................
  REGEXP_CONTAINS(content, r&quot;[a-z0-9]{80}&quot;) OR -- expression n-1
  REGEXP_CONTAINS(content, r&quot;[a-z0-9A-Z\%]{35}&quot;)) -- expression n  
  </code></pre>
  <p>But, in this case, I did not know which regular expression has been matched for a returned result row. Then, after this <a href="https://stackoverflow.com/a/73102359/4299527">suggestion</a>, I changed my query to below:</p>
  <pre><code>with patterns as (
   SELECT 1 pattern_id, r&quot;(?i)(?:abbysale)(?:.|[\n\r]){0,40}\b([a-z0-9A-Z]{40})\b&quot; pattern
  UNION ALL SELECT 2, r&quot;(?i)(?:abstract)(?:.|[\n\r]){0,40}\b([0-9a-z]{32})\b&quot;
  UNION ALL SELECT 3, r&quot;(?i)(?:abuseipdb)(?:.|[\n\r]){0,40}\b([a-z0-9]{80})\b&quot;
  UNION ALL SELECT 4, r&quot;(?i)(?:accuweather)(?:.|[\n\r]){0,40}([a-z0-9A-Z\%]{35})\b&quot;
  UNION ALL SELECT 5, r&quot;\b(aio\_[a-zA-Z0-9]{28})\b&quot;
  UNION ALL SELECT 6, r&quot;(?i)(?:adobe)(?:.|[\n\r]){0,40}\b([a-z0-9]{32})\b&quot;
  UNION ALL SELECT 7, r&quot;(?i)(?:adzuna)(?:.|[\n\r]){0,40}\b([a-z0-9]{32})\b&quot;
  UNION ALL SELECT 8, r&quot;(?i)(?:aeroworkflow)(?:.|[\n\r]){0,40}\b([a-zA-Z0-9^!]{20})\b&quot;
  UNION ALL SELECT 9, r&quot;(?i)(?:agora)(?:.|[\n\r]){0,40}\b([a-z0-9]{32})\b&quot;
  UNION ALL SELECT 10, r&quot;(?i)(?:aha)(?:.|[\n\r]){0,40}\b([0-9a-f]{64})\b&quot;
  UNION ALL SELECT 11, r&quot;(?i)(?:airbrake)(?:.|[\n\r]){0,40}\b([a-zA-Z-0-9]{32})\b&quot;
  UNION ALL SELECT 12, r&quot;(?i)(?:airship)(?:.|[\n\r]){0,40}\b([0-9Aa-zA-Z]{91})\b&quot;
  UNION ALL SELECT 13, r&quot;\b(key[a-zA-Z0-9_-]{14})\b&quot;
  UNION ALL SELECT 14, r&quot;(?i)(?:airvisual)(?:.|[\n\r]){0,40}\b([a-z0-9-]{36})\b&quot;
  UNION ALL SELECT 15, r&quot;(?i)(?:alconost)(?:.|[\n\r]){0,40}\b([0-9Aa-z]{32})\b&quot;
  UNION ALL SELECT 16, r&quot;(?i)(?:alegra)(?:.|[\n\r]){0,40}\b([a-z0-9-]{20})\b&quot;
  UNION ALL SELECT 17, r&quot;(?i)(?:aletheiaapi)(?:.|[\n\r]){0,40}\b([A-Z0-9]{32})\b&quot;
  UNION ALL SELECT 18, r&quot;(?i)(?:algolia)(?:.|[\n\r]){0,40}\b([a-zA-Z0-9]{32})\b&quot;
  UNION ALL SELECT 19, r&quot;\b([a-zA-Z0-9]{30})\b&quot;
  UNION ALL SELECT 20, r&quot;(?i)(?:alienvault)(?:.|[\n\r]){0,40}\b([a-z0-9]{64})\b&quot;
  UNION ALL SELECT 21, r&quot;(?i)(?:allsports)(?:.|[\n\r]){0,40}\b([a-z0-9]{64})\b&quot;
  UNION ALL SELECT 22, r&quot;(?i)(?:amadeus)(?:.|[\n\r]){0,40}\b([0-9A-Za-z]{32})\b&quot;
  UNION ALL SELECT 23, r&quot;(?i)(?:ambee)(?:.|[\n\r]){0,40}\b([0-9a-f]{64})\b&quot;
  UNION ALL SELECT 24, r&quot;(?i)(?:amplitude)(?:.|[\n\r]){0,40}\b([0-9a-f]{32})\b&quot;
  UNION ALL SELECT 25, r&quot;\b([0-9a-z]{8}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{12})\b&quot;
  UNION ALL SELECT 26, r&quot;(?i)(?:apacta)(?:.|[\n\r]){0,40}\b([a-z0-9-]{36})\b&quot;
  UNION ALL SELECT 27, r&quot;(?i)(?:api2cart)(?:.|[\n\r]){0,40}\b([0-9a-f]{32})\b&quot;
  UNION ALL SELECT 28, r&quot;\b(sk_live_[a-z0-9A-Z-]{93})\b&quot;
  UNION ALL SELECT 29, r&quot;(?i)(?:apideck)(?:.|[\n\r]){0,40}\b([a-z0-9A-Z]{40})\b&quot;
  UNION ALL SELECT 30, r&quot;(?i)(?:apiflash)(?:.|[\n\r]){0,40}\b([a-z0-9]{32})\b&quot;
  UNION ALL SELECT 31, r&quot;(?i)(?:apiflash)(?:.|[\n\r]){0,40}\b([a-zA-Z0-9\S]{21,30})\b&quot;
  UNION ALL SELECT 32, r&quot;(?i)(?:apifonica)(?:.|[\n\r]){0,40}\b([0-9a-z]{11}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{12})\b&quot;
  UNION ALL SELECT 33, r&quot;\b(apify\_api\_[a-zA-Z-0-9]{36})\b&quot;
  UNION ALL SELECT 34, r&quot;(?i)(?:apimatic)(?:.|[\n\r]){0,40}\b([a-zA-Z0-9]{3,20}@[a-zA-Z0-9]{2,12}.[a-zA-Z0-9]{2,5})\b&quot;
  UNION ALL SELECT 35, r&quot;(?i)(?:apimatic)(?:.|[\n\r]){0,40}\b([a-z0-9-\S]{8,32})\b&quot;
  UNION ALL SELECT 36, r&quot;(?i)(?:apiscience)(?:.|[\n\r]){0,40}\b([a-bA-Z0-9\S]{22})\b&quot;
  UNION ALL SELECT 37, r&quot;(?i)(?:apitemplate)(?:.|[\n\r]){0,40}\b([0-9a-zA-Z]{39})\b&quot;
  UNION ALL SELECT 38, r&quot;(?i)(?:apollo)(?:.|[\n\r]){0,40}\b([a-zA-Z0-9]{22})\b&quot;
  UNION ALL SELECT 39, r&quot;(?i)(?:appcues)(?:.|[\n\r]){0,40}\b([a-z0-9-]{36})\b&quot;
  UNION ALL SELECT 40, r&quot;(?i)(?:appcues)(?:.|[\n\r]){0,40}\b([a-z0-9-]{39})\b&quot;
  UNION ALL SELECT 41, r&quot;(?i)(?:appcues)(?:.|[\n\r]){0,40}\b([0-9]{5})\b&quot;
  UNION ALL SELECT 42, r&quot;(?i)(?:appfollow)(?:.|[\n\r]){0,40}\b([0-9A-Za-z]{20})\b&quot;
  UNION ALL SELECT 43, r&quot;(?i)(?:appsynergy)(?:.|[\n\r]){0,40}\b([a-z0-9]{64})\b&quot;
  UNION ALL SELECT 44, r&quot;(?i)(?:apptivo)(?:.|[\n\r]){0,40}\b([a-z0-9-]{36})\b&quot;
  UNION ALL SELECT 45, r&quot;\b([a-zA-Z0-9]{73})&quot;
  UNION ALL SELECT 46, r&quot;\b([A-Za-z0-9](?:[A-Za-z0-9\-]{0,61}[A-Za-z0-9])\.jfrog\.io)&quot;
  UNION ALL SELECT 47, r&quot;(?i)(?:artsy)(?:.|[\n\r]){0,40}\b([0-9a-zA-Z]{32})\b&quot;
  UNION ALL SELECT 48, r&quot;(?i)(?:asana)(?:.|[\n\r]){0,40}\b([a-z\/:0-9]{51})\b&quot;
  UNION ALL SELECT 49, r&quot;(?i)(?:asana)(?:.|[\n\r]){0,40}\b([0-9]{1,}\/[0-9]{16,}:[A-Za-z0-9]{32,})\b&quot;
  UNION ALL SELECT 50, r&quot;(?i)(?:assemblyai)(?:.|[\n\r]){0,40}\b([0-9a-z]{32})\b&quot;
  )
  
  SELECT any_value(F.repo_name) AS repo_name, string_agg(DISTINCT ('' ||  pattern_id)) as matches  
  FROM bigquery-public-data.github_repos.contents AS C
  INNER JOIN bigquery-public-data.github_repos.files AS F
  ON C.id = F.id
  ,patterns p
  WHERE NOT BINARY AND regexp_contains(C.content, p.pattern)
  GROUP BY F.repo_name
        
  </code></pre>
  <p>Here, my goal is to have the repository name with the distinct matched regular expressions in a comma separated string like below:</p>
  <p><a href="https://i.stack.imgur.com/in1Ez.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/in1Ez.png" alt="enter image description here" /></a></p>
  <p>Though I want to run all my regular expressions because of the Google BigQuery resource limitation, but I ran the above query with only 50 regular expressions. Unfortunately, I got timeout error.</p>
  <p>Operation timed out after 6.0 hours. Consider reducing the amount of work performed by your operation so that it can complete within this limit.</p>
  <p><a href="https://i.stack.imgur.com/jmO1B.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jmO1B.png" alt="enter image description here" /></a></p>
  <p>I am not sure how can I optimize the query. Can anyone please help?</p>