如何通过BigQuery查询指定GitHub仓库的带时间戳贡献记录

原查询存在的问题 #

你写的查询有三个核心问题，导致无法返回正确结果：

• 字段名错误：GH Archive数据集没有repository_name字段，仓库名存储在repo.name字段，且完整值为「仓库所有者/仓库名」格式，你只写repoxplorer既匹配不到正确字段，也漏写所有者前缀，会匹配到其他同名仓库的脏数据
• 范围过窄：你只查了2019年1月1日单日的IssuesEvent事件，既没有覆盖仓库从创建到现在的全时间周期，也漏了代码推送、PR提交、评论、发版等其他贡献类型
• 字段缺失：没有查询时间戳字段，也没有提取贡献相关的详情字段，达不到带时间戳、匹配贡献者的需求

可直接使用的正确查询语句 #

下面的语句会覆盖仓库全生命周期的公开贡献记录，返回每条贡献的时间戳、贡献者信息、贡献类型、关联内容ID：

SELECT
  created_at AS event_timestamp,
  actor.login AS contributor_username,
  actor.display_login AS contributor_display_name,
  type AS contribution_type,
  JSON_EXTRACT_SCALAR(payload, '$.action') AS contribution_action,
  CASE
    WHEN type = 'PushEvent' THEN JSON_EXTRACT_SCALAR(payload, '$.head')
    WHEN type LIKE 'PullRequest%' OR type LIKE 'Issue%' THEN JSON_EXTRACT_SCALAR(payload, '$.number')
    ELSE NULL
  END AS related_target_id,
  JSON_EXTRACT(payload, '$.commits') AS push_commit_details
FROM `githubarchive.month.*`
WHERE
  repo.name = 'morucci/repoxplorer'
  AND type IN (
    'PushEvent',
    'PullRequestEvent',
    'PullRequestReviewEvent',
    'PullRequestReviewCommentEvent',
    'IssuesEvent',
    'IssueCommentEvent',
    'CommitCommentEvent',
    'ReleaseEvent'
  )
ORDER BY created_at DESC

使用提示 #

• 如果不需要全时间段的数据，可以加_TABLE_SUFFIX条件过滤分区，比如要查2018年之后的数据，就在WHERE里加AND _TABLE_SUFFIX >= '201801'，能大幅降低查询扫描的数据量，减少配额消耗
• 如果需要提取更细节的内容，比如PR标题、Issue内容、提交信息，可以直接在payload对应的JSON路径里加字段提取即可
• 该数据集仅收录GitHub公开的事件数据，不包含私有仓库行为、用户隐藏的活动记录

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