GetIssues.sql

SELECT
  url as issue_url
  -- replace more than one white-space character in a row with a single space
, REGEXP_REPLACE(title, r"\s{2,}", ' ') as issue_title
, REGEXP_REPLACE(body, r"\s{2,}", ' ') as body

FROM(
    SELECT
        JSON_EXTRACT(payload, '$.issue.html_url') as url
        -- extract the title and body removing parentheses, brackets, and quotes
      , LOWER(TRIM(REGEXP_REPLACE(JSON_EXTRACT(payload, '$.issue.title'), r"\\n|\(|\)|\[|\]|#|\*|`", ' '))) as title
      , LOWER(TRIM(REGEXP_REPLACE(JSON_EXTRACT(payload, '$.issue.body'), r"\\n|\(|\)|\[|\]|#|\*|`", ' '))) as body
    FROM `githubarchive.day.2017*`
    WHERE 
      -- 70 random days in 2017 (because it costs money to query these tables!!)  
          _TABLE_SUFFIX BETWEEN '0101' and '1231'
      and type="IssuesEvent" 
      -- Only want the issue at a specific point otherwise will have duplicates
      and JSON_EXTRACT(payload, '$.action') = "\"opened\"" 
) as tbl

WHERE 
  -- the body must be at least 8 words long and the title at least 3 words long
  --  this is an arbitrary way to filter out empty or sparse issues
      ARRAY_LENGTH(SPLIT(body, ' ')) >= 6
  and ARRAY_LENGTH(SPLIT(title, ' ')) >= 3
  -- filter out issues that have really long titles or bodies
  --    (these are outliers, and will slow tokenization down).
  and LENGTH(title) <= 400
  and LENGTH(body) <= 2000