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

使用 ClickHouse 分析 Stack Overflow 数据

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

用户可以下载预先准备好的 Parquet 格式数据，该数据包含截至 2024 年 4 月的所有帖子，或者下载最新的 XML 格式数据并加载。Stack Overflow 定期提供该数据的更新 - 历史上每 3 个月更新一次。

下图显示了假设为 Parquet 格式的可用表的模式。

Stack Overflow schema

有关该数据模式的描述，请参见 这里。

预先准备好的数据​

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

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

Posts​

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

Votes​

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

Comments​

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

Users​

Badges​

PostHistory​

原始数据集​

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

下载​

这些文件的大小可达 35GB，下载时间可能会根据网络连接时间长达 30 分钟 - 下载服务器的下载速率限制约为 20MB/秒。

转换为 JSON​

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

我们推荐使用 xq Linux 工具，它是 XML 文档的简单 jq 包装器。

安装 xq 和 jq：

以下步骤适用于上述任何文件。我们使用 stackoverflow.com-Posts.7z 文件作为示例，根据需要进行修改。

使用 p7zip 解压缩文件。这将生成一个 XML 文件 - 在这种情况下为 Posts.xml。

文件的压缩率约为 4.5x。压缩后为 22GB 的帖子文件解压后需要大约 97G。

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

运行上述命令后，用户将拥有一组每个包含 10000 行的文件。这确保了下一条命令的内存开销不是过高（XML 到 JSON 的转换是在内存中完成的）。

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

使用以下命令加载到 ClickHouse 中。注意，针对 posts.json 文件的方案需要指定。此处需根据数据类型调整以与目标表对齐。

示例查询​

几个简单的问题让您开始。

答案最多的用户（活跃账户）​

账户需要 UserId。

最有争议的帖子​

归属​

我们感谢 Stack Overflow 在 cc-by-sa 4.0 许可下提供这些数据，承认其努力并原始数据源为 https://archive.org/details/stackexchange。