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.

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

| improve this answer | |
  • 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.

| improve this answer | |

You must log in to answer this question.

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