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 ModCommented Dec 24, 2016 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.– SiennaCommented Dec 24, 2016 at 22:21
-
Related on the verge of dupe: meta.stackexchange.com/questions/264678/…– Shadow WizardCommented Dec 24, 2016 at 23:31
Add a comment
|
2 Answers
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).
answered Dec 24, 2016 at 23:34
-
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. Commented Dec 25, 2016 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. Commented Dec 25, 2016 at 0:44
-
It's also entirely possible that I misremember or we did something bad. :) Commented Dec 25, 2016 at 0:45
-
@ArtOfCode that's what I figured. However, I did find a workaround!– SiennaCommented Dec 25, 2016 at 16:05
-
@AdamLear PostsWithDeleted, all the review queue information, and PendingFlags are all left out of the data dump. Commented Dec 25, 2016 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. Commented Dec 25, 2016 at 19:58
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.