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

使用 ClickHouse 分析 Stack Overflow 数据

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

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

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

Stack Overflow schema

关于此数据模式的描述可以在 这里 找到。

预准备数据​

我们提供了这份数据的 Parquet 格式副本，更新截止到 2024 年 4 月。虽然对于 ClickHouse 而言行数不多（6000 万条帖子），但该数据集包含大量文本和大 String 列。

以下时间记录适用于位于 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/votes/2020.parquet

Comments​

评论也按年份提供，例如 https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/comments/2020.parquet

Users​

Badges​

PostHistory​

原始数据集​

原始数据集以压缩（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.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。