phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jean-Marc Spaggiari <jean-m...@spaggiari.org>
Subject Re: Recursive queries?
Date Thu, 25 Sep 2014 12:38:24 GMT
Hi James,

Thanks for the feedback. My knowledge of Phoenix and SQL is not good enough
for now to jump on such a big patch.

Regarding your suggesting with UPSERT SELECT and temp table, the question
is when do we know we should stop.

I upsert select from the first level, then do that again with the IN from
this temp table, and then again and again, but at some point I'm at the
bottom of the tree so I can stop, but how do I know? Does UPSERT SELECT
return the number of rows inserted into the destination table? If so it
might be an option.

That might give something like:

UPSERT INTO tmp.targetTable(id) SELECT id FROM test.sourceTable WHERE
parent IN (SELECT id FROM tmp.targetTable) AND id NOT IN (SELECT * FROM
tmp.targetTable)

Will something like that work? Bascially, I insert all rows which are not
already there and which have the parent in the temp table. I just need to
populate the table with the top node and iterate until this request doesn't
give me any result back.

Thanks,

JM

2014-09-24 23:43 GMT-04:00 James Taylor <jamestaylor@apache.org>:

> Hey JM,
> We'd like to support all of SQL-99 eventually, so based on that, it's
> on our roadmap. Like most open source projects, we'd look for a
> volunteer to take this on - it certainly meets the criteria of being
> interesting.
>
> I think priority-wise, it's lower than most of the join work
> identified on our Roadmap (http://phoenix.apache.org/roadmap.html).
> Would it be feasible to drive this through multiple client queries (1
> per level) using the IN clause support we have (i.e. by generating a
> query)? You could use UPSERT SELECT to dump the IDs you get back at
> each level into a temp table if need be and join against it for the
> next query.
>
> Thanks,
> James
>
> On Wed, Sep 24, 2014 at 1:08 PM, Jean-Marc Spaggiari
> <jean-marc@spaggiari.org> wrote:
> > Hi,
> >
> > We have something like this that we want to "translate" into Phoenix
> > (snippet):
> >
> >
> >
> > RETURN QUERY WITH RECURSIVE first_level AS (
> > -- non-recursive term
> >  (
> >     SELECT a.id AS id FROM asset a
> >        WHERE a.parent_id = p_id AND TYPE = 2
> > )
> > UNION
> >   -- Recursive Term
> >   SELECT a.id AS id FROM first_level fflf, asset a
> >      WHERE a.parent_id = flf.id AND type = 2
> > )
> >
> >
> > Basically, let's consider we have millions of trees stored into HBase.
> For
> > any node, we want to get all the children recursively.
> >
> > Is that something we can translate to Phoenix? If not, is it in the
> roadmap?
> >
> > Thanks,
> >
> > JM
>

Mime
View raw message