跳到主要内容
跳到主要内容

使用 ClickHouse 分析 Stack Overflow 数据

这个数据集包含了 Stack Overflow 上发生的所有 Posts、Users、Votes、Comments、Badges、PostHistory 和 PostLinks。

用户可以下载包含截至 2024 年 4 月所有帖子的预备 Parquet 版本数据，或下载最新的数据 XML 格式并加载。Stack Overflow 周期性地更新这些数据 - 历史上每 3 个月更新一次。

以下图表显示了在 Parquet 格式下可用表的模式。

这些数据的模式描述可以在 这里 找到。

预备数据​

我们提供了截至 2024 年 4 月的 Parquet 格式的数据副本。就行数而言（6000 万帖子），对于 ClickHouse 而言，这个数据集的大小较小，但它包含了大量文本和大字符串列。

以下时间是针对位于 eu-west-2 的 96 GiB、24 vCPU ClickHouse Cloud 集群的。数据集位于 eu-west-3。

帖子​

帖子也可以按年份获取，例如 https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2020.parquet。

投票​

投票也可以按年份获取，例如 https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/votes/2020.parquet。

评论​

评论也可以按年份获取，例如 https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/comments/2020.parquet。

用户​

徽章​

帖子历史​

原始数据集​

原始数据集以压缩（7zip）XML 格式提供，下载链接为 https://archive.org/download/stackexchange - 文件前缀为 stackoverflow.com*。

下载​

这些文件大小可达 35GB，下载时间大约为 30 分钟，具体取决于互联网连接 - 下载服务器的速度限制约为 20MB/sec。

转换为 JSON​

在撰写本文时，ClickHouse 不支持 XML 作为输入格式。要将数据加载到 ClickHouse 中，我们首先将其转换为 NDJSON。

要将 XML 转换为 JSON，推荐使用 xq linux 工具，这是一个用于 XML 文档的简单 jq 包装器。

安装 xq 和 jq：

以下步骤适用于上述文件中的任何一个。我们以 stackoverflow.com-Posts.7z 文件为例。按需修改。

使用 p7zip 解压文件。这将生成一个单一的 xml 文件 - 在这个例子中是 Posts.xml。

文件的压缩比例约为4.5倍。压缩为22GB的帖子文件需要约97GB的解压缩空间。

以下命令将 xml 文件拆分为每个包含 10000 行的文件。

运行上述命令后，用户将获得一组文件，每个文件包含 10000 行。这可以确保下一个命令的内存开销不会过大（xml 到 JSON 的转换在内存中完成）。

上述命令将生成一个名为 posts.json 的文件。

使用以下命令加载到 ClickHouse 中。请注意，为 posts.json 文件指定了模式。根据数据类型，需要进行调整以与目标表对齐。

示例查询​

一些简单的问题供您开始。

拥有最多回答的用户（活跃账号）​

账户需要一个 UserId。

最具争议的帖子​

归属​

我们感谢 Stack Overflow 提供此数据，遵循 cc-by-sa 4.0 许可证，感谢他们的努力以及数据的原始来源 https://archive.org/details/stackexchange。