5 ms·

Django SQL Explorer

193 points by uyoakaoma 10y ago

nepsilon 10y ago
So this has nothing to do with Django, other than being built with it? It took me a while to realize that.
- webmaven 10y ago
  What does something that does "have to do with Django" look like to you?
  - alexbecker 10y ago
    I think the parent was expecting this to be more like a plugin for Django.
    - wheelerwj 10y ago
      It's a Django app, so it is pretty much a plugin isn't it?
    - Ensorceled 10y ago
      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.
  - rer0tsaz 10y ago
    I was expecting a "Django SQL" Explorer, for example a Django ORM -> SQL compiler playground, but got a Django "SQL Explorer".
- numlocked 10y ago
  (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.
webmaven 10y ago
This looks like an interesting tool, and a great way to sharethis sort of knowledge internally. I'm hankering after a SQLAlchemy version, though.
lsh 10y ago
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/
- abrookewood 10y ago
  How is it different to Metabase? They look like they do very similar things. Can I ask why you are using both?
  - lsh 10y ago
    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.
- arikfr 10y ago
  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://redash.io https://github.com/getredash/redash https://github.com/getredash/redash Disclaimer: I'm the author/founder of Redash.
  - lsh 10y ago
    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
viiralvx 10y ago
Something that we use at my job is Looker (https://looker.com/ 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.
- thenaturalist 10y ago
  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).
  - teej 10y ago
    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.
  - viiralvx 10y ago
    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.
- numlocked 10y ago
  (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).
iriche 10y ago
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!
- numlocked 10y ago
  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: cc@grove.co
uyoakaoma 10y ago
It's trending https://github.com/trending/python https://github.com/trending/python
numlocked 10y ago
Well...this is a surprise. I wrote SQL Explorer :) Glad folks are finding it useful! Happy to answer any questions or take feedback/feature requests (I'm cc@grove.co)! 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...
agentgt 10y ago
An easy way to fix many of the "production" concerns is to run SQL explorer only on syndicated read only slave servers. Most databases support slave servers. Probably should be added to the readme.
- numlocked 10y ago
  Good idea. Will do!
madrox 10y ago
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.

Django SQL Explorer

193 points by uyoakaoma 10y ago

nepsilon 10y ago

So this has nothing to do with Django, other than being built with it? It took me a while to realize that.

webmaven 10y ago
What does something that does "have to do with Django" look like to you?
- alexbecker 10y ago
  I think the parent was expecting this to be more like a plugin for Django.
  - wheelerwj 10y ago
    It's a Django app, so it is pretty much a plugin isn't it?
  - Ensorceled 10y ago
    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.
- rer0tsaz 10y ago
  I was expecting a "Django SQL" Explorer, for example a Django ORM -> SQL compiler playground, but got a Django "SQL Explorer".
numlocked 10y ago
(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.

webmaven 10y ago

This looks like an interesting tool, and a great way to sharethis sort of knowledge internally. I'm hankering after a SQLAlchemy version, though.

lsh 10y ago

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/

abrookewood 10y ago
How is it different to Metabase? They look like they do very similar things. Can I ask why you are using both?
- lsh 10y ago
  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.
arikfr 10y ago
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://redash.io https://github.com/getredash/redash https://github.com/getredash/redash Disclaimer: I'm the author/founder of Redash.
- lsh 10y ago
  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

viiralvx 10y ago

Something that we use at my job is Looker (https://looker.com/ 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.

thenaturalist 10y ago
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).
- teej 10y ago
  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.
- viiralvx 10y ago
  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.
numlocked 10y ago
(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).

iriche 10y ago

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!

numlocked 10y ago
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: cc@grove.co

uyoakaoma 10y ago

It's trending https://github.com/trending/python https://github.com/trending/python

numlocked 10y ago

Well...this is a surprise. I wrote SQL Explorer :) Glad folks are finding it useful! Happy to answer any questions or take feedback/feature requests (I'm cc@grove.co)! 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...

agentgt 10y ago

An easy way to fix many of the "production" concerns is to run SQL explorer only on syndicated read only slave servers. Most databases support slave servers. Probably should be added to the readme.

numlocked 10y ago
Good idea. Will do!

madrox 10y ago

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.