使用 ClickHouse 和 Hex 构建实时分析应用

作为世界上最快的分析型数据库，我们一直在寻找能够帮助用户快速轻松地实现基于 ClickHouse 构建应用程序梦想的工具。因此，当 Hex.tech 宣布 ClickHouse 作为数据源可用时，我们很高兴看到构建实时分析应用程序的潜力。

在之前的文章中，我们探索了 ClickHouse 原生工具 git-import。该工具与 ClickHouse 一起分发，能够生成任何 git 代码库的完整提交历史记录，包括文件和行的更改。虽然从历史上看，我们一直使用此工具来分析选择性的代码库，包括 ClickHouse 和 Grafana，但我们有更大的愿望。通过一些简单的调度代码、工作进程池和大量的磁盘空间，我们希望收集 GitHub 上排名前 5 万最受欢迎（星标）的代码库的完整提交历史记录，大约 10TB 的数据。使用此数据集，我们希望构建一个简单的应用程序，使用户能够实时获得对任何选定代码库的洞察。

在这篇博文中，我们将探讨我们如何收集数据并使用 Hex.tech 为我们的应用程序“ClickHub”构建一个简单的概念验证。作为此过程的一部分，我们利用了最近添加的 KeeperMap 表引擎，并对其进行了改进以简化我们的解决方案。

考虑到可能插入的数据量，我们为我们的应用程序使用了 ClickHouse Cloud 的生产实例，因为生产实例可以根据您的需要扩展资源。此演示中使用的代码已公开提供，并且可以轻松运行以收集较少数量的代码库，从而可以在较小的自管理或开发云实例上重现它。Hex 笔记本也是公开可访问的，使读者能够查看 ClickHouse 查询和可视化配置。

如果您确实想深入研究此数据集并重现该应用程序，ClickHouse Cloud 是一个很好的起点 - 启动一个集群并获得 300 美元的免费额度，加载数据，让我们处理基础设施，然后开始查询！如果您希望复制该应用程序，Hex 还提供 14 天的试用期。

Hex 是一个现代数据工作区。它使连接数据、在协作式 SQL 和 Python 驱动的笔记本中分析数据以及将工作共享为交互式数据应用程序变得容易。在提供数据科学家中流行的经典 Python 笔记本功能的同时，用户还可以连接到 ClickHouse 等外部数据源，并将笔记本发布为轻量级应用程序。

通过公开用户输入结果（例如，下拉菜单）或潜在的 Python 代码和其他 SQL 结果中的变量，用户可以构建动态查询并将其与 ClickHouse 一起使用。ClickHouse 结果集以数据帧的形式返回，可以轻松地在可视化中呈现，而无需编写任何代码。

我们越来越多地看到用户利用 Hex 的笔记本功能对 ClickHouse 中的数据进行临时分析。用户可以通过将计算密集型查询（例如聚合）下推到 ClickHouse 来快速分析 TB 甚至 PB 级的数据。由于 ClickHouse 执行繁重的工作并将紧凑的结果集以数据帧的形式在毫秒内返回，因此 Hex 用户能够专注于基于 Python 的分析和数据科学任务。

希望构建实时应用程序的 ClickHouse 用户可能会特别有兴趣了解 Hex 还支持将笔记本中的元素排列成交互式应用程序，并将其发布以供外部使用。此功能正是我们的“Github 分析”应用程序所需要的。

根据我们的测试，我们建议主要将 Hex 用于概念验证工作、笔记本和面向内部的分析应用程序。随着 Hex 不断增强功能，我们预计它将发展成为一个成熟的交互式应用程序构建器。

安装了 ClickHouse 的用户将立即可以使用 git-import 工具。当在任何克隆的代码库的根目录中运行时，此命令行实用程序会生成三个文件：commits.tsv、file_changes.tsv 和 line_changes.tsv。这些文件的内容已通过该工具的内置帮助进行了方便的解释

ClickHouse % clickhouse-git-import -h

