x-lab2017/open-digger/open-digger-clickhouse-schema

OpenDigger ClickHouse 数据库表结构

OpenDigger 使用 ClickHouse 作为核心数据存储，数据库名固定为 `opensource`。所有开源指标（OpenRank、活跃度、协作网络等）都从底层事件数据计算而来。

平台维度统一用 platform 字段标识，支持多平台：GitHub、Gitee、GitLab、AtomGit（字段值为对应平台名，如 'GitHub'、'Gitee'、'AtomGit'）。

编写查询时务必带上 platform 条件，绝大多数表的排序键都以 platform 或包含 platform 来过滤。

1. 核心表总览

2. events 表（最核心）

• 用途: 存储所有平台的事件日志，是所有指标计算的基础数据源。
• 引擎: ReplacingMergeTree(from_api) —— 以 from_api 作为版本列去重。
• 分区: toYYYYMM(created_at)（按月分区）。
• 主键/排序键:

(platform, org_id, repo_id, actor_id, type, action, toYear(created_at), toYYYYMM(created_at))

• type 枚举值（12 种）:

CommitCommentEvent、ForkEvent、ReleaseEvent、IssueCommentEvent、IssuesEvent、PullRequestEvent、PullRequestReviewCommentEvent、PushEvent、WatchEvent、PullRequestReviewEvent、IssuesReactionEvent、IssueCommentsReactionEvent

• action 常见值: opened、closed、created、started、added、published 等（不同 type 对应不同 action）。

主要字段分组

events 表有 80+ 字段，按事件类型组织。完整字段列表见 [events-table-reference.md](events-table-reference.md)。分组概览：

• 基础字段：platform、type、action、actor_id/login、repo_id/name、org_id/login、created_at
• Issue/PR 通用字段：issue_id、issue_number、issue_title、body、issue_labels、issue_author_*、issue_assignee*、时间戳等
• Issue Comment 字段：issue_comment_*
• PR 特有字段：pull_commits、pull_additions、pull_deletions、pull_merged、pull_merged_*、pull_base_ref、pull_head_* 等
• PR Review 字段：pull_review_state、pull_review_id、pull_review_author_association
• PR Review Comment 字段：pull_review_comment_*
• Push 字段：push_id、push_size、push_distinct_size、push_ref、push_head、push_commits
• Fork 字段：fork_forkee_*
• Release 字段：release_*
• Commit Comment 字段：commit_comment_*
• 元数据：from_api（0=日志采集，1=API采集）

关键提示：events 表很大，查询务必加 platform + 时间范围（created_at 或 toYYYYMM(created_at)）+ type 条件以命中分区与排序键。同一记录可能因采集方式不同而存在多版本，统计精确值时按需用 argMax(..., created_at) 或子查询去重。对于仓库、组织、开发者的查询，除必要情况外尽量使用平台 ID 为条件查询。

3. repo_info 表

• 用途: 仓库元信息（主要来自 GitHub API）。
• 引擎: ReplacingMergeTree
• 排序键: (platform, id, updated_at)

4. user_info 视图（用户查询首选）

• 用途: 标准化用户信息视图，国家/省市已通过 labels（Division 标签）标准化，可直接用于按地域聚合查询。
• 类型: 物化视图（REFRESH EVERY 3 HOUR）
• 引擎: MergeTree()
• 排序键: (id, platform)
• 数据来源: gh_user_info（GitHub 原始用户表）+ global_openrank（取最新 login）+ location_info（地理编码）+ labels（Division-0/1 国家/省份标签匹配）

注意：gh_user_info 是 GitHub 平台原始用户表（含未标准化的 location 文本），一般情况下不要直接查询 gh_user_info，而应查询 user_info 视图获取标准化后的地域信息。

5. global_openrank 表

• 用途: 全局 OpenRank 排名，基于所有仓库的贡献行为计算。
• 引擎: MergeTree
• 排序键: (repo_id, actor_id, org_id, platform, created_at)

6. community_openrank 表

