使用 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 数据加载到 play.clickhouse.com（注意数据库为 git_clickhouse）中，以便我们的社区可以使用一些 示例查询 进行操作。

我们希望收集 Github 上排名前 10 万个存储库，这需要做更多工作。为此，我们利用了队列工作模型，其中每个作业都代表一个存储库。工作者从该队列中获取作业/存储库，克隆关联的存储库（如果磁盘上不存在），调用 git-import 并将数据索引到 ClickHouse 中。在我们简单的方案中，工作者共享一个公共磁盘。如果一个存储库以前被索引过，因此已被克隆，我们只需运行一个 git pull，只插入主分支上的差异。这种磁盘和克隆存储库的共享避免了不必要的重新克隆以及网络/磁盘 I/O。上面的过程在下图中可视化

如果我们有足够的计算资源和磁盘资源，只需添加更多工作者进程，我们就可以扩展此方法。

使用这种方法，一个存储库在任何时刻都应该只被一个工作者处理。两个工作者在同一个磁盘位置克隆同一个存储库或运行 git-import 可能会导致错误和不一致。此外，我们可能希望优先考虑一些作业/存储库，并确保这些作业首先由工作者处理。

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

• 禁止调度重复作业（以存储库名称为键）
• 确保作业只被一个工作者获取

有很多队列技术可以配置来解决这个问题 - 例如 Amazon SQS。但是，为了保持依赖关系的最小化，以及为了突破 ClickHouse 的界限，我们决定看看是否可以使用由 KeeperMap 引擎支持的表来模拟一个队列。此引擎在幕后使用 ClickHouse Keeper 实例，提供一致的键值存储，具有线性化写入和顺序一致读取。此引擎类型也已在云中启用，允许我们编写不依赖于 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 而无法对行执行 ALTER，则会尝试另一个作业。工作者会尝试声明 N 次，然后再放弃并休眠一段时间，以稍后重试。
• 作业完成后，会删除其行。
• ALTER 是通过与 repo_name 和 worker_id 的匹配来实现的，其中后者必须为空。如果没有任何行匹配，该语句将成功执行。如果作业已被声明，并且 worker_id 已设置，则可能发生这种情况。因此，在成功执行 ALTER 后，我们还会检查 worker_id 是否已设置为当前工作者的值，方法是使用一个 简单的 SELECT。这在上面的 “确认作业” 步骤中显示。
• 在工作者通过 git pull 更新仓库之前，它首先会检查已插入 ClickHouse 的最新提交。这是通过对 commits 表查询来实现的，以识别提交数据的最新时间。然后，将此时间用于过滤行，即，只有时间大于该值的时间才发送到 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 中 commits 的最大时间来过滤掉仓库更新期间已经插入的行的方法失败的情况下。这是最终的去重，应该足以满足我们的分析需求。因此，其他表具有提供所需唯一行标识的 ORDER BY 密钥。

最后，使用 updated_at，我们定期识别具有最旧时间需要更新的仓库。然后，这些仓库被安排到队列中进行处理。

在 25 个工作者在具有 16TB 磁盘空间的 48 核机器上运行了大约 24 小时后，我们克隆并插入了 50,000 个仓库的完整提交历史。

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 没有提供自动完成功能的输入组件。为了避免用 100,000 个仓库名称填充下拉列表并影响页面性能，我们需要用户输入一个搜索词 (项目或组织过滤器)。这将我们的下拉列表限制在适度的示例数量内。创建这些组件的过程如下所示

我们的每个可视化都需要一个 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 的发展，敬请关注，因为我们将从最初的概念验证中继续前进……