A tool to extract information from Git repository for analytics.

It dumps the data for the following tables:
- commits - commits with statistics;
- file_changes - files changed in every commit with the info about the change and statistics;
- line_changes - every changed line in every changed file in every commit with full info about the line and the information about previous change of this line.

The largest and the most important table is "line_changes".

Run this tool inside your git repository. It will create .tsv files that can be loaded into ClickHouse (or into other DBMS if you dare).

生成的 TSV 文件提供了代码库的详细历史记录，并且可以使用客户端直接索引到 ClickHouse 中，前提是已创建了表（注意 clickhouse-git-import 通过帮助提供了所需的模式）

clickhouse-client --query 'INSERT INTO git.commits FORMAT TSV' < commits.tsv

您可以为 Github 上的任何代码库生成此数据，并使用 此处的问题来分析您自己的项目以获得灵感。如果您没有时间自己生成数据，我们已将我们自己的 ClickHouse 数据加载到 sql.clickhouse.com 中（注意数据库是 git_clickhouse），以便我们的社区可以使用一些 示例查询。

我们收集 Github 上排名前 10 万的代码库的愿望需要更多的工作。为此，我们使用队列-工作进程模型，其中每个作业代表一个代码库。工作进程从此队列中声明作业/代码库，克隆关联的代码库（如果磁盘上不存在），调用 git-import，并将数据索引到 ClickHouse 中。在我们的简单方法中，工作进程共享一个公共磁盘。如果代码库先前已被索引，因此已被克隆，我们只需运行 git pull，仅插入主分支上的差异。这种磁盘和克隆的代码库的共享避免了不必要的重新克隆和网络/磁盘 IO。上述过程可视化如下

如果我们有足够的计算和磁盘资源，我们可以通过简单地添加更多工作进程来扩展此方法。

使用此方法，代码库在任何时候都应仅由一个工作进程处理。两个工作进程在同一磁盘位置克隆同一代码库或运行 git-import 可能会导致错误和不一致。此外，我们可能希望优先处理某些作业/代码库，并确保工作进程首先处理这些作业/代码库。

为了实现上述目标，我们希望我们的队列能够

• 禁止计划重复作业（以代码库名称为键）
• 确保作业仅由一个工作进程声明

有许多排队技术可以配置为解决此问题，例如 Amazon SQS。但是，为了保持最小的依赖性，并突破 ClickHouse 的边界，我们决定看看是否可以使用由 KeeperMap 引擎驱动的表来建模队列。此引擎在底层利用 ClickHouse Keeper 实例，提供具有线性化写入和顺序一致性读取的一致键值存储。此引擎类型也在 Cloud 中启用，使我们能够编写我们的应用程序，而无需 ClickHouse 之外的任何依赖项。

虽然 Keeper Map 为我们提供了线性化写入和顺序一致性读取，但没有实现原子更新的方法，这是工作进程独占声明作业所必需的。例如，考虑我们的队列的表架构。

CREATE TABLE git.work_queue
(
    `repo_name` String,
    `scheduled` DateTime,
    `priority` Int32,
    `worker_id` String,
    `started_time` DateTime
)
ENGINE = KeeperMap('git_queue')
PRIMARY KEY repo_name

请注意，repo_name 是唯一的主键。假设作业排队时 worker_id 为空，则工作进程需要设置行上的 worker_id 列以声明作业及其关联的代码库。这可以使用 ALTER TABLE git.work_queue UPDATE worker_id=<worker_id>, started_time=<started_time> WHERE worker_id=’’ 命令来实现。在最初开发时，KeeperMap 不能保证对行的更新是原子性的，这可能会允许两个工作进程声明该作业。此外，我们需要确保任何代码库都不能在队列中插入多次，从而确保任何单个代码库仅存在一行。一旦作业完成并且 ClickHouse 中更新了代码库的数据，就可以删除该行。

