BigQuery GitHub公共数据集查询结果与GitHub搜索不一致求助

Let's break down the possible reasons for the discrepancy between your BigQuery results and GitHub's search count, along with actionable suggestions:

1. Outdated BigQuery Dataset #

The bigquery-public-data:github_repos.files table you're using was last updated in May 2018. GitHub's search results reflect the current state of all public repositories—countless new repos using goreleaser have been created since 2018, and existing ones may have added goreleaser config files post-2018. This is likely the biggest factor behind the lower number of results in BigQuery.

2. Case Sensitivity in Regex Matching #

GitHub's search is case-insensitive by default, so it will match filenames like Goreleaser.yml or .Goreleaser.yaml alongside lowercase variants. However, BigQuery's REGEXP_MATCH function is case-sensitive unless explicitly specified. Your current regex r'\.?goreleaser.ya?ml' won't catch any uppercase variations, which could account for some missing results.

3. Dataset Scope Differences #

The BigQuery public GitHub dataset doesn't include every single public GitHub repository. It's curated (likely focusing on repos with higher star counts or activity levels) and excludes forks in many cases. Meanwhile, GitHub's default search includes forked repositories, which adds to the 1k+ result count.

Suggestions to Narrow the Gap #

• Update your regex for case insensitivity: Modify your query to use a case-insensitive flag. Here's the adjusted SQL:

  SELECT repo_name, path FROM `bigquery-public-data.github_repos.files` 
  WHERE REGEXP_MATCH(path, r'(?i)\.?goreleaser.ya?ml')
  

• Filter GitHub search to exclude forks: Try running filename:goreleaser extension:yaml extension:yml path:/ -fork:true in GitHub search. This will give you a count of non-forked repos, which should align more closely with BigQuery's dataset scope.
• Use a more up-to-date dataset: If you need current data, consider setting up your own GitHub data pipeline using GitHub's official API or exploring newer community-curated datasets. Alternatively, check if Google has updated the public dataset (though as of now, the 2018 version is still the latest public one).
• Verify path matching: Ensure your regex covers all possible path variations. For example, if some config files are nested in subdirectories like .github/goreleaser.yml, your current regex still works since it checks for the pattern anywhere in the path string—but double-check if there are edge cases you're missing.

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