Custom SQL as a source for Caravel

[lerrua]

Hi,

I'm already use Caravel in my work day and it seems to evolve as well, it's really a great job.

However, some people are used with tools like Heroku Dataclips. Caravel Explore/QueryBuilder it's nice but it would be great to write our own sql queries and this custom query become a Caravel Slice.

This is a feature that is in the roadmap?

[mistercrunch]

Yes, there's already some scaffolding done to allow that. In the table list view there's a SQL link you can follow to this:

The "Create view" button doesn't work yet, but the idea is you'd create a view here and that button would create a new entry that you can then use the same way you use individual tables in Caravel. / What do you think

[kartha01]

+1 That would be useful. In fact this can allow creating queries with joins on tables from other schemas as well (my current pain point) ;-)

[kartha01]

What I meant was entering custom SQL can allow joins and using other tables to create Views. Looks like today, the SQL can be used only against a table from the drop down list.

Btw, I am using 0.8.6 and I see exceptions when I go to the SQL link:

File "/usr/lib/python2.7/site-packages/pandas/core/format.py", line 669, in to_html html_renderer.write_result(self.buf) File "/usr/lib/python2.7/site-packages/pandas/core/format.py", line 970, in write_result 'not %s' % type(self.classes)) AssertionError: classes must be list or tuple, not <type 'unicode'>

[philippfrenzel]

Hi,

we started creating a datalayer with migrations which creates all views we need. I guess what you are looking for is some kind of entity relationship management - which is a mandantory feature for professional BI-Tools like: QlikSense, Tibco Spotfire, Tableau ...

Looking forward to see the tool to enhance ;)

[mistercrunch]

I don't think we're planning to enhance the semantic layer in the direction of deeply understanding your schema and generating joins on your behalf anytime soon. Having used Businesses Object and MicroStrategy extensively i feel like it's a pitfall that puts the wrong abstraction in the wrong place. Even with modest schema it becomes hard to manage pretty quickly.

[mistercrunch]

Oh and yes, the sql view was busted in 0.8.6 but it's fixed on master now.

[Huangsir]

+1

[Frituurpanda]

I'll try that later. Seems like a good temporary solution.

[alanmcruickshank]

Hi guys, I'd like to have a crack at implementing some join functionality at the table level. It wouldn't be a full "custom SQL" solution but it would allow some limited functionality for joining tables.

My suggestion would be to create a new class as an extension of the current SqlaTable called something like CompositeSqlaTable. This would start with a base table (e.g. customers) and allow the joining of other tables to it (e.g. orders) with a choice of join options (left/right/inner/outer) and explicit specification of which field to use for joining on the left and right. The created composite table would then function and be queriable like a normal table at the moment in caravel (I guess it might be most sensible to autodetect columns from both sides of the join).

@mistercrunch thoughts on implementation/general idea? I know you mentioned a while back that you might be interested in limited join functionality.

@kartha01 @lerrua would this solve some of your pain points?

This would be massively helpful for me beyond using views, especially where the relevant datetime column for filtering is in a different table to the entity which I'm trying to query.

[giaosudau]

Could it be something like write a SQL for instance a join then create table (temp) sort of view then user can explore on that view (temp table)?

[eliab]

@mistercrunch - whats the ETA for this? it looks promising..

[mistercrunch]

SQL Lab does that, the slice model also allows from a SELECT statement instead of a table (that's the way SQL Lab does it)