calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dan Di Spaltro <dan.dispal...@gmail.com>
Subject Re: Tenanted SQL
Date Sat, 09 Apr 2016 09:50:25 GMT
I checked out CURRENT_TIMESTAMP, so there seem to be two paths it can
take.  In the case of a jdbc backend it just passes the function name
during the rel2sql phase.  I see maybe if you are using the reflective
schema it does uses the datacontext (which I think refers to the special
logic you mention above for holding the value) but I just can't seem to
understand all the pieces and how'd they fit together.  Here's what I've
been discovering:

1) CURRENT_PATH doesn't work on something like Postgres, so I noticed it
gets all the way to sending the sql to the backend before failing at the PG
level.  CURRENT_TIMESTAMP does work in PG and get's passed directly in,
similarly.
2) Do views work differently or is there some materialization that happens
earlier, I couldn't quite figure that out.  Specifically, how this relates
to making a special function.
3) Code wise, I started off by using the sql parser, and then manipulating
the tree such that constraints of tenant were imposed, but it feels real
hacky (and fragily) so now I am trying to move to this model which seems
like a better solution.  I basically have the following code working,
defined my own AdapterContext and DataContext, inherited from
CalcitePrepareImpl have an execute function that basically hobbles it all
together. Would I have do something on the implementor side to get the
generated SQL to fill in that particular value at execution time?  I've
tried adding functions, but that seems like it's meant to correspond to an
actual UDF, or am I declaring them incorrectly?
4) Should I use the rules system to accomplish any of this?

And just to step back is this a valid usecase?  More or less I want to
provide people with a SQL interface that feels like you have access to the
whole database when in reality it's just your tenant's slice of your own
data.  Calcite feels like a good fit for something like that.

Thanks for any pointers!

-Dan

On Thu, Apr 7, 2016 at 11:26 PM, Jacques Nadeau <jacques@apache.org> wrote:

> FYI, In Apache Drill we expose USER for exactly this purpose, allow it to
> be used in views and also ensure that it leverages constant reduction and
> or partition pruning where possible. I think we also have a group concept
> but I can't find the docs on that at the moment.
>
> On Thu, Apr 7, 2016 at 10:08 PM, Julian Hyde <jhyde@apache.org> wrote:
>
> > CURRENT_TENANT would not be an ordinary function. As I said, it should
> > work something like CURRENT_TIMESTAMP, which is not an ordinary function
> > either.
> >
> > Yeah, I wish the doc was more flushed out, too. :)
> >
> >
> > > On Apr 7, 2016, at 8:50 PM, Dan Di Spaltro <dan.dispaltro@gmail.com>
> > wrote:
> > >
> > > So are you suggesting doing something like this?
> > >
> >
> https://calcite.apache.org/docs/tutorial.html#tables-and-views-in-schemas
> > >
> > > And using a special function `tenant_id = CURRENT_TENANT()` in place of
> > > `gender = \'F\'`
> > >
> > > I really wish some of the further topics at the bottom were more
> flushed
> > > out, specifically how to add functions.
> > >
> > > -Dan
> > >
> > > On Sun, Mar 27, 2016 at 9:11 PM, Julian Hyde <jhyde@apache.org> wrote:
> > >
> > >> See comments inline.
> > >>
> > >> Julian
> > >>
> > >>
> > >>> On Mar 25, 2016, at 9:29 AM, Dan Di Spaltro <dan.dispaltro@gmail.com
> >
> > >> wrote:
> > >>>
> > >>> So I definitely understand the data side of the target database
> ("A"),
> > >> that
> > >>> I am virtualizing.
> > >>>
> > >>> I guess more specific questions would be:
> > >>> * How would I expose only two tables from "A" (they would both
> include
> > >> the
> > >>> tenantId field), I'm guess I might override the JdbcSchema using some
> > >>> whitelist.
> > >>
> > >> A whitelist is one approach. Another is to keep the JdbcSchema
> private,
> > >> but create views in another schema that reference those tables.
> Tenants
> > >> would only see the views.
> > >>
> > >>> * Since I want to give everyone the same virtual table space (with
> > >>> different "data"), would I need to look in overriding some of the
> Jdbc
> > >> core
> > >>> implementation?
> > >>
> > >> I don’t think you need to change anything in the JDBC adapter. All of
> > the
> > >> smarts will be in the views.
> > >>
> > >>> * I would need to use the parsed tree and then add the tenantId
> filter
> > >>> * Somehow pass in the tenantId during query time, ideally at the
> > >> statement
> > >>> vs the connection level.
> > >>
> > >> You could put tenantId into the DataContext. In SQL it would be
> accessed
> > >> using a function. This is very similar to how CURRENT_TIMESTAMP
> function
> > >> works.
> > >>
> > >>>
> > >>> Anyways, was just looking for some pointers, as there is a lot of
> code
> > >>> here. And anything would be much appreciated.  I am happy to share
> some
> > >> of
> > >>> the work once it's done.
> > >>
> > >>
> > >>
> > >>
> >
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message