You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by Stefán Baxter <st...@activitystream.com> on 2016/01/16 14:18:01 UTC
Efficient joins in Drill - avoiding the massive overhead of scan
based joins
Hi,
Can anyone point me to an implementation where joins are implemented with
full support for filters and efficient handling of joins based on indexes.
The only code I have come across all seems to rely on complete scan of the
related table and that is not acceptable for the use case we are working on
(Lucene reader).
Regards,
-Stefán
Re: Efficient joins in Drill - avoiding the massive overhead of scan
based joins
Posted by Stefán Baxter <st...@activitystream.com>.
Hi,
After a quick glance at Drill-3929 I think I should state that this is
"only" about the push down of the join filter and a efficient way to do
join that does not require a full scan.
We are not using Lucene as an external index for a separate data source ea.
the Lucene index contains all the information we need for the join (stored
fields).
I guess this would make more sense to people if we said we were using Solr
or Elastic Search but this use-case is not as complex as the one detailed
in Drill-3929.
Regards,
-Stefan
On Sat, Jan 16, 2016 at 8:11 PM, Stefán Baxter <st...@activitystream.com>
wrote:
> Hi Jacques,
>
> Thank you for taking the time, it's appreciated.
>
> I'm trying to contribute to the Lucene reader for Drill (Started by Rahul
> Challapalli). We would like to use it for storage of metadata used in our
> Drill setup.
> This is perfectly suited for our needs as the metadata is already
> available in Lucene document+indexes and it's tenant specific (So this is
> not the global metadata that should reside in Postgres/HBase or something
> similar)
>
> I think it's best that I confess that I'm not sure what I'm looking for or
> how to ask for it, at least not in proper Drill terms.
>
> The Lucene reader is working but the joins currently rely on full scan
> which introduces ~20 time longer execution time on simple data sets (few
> million records) so I need to get the index based joins going but I don't
> know how.
>
> We have resources to do this now but our knowlidge of Drill is limited and
> I could not, in my initial scan of the project, find any use
> of DrillJoinRel that indicated indexes were involved (please forgive me if
> this is a false assumption).
>
> Can you please clarify things for me a bit:
>
> - Is the JDBC connector already doing proper pushdown of filters for
> joins? (If so then I must really get my reading glasses on)
> - What will change with this new approach.
>
> I'm not really sure what you need from me now but I'm more than happy to
> share everything except the data it self :).
>
> The fork is places here:
> https://github.com/activitystream/drill/tree/lucene-work but no tests
> files are included in the repo, sorry, and this is all very immature.
>
> Regards,
> -Stefán
>
>
>
>
> On Sat, Jan 16, 2016 at 7:46 PM, Jacques Nadeau <ja...@dremio.com>
> wrote:
>
>> Closest things already done to date is the join pushdown in the jdbc
>> connector and the prototype code someone built a while back to do a join
>> using HBase as a hash table. Aman and I have an ongoing thread discussing
>> using elastic indexing and sideband communication to accelerate joins. If
>> would be great if you could cover exactly what you're doing (including
>> relevant stats), that would give us a better idea of how to point you in
>> the right direction.
>>
>> --
>> Jacques Nadeau
>> CTO and Co-Founder, Dremio
>>
>> On Sat, Jan 16, 2016 at 5:18 AM, Stefán Baxter <stefan@activitystream.com
>> >
>> wrote:
>>
>> > Hi,
>> >
>> > Can anyone point me to an implementation where joins are implemented
>> with
>> > full support for filters and efficient handling of joins based on
>> indexes.
>> >
>> > The only code I have come across all seems to rely on complete scan of
>> the
>> > related table and that is not acceptable for the use case we are
>> working on
>> > (Lucene reader).
>> >
>> > Regards,
>> > -Stefán
>> >
>>
>
>
Re: Efficient joins in Drill - avoiding the massive overhead of scan
based joins
Posted by Stefán Baxter <st...@activitystream.com>.
Hi,
All the numbers are smaller but the outcome is similar as in your
hypothetical.
A left-side query, without a join, takes takes <1 second (returns a few
thousand records from a few a collection of a few millions).
A right-side query, Lucene without a join, looking up a list of values
(using IN), returns a few hundred records in ~300ms.. (Lookup for low
cardinality fields in 9+ million metadata entries)
The same query run using joins takes ~18 seconds to execute as that is the
time it takes to iterate through all the records on the Lucene side.
The raw iteration speed of Lucene/Elastic/Solar is not really an issue when
executing a single query but it will become one for us if all joins require
a full scan and usage increases.
Where in the JDBC connector should I start looking for the filter pushdown
support for joins?
- Stefan
On Sun, Jan 17, 2016 at 7:20 PM, Jacques Nadeau <ja...@dremio.com> wrote:
> Can you give more detail about the join stats themselves? You also state
> 20x slower but I'm trying to understand what that means. 20x slower than
> what? Are you parallelizing the Lucene read or is this a single reader?
>
> For example:
>
> I have a join.
> The left side has a billion rows.
> The right side has 10 million rows.
> When applying the join condition, only 10k rows are needed from the right
> side.
>
> How long does it take to read a few million records from Lucene? (Recently
> with Elastic we've been seeing ~50-100k/second per thread when only
> retrieving a single stored field.)
>
> --
> Jacques Nadeau
> CTO and Co-Founder, Dremio
>
> On Sat, Jan 16, 2016 at 12:11 PM, Stefán Baxter <stefan@activitystream.com
> >
> wrote:
>
> > Hi Jacques,
> >
> > Thank you for taking the time, it's appreciated.
> >
> > I'm trying to contribute to the Lucene reader for Drill (Started by Rahul
> > Challapalli). We would like to use it for storage of metadata used in our
> > Drill setup.
> > This is perfectly suited for our needs as the metadata is already
> available
> > in Lucene document+indexes and it's tenant specific (So this is not the
> > global metadata that should reside in Postgres/HBase or something
> similar)
> >
> > I think it's best that I confess that I'm not sure what I'm looking for
> or
> > how to ask for it, at least not in proper Drill terms.
> >
> > The Lucene reader is working but the joins currently rely on full scan
> > which introduces ~20 time longer execution time on simple data sets (few
> > million records) so I need to get the index based joins going but I don't
> > know how.
> >
> > We have resources to do this now but our knowlidge of Drill is limited
> and
> > I could not, in my initial scan of the project, find any use
> > of DrillJoinRel that indicated indexes were involved (please forgive me
> if
> > this is a false assumption).
> >
> > Can you please clarify things for me a bit:
> >
> > - Is the JDBC connector already doing proper pushdown of filters for
> > joins? (If so then I must really get my reading glasses on)
> > - What will change with this new approach.
> >
> > I'm not really sure what you need from me now but I'm more than happy to
> > share everything except the data it self :).
> >
> > The fork is places here:
> > https://github.com/activitystream/drill/tree/lucene-work but no tests
> > files
> > are included in the repo, sorry, and this is all very immature.
> >
> > Regards,
> > -Stefán
> >
> >
> >
> >
> > On Sat, Jan 16, 2016 at 7:46 PM, Jacques Nadeau <ja...@dremio.com>
> > wrote:
> >
> > > Closest things already done to date is the join pushdown in the jdbc
> > > connector and the prototype code someone built a while back to do a
> join
> > > using HBase as a hash table. Aman and I have an ongoing thread
> discussing
> > > using elastic indexing and sideband communication to accelerate joins.
> If
> > > would be great if you could cover exactly what you're doing (including
> > > relevant stats), that would give us a better idea of how to point you
> in
> > > the right direction.
> > >
> > > --
> > > Jacques Nadeau
> > > CTO and Co-Founder, Dremio
> > >
> > > On Sat, Jan 16, 2016 at 5:18 AM, Stefán Baxter <
> > stefan@activitystream.com>
> > > wrote:
> > >
> > > > Hi,
> > > >
> > > > Can anyone point me to an implementation where joins are implemented
> > with
> > > > full support for filters and efficient handling of joins based on
> > > indexes.
> > > >
> > > > The only code I have come across all seems to rely on complete scan
> of
> > > the
> > > > related table and that is not acceptable for the use case we are
> > working
> > > on
> > > > (Lucene reader).
> > > >
> > > > Regards,
> > > > -Stefán
> > > >
> > >
> >
>
Re: Efficient joins in Drill - avoiding the massive overhead of scan
based joins
Posted by Stefán Baxter <st...@activitystream.com>.
Guys,
We are not dealing with the same data volume as you are, at least not for
on a single-tenant basis, but we are very execution-time sensitive.
Can you please help me implement this join filter pushdown properly, to
avoid these complete scans, by pointing me to the right examples so we can
continue with our Lucene based plan?
We are more than happy to sponsor this work and/or pay for professional
service to anyone that has the knowledge and the time to assist us.
Regards,
-Stefán
On Sun, Jan 17, 2016 at 7:50 PM, Stefán Baxter <st...@activitystream.com>
wrote:
> Hi Rahul,
>
> I'm aware of the segment parallelization and the option of rewriting the
> queries but I disagree with that being the best option.
>
> Since Drill supports push down of join filters I think our best option is
> to implement that in the Lucene reader.
>
> Rewriting the queries ma be a temporary option but we are already using
> sub queries for more complex things and I really need these simple lookup
> joins to be both simple and effective.
>
> - Stefan
>
> On Sun, Jan 17, 2016 at 7:44 PM, rahul challapalli <
> challapallirahul@gmail.com> wrote:
>
>> The level of parallelization in the lucene plugin is a segment.
>>
>> Stefan,
>>
>> I think it would be more accurate if you rewrite your join query so that
>> we
>> push the join keys into the lucene group scan and then compare the
>> numbers.
>> Something like the below
>>
>> select * from tbl1 a left join (select * from tbl2 where tbl2.col1 in
>> (select col1 from tbl1)) b where a.col1 = b.col1;
>>
>> - Rahul
>>
>> On Sun, Jan 17, 2016 at 11:20 AM, Jacques Nadeau <ja...@dremio.com>
>> wrote:
>>
>> > Can you give more detail about the join stats themselves? You also state
>> > 20x slower but I'm trying to understand what that means. 20x slower than
>> > what? Are you parallelizing the Lucene read or is this a single reader?
>> >
>> > For example:
>> >
>> > I have a join.
>> > The left side has a billion rows.
>> > The right side has 10 million rows.
>> > When applying the join condition, only 10k rows are needed from the
>> right
>> > side.
>> >
>> > How long does it take to read a few million records from Lucene?
>> (Recently
>> > with Elastic we've been seeing ~50-100k/second per thread when only
>> > retrieving a single stored field.)
>> >
>> > --
>> > Jacques Nadeau
>> > CTO and Co-Founder, Dremio
>> >
>> > On Sat, Jan 16, 2016 at 12:11 PM, Stefán Baxter <
>> stefan@activitystream.com
>> > >
>> > wrote:
>> >
>> > > Hi Jacques,
>> > >
>> > > Thank you for taking the time, it's appreciated.
>> > >
>> > > I'm trying to contribute to the Lucene reader for Drill (Started by
>> Rahul
>> > > Challapalli). We would like to use it for storage of metadata used in
>> our
>> > > Drill setup.
>> > > This is perfectly suited for our needs as the metadata is already
>> > available
>> > > in Lucene document+indexes and it's tenant specific (So this is not
>> the
>> > > global metadata that should reside in Postgres/HBase or something
>> > similar)
>> > >
>> > > I think it's best that I confess that I'm not sure what I'm looking
>> for
>> > or
>> > > how to ask for it, at least not in proper Drill terms.
>> > >
>> > > The Lucene reader is working but the joins currently rely on full scan
>> > > which introduces ~20 time longer execution time on simple data sets
>> (few
>> > > million records) so I need to get the index based joins going but I
>> don't
>> > > know how.
>> > >
>> > > We have resources to do this now but our knowlidge of Drill is limited
>> > and
>> > > I could not, in my initial scan of the project, find any use
>> > > of DrillJoinRel that indicated indexes were involved (please forgive
>> me
>> > if
>> > > this is a false assumption).
>> > >
>> > > Can you please clarify things for me a bit:
>> > >
>> > > - Is the JDBC connector already doing proper pushdown of filters
>> for
>> > > joins? (If so then I must really get my reading glasses on)
>> > > - What will change with this new approach.
>> > >
>> > > I'm not really sure what you need from me now but I'm more than happy
>> to
>> > > share everything except the data it self :).
>> > >
>> > > The fork is places here:
>> > > https://github.com/activitystream/drill/tree/lucene-work but no tests
>> > > files
>> > > are included in the repo, sorry, and this is all very immature.
>> > >
>> > > Regards,
>> > > -Stefán
>> > >
>> > >
>> > >
>> > >
>> > > On Sat, Jan 16, 2016 at 7:46 PM, Jacques Nadeau <ja...@dremio.com>
>> > > wrote:
>> > >
>> > > > Closest things already done to date is the join pushdown in the jdbc
>> > > > connector and the prototype code someone built a while back to do a
>> > join
>> > > > using HBase as a hash table. Aman and I have an ongoing thread
>> > discussing
>> > > > using elastic indexing and sideband communication to accelerate
>> joins.
>> > If
>> > > > would be great if you could cover exactly what you're doing
>> (including
>> > > > relevant stats), that would give us a better idea of how to point
>> you
>> > in
>> > > > the right direction.
>> > > >
>> > > > --
>> > > > Jacques Nadeau
>> > > > CTO and Co-Founder, Dremio
>> > > >
>> > > > On Sat, Jan 16, 2016 at 5:18 AM, Stefán Baxter <
>> > > stefan@activitystream.com>
>> > > > wrote:
>> > > >
>> > > > > Hi,
>> > > > >
>> > > > > Can anyone point me to an implementation where joins are
>> implemented
>> > > with
>> > > > > full support for filters and efficient handling of joins based on
>> > > > indexes.
>> > > > >
>> > > > > The only code I have come across all seems to rely on complete
>> scan
>> > of
>> > > > the
>> > > > > related table and that is not acceptable for the use case we are
>> > > working
>> > > > on
>> > > > > (Lucene reader).
>> > > > >
>> > > > > Regards,
>> > > > > -Stefán
>> > > > >
>> > > >
>> > >
>> >
>>
>
>
Re: Efficient joins in Drill - avoiding the massive overhead of scan
based joins
Posted by Stefán Baxter <st...@activitystream.com>.
Hi Rahul,
I'm aware of the segment parallelization and the option of rewriting the
queries but I disagree with that being the best option.
Since Drill supports push down of join filters I think our best option is
to implement that in the Lucene reader.
Rewriting the queries ma be a temporary option but we are already using sub
queries for more complex things and I really need these simple lookup joins
to be both simple and effective.
- Stefan
On Sun, Jan 17, 2016 at 7:44 PM, rahul challapalli <
challapallirahul@gmail.com> wrote:
> The level of parallelization in the lucene plugin is a segment.
>
> Stefan,
>
> I think it would be more accurate if you rewrite your join query so that we
> push the join keys into the lucene group scan and then compare the numbers.
> Something like the below
>
> select * from tbl1 a left join (select * from tbl2 where tbl2.col1 in
> (select col1 from tbl1)) b where a.col1 = b.col1;
>
> - Rahul
>
> On Sun, Jan 17, 2016 at 11:20 AM, Jacques Nadeau <ja...@dremio.com>
> wrote:
>
> > Can you give more detail about the join stats themselves? You also state
> > 20x slower but I'm trying to understand what that means. 20x slower than
> > what? Are you parallelizing the Lucene read or is this a single reader?
> >
> > For example:
> >
> > I have a join.
> > The left side has a billion rows.
> > The right side has 10 million rows.
> > When applying the join condition, only 10k rows are needed from the right
> > side.
> >
> > How long does it take to read a few million records from Lucene?
> (Recently
> > with Elastic we've been seeing ~50-100k/second per thread when only
> > retrieving a single stored field.)
> >
> > --
> > Jacques Nadeau
> > CTO and Co-Founder, Dremio
> >
> > On Sat, Jan 16, 2016 at 12:11 PM, Stefán Baxter <
> stefan@activitystream.com
> > >
> > wrote:
> >
> > > Hi Jacques,
> > >
> > > Thank you for taking the time, it's appreciated.
> > >
> > > I'm trying to contribute to the Lucene reader for Drill (Started by
> Rahul
> > > Challapalli). We would like to use it for storage of metadata used in
> our
> > > Drill setup.
> > > This is perfectly suited for our needs as the metadata is already
> > available
> > > in Lucene document+indexes and it's tenant specific (So this is not the
> > > global metadata that should reside in Postgres/HBase or something
> > similar)
> > >
> > > I think it's best that I confess that I'm not sure what I'm looking for
> > or
> > > how to ask for it, at least not in proper Drill terms.
> > >
> > > The Lucene reader is working but the joins currently rely on full scan
> > > which introduces ~20 time longer execution time on simple data sets
> (few
> > > million records) so I need to get the index based joins going but I
> don't
> > > know how.
> > >
> > > We have resources to do this now but our knowlidge of Drill is limited
> > and
> > > I could not, in my initial scan of the project, find any use
> > > of DrillJoinRel that indicated indexes were involved (please forgive me
> > if
> > > this is a false assumption).
> > >
> > > Can you please clarify things for me a bit:
> > >
> > > - Is the JDBC connector already doing proper pushdown of filters for
> > > joins? (If so then I must really get my reading glasses on)
> > > - What will change with this new approach.
> > >
> > > I'm not really sure what you need from me now but I'm more than happy
> to
> > > share everything except the data it self :).
> > >
> > > The fork is places here:
> > > https://github.com/activitystream/drill/tree/lucene-work but no tests
> > > files
> > > are included in the repo, sorry, and this is all very immature.
> > >
> > > Regards,
> > > -Stefán
> > >
> > >
> > >
> > >
> > > On Sat, Jan 16, 2016 at 7:46 PM, Jacques Nadeau <ja...@dremio.com>
> > > wrote:
> > >
> > > > Closest things already done to date is the join pushdown in the jdbc
> > > > connector and the prototype code someone built a while back to do a
> > join
> > > > using HBase as a hash table. Aman and I have an ongoing thread
> > discussing
> > > > using elastic indexing and sideband communication to accelerate
> joins.
> > If
> > > > would be great if you could cover exactly what you're doing
> (including
> > > > relevant stats), that would give us a better idea of how to point you
> > in
> > > > the right direction.
> > > >
> > > > --
> > > > Jacques Nadeau
> > > > CTO and Co-Founder, Dremio
> > > >
> > > > On Sat, Jan 16, 2016 at 5:18 AM, Stefán Baxter <
> > > stefan@activitystream.com>
> > > > wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Can anyone point me to an implementation where joins are
> implemented
> > > with
> > > > > full support for filters and efficient handling of joins based on
> > > > indexes.
> > > > >
> > > > > The only code I have come across all seems to rely on complete scan
> > of
> > > > the
> > > > > related table and that is not acceptable for the use case we are
> > > working
> > > > on
> > > > > (Lucene reader).
> > > > >
> > > > > Regards,
> > > > > -Stefán
> > > > >
> > > >
> > >
> >
>
Re: Efficient joins in Drill - avoiding the massive overhead of scan
based joins
Posted by rahul challapalli <ch...@gmail.com>.
The level of parallelization in the lucene plugin is a segment.
Stefan,
I think it would be more accurate if you rewrite your join query so that we
push the join keys into the lucene group scan and then compare the numbers.
Something like the below
select * from tbl1 a left join (select * from tbl2 where tbl2.col1 in
(select col1 from tbl1)) b where a.col1 = b.col1;
- Rahul
On Sun, Jan 17, 2016 at 11:20 AM, Jacques Nadeau <ja...@dremio.com> wrote:
> Can you give more detail about the join stats themselves? You also state
> 20x slower but I'm trying to understand what that means. 20x slower than
> what? Are you parallelizing the Lucene read or is this a single reader?
>
> For example:
>
> I have a join.
> The left side has a billion rows.
> The right side has 10 million rows.
> When applying the join condition, only 10k rows are needed from the right
> side.
>
> How long does it take to read a few million records from Lucene? (Recently
> with Elastic we've been seeing ~50-100k/second per thread when only
> retrieving a single stored field.)
>
> --
> Jacques Nadeau
> CTO and Co-Founder, Dremio
>
> On Sat, Jan 16, 2016 at 12:11 PM, Stefán Baxter <stefan@activitystream.com
> >
> wrote:
>
> > Hi Jacques,
> >
> > Thank you for taking the time, it's appreciated.
> >
> > I'm trying to contribute to the Lucene reader for Drill (Started by Rahul
> > Challapalli). We would like to use it for storage of metadata used in our
> > Drill setup.
> > This is perfectly suited for our needs as the metadata is already
> available
> > in Lucene document+indexes and it's tenant specific (So this is not the
> > global metadata that should reside in Postgres/HBase or something
> similar)
> >
> > I think it's best that I confess that I'm not sure what I'm looking for
> or
> > how to ask for it, at least not in proper Drill terms.
> >
> > The Lucene reader is working but the joins currently rely on full scan
> > which introduces ~20 time longer execution time on simple data sets (few
> > million records) so I need to get the index based joins going but I don't
> > know how.
> >
> > We have resources to do this now but our knowlidge of Drill is limited
> and
> > I could not, in my initial scan of the project, find any use
> > of DrillJoinRel that indicated indexes were involved (please forgive me
> if
> > this is a false assumption).
> >
> > Can you please clarify things for me a bit:
> >
> > - Is the JDBC connector already doing proper pushdown of filters for
> > joins? (If so then I must really get my reading glasses on)
> > - What will change with this new approach.
> >
> > I'm not really sure what you need from me now but I'm more than happy to
> > share everything except the data it self :).
> >
> > The fork is places here:
> > https://github.com/activitystream/drill/tree/lucene-work but no tests
> > files
> > are included in the repo, sorry, and this is all very immature.
> >
> > Regards,
> > -Stefán
> >
> >
> >
> >
> > On Sat, Jan 16, 2016 at 7:46 PM, Jacques Nadeau <ja...@dremio.com>
> > wrote:
> >
> > > Closest things already done to date is the join pushdown in the jdbc
> > > connector and the prototype code someone built a while back to do a
> join
> > > using HBase as a hash table. Aman and I have an ongoing thread
> discussing
> > > using elastic indexing and sideband communication to accelerate joins.
> If
> > > would be great if you could cover exactly what you're doing (including
> > > relevant stats), that would give us a better idea of how to point you
> in
> > > the right direction.
> > >
> > > --
> > > Jacques Nadeau
> > > CTO and Co-Founder, Dremio
> > >
> > > On Sat, Jan 16, 2016 at 5:18 AM, Stefán Baxter <
> > stefan@activitystream.com>
> > > wrote:
> > >
> > > > Hi,
> > > >
> > > > Can anyone point me to an implementation where joins are implemented
> > with
> > > > full support for filters and efficient handling of joins based on
> > > indexes.
> > > >
> > > > The only code I have come across all seems to rely on complete scan
> of
> > > the
> > > > related table and that is not acceptable for the use case we are
> > working
> > > on
> > > > (Lucene reader).
> > > >
> > > > Regards,
> > > > -Stefán
> > > >
> > >
> >
>
Re: Efficient joins in Drill - avoiding the massive overhead of scan
based joins
Posted by Jacques Nadeau <ja...@dremio.com>.
Can you give more detail about the join stats themselves? You also state
20x slower but I'm trying to understand what that means. 20x slower than
what? Are you parallelizing the Lucene read or is this a single reader?
For example:
I have a join.
The left side has a billion rows.
The right side has 10 million rows.
When applying the join condition, only 10k rows are needed from the right
side.
How long does it take to read a few million records from Lucene? (Recently
with Elastic we've been seeing ~50-100k/second per thread when only
retrieving a single stored field.)
--
Jacques Nadeau
CTO and Co-Founder, Dremio
On Sat, Jan 16, 2016 at 12:11 PM, Stefán Baxter <st...@activitystream.com>
wrote:
> Hi Jacques,
>
> Thank you for taking the time, it's appreciated.
>
> I'm trying to contribute to the Lucene reader for Drill (Started by Rahul
> Challapalli). We would like to use it for storage of metadata used in our
> Drill setup.
> This is perfectly suited for our needs as the metadata is already available
> in Lucene document+indexes and it's tenant specific (So this is not the
> global metadata that should reside in Postgres/HBase or something similar)
>
> I think it's best that I confess that I'm not sure what I'm looking for or
> how to ask for it, at least not in proper Drill terms.
>
> The Lucene reader is working but the joins currently rely on full scan
> which introduces ~20 time longer execution time on simple data sets (few
> million records) so I need to get the index based joins going but I don't
> know how.
>
> We have resources to do this now but our knowlidge of Drill is limited and
> I could not, in my initial scan of the project, find any use
> of DrillJoinRel that indicated indexes were involved (please forgive me if
> this is a false assumption).
>
> Can you please clarify things for me a bit:
>
> - Is the JDBC connector already doing proper pushdown of filters for
> joins? (If so then I must really get my reading glasses on)
> - What will change with this new approach.
>
> I'm not really sure what you need from me now but I'm more than happy to
> share everything except the data it self :).
>
> The fork is places here:
> https://github.com/activitystream/drill/tree/lucene-work but no tests
> files
> are included in the repo, sorry, and this is all very immature.
>
> Regards,
> -Stefán
>
>
>
>
> On Sat, Jan 16, 2016 at 7:46 PM, Jacques Nadeau <ja...@dremio.com>
> wrote:
>
> > Closest things already done to date is the join pushdown in the jdbc
> > connector and the prototype code someone built a while back to do a join
> > using HBase as a hash table. Aman and I have an ongoing thread discussing
> > using elastic indexing and sideband communication to accelerate joins. If
> > would be great if you could cover exactly what you're doing (including
> > relevant stats), that would give us a better idea of how to point you in
> > the right direction.
> >
> > --
> > Jacques Nadeau
> > CTO and Co-Founder, Dremio
> >
> > On Sat, Jan 16, 2016 at 5:18 AM, Stefán Baxter <
> stefan@activitystream.com>
> > wrote:
> >
> > > Hi,
> > >
> > > Can anyone point me to an implementation where joins are implemented
> with
> > > full support for filters and efficient handling of joins based on
> > indexes.
> > >
> > > The only code I have come across all seems to rely on complete scan of
> > the
> > > related table and that is not acceptable for the use case we are
> working
> > on
> > > (Lucene reader).
> > >
> > > Regards,
> > > -Stefán
> > >
> >
>
Re: Efficient joins in Drill - avoiding the massive overhead of scan
based joins
Posted by Stefán Baxter <st...@activitystream.com>.
Hi Jacques,
Thank you for taking the time, it's appreciated.
I'm trying to contribute to the Lucene reader for Drill (Started by Rahul
Challapalli). We would like to use it for storage of metadata used in our
Drill setup.
This is perfectly suited for our needs as the metadata is already available
in Lucene document+indexes and it's tenant specific (So this is not the
global metadata that should reside in Postgres/HBase or something similar)
I think it's best that I confess that I'm not sure what I'm looking for or
how to ask for it, at least not in proper Drill terms.
The Lucene reader is working but the joins currently rely on full scan
which introduces ~20 time longer execution time on simple data sets (few
million records) so I need to get the index based joins going but I don't
know how.
We have resources to do this now but our knowlidge of Drill is limited and
I could not, in my initial scan of the project, find any use
of DrillJoinRel that indicated indexes were involved (please forgive me if
this is a false assumption).
Can you please clarify things for me a bit:
- Is the JDBC connector already doing proper pushdown of filters for
joins? (If so then I must really get my reading glasses on)
- What will change with this new approach.
I'm not really sure what you need from me now but I'm more than happy to
share everything except the data it self :).
The fork is places here:
https://github.com/activitystream/drill/tree/lucene-work but no tests files
are included in the repo, sorry, and this is all very immature.
Regards,
-Stefán
On Sat, Jan 16, 2016 at 7:46 PM, Jacques Nadeau <ja...@dremio.com> wrote:
> Closest things already done to date is the join pushdown in the jdbc
> connector and the prototype code someone built a while back to do a join
> using HBase as a hash table. Aman and I have an ongoing thread discussing
> using elastic indexing and sideband communication to accelerate joins. If
> would be great if you could cover exactly what you're doing (including
> relevant stats), that would give us a better idea of how to point you in
> the right direction.
>
> --
> Jacques Nadeau
> CTO and Co-Founder, Dremio
>
> On Sat, Jan 16, 2016 at 5:18 AM, Stefán Baxter <st...@activitystream.com>
> wrote:
>
> > Hi,
> >
> > Can anyone point me to an implementation where joins are implemented with
> > full support for filters and efficient handling of joins based on
> indexes.
> >
> > The only code I have come across all seems to rely on complete scan of
> the
> > related table and that is not acceptable for the use case we are working
> on
> > (Lucene reader).
> >
> > Regards,
> > -Stefán
> >
>
Re: Efficient joins in Drill - avoiding the massive overhead of scan
based joins
Posted by Aman Sinha <am...@apache.org>.
DRILL-3929 covers the related discussion regarding secondary indexing.
Re: Efficient joins in Drill - avoiding the massive overhead of scan
based joins
Posted by Aman Sinha <am...@apache.org>.
DRILL-3929 covers the related discussion regarding secondary indexing.
Re: Efficient joins in Drill - avoiding the massive overhead of scan
based joins
Posted by Jacques Nadeau <ja...@dremio.com>.
Closest things already done to date is the join pushdown in the jdbc
connector and the prototype code someone built a while back to do a join
using HBase as a hash table. Aman and I have an ongoing thread discussing
using elastic indexing and sideband communication to accelerate joins. If
would be great if you could cover exactly what you're doing (including
relevant stats), that would give us a better idea of how to point you in
the right direction.
--
Jacques Nadeau
CTO and Co-Founder, Dremio
On Sat, Jan 16, 2016 at 5:18 AM, Stefán Baxter <st...@activitystream.com>
wrote:
> Hi,
>
> Can anyone point me to an implementation where joins are implemented with
> full support for filters and efficient handling of joins based on indexes.
>
> The only code I have come across all seems to rely on complete scan of the
> related table and that is not acceptable for the use case we are working on
> (Lucene reader).
>
> Regards,
> -Stefán
>
Re: Efficient joins in Drill - avoiding the massive overhead of scan
based joins
Posted by Jacques Nadeau <ja...@dremio.com>.
Closest things already done to date is the join pushdown in the jdbc
connector and the prototype code someone built a while back to do a join
using HBase as a hash table. Aman and I have an ongoing thread discussing
using elastic indexing and sideband communication to accelerate joins. If
would be great if you could cover exactly what you're doing (including
relevant stats), that would give us a better idea of how to point you in
the right direction.
--
Jacques Nadeau
CTO and Co-Founder, Dremio
On Sat, Jan 16, 2016 at 5:18 AM, Stefán Baxter <st...@activitystream.com>
wrote:
> Hi,
>
> Can anyone point me to an implementation where joins are implemented with
> full support for filters and efficient handling of joins based on indexes.
>
> The only code I have come across all seems to rely on complete scan of the
> related table and that is not acceptable for the use case we are working on
> (Lucene reader).
>
> Regards,
> -Stefán
>