6

I just learned about the Stack Exchange Data Explorer, and I'm really excited to explore the data! I was wondering, is there a way to get an authenticated, read-only connection to the database? I don't mind the web-based format, but it would be much nicer if I could hook it to SQL Server, MySQL Workbench, DataGrip, etc.

  • AFAIK, you can download the Data Dump and import it to your own database. See e.g. this question. – Glorfindel Dec 24 '16 at 21:03
  • Oh, that's cool! That's sorta what I am looking for, but those dumps contain much less data than the SEDE. I am looking for core SEDE read-only access. – mxplusb Dec 24 '16 at 22:21
  • Related on the verge of dupe: meta.stackexchange.com/questions/264678/… – Shadow9 Dec 24 '16 at 23:31
4

TL;DR: No.

SEDE does not provide an API, nor any method of connecting directly to the database (which would be a security concern anyway).

Your options are the API (which is not a database, but provides the most access to the widest set of data); the web-based SEDE instance (which has the next most wide-ranging set of data, but is web-based and without an API), or to download the data dumps and import to your own database (which has the least wide-ranging data - though still fairly rich - but is a database that you control fully).

  • The data dump and SEDE should have the same type/amount of data. They are powered by the same export queries, IIRC. The only difference is that the data dump is released quarterly vs weekly for SEDE. – Adam Lear Dec 25 '16 at 0:40
  • @AdamLear Huh. I checked my imported database against SEDE to come up wth this answer, though I guess my import might be out of date by now. – ArtOfCode Dec 25 '16 at 0:44
  • It's also entirely possible that I misremember or we did something bad. :) – Adam Lear Dec 25 '16 at 0:45
  • @ArtOfCode that's what I figured. However, I did find a workaround! – mxplusb Dec 25 '16 at 16:05
  • @AdamLear PostsWithDeleted, all the review queue information, and PendingFlags are all left out of the data dump. – hichris123 Dec 25 '16 at 18:03
  • @hichris123 Ah, right. PostsWithDeleted is possibly an oversight, but the other two I think were deemed not useful enough from the long-term content preservation standpoint. – Adam Lear Dec 25 '16 at 19:58
0

So it turns out their web-based explorer exposes a lot more of the database than I thought! After lots of playing with SQL queries, I was able to learn that it is a MSSQL database, Microsoft SQL Server 2016 (RTM-CU1)!! In PostgreSQL and MySQL, you can actually recreate your tables with show create table 'table-name', which outputs a text of the SQL required to recreate your tables. MSSQL has the same feature, however it's ONLY available through SSMS, which caused a challenge. After lots of searching and testing, I was able to find a SQL script that could generate the SQL required to recreate the entire schema of a SE site!

Now I can use DataGrip/SSMS to build queries locally against an empty database and then drop them in the web-based DE when I want to execute them. This is helpful for complicated joins and stuff as SQL is not my primary language. While this isn't EXACTLY what I was hoping for, it's good enough for me! Hopefully others can find this as helpful as I did!

Here is the SQL statement. Should work on all SE sites within the Data Explorer.

You must log in to answer this question.

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