News for hackers. Stuff that matters.
Re:Django SQL Explorer by numlocked on Monday October 31, 2016 @11:19AM (#12837536)
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 [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...

Re:Django SQL Explorer by lsh on Monday October 31, 2016 @04:34AM (#12835038)
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/

Re:Django SQL Explorer by arikfr on Monday October 31, 2016 @08:42AM (#12836174)
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.

Re:Django SQL Explorer by lsh on Monday October 31, 2016 @09:28AM (#12836480)
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

Re:Django SQL Explorer by lsh on Monday October 31, 2016 @09:51AM (#12836653)
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.
Re:Django SQL Explorer by arikfr on Monday October 31, 2016 @01:34PM (#12838982)
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.
Re:Django SQL Explorer by lsh on Monday October 31, 2016 @02:25PM (#12839561)
> 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.

Re:Django SQL Explorer by fgasparini on Monday October 31, 2016 @01:43PM (#12839088)
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-...

Re:Django SQL Explorer by arikfr on Monday October 31, 2016 @01:50PM (#12839155)
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).

Re:Django SQL Explorer by tlrobinson on Monday October 31, 2016 @12:53PM (#12838575)
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.

Re:Django SQL Explorer by abrookewood on Monday October 31, 2016 @05:30AM (#12835304)
How is it different to Metabase? They look like they do very similar things. Can I ask why you are using both?
Re:Django SQL Explorer by lsh on Monday October 31, 2016 @09:33AM (#12836516)
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.

Re:Django SQL Explorer by viiralvx on Monday October 31, 2016 @05:28AM (#12835294)
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.

Re:Django SQL Explorer by numlocked on Monday October 31, 2016 @11:23AM (#12837567)
(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).

Re:Django SQL Explorer by thenaturalist on Monday October 31, 2016 @05:46AM (#12835402)
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).

Re:Django SQL Explorer by teej on Monday October 31, 2016 @08:14AM (#12836025)
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.
Re:Django SQL Explorer by viiralvx on Monday October 31, 2016 @11:37AM (#12837724)
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.
Re:Django SQL Explorer by iriche on Monday October 31, 2016 @08:58AM (#12836282)
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!
Re:Django SQL Explorer by numlocked on Monday October 31, 2016 @11:35AM (#12837708)
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]
Re:Django SQL Explorer by agentgt on Monday October 31, 2016 @01:15PM (#12838772)
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.

Re:Django SQL Explorer by numlocked on Monday October 31, 2016 @01:17PM (#12838788)
Good idea. Will do!
Re:Django SQL Explorer by uyoakaoma on Monday October 31, 2016 @09:54AM (#12836673)
Re:Django SQL Explorer by webmaven on Monday October 31, 2016 @03:18AM (#12834802)
This looks like an interesting tool, and a great way to sharethis sort of knowledge internally.

I'm hankering after a SQLAlchemy version, though.

Re:Django SQL Explorer by madrox on Monday October 31, 2016 @01:23PM (#12838865)
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.
Re:Django SQL Explorer by nepsilon on Monday October 31, 2016 @03:12AM (#12834781)
So this has nothing to do with Django, other than being built with it? It took me a while to realize that.
Re:Django SQL Explorer by numlocked on Monday October 31, 2016 @11:29AM (#12837646)
(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.

Re:Django SQL Explorer by webmaven on Monday October 31, 2016 @03:14AM (#12834789)
What does something that does "have to do with Django" look like to you?
Re:Django SQL Explorer by rer0tsaz on Monday October 31, 2016 @05:34AM (#12835335)
I was expecting a "Django SQL" Explorer, for example a Django ORM -> SQL compiler playground, but got a Django "SQL Explorer".
Re:Django SQL Explorer by alexbecker on Monday October 31, 2016 @03:41AM (#12834871)
I think the parent was expecting this to be more like a plugin for Django.
Re:Django SQL Explorer by Ensorceled on Monday October 31, 2016 @07:05AM (#12835724)
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.
Re:Django SQL Explorer by wheelerwj on Monday October 31, 2016 @03:45AM (#12834880)
It's a Django app, so it is pretty much a plugin isn't it?