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