在与我们的开发团队交谈后（感谢 Antonio Andelic）以及几个 PR 之后，ClickHouse 现在具有 keeper_map_strict_mode。当设置为 true 时，此

• 防止由主键（repo_name）标识的行被插入多次。尝试这样做会导致异常。
• 仅当通过原子方式执行时，通过 ALTER 获取和更新行才会成功，即版本在操作之间没有更改。
• 删除行需要获取和删除。同样，只有在可以原子地完成的情况下，这才会成功。

有了这些属性，我们就可以实现我们的队列。完整的调度和工作进程代码可以在此处找到。我们将完整的过程可视化如下

一些重要的注意事项

• 如果工作进程未能对行发出 ALTER，因为另一个工作进程已首先成功，则会尝试另一个作业。工作进程尝试声明 N 次，然后退避并休眠以稍后重试。
• 一旦作业完成，其行将被删除。
• ALTER 使用 repo_name 和 worker_id 进行匹配来调节，后者必须为空。如果没有任何行匹配，则此语句将成功。如果作业已被声明并且 worker_id 已设置，则可能会发生这种情况。因此，在成功完成 ALTER 后，我们还要检查 worker_id 是否设置为当前工作进程的值，并使用 简单的 SELECT。这在上面显示为“确认作业”步骤。
• 在工作进程通过 git pull 更新代码库之前，它首先检查已插入到 ClickHouse 中的最后一次提交。这是通过查询 commits 表来识别提交数据的最新时间来实现的。然后，此时间用于筛选行，即只有时间大于此值的 commits、file_changes 和 line_changes 会发送到 ClickHouse。

对于插入由 git-import 生成的数据，我们只需使用 clickhouse-client 和 clickhouse-local 工具 - 见下文。

表 commits、file_changes 和 line_changes 都具有非常独特的架构。我们没有决定每个代码库使用一个数据库（或带有代码库识别后缀/前缀的表），而是决定将所有代码库的所有数据存储在相同的三个表中。这意味着我们将来可能会扩展到数百万个代码库。但是，commits、line_changes 和 file_changes 表的默认架构没有用于标识代码库名称的列。因此，我们在插入时将其添加到行中。鉴于代码库名称将是我们应用程序中的主要过滤器，我们还将此名称作为我们所有表的主键中的第一个条目，从而遵守 ClickHouse 最佳实践并与我们的访问模式保持一致。最后，我们添加了一个 updated_at 物化列，该列始终在插入行时设置为 now() 的值。这使我们能够轻松识别代码库上次更新的时间（我们在保持数据最新时使用它）。下面我们展示了 commits 表的架构。其他表的完整架构可以在此处找到。

CREATE TABLE git.commits
(
    `hash` String,
    `author` LowCardinality(String),
    `time` DateTime,
    `message` String,
    `files_added` UInt32,
    `files_deleted` UInt32,
    `files_renamed` UInt32,
    `files_modified` UInt32,
    `lines_added` UInt32,
    `lines_deleted` UInt32,
    `hunks_added` UInt32,
    `hunks_removed` UInt32,
    `hunks_changed` UInt32,
    `repo_name` LowCardinality(String),
    `updated_at` DateTime MATERIALIZED now()
)
ENGINE = ReplacingMergeTree
ORDER BY (repo_name, time, hash)

请注意，我们使用了 ReplacingMergeTree。此表引擎在后台合并期间异步删除重复行（具有相同主键）。这确保最终删除任何重复项，例如，在我们早期使用 ClickHouse 中提交的最大时间来过滤掉代码库更新期间已插入的行的方法失败的情况下。这是最终的重复数据删除，应该足以满足我们的分析需求。因此，其他表具有 ORDER BY 键，这些键提供了所需的唯一行标识。

最后，使用 updated_at，我们定期识别具有最旧时间的代码库，因此需要更新。然后将这些代码库计划到队列中以进行处理。

在 48 核机器上使用 25 个工作进程运行约 24 小时，并使用 16TB 的磁盘空间后，我们克隆并插入了 5 万个代码库的完整提交历史记录。

