4

Prologue

We all know that we can use the Stack Exchange Data Explorer to perform queries in (most if not all) of the Stack Exchange tables. The tables listed, as for [2021-08-05 Thu 18:21], are 29. They are listed below (in alphabetical order)

  1. Badges
  2. CloseAsOffTopicReasonTypes
  3. CloseReasonTypes
  4. Comments
  5. FlagTypes
  6. PendingFlags
  7. PostFeedback
  8. PostHistory
  9. PostHistoryTypes
  10. PostLinks
  11. PostNoticeTypes
  12. PostNotices
  13. PostTags
  14. PostTypes
  15. Posts
  16. PostsWithDeleted
  17. ReviewRejectionReasons
  18. ReviewTaskResultTypes
  19. ReviewTaskResults
  20. ReviewTaskStates
  21. ReviewTaskTypes
  22. ReviewTasks
  23. SuggestedEditVotes
  24. SuggestedEdits
  25. TagSynonyms
  26. Tags
  27. Users
  28. VoteTypes
  29. Votes

In addition to that, we know the data dumps can be found in the Stack Exchange site at the Internet Archive as XML files.

I've downloaded the torrent file 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.

The question

I have two questions

  1. Why aren't the 29 tables provided for each Stack Exchange site in the torrent file at the Internet Archive?
  2. Is there any effortless way I can get the data dump of the 29 tables for each site?

Any help is really appreciated.

Additional context

I've noticed that there is the Download CSV button in the Stack Exchange Data Explorer (see image below)

enter image description here

I was thinking in using this feature for writing SELECT * FROM <<table-name>> for each table so that I can get the data from the Download CSV button. However, I consider this my last option since that would imply more effort (I would need to do this 21 * n times, where n is the number of Stack Exchange sites).

4
  • 1
    So what you want to do is download all of the public-data-explorer available data, for every site?
    – bobble
    Commented Aug 5, 2021 at 23:52
  • Yes. I've already downloaded the data available at the Internet Archive (i.e. the torrent file), but, as I mentioned, only Badges.xml, Comments.xml, PostHistory.xml, PostLinks.xml, Posts.xml, Tags.xml, Users.xml and Votes.xml are provided for each site. Commented Aug 6, 2021 at 0:12
  • 1
    You might want to add exactly why you would want to do this, and can't do what you wan't on the public SEDE. "Just because I would like to" is hardly ever a good argument
    – Luuklag
    Commented Aug 6, 2021 at 7:02
  • @Luuklag I want to do this because performing queries on SEDE is a task that I commonly do in my daily basis (since that way I can search information quicker and execute complex queries) and sometimes I get limited by the resources given to me at SEDE so I want to use the resources of my machines instead. By having that information on my hands, SEDE would be freed by some user and thus, people that don't have the resources to download the data dumps can use SEDE with shorter latencies. Commented Aug 6, 2021 at 7:12

1 Answer 1

3

That is all the data you will get from the public data dump.

Notice in Database schema documentation for the public data dump and SEDE how it says near the top: italic table names are found in both the Data Dump on Archive.org as well as in the SEDE

Answering your 2 questions:

Why aren't the 29 tables provided for each Stack Exchange site in the torrent file at the Internet Archive?

Only SE can answer this but it is either no one thought about adding all tables to the data dump or time and size restrictions forbid doing this. As far as I know seeding SEDE and seeding the data-dump are separate processes, steered with different scripts. It is not like they take the SEDE database and zip it.

Is there any effortless way I can get the data dump of the 29 tables for each site?

No, there is not. SEDE doesn't have an API so at best you have to do some Selenium/WebDriver scripting. But you can make your life a bit easier for those tables you have to fetch that are > 50,000 rows by using FOR JSON AUTO as explained in my answer here

See also:

4
  • Thanks for the help! Do you know how I can let know the SE team about this? Best case scenario, they will consider adding the other tables in the data dump. Worst case scenario, they won't approve this. I'm thinking in creating a new question with the "feature-request" tag. Commented Aug 6, 2021 at 15:38
  • 1
    You can start an FR but I won't have high hopes of it getting implemented unless you come up with a very good reason that explains why this is beneficial not only for you.
    – rene
    Commented Aug 6, 2021 at 16:11
  • Do note there are some specific FRs that ask for one table to be added for a specific purpose and those are not fulfilled yet either: meta.stackexchange.com/questions/226657/…
    – rene
    Commented Aug 6, 2021 at 16:18
  • Thank you very much for the detailed information. I've just created the feature request. You can find the question here. Commented Aug 7, 2021 at 17:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .