Francesco Tisiot:将StackOverflow的StackExchange数据加载到PostgreSQL中

💡 原文英文,约2400词,阅读约需9分钟。
📝

内容提要

这篇文章介绍了如何将StackOverflow数据集上传到PostgreSQL数据库中的多个表中。首先创建数据库表,然后通过两步骤的方法加载XML文件到临时表中,最后将数据加载到正确的表和列中。文章还提供了加载其他表的步骤,并给出了一个查询示例。

🔎

延伸解读

两步加载法的必要性

文章采用先将XML文件整体加载到临时表data_load,再通过XPath解析插入目标表的两步法。这是因为原始数据集将所有列(包括ID)都视为字符串,直接加载会导致类型不匹配。两步法允许在解析时进行类型转换,确保数据正确存入优化后的列类型中。

数据加载中的关键处理

使用psql的\copy命令时,通过tr删除制表符、sed转义反斜杠,并跳过XML声明行。这些处理避免了特殊字符导致的加载错误。解析时利用PostgreSQL的xpath函数提取属性值,并用regexp_like过滤掉非数据行,如<users>标签。

多表加载的重复模式

加载其他表(如Posts、Badges等)时,需先清空data_load表,再重复相同的加载和解析过程。每个表的解析SQL结构相似,只需替换XPath表达式和目标表名。这种模式化操作便于扩展,但需注意每次加载前清空临时表。

查询示例与数据应用

数据加载完成后,文章提供了一个查询示例:找出评论得分最高的前两个帖子。这展示了如何关联Posts和Comments表进行数据分析。读者可基于此探索更多问题,如用户活跃度、标签趋势等,充分利用StackExchange数据集。

❓

Q&A

如何将StackOverflow的StackExchange数据加载到PostgreSQL中?

文章介绍了一种两步法:首先将XML文件加载到临时表data_load中,然后使用XPath解析XML并将数据插入到目标表中。需要先创建数据库表,然后对每个XML文件执行加载和解析。

加载StackExchange数据需要创建哪些PostgreSQL表?

需要创建data_load临时表以及Posts、Users、Votes、Comments、PostHistory、PostLinks、Badges、Tags等表。data_load表只有一个text列,用于逐行加载XML数据。

为什么加载StackExchange数据要采用两步法?

因为原始数据集将所有列(包括ID)都视为字符串,两步法可以先将XML加载到临时表,再通过XPath解析并转换为正确的列类型,避免将所有ID定义为字符串。

如何使用psql命令将Users.xml加载到data_load表中?

使用\copy命令:\copy data_load from program 'tr -d "\t" < Users.xml | sed -e ''s/\\/\\\\/g''' HEADER。其中tr删除制表符,sed转义反斜杠,HEADER跳过XML声明行。

如何用XPath从data_load表中解析并插入Users数据?

使用INSERT INTO users SELECT (xpath('//@Id', x))[1]::text::int, ... FROM data_load, unnest(xpath('//row', data::xml)) x WHERE regexp_like(data, '[ ]+\<row')。通过xpath提取属性,并转换为适当类型。

加载完数据后,如何查询评论得分最高的前两个帖子?

使用SQL:SELECT POSTS.Id, POSTS.Title, COMMENTS.Text, COMMENTS.SCORE FROM POSTS JOIN COMMENTS ON POSTS.ID = COMMENTS.POSTID ORDER BY SCORE DESC LIMIT 2;

🏷️

标签

➡️

继续阅读