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 Mon, 29 Sep 2014 13:05:37 GMT
Hi James,

Thanks for this feedback. There should not be any cycle in he trees.

I will give a try at this and see how it works.

JM

2014-09-25 18:10 GMT-04:00 James Taylor <jamestaylor@apache.org>:

> Hey JM,
>
> My idea for processing this kind of query assumes that the tree does
> not have cycles and would do one query per depth of the hierarchy.
> Something like the following, with targetTable having a DEPTH and ID
> column:
>
> int collectChildren(long rootId) {
>     connection.setAutoCommit(true)
>     int depth = 0;
>     // seed our temp table:
>     upsert into targetTable(depth, id) values (0, <rootId>);
>     do {
>         depth++;
>         rowCount =
>             upsert into tmp.target(depth, id)
>             select <depth>,id
>             from targetTable t join sourceTable s
>             on (t.id = s.id and t.depth=<depth - 1>)
>     } while (rowCount > 0);
>
>     return depth;
> }
>
> I suppose if there were cycles, you could add a WHERE NOT IN clause.
>
> Thanks,
> James
>
> On Thu, Sep 25, 2014 at 5:38 AM, Jean-Marc Spaggiari
> <jean-marc@spaggiari.org> wrote:
> > 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