Django SQL Explorer(github.com)
github.com
Django SQL Explorer
https://github.com/groveco/django-sql-explorer
31 comments
Just like to say that django sql explorer has been very useful in getting a few non-technical people (who know sql) investigating the state of our databases, their relations and attempting to express reports they need. I'm hoping their work will be easy to translate into Metabase 'questions' while I pull our reporting project together.
http://www.metabase.com/
http://www.metabase.com/
I hope you don't mind the self plug: I think Metabase has support for raw SQL, but if you find it hard to translate to Metabase "questions", it sure will be easy to translate into queries in Redash... In Redash you create visualizations from regular SQL queries (although we do support adding parameters and such).
https://redash.io https://github.com/getredash/redash
Disclaimer: I'm the author/founder of Redash.
https://redash.io https://github.com/getredash/redash
Disclaimer: I'm the author/founder of Redash.
I don't mind the plug at all. I know Metabase supports raw queries, but we're going from normalised schemas to 'flattened' denormalised ones to help the reporting effort.
At the same time, I don't expect the whole organisation to know SQL while I do expect them to make a slight effort to use Metabase's 'Question' interface (if they want data out).
I'll be sure to take a look at Redash
At the same time, I don't expect the whole organisation to know SQL while I do expect them to make a slight effort to use Metabase's 'Question' interface (if they want data out).
I'll be sure to take a look at Redash
Well done on Redash - It looks and feel very nice. I'll definitely keep an eye on it, but Metabase's 'Questions' and visualisations are a bit more polished for now.
I guess that's the kind of polish VC money buys :-) I went the bootstrap/community route. But I hope that in about two months we will have funds to hire a UI/UX designer to improve the user experience and general look and feel.
> I guess that's the kind of polish VC money buys :-)
quite possibly. I've also read some of their bug threads and they're quite discerning (protective?) of what makes it in from the community. so, money+perfectionism, perhaps.
but honestly - their 'questions' interface is a hell of a lot more fun than sql. thanks to ORMs, my sql knowledge is really quite rusty as well and the simple/constrained "select, filter, aggregate" options on a single table or data source lets me faceroll what I need.
quite possibly. I've also read some of their bug threads and they're quite discerning (protective?) of what makes it in from the community. so, money+perfectionism, perhaps.
but honestly - their 'questions' interface is a hell of a lot more fun than sql. thanks to ORMs, my sql knowledge is really quite rusty as well and the simple/constrained "select, filter, aggregate" options on a single table or data source lets me faceroll what I need.
I use both Metabase and Redash in the company that I work for.
Both are really great tools and can replace (at least in part) expensive BI tools.
I'm using both not because they are complementary, but at this moment they have a distinct feature set and no one has all the features that I want/need.
Btw, congrats for the Mozilla award, really well deserved! https://blog.mozilla.org/blog/2016/10/03/moss-supports-four-...
I'm using both not because they are complementary, but at this moment they have a distinct feature set and no one has all the features that I want/need.
Btw, congrats for the Mozilla award, really well deserved! https://blog.mozilla.org/blog/2016/10/03/moss-supports-four-...
Thanks!
I would love to understand what you're missing in Redash. If it's too much for a comment here, feel free to email me (arik at redash.io).
I would love to understand what you're missing in Redash. If it's too much for a comment here, feel free to email me (arik at redash.io).
Hey thanks for mentioning Metabase, I work there. Not to hijack this thread, but I'm happy to answer any questions.
We do have raw SQL (and Mongo/Druid/etc) mode if that's your preference, or if you can't express something in our graphical query builder.
There's also a neat little feature that will translate your query builder query to the equivalent SQL to use as a starting point if you can't express something using the UI.
We also have parameters so you can parameterize questions (both graphical and SQL) on a dashboard.
Finally, we recently started added permissions and groups, so Metabase will be able to grow with your company. A lot more to come there too.
We do have raw SQL (and Mongo/Druid/etc) mode if that's your preference, or if you can't express something in our graphical query builder.
There's also a neat little feature that will translate your query builder query to the equivalent SQL to use as a starting point if you can't express something using the UI.
We also have parameters so you can parameterize questions (both graphical and SQL) on a dashboard.
Finally, we recently started added permissions and groups, so Metabase will be able to grow with your company. A lot more to come there too.
How is it different to Metabase? They look like they do very similar things. Can I ask why you are using both?
They do do very similar things, Metabase even has a raw sql interface hidden away.
Metabase however has a better interface in many regards but especially in it's 'Questions' interface - under the hood it's generating SQL, but it gives any person just enough flexibility to select, filter and aggregate results and then turn them into graphs and stick the graphs on a dashboard.
I don't particularly like Metabase's false peppiness - it's a bit like Slack's "You look good today!" type messages - that really get under my skin, and the blue+light-grey on white colour scheme.
For those who do know SQL and write the majority of the reports, Django SQL Explorer was a great drop-in just-works lib to get insight into several of our data silos.
Metabase however has a better interface in many regards but especially in it's 'Questions' interface - under the hood it's generating SQL, but it gives any person just enough flexibility to select, filter and aggregate results and then turn them into graphs and stick the graphs on a dashboard.
I don't particularly like Metabase's false peppiness - it's a bit like Slack's "You look good today!" type messages - that really get under my skin, and the blue+light-grey on white colour scheme.
For those who do know SQL and write the majority of the reports, Django SQL Explorer was a great drop-in just-works lib to get insight into several of our data silos.
Something that we use at my job is Looker (https://looker.com/). While it's not open-source or free, I didn't know about the product until I joined and I love their features for data visualization and running ad-hoc queries. We just set a read-only user named 'looker' and its good to go. :)
Disclaimer: I'm not a Looker employee or anything, just a happy user of their product.
Disclaimer: I'm not a Looker employee or anything, just a happy user of their product.
(SQL Explorer author here)
We actually use Looker as well internally. It's a great tool. When we were smaller, Explorer fit our needs really well (I should hope so - since I specifically wrote it for us), but as we got bigger (~40 employees now) it's harder to manage a large number of reports, and we started getting a bit of a backlog of "please add field x to report y" engineering tickets. Looker has been a great solution to allow people to self-serve even more.
We still use the heck out of SQL Explorer as well, but Looker adds a ton of additional value (though notably, is not terribly cheap).
We actually use Looker as well internally. It's a great tool. When we were smaller, Explorer fit our needs really well (I should hope so - since I specifically wrote it for us), but as we got bigger (~40 employees now) it's harder to manage a large number of reports, and we started getting a bit of a backlog of "please add field x to report y" engineering tickets. Looker has been a great solution to allow people to self-serve even more.
We still use the heck out of SQL Explorer as well, but Looker adds a ton of additional value (though notably, is not terribly cheap).
Hey, we're thinking about switching our reporting to Looker. Would you mind sharing your impressions of the tool and maybe how your daily workflow roughly looks like?
You can shoot me an email via jankyri.com (link at the bottom).
You can shoot me an email via jankyri.com (link at the bottom).
If you're at the point where you have the need and the budget for a business intelligence tool, Looker is fantastic. Anyone who can pivot in Excel will pick up Looker quickly and be able to get actionable insights.
Hey there! I am not on the Data Engineering team, I'm just a regular ol' Software Engineer who likes making data visualizations for fun. But I do know that a lot of the people on the Data Engineering team enjoy using Looker primarily because you can do so many things with it and make it interactive. They even setup some nice things such as User Projections, revenue projections, etc. And it's easily interactive. Also, it's easy to auto-generate reports to be sent to stakeholders, complete with charts and visualizations when necessary. Nothing but good impressions on my end here.
Really fun to see this on HN:s front page - I started using this last week to give some non technical people access to some queries I usually run for them. Works perfect so far!
Woohoo! Thanks and great to hear. If you have any feedback or suggestions, feel free to open a GH issue (...or a pull request), or email me directly: [email protected]
It's trending https://github.com/trending/python
Well done! We built something similar at my company for the same purposes. This functionality is powerful for any team that wants easier data access for casual SQL users.
So this has nothing to do with Django, other than being built with it? It took me a while to realize that.
(app author here)
It is tied to Django. When generating the schema information to show fields, etc, it introspects the Django application metadata. So in that sense it is tied to Django and expects to be querying a Django application DB. I would actually love to remove this requirement though, and have it just be SQL Explorer.
It is tied to Django. When generating the schema information to show fields, etc, it introspects the Django application metadata. So in that sense it is tied to Django and expects to be querying a Django application DB. I would actually love to remove this requirement though, and have it just be SQL Explorer.
What does something that does "have to do with Django" look like to you?
I was expecting a "Django SQL" Explorer, for example a Django ORM -> SQL compiler playground, but got a Django "SQL Explorer".
I think the parent was expecting this to be more like a plugin for Django.
But it is a Django plugin. The readme.md includes instructions for integrating it into your Django app and the use case provided is for exploring your Django database.
It's a Django app, so it is pretty much a plugin isn't it?
This looks like an interesting tool, and a great way to sharethis sort of knowledge internally.
I'm hankering after a SQLAlchemy version, though.
I'm hankering after a SQLAlchemy version, though.
Glad folks are finding it useful!
Happy to answer any questions or take feedback/feature requests (I'm [email protected])! We use the heck out of this thing internally.
A couple fun notes:
1. Every SQL statement is logged as a QueryLog object. This really simple data model enables a ton of the functionality:
- unique, bookmarkable URLs for every single query (including ad-hoc queries)
- historic runtime/query duration reporting
- query versioning
- 'popularity' in the form of run-count
2. The 'snapshot' feature is incredibly powerful. Tick a box on a saved query and (assuming you have Celery), Explorer will the run query on a cron and save the results to s3. Super useful for mutable data. Like, if you have a product catalog and the prices sometimes change, but not very often, it's great to have a query of 'current prices' that snapshots once per day.
3. Queries are all exposed as CSV and JSON endpoints (with token auth). A great way to shuffle data between applications.
4. The only 'django-specific' part of this is that it introspects the Django app metadata to build the 'schema viewer' on the right hand side. If not for this one feature, this would just be a totally general-purpose query tool that happened to be built on Django. One day...