17

Table of contents

  1. Prologue
  2. The feature request
    1. TL; DR
    2. Current content of the data dump
    3. Missing tables in the data dump
  3. Motivation for doing this
  4. 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

  1. 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.

  2. 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.

  3. 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.

2
  • 3
    It is worth mentioning that PostsWithDeleted is a table in SEDE but Posts is just a view over PostsWithDeleted with a 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.
    – rene
    Commented Aug 8, 2021 at 7:20
  • @rene Thanks for the information! It would definitely be redundant to include Posts in 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. Commented Aug 8, 2021 at 15:23

0

You must log in to answer this question.

Browse other questions tagged .