Table of contents
- Prologue
- The feature request
- TL; DR
- Current content of the data dump
- Missing tables in the data dump
- Motivation for doing this
- Epilogue
Prologue
First of all, I would like to thank the SE team for creating the Stack Exchange Data Explorer and the data dump at the Internet Archive. Both of those are such useful resources for effectively searching information.
The feature request
TL; DR
As the title states, I've noticed that the data dump at the Internet Archive doesn't include all the tables that are available in SEDE. Because of this, I propose that all the tables available in SEDE be included in the data dump.
Current content of the data dump
I've downloaded the torrent file of the data dump (with hash ee64468a824040067d468d57f51e63ae9cf1aaa6) presented at the Internet Archive and I've decompressed some of the *.7z files, but I've noticed that only 8 XML files are provided for each site (see below).
7z l ~/Downloads/stack-exchange-data-dump/emacs.stackexchange.com.7z
7-Zip [64] 17.04 : Copyright (c) 1999-2021 Igor Pavlov : 2017-08-28
p7zip Version 17.04 (locale=en_US.UTF-8,Utf16=on,HugeFiles=on,64 bits,16 CPUs x64)
Scanning the drive for archives:
1 file, 43688623 bytes (42 MiB)
Listing archive: /home/myusername/Downloads/stack-exchange/emacs.stackexchange.com.7z
--
Path = /home/myusername/Downloads/stack-exchange/emacs.stackexchange.com.7z
Type = 7z
Physical Size = 43688623
Headers Size = 322
Method = BZip2
Solid = +
Blocks = 3
Date Time Attr Size Compressed Name
------------------- ----- ------------ ------------ ------------------------
2021-06-06 20:08:38 ....A 6234792 5551134 Badges.xml
2021-06-06 20:08:39 ....A 25008971 Comments.xml
2021-06-06 20:08:43 ....A 129528581 22082456 PostHistory.xml
2021-06-06 20:08:46 ....A 531208 16054711 PostLinks.xml
2021-06-06 20:08:46 ....A 72569952 Posts.xml
2021-06-06 20:08:46 ....A 56027 Tags.xml
2021-06-06 20:08:47 ....A 10755066 Users.xml
2021-06-06 20:08:47 ....A 16669869 Votes.xml
------------------- ----- ------------ ------------ ------------------------
2021-06-06 20:08:47 261354466 43688301 8 files
As we can see above, 8 XML files are provided: Badges.xml, Comments.xml, PostHistory.xml, PostLinks.xml, Posts.xml, Tags.xml, Users.xml and Votes.xml which correspond to the table names.
Missing tables in the data dump
As for the time of this writing, SEDE provides 29 tables for each site while the data dump only provides 8 tables for each site. The following table presents this information in a fancier way.
| Row | Table name at SEDE | Included in the data dump? |
|---|---|---|
| 1 | Badges |
Yes |
| 2 | CloseAsOffTopicReasonTypes |
No |
| 3 | CloseReasonTypes |
No |
| 4 | Comments |
Yes |
| 5 | FlagTypes |
No |
| 6 | PendingFlags |
No |
| 7 | PostFeedback |
No |
| 8 | PostHistory |
Yes |
| 9 | PostHistoryTypes |
No |
| 10 | PostLinks |
Yes |
| 11 | PostNoticeTypes |
No |
| 12 | PostNotices |
No |
| 13 | PostTags |
No |
| 14 | PostTypes |
No |
| 15 | Posts |
Yes |
| 16 | PostsWithDeleted |
No |
| 17 | ReviewRejectionReasons |
No |
| 18 | ReviewTaskResultTypes |
No |
| 19 | ReviewTaskResults |
No |
| 20 | ReviewTaskStates |
No |
| 21 | ReviewTaskTypes |
No |
| 22 | ReviewTasks |
No |
| 23 | SuggestedEditVotes |
No |
| 24 | SuggestedEdits |
No |
| 25 | TagSynonyms |
No |
| 26 | Tags |
Yes |
| 27 | Users |
Yes |
| 28 | VoteTypes |
No |
| 29 | Votes |
Yes |
Motivation for doing this
Some users, as me, are used to perform queries on SEDE, but sometimes they get limited by the resources given to them at SEDE. They would probably prefer use their machines' resources instead. Because of this, by accepting my request, SEDE would be freed by some users and thus, the payload at SEDE would be less which means that users that don't have the resources for downloading the data dump will have shorter latencies.
Some users, as me, use this data dump as a learning resource for learning SQL. Because of this, by accepting my request, learners would be able to write more complex queries and thus be amazed by the knowledge they have found in the data dump through their experimentation with SQL.
Some users use the data dump for data mining projects. For example, this article explains how the most mentioned books at Stack Overflow were obtained from the data dump. Because of this, by accepting my request, users would be able to extract more interesting results from the data dump.
Epilogue
Note that I'm not requesting that some data, not currently available, is made available at SEDE. What I'm requesting is that all the tables, made available in SEDE, be also included in the data dump in the Internet Archive.
where deletiondate is null. It would be a tremendous waste of space and bandwidth if these tables are both, Posts and PostsWithDeleted, included in the datadump as is. On the other hand, changing it would mean SEDE and the dump still have a different structure. I'm not sure if there is a solution that keeps backward compatibility and keeps the size down.Postsin the data dump (given that it is a view). To avoid this problem of data redundancy, I propose this solution that consist of two parts: (1) omit views in the data dump because views get their data from another table (possibly by doing some computation) and (2) let users know the structure of the public views at SEDE, so that they can define them in their RDBMS if they are interested.