SELECT uniqExact(repo_name)
FROM git.commits

┌─uniqExact(repo_name)─┐
│               50035  │
└──────────────────────┘

这消耗了大约 8TB 的磁盘空间（克隆的代码库）。

/dev/nvme1n1    16T  8.2T  7.9T  51% /data

在 ClickHouse 中，我们可以看到生成的数据压缩良好

SELECT
    formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE database = 'git'

┌─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ 890.80 GiB      │ 12.39 TiB         │ 14.24 │
└─────────────────┴───────────────────┴───────┘

1 row in set. Elapsed: 0.004 sec.

如图所示，我们最大的表 line_changes 超过 400 亿行。

SELECT formatReadableQuantity(count())
FROM git.line_changes

┌─formatReadableQuantity(count())─┐
│ 44.69 billion                   │
└─────────────────────────────────┘

1 row in set. Elapsed: 0.003 sec.

为了补充上述数据集，我们还插入了 Github 事件数据集。其中包含自 2011 年以来 GitHub 上的所有事件，在撰写本文时约有 58 亿条记录。这包括所有问题、评论、PR、审查、关注（星标）、fork 和发布事件，补充了我们较低级别的提交历史记录。有关此数据集的完整描述以及建议的查询，可以在此处找到。有关如何持续更新此数据集的简单脚本也可以在此处找到。

SELECT count()
FROM github_events

┌────count()─┐
│ 5847150938 │
└────────────┘

1 row in set. Elapsed: 0.002 sec.

SELECT
    formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE table = 'github_events'

┌─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ 258.22 GiB      │ 2.63 TiB          │ 10.43 │
└─────────────────┴───────────────────┴───────┘

在准备好数据集并保持最新状态后，我们着手构建我们的概念验证应用程序。对于那些对完整的 SQL 查询和笔记本配置感兴趣的读者，我们已将其在此处提供。我们在下面逐步介绍应用程序的创建过程，重点介绍选定的可视化和查询。请注意，我们无需编写任何代码。虽然将来可以使用 Python 来提供更高程度的交互性 - 请参阅“未来工作”，但我们特意避免了这种情况，以使应用程序现在保持简单。

Hex 有两个主要视图

• 逻辑视图 - 基于笔记本的界面，您可以在其中进行分析开发。用户可以在我们的共享笔记本中检查 SQL 和可视化配置。
• 应用构建器 - 一个集成的界面构建器，您可以在其中将逻辑视图中的元素排列成交互式应用程序。

我们的大部分开发工作都发生在逻辑视图中，这是我们首次创建项目时呈现的视图。

用户可以在此页面上创建许多组件，包括 Python、SQL、可视化、文本和输入，以及更复杂的概念，例如 转换 和 回写，这些概念超出了我们应用程序的需求。所有这些都可以将来自其他组件的输出作为变量使用，并且还可以自行生成输出。但是，在添加任何组件之前，我们需要连接到 ClickHouse。我们通过创建一个简单的数据连接来完成此操作，如下所示

对于最初的尝试，我们决定保持交互性相当低。在加载应用程序时，用户需要选择一个代码库以从中获取洞察。这将设置一个 repo_name 变量，该变量用于过滤所有后续查询。为此，我们需要一个简单的输入 - 一个由 SQL 查询驱动的代码库名称下拉菜单，该查询标识可能的值。

在撰写本文时，Hex 不提供自动完成输入组件。与其使用所有 10 万个代码库名称填充下拉菜单并影响页面性能，我们要求用户输入搜索词（项目或组织过滤器）。这会将我们的下拉菜单限制为适度数量的示例。创建这些组件的过程如下所示