• 用途: 社区级贡献排名，通过 Neo4j 图算法计算。
• 引擎: MergeTree
• 排序键: (repo_id, created_at)

7. normalized_community_openrank 表（重点）

• 用途: 归一化社区贡献度，提供全域横向可比的归一化贡献度数据。
• 来源脚本: src/scripts/importNormalizedCommunityOpenrank.ts
• 计算依据: 基于 events + global_openrank + community_openrank 计算；当某仓库未计算社区 OpenRank 时，使用 activity（活跃度）作为兜底。
• 引擎: MergeTree()
• 排序键: (repo_id, platform)

变体表 `normalized_community_openrank_with_bot`：字段与上表完全一致，并在 openrank 之后额外包含一列 is_bot UInt8（是否为 Bot 账号）。排除 Bot 时查无 bot 表，需要含 Bot 数据时查 with_bot 表。

8. export_repo / export_user 表

标记需要导出到静态文件的对象（仓库 openrank > 5 或带标签的仓库）。

export_repo — 引擎 ReplacingMergeTree，排序键 (id, platform)：

export_user — 引擎 ReplacingMergeTree，排序键 (id, platform)：

9. labels 表

• 用途: 分层标签体系，将仓库/组织/用户归类到 Project / Company / University / Foundation / Agency / Tech / Division 等类别。
• 引擎: ReplacingMergeTree
• 排序键: id

labels.data 字段重要内容

对于国家级标签（type = 'Division-0'），data JSON 中包含重要的 developers 数组，记录了该国家在 GitHub InnovationGraph 中从 2020 年 Q1 开始每季度的 GitHub 平台注册开发者总量。这是做宏观分析时非常有用的官方数据。

JSON 结构示例（labels.data 字段内容）：

{
  "developers": [
    {"year": 2020, "quarter": 1, "count": "6709883"},
    {"year": 2020, "quarter": 2, "count": "7200238"},
    ...
    {"year": 2025, "quarter": 4, "count": "16099348"}
  ],
  "includes": ["China", "CN", "156", "中国"],
  ...
}

• developers[-1].count：该国家最新一个季度的 GitHub 注册开发者总量
• includes：国家名称的多种写法（用于 user_info.country 匹配）

典型用途：计算 OpenDigger 已追踪开发者占各国 GitHub 总开发者的比例（“覆盖倍率”）。

10. location_info 表

• 用途: 用户位置文本的地理编码结果（通过 Google Maps API）。
• 引擎: ReplacingMergeTree
• 排序键: (location)

11. issue_info 表（AI 分析）

• 用途: AI（Qwen LLM）生成的 Issue 质量分析。
• 引擎: ReplacingMergeTree，排序键 (id, platform)

12. pull_info 表（AI 分析）

• 用途: AI（Qwen LLM）生成的 PR 质量分析。
• 引擎: ReplacingMergeTree，排序键 (id, platform)

13. pull_diff 表

• 用途: 缓存 PR 的 diff 内容供分析使用。
• 引擎: ReplacingMergeTree，排序键 (id)

14. 物化视图（Materialized Views）

来源脚本 src/scripts/createViews.ts，均使用 ClickHouse REFRESH 周期刷新。

flatten_labels 关键列：id(标签ID)、type、name、name_zh、platform、entity_id(UInt64)、entity_type(Enum8 Repo=1/Org=2/User=3)。它是把 labels 的 platforms 嵌套结构展开成「标签 ↔ 实体」一对一行的桥接视图，标签相关查询基本都走它。

15. 表关联关系

• events.repo_id → repo_info.id / export_repo.id / global_openrank.repo_id
• events.actor_id → user_info.id / export_user.id / global_openrank.actor_id
• events.org_id → 标签（经 flatten_labels 关联到 labels）
• global_openrank + community_openrank → normalized_community_openrank
• gh_user_info → user_info（视图聚合了 gh_user_info + location_info + labels + global_openrank）
• labels → flatten_labels → pulls_with_label / issues_with_label
• events.issue_id → pull_info.id / issue_info.id / pull_diff.id（AI 分析与 diff 缓存）