我们的每个可视化都需要一个 SQL 组件。这将返回一个数据帧，然后可以由可视化组件使用 - 这可以是图表、表格、单个值，甚至是地图。我们将我们的分析分为“提交”、“贡献者”、“问题”、“代码分析”子部分。我们在下面对每个部分的可视化进行了抽样。对于我们选择的代码库，我们使用了 ClickHouse。用户可以自由地与笔记本交互，并通过更改输入值来分析他们自己的代码库。对于任何 SQL 查询，变量 `{{repo_name}} "用于确保代码库仅限于用户的选择。

下面，我们创建了两个可视化，一个显示所选代码库（在本例中为 ClickHouse）的收藏数，第二个显示一段时间内的提交。这些分别使用 github_events 和 commits 表。

使用 github_events 数据计算出的 ClickHouse 的收藏数略低，这是由于代码库重命名。

显示顶级贡献者及其随时间推移的活动需要使用 commits 表。我们还可以使用同一表来识别哪些贡献者具有最长的连续提交天数。

对代码库问题的分析需要 github_events 数据集。我们在下面展示了两个可视化，一个捕获问题关闭所需的平均时间，另一个显示负责报告最多问题的社区成员。

任何形式的代码分析都需要我们最大的 line_changes 数据集。幸运的是，尽管查询超过 500 亿行，但对此表的查询仍然很快，因为我们使用了代码库名称作为排序键中的第一列。我们使用代码行数作为代码库大小的简单度量，计算每日添加和删除的代码行数的累积总和和值。这里的 ClickHouse 值很高，这是计算子模块中代码的结果。更集中的分析可能会排除特定文件夹，这是以后的迭代需要考虑的事情。

为了引发项目开发人员之间的讨论，我们决定看看我们是否可以识别谁删除了顶级贡献者的代码。我们将此限制为前 20 名提交者。

作为最后的洞察，我们希望识别“关联代码库”，即所选代码库的贡献者还贡献了哪些其他代码库。

对于 ClickHouse，用户显然也为更广泛的 ClickHouse 生态系统以及分析领域的其他项目做出了贡献。

我们在此仅提供了我们的可视化示例，并鼓励用户探索和克隆完整的笔记本，其中可视化已完全样式化和分组。

当前应用程序的交互性有限，用户只能按代码库名称进行筛选。Hex 提供了使用传递到新数据帧的结果筛选可视化的功能。这些筛选的数据帧反过来可以用于呈现新的可视化，或者传递给 Python 代码，Python 代码反过来将生成新的变量，可以在这些变量上筛选 SQL。这允许可视化被动态链接并相互依赖，并且可能具有很高的复杂性。虽然我们尝试了这些功能以提供更高的交互性，但如果处理不当，它会显着增加笔记本的复杂性并影响渲染时间。使用 Python 代码来操作数据帧并执行数据科学和建模的能力为丰富应用程序提供了更多机会，例如，我们可以根据回归预测代码库的收藏数。该应用程序的未来迭代也可能提供每个可视化的筛选，例如，可以按文件夹名称筛选代码行。

虽然所有这些都是可能的，但对于我们的初始概念验证，这些不是必要的投入。但是，我们可能会在未来的博客或网络研讨会中探讨它……敬请期待。用户应该意识到，任何应用程序都是单页的，并且执行非常线性，非常像笔记本。这对于需要“讲述故事”的应用程序很有用，但不适用于需要更动态的多页体验的更多用户。

如果 Hex 计划提供更多可视化调色板，我们将感到非常兴奋 - 热图、漏斗图和极坐标图都将是受欢迎的补充。

关于数据，我们需要进行进一步的工作。例如，github_events 和 commits 数据集使用了不同的作者 - 前者使用 github 句柄，后者使用完整的贡献者姓名。协调重命名的代码库的数据（如 ClickHouse 的情况）也是当务之急。

总的来说，对于那些需要构建快速体验而无需（可选）代码的人来说，Hex 体验是一个非常积极且出色的工具。我们鼓励我们的用户探索他们自己的数据集，并让我们知道您构建了什么！

最后，关于 ClickHub 的发展，请继续关注，因为我们将从最初的概念验证继续前进……