You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by David Alves <da...@gmail.com> on 2013/04/21 05:39:38 UTC
question about schema
Hi Jacques
I'm implementing a ProjectFilter for HBase and I got to the point where I need to pass to HBase the fields that are required (even if it's simply "all" as in *).
How to know which fields to scan in the SE and their expected type?
There's a bunch of schema stuff in the org/apache/drill/exec/schema but I can't figure how SE uses that.
Will this info come inside the scan logical op in getReadEntries(Scan scan) (in the arbitrary "selection" section)?
Is this method still going to receive a logical Scan op or is this just a legacy stuff that you didn't have the chance to get to yet?
BatchSchema seems to only refer to field ids…
I'm thinking this is most likely because the work is still very much in progress but as I browse the code I can see you have put a lot of thought into almost everything even when it's not being used right now and I don't want to make any stupid assumption.
I can definitely make that info get to the SE iface myself just wondering how do you envision it should get there…
Best
David
Re: question about schema
Posted by Jacques Nadeau <ja...@apache.org>.
Try to flip the thinking about pushdown a little bit. Using a
approach that encapsulates transformations in optimization rules helps
to simplify other pieces of the puzzle. Optimization rules can expose
the types of relational patterns that they can transform (physical
operator specific) and then they internally manage the actual
transformation. Basically, storage engines expose something akin to
getOptimizationRules() that returns an ordered list of optimization
rules.
You can see an example of how optiq handles this here:
https://github.com/julianhyde/optiq-splunk/blob/master/src/main/java/net/hydromatic/optiq/impl/splunk/SplunkPushDownRule.java
The rules are what understand the specifics of the transformation
(converting one or multiple relations into another). They can
interact with the storage engine specialization/typed components of
the scan (e.g. what a column family is).
Initially, I liked the idea of Scan being the single top level concept
that contained Storage Engine specializations. However, I think the
typing model is too weak and confusing. We should probably just
specialize the Scan operator to have things like HBaseScan to simplify
later binding. (In optiq this is called a TableAccessRel.)
All this being said, it is likely that there will be a core set of
optimization rules which could be mostly the same and cloned for
different storage engines (e.g. filtering and field-level project).
J
On Sun, Apr 21, 2013 at 9:28 PM, David Alves <da...@gmail.com> wrote:
> Thank you for the sunday reply.
> Your overview was pretty much what I was assuming in general (the info comes inside the Scan).
> So in general the SE's optimizer rules will push the relevant ops inside the scan op, what gets pushed is, of course, SE dependent, but the definitions themselves are SE agnostic (just a bunch of physical ops that the SE will interpret internally).
> The Scan op that reaches the SE itself is physical OP correct (and not the current logical op)?
> We could even do something even a bit simpler like:
>
> ****POST-OPTIMIZATION****
>
> {
> @id:1,
> pop:"scan",
> storageengine:"hbase",
> internal: [{
> @id:2,
> child: 1,
> pop:"project",
> select: [
> {expr: "CAST('fam1.qual1', int)", mode: "VECTOR"},
> {expr: "CAST('fam1.qual2', nvarchar)", mode: "VECTOR"}
> ],
> output:[
> {mode: "VECTOR", type:"SQL_INT"}, // field 1
> {mode: "VECTOR", type:"SQL_NVARCHAR"} // field 2
> ]
> }],
> entries:[
> {locations: ["hserver1.local"], table: "donuts", regionId:"1234"},
> {locations: ["hserver2.local"], table: "donuts", regionId:"5678"}
> ],
> output:[ // matches the output of the last internal op so we might even refer to it directy
> {mode: "VECTOR", type:"SQL_INT"}, // output field 1
> is a value vector driven by expression 1.
> {mode: "VECTOR", type:"SQL_NVARCHAR"} // field 2
> ]
> },
>
> Final question, all of this will be "strongly typed" correct? I mean these will be properties of the Scan physical op and not arbitrary json that maps to an SE specific *InputConfig?
>
> -david
>
> On Apr 21, 2013, at 9:56 PM, Jacques Nadeau <ja...@apache.org> wrote:
>
>> Here is roughly what I'm thinking.
>>
>> Storage engines have three levels of configuration that they receive.
>>
>> 1. System-level configuration (such as picking the particular hbase
>> cluster). This is bound to StorageEngineConfig.
>> 2. Scan-node level configuration to be applied across read entries for
>> a particular scan entry. I'm currently modeling this as ScanSettings
>> locally.
>> 3. Read-level settings for a particular portion of a scan. (The
>> subdivision for parallelization.) (ReadEntry)
>>
>> Initially, a scan node will be used to describe an ambiguous scan
>> where the output is unknown. It is the project's responsibility to
>> convert the scan output into the desired schema. However, in the case
>> that a particular SE supports some level of projection, that
>> information would be pushed by the optimizer down to the scan node and
>> the projection node will be removed (or modified to simplify the
>> projection). The storage engine should be able to receive the
>> portions of the projection that it is responsible for via a TBD
>> interface that then writes the information to the ScanSettings or the
>> ReadEntry depending on where the Storage Engine wants it.
>>
>> Below I've given a rough example of what a pre-optimization and
>> post-optimization physical plan might look like. You can see how the
>> output of the scan changed and the addition of the storage engine
>> specific settings object.
>>
>>
>> ****PRE-OPTIMIZATION****
>> {
>> @id:1,
>> pop:"scan",
>> storageengine:"hbase",
>> entries:[
>> {locations: ["hserver1.local"], table: "donuts", regionId:"1234"},
>> {locations: ["hserver2.local"], table: "donuts", regionId:"5678"}
>> ],
>> output: [
>> {mode: "VECTOR", type: "MAP"} //field 1
>> ]
>> },
>> {
>> @id:2,
>> child: 1,
>> pop:"project",
>> select: [
>> {expr: "CAST('fam1.qual1', int)", mode: "VECTOR"},
>> {expr: "CAST('fam1.qual2', nvarchar)", mode: "VECTOR"}
>> ],
>> output:[
>> {mode: "VECTOR", type:"SQL_INT"}, // field 1
>> {mode: "VECTOR", type:"SQL_NVARCHAR"} // field 2
>> ]
>> },
>>
>>
>>
>> ****POST-OPTIMIZATION****
>>
>> {
>> @id:1,
>> pop:"scan",
>> storageengine:"hbase",
>> settings: {
>> fields: [
>> {family: "fam1", qualifier: "qual1", convert: "INT",
>> output-mode: "VECTOR"},
>> {family: "fam1", qualifier: "qual2", convert: "UTF8",
>> output-mode: "VECTOR"}
>> ]
>> },
>> entries:[
>> {locations: ["hserver1.local"], table: "donuts", regionId:"1234"},
>> {locations: ["hserver2.local"], table: "donuts", regionId:"5678"}
>> ],
>> output:[
>> {mode: "VECTOR", type:"SQL_INT"}, // output field 1
>> is a value vector driven by expression 1.
>> {mode: "VECTOR", type:"SQL_NVARCHAR"} // field 2
>> ]
>> },
>>
>>
>>
>> On Sat, Apr 20, 2013 at 10:45 PM, David Alves <da...@gmail.com> wrote:
>>>
>>> had a "duh" moment, realizing that, of course, I don't need a ProjectFilter as I can set the relevant cq's and cf's on HBase's Scan.
>>> the question or how to get the names of the columns the query is asking for or even "*" if that is the case, still stands though…
>>>
>>> -david
>>>
>>> On Apr 20, 2013, at 10:39 PM, David Alves <da...@gmail.com> wrote:
>>>
>>>> Hi Jacques
>>>>
>>>> I'm implementing a ProjectFilter for HBase and I got to the point where I need to pass to HBase the fields that are required (even if it's simply "all" as in *).
>>>> How to know which fields to scan in the SE and their expected type?
>>>> There's a bunch of schema stuff in the org/apache/drill/exec/schema but I can't figure how SE uses that.
>>>> Will this info come inside the scan logical op in getReadEntries(Scan scan) (in the arbitrary "selection" section)?
>>>> Is this method still going to receive a logical Scan op or is this just a legacy stuff that you didn't have the chance to get to yet?
>>>> BatchSchema seems to only refer to field ids…
>>>>
>>>> I'm thinking this is most likely because the work is still very much in progress but as I browse the code I can see you have put a lot of thought into almost everything even when it's not being used right now and I don't want to make any stupid assumption.
>>>> I can definitely make that info get to the SE iface myself just wondering how do you envision it should get there…
>>>>
>>>> Best
>>>> David
>>>>
>>>>
>>>>
>>>>
>>>
>
Re: question about schema
Posted by David Alves <da...@gmail.com>.
Thank you for the sunday reply.
Your overview was pretty much what I was assuming in general (the info comes inside the Scan).
So in general the SE's optimizer rules will push the relevant ops inside the scan op, what gets pushed is, of course, SE dependent, but the definitions themselves are SE agnostic (just a bunch of physical ops that the SE will interpret internally).
The Scan op that reaches the SE itself is physical OP correct (and not the current logical op)?
We could even do something even a bit simpler like:
****POST-OPTIMIZATION****
{
@id:1,
pop:"scan",
storageengine:"hbase",
internal: [{
@id:2,
child: 1,
pop:"project",
select: [
{expr: "CAST('fam1.qual1', int)", mode: "VECTOR"},
{expr: "CAST('fam1.qual2', nvarchar)", mode: "VECTOR"}
],
output:[
{mode: "VECTOR", type:"SQL_INT"}, // field 1
{mode: "VECTOR", type:"SQL_NVARCHAR"} // field 2
]
}],
entries:[
{locations: ["hserver1.local"], table: "donuts", regionId:"1234"},
{locations: ["hserver2.local"], table: "donuts", regionId:"5678"}
],
output:[ // matches the output of the last internal op so we might even refer to it directy
{mode: "VECTOR", type:"SQL_INT"}, // output field 1
is a value vector driven by expression 1.
{mode: "VECTOR", type:"SQL_NVARCHAR"} // field 2
]
},
Final question, all of this will be "strongly typed" correct? I mean these will be properties of the Scan physical op and not arbitrary json that maps to an SE specific *InputConfig?
-david
On Apr 21, 2013, at 9:56 PM, Jacques Nadeau <ja...@apache.org> wrote:
> Here is roughly what I'm thinking.
>
> Storage engines have three levels of configuration that they receive.
>
> 1. System-level configuration (such as picking the particular hbase
> cluster). This is bound to StorageEngineConfig.
> 2. Scan-node level configuration to be applied across read entries for
> a particular scan entry. I'm currently modeling this as ScanSettings
> locally.
> 3. Read-level settings for a particular portion of a scan. (The
> subdivision for parallelization.) (ReadEntry)
>
> Initially, a scan node will be used to describe an ambiguous scan
> where the output is unknown. It is the project's responsibility to
> convert the scan output into the desired schema. However, in the case
> that a particular SE supports some level of projection, that
> information would be pushed by the optimizer down to the scan node and
> the projection node will be removed (or modified to simplify the
> projection). The storage engine should be able to receive the
> portions of the projection that it is responsible for via a TBD
> interface that then writes the information to the ScanSettings or the
> ReadEntry depending on where the Storage Engine wants it.
>
> Below I've given a rough example of what a pre-optimization and
> post-optimization physical plan might look like. You can see how the
> output of the scan changed and the addition of the storage engine
> specific settings object.
>
>
> ****PRE-OPTIMIZATION****
> {
> @id:1,
> pop:"scan",
> storageengine:"hbase",
> entries:[
> {locations: ["hserver1.local"], table: "donuts", regionId:"1234"},
> {locations: ["hserver2.local"], table: "donuts", regionId:"5678"}
> ],
> output: [
> {mode: "VECTOR", type: "MAP"} //field 1
> ]
> },
> {
> @id:2,
> child: 1,
> pop:"project",
> select: [
> {expr: "CAST('fam1.qual1', int)", mode: "VECTOR"},
> {expr: "CAST('fam1.qual2', nvarchar)", mode: "VECTOR"}
> ],
> output:[
> {mode: "VECTOR", type:"SQL_INT"}, // field 1
> {mode: "VECTOR", type:"SQL_NVARCHAR"} // field 2
> ]
> },
>
>
>
> ****POST-OPTIMIZATION****
>
> {
> @id:1,
> pop:"scan",
> storageengine:"hbase",
> settings: {
> fields: [
> {family: "fam1", qualifier: "qual1", convert: "INT",
> output-mode: "VECTOR"},
> {family: "fam1", qualifier: "qual2", convert: "UTF8",
> output-mode: "VECTOR"}
> ]
> },
> entries:[
> {locations: ["hserver1.local"], table: "donuts", regionId:"1234"},
> {locations: ["hserver2.local"], table: "donuts", regionId:"5678"}
> ],
> output:[
> {mode: "VECTOR", type:"SQL_INT"}, // output field 1
> is a value vector driven by expression 1.
> {mode: "VECTOR", type:"SQL_NVARCHAR"} // field 2
> ]
> },
>
>
>
> On Sat, Apr 20, 2013 at 10:45 PM, David Alves <da...@gmail.com> wrote:
>>
>> had a "duh" moment, realizing that, of course, I don't need a ProjectFilter as I can set the relevant cq's and cf's on HBase's Scan.
>> the question or how to get the names of the columns the query is asking for or even "*" if that is the case, still stands though…
>>
>> -david
>>
>> On Apr 20, 2013, at 10:39 PM, David Alves <da...@gmail.com> wrote:
>>
>>> Hi Jacques
>>>
>>> I'm implementing a ProjectFilter for HBase and I got to the point where I need to pass to HBase the fields that are required (even if it's simply "all" as in *).
>>> How to know which fields to scan in the SE and their expected type?
>>> There's a bunch of schema stuff in the org/apache/drill/exec/schema but I can't figure how SE uses that.
>>> Will this info come inside the scan logical op in getReadEntries(Scan scan) (in the arbitrary "selection" section)?
>>> Is this method still going to receive a logical Scan op or is this just a legacy stuff that you didn't have the chance to get to yet?
>>> BatchSchema seems to only refer to field ids…
>>>
>>> I'm thinking this is most likely because the work is still very much in progress but as I browse the code I can see you have put a lot of thought into almost everything even when it's not being used right now and I don't want to make any stupid assumption.
>>> I can definitely make that info get to the SE iface myself just wondering how do you envision it should get there…
>>>
>>> Best
>>> David
>>>
>>>
>>>
>>>
>>
Re: question about schema
Posted by Jacques Nadeau <ja...@apache.org>.
Here is roughly what I'm thinking.
Storage engines have three levels of configuration that they receive.
1. System-level configuration (such as picking the particular hbase
cluster). This is bound to StorageEngineConfig.
2. Scan-node level configuration to be applied across read entries for
a particular scan entry. I'm currently modeling this as ScanSettings
locally.
3. Read-level settings for a particular portion of a scan. (The
subdivision for parallelization.) (ReadEntry)
Initially, a scan node will be used to describe an ambiguous scan
where the output is unknown. It is the project's responsibility to
convert the scan output into the desired schema. However, in the case
that a particular SE supports some level of projection, that
information would be pushed by the optimizer down to the scan node and
the projection node will be removed (or modified to simplify the
projection). The storage engine should be able to receive the
portions of the projection that it is responsible for via a TBD
interface that then writes the information to the ScanSettings or the
ReadEntry depending on where the Storage Engine wants it.
Below I've given a rough example of what a pre-optimization and
post-optimization physical plan might look like. You can see how the
output of the scan changed and the addition of the storage engine
specific settings object.
****PRE-OPTIMIZATION****
{
@id:1,
pop:"scan",
storageengine:"hbase",
entries:[
{locations: ["hserver1.local"], table: "donuts", regionId:"1234"},
{locations: ["hserver2.local"], table: "donuts", regionId:"5678"}
],
output: [
{mode: "VECTOR", type: "MAP"} //field 1
]
},
{
@id:2,
child: 1,
pop:"project",
select: [
{expr: "CAST('fam1.qual1', int)", mode: "VECTOR"},
{expr: "CAST('fam1.qual2', nvarchar)", mode: "VECTOR"}
],
output:[
{mode: "VECTOR", type:"SQL_INT"}, // field 1
{mode: "VECTOR", type:"SQL_NVARCHAR"} // field 2
]
},
****POST-OPTIMIZATION****
{
@id:1,
pop:"scan",
storageengine:"hbase",
settings: {
fields: [
{family: "fam1", qualifier: "qual1", convert: "INT",
output-mode: "VECTOR"},
{family: "fam1", qualifier: "qual2", convert: "UTF8",
output-mode: "VECTOR"}
]
},
entries:[
{locations: ["hserver1.local"], table: "donuts", regionId:"1234"},
{locations: ["hserver2.local"], table: "donuts", regionId:"5678"}
],
output:[
{mode: "VECTOR", type:"SQL_INT"}, // output field 1
is a value vector driven by expression 1.
{mode: "VECTOR", type:"SQL_NVARCHAR"} // field 2
]
},
On Sat, Apr 20, 2013 at 10:45 PM, David Alves <da...@gmail.com> wrote:
>
> had a "duh" moment, realizing that, of course, I don't need a ProjectFilter as I can set the relevant cq's and cf's on HBase's Scan.
> the question or how to get the names of the columns the query is asking for or even "*" if that is the case, still stands though…
>
> -david
>
> On Apr 20, 2013, at 10:39 PM, David Alves <da...@gmail.com> wrote:
>
> > Hi Jacques
> >
> > I'm implementing a ProjectFilter for HBase and I got to the point where I need to pass to HBase the fields that are required (even if it's simply "all" as in *).
> > How to know which fields to scan in the SE and their expected type?
> > There's a bunch of schema stuff in the org/apache/drill/exec/schema but I can't figure how SE uses that.
> > Will this info come inside the scan logical op in getReadEntries(Scan scan) (in the arbitrary "selection" section)?
> > Is this method still going to receive a logical Scan op or is this just a legacy stuff that you didn't have the chance to get to yet?
> > BatchSchema seems to only refer to field ids…
> >
> > I'm thinking this is most likely because the work is still very much in progress but as I browse the code I can see you have put a lot of thought into almost everything even when it's not being used right now and I don't want to make any stupid assumption.
> > I can definitely make that info get to the SE iface myself just wondering how do you envision it should get there…
> >
> > Best
> > David
> >
> >
> >
> >
>
Re: question about schema
Posted by Lisen Mu <im...@gmail.com>.
David,
Thanks, I'm willing to help.
Sorry I missed the conclusion in jira. Thanks for the explanation, I guess
further push from you and Jacques would make things clearer.
On Mon, Apr 22, 2013 at 12:47 PM, David Alves <da...@gmail.com> wrote:
> Lisen
>
> Ah, got what you mean by encoding mutliple fields into rowkey.
> Well that makes projection trickier, but still definitely possible
> to do with Filters.
> As soon as I get something reasonable working I'll push it and I
> welcome your help in dealing with that particular situation and any others
> you can come up with.
>
> With regard to pushdown after a bit of the discussion in the SE
> jira (I forget the number) the consensus seems to be that the SE advertises
> opaque OptimizerRules that the optimizer runs.
> These can for instance, push the project in Jacques example inside
> the scan, or change the order of ops.
> In general I can see the case where a typical RDBMS would publish
> multiple rules (for agg, proj, select, even join) which, when run by the
> optimizer would go through the ops directly above the scan and keep pushing
> most inside the scan until there is either nothing left but the sink and
> the scan (and not even the sink if it goes into the same data source) or
> there's a multi-branch multi-data source op such as union or join.
> All of there are inside the Scan physical op (and are SE agnostic
> up to this point).
> So the physical plan portion to be executed by the SE is actually
> inside the scan op.
> At least this is how I'm thinking about it right now…
>
> Best
> David
>
>
> On Apr 21, 2013, at 11:29 PM, Lisen Mu <im...@gmail.com> wrote:
>
> > David,
> >
> > Suppose we have planned to use domainId+uid+timestamp as my HTable
> rowkey.
> >
> > I wish to retrieve uid portion from my rowkey, like:
> >
> > SELECT distinct(uid) from `my_table` where xxx
> >
> > Or, I wish I can do:
> >
> > a) SELECT xxx from `my_table` where domainId='a'
> > b) SELECT xxx from `my_table` where uid='a@abc.com'
> >
> > And HBase SE would determine the best startKey and endKey according to
> > rowkey definition info, so a) and b) would get different performance.
> >
> >> about selection/Filter & aggregation:
> >
> > I have too many questions that I feel it be better to wait your HBase SE
> > first... However:
> >
> > How to push down aggregation and selection into scan pop?
> >
> > @Jacques, It seems to me that your idea is to use a scan pop node to
> > describe what SE would do in a query, right?
> >
> > Would scan pop become a little too complicated if scan pop stay SE
> > independent? Since mysql & mongo need more for scan pop.
> >
> > Previously I thought you would provide something like
> >
> > RecordReader getReader(PhysicalPlan subPlan)
> >
> > SE advertises ability back to drill, drill push part of physical plan to
> SE
> > and let SE figure out how to deal with the subdag as long as SE can
> provide
> > correct RecordBatch.
> >
> >
> >
> >
> >
> > On Mon, Apr 22, 2013 at 12:06 PM, David Alves <da...@gmail.com>
> wrote:
> >
> >> Hi Lisen
> >>
> >> Phoenix has been a good source of inspiration.
> >> Had it not been for license issues (non-standard license) and the
> >> fact it is designed to run locally I would have used it directly
> instead of
> >> coding my own.
> >> Not completely sure what you mean wrt to "map fields in the query
> >> into portion of rowkey in HBase" but here's what I'm doing with regard
> to
> >> the operations that are pushed to HBase:
> >>
> >> Projection comes from setting the interesting CF's and CQ's in
> the
> >> Scan prior to starting it (where those come from in drill was the reason
> >> for my previous email).
> >> Selection comes from setting Filters that are created directly
> >> form expresssions in drlll and are submitted with the scan.
> >> Partial Aggregation (which I'm not doing right now but will do
> >> soon ) will come from co-processors.
> >> Joins: I'm investigating a couple on pushing some of the work to
> >> hbase.
> >>
> >> All the remaining operations will happen within drill itself.
> >>
> >> Best
> >> David
> >>
> >> On Apr 21, 2013, at 10:45 PM, Lisen Mu <im...@gmail.com> wrote:
> >>
> >>> David,
> >>>
> >>> Another case about schema: how to map fields in the query into portion
> of
> >>> rowkey in HBase? Like phoenix does.
> >>> http://files.meetup.com/1350427/IntelPhoenixHBaseMeetup.ppt
> >>>
> >>> I think it might be common in HBase schema design that several logical
> >>> parts form rowkey in a particular order for the most frequent access
> >>> pattern.
> >>>
> >>>
> >>>
> >>>
> >>> On Sun, Apr 21, 2013 at 1:45 PM, David Alves <da...@gmail.com>
> >> wrote:
> >>>
> >>>> had a "duh" moment, realizing that, of course, I don't need a
> >>>> ProjectFilter as I can set the relevant cq's and cf's on HBase's Scan.
> >>>> the question or how to get the names of the columns the query is
> asking
> >>>> for or even "*" if that is the case, still stands though…
> >>>>
> >>>> -david
> >>>>
> >>>> On Apr 20, 2013, at 10:39 PM, David Alves <da...@gmail.com>
> >> wrote:
> >>>>
> >>>>> Hi Jacques
> >>>>>
> >>>>> I'm implementing a ProjectFilter for HBase and I got to the point
> >>>> where I need to pass to HBase the fields that are required (even if
> it's
> >>>> simply "all" as in *).
> >>>>> How to know which fields to scan in the SE and their expected
> >> type?
> >>>>> There's a bunch of schema stuff in the
> >>>> org/apache/drill/exec/schema but I can't figure how SE uses that.
> >>>>> Will this info come inside the scan logical op in
> >>>> getReadEntries(Scan scan) (in the arbitrary "selection" section)?
> >>>>> Is this method still going to receive a logical Scan op or is
> this
> >>>> just a legacy stuff that you didn't have the chance to get to yet?
> >>>>> BatchSchema seems to only refer to field ids…
> >>>>>
> >>>>> I'm thinking this is most likely because the work is still very
> >>>> much in progress but as I browse the code I can see you have put a lot
> >> of
> >>>> thought into almost everything even when it's not being used right now
> >> and
> >>>> I don't want to make any stupid assumption.
> >>>>> I can definitely make that info get to the SE iface myself just
> >>>> wondering how do you envision it should get there…
> >>>>>
> >>>>> Best
> >>>>> David
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>
> >>>>
> >>
> >>
>
>
Re: question about schema
Posted by David Alves <da...@gmail.com>.
Lisen
Ah, got what you mean by encoding mutliple fields into rowkey.
Well that makes projection trickier, but still definitely possible to do with Filters.
As soon as I get something reasonable working I'll push it and I welcome your help in dealing with that particular situation and any others you can come up with.
With regard to pushdown after a bit of the discussion in the SE jira (I forget the number) the consensus seems to be that the SE advertises opaque OptimizerRules that the optimizer runs.
These can for instance, push the project in Jacques example inside the scan, or change the order of ops.
In general I can see the case where a typical RDBMS would publish multiple rules (for agg, proj, select, even join) which, when run by the optimizer would go through the ops directly above the scan and keep pushing most inside the scan until there is either nothing left but the sink and the scan (and not even the sink if it goes into the same data source) or there's a multi-branch multi-data source op such as union or join.
All of there are inside the Scan physical op (and are SE agnostic up to this point).
So the physical plan portion to be executed by the SE is actually inside the scan op.
At least this is how I'm thinking about it right now…
Best
David
On Apr 21, 2013, at 11:29 PM, Lisen Mu <im...@gmail.com> wrote:
> David,
>
> Suppose we have planned to use domainId+uid+timestamp as my HTable rowkey.
>
> I wish to retrieve uid portion from my rowkey, like:
>
> SELECT distinct(uid) from `my_table` where xxx
>
> Or, I wish I can do:
>
> a) SELECT xxx from `my_table` where domainId='a'
> b) SELECT xxx from `my_table` where uid='a@abc.com'
>
> And HBase SE would determine the best startKey and endKey according to
> rowkey definition info, so a) and b) would get different performance.
>
>> about selection/Filter & aggregation:
>
> I have too many questions that I feel it be better to wait your HBase SE
> first... However:
>
> How to push down aggregation and selection into scan pop?
>
> @Jacques, It seems to me that your idea is to use a scan pop node to
> describe what SE would do in a query, right?
>
> Would scan pop become a little too complicated if scan pop stay SE
> independent? Since mysql & mongo need more for scan pop.
>
> Previously I thought you would provide something like
>
> RecordReader getReader(PhysicalPlan subPlan)
>
> SE advertises ability back to drill, drill push part of physical plan to SE
> and let SE figure out how to deal with the subdag as long as SE can provide
> correct RecordBatch.
>
>
>
>
>
> On Mon, Apr 22, 2013 at 12:06 PM, David Alves <da...@gmail.com> wrote:
>
>> Hi Lisen
>>
>> Phoenix has been a good source of inspiration.
>> Had it not been for license issues (non-standard license) and the
>> fact it is designed to run locally I would have used it directly instead of
>> coding my own.
>> Not completely sure what you mean wrt to "map fields in the query
>> into portion of rowkey in HBase" but here's what I'm doing with regard to
>> the operations that are pushed to HBase:
>>
>> Projection comes from setting the interesting CF's and CQ's in the
>> Scan prior to starting it (where those come from in drill was the reason
>> for my previous email).
>> Selection comes from setting Filters that are created directly
>> form expresssions in drlll and are submitted with the scan.
>> Partial Aggregation (which I'm not doing right now but will do
>> soon ) will come from co-processors.
>> Joins: I'm investigating a couple on pushing some of the work to
>> hbase.
>>
>> All the remaining operations will happen within drill itself.
>>
>> Best
>> David
>>
>> On Apr 21, 2013, at 10:45 PM, Lisen Mu <im...@gmail.com> wrote:
>>
>>> David,
>>>
>>> Another case about schema: how to map fields in the query into portion of
>>> rowkey in HBase? Like phoenix does.
>>> http://files.meetup.com/1350427/IntelPhoenixHBaseMeetup.ppt
>>>
>>> I think it might be common in HBase schema design that several logical
>>> parts form rowkey in a particular order for the most frequent access
>>> pattern.
>>>
>>>
>>>
>>>
>>> On Sun, Apr 21, 2013 at 1:45 PM, David Alves <da...@gmail.com>
>> wrote:
>>>
>>>> had a "duh" moment, realizing that, of course, I don't need a
>>>> ProjectFilter as I can set the relevant cq's and cf's on HBase's Scan.
>>>> the question or how to get the names of the columns the query is asking
>>>> for or even "*" if that is the case, still stands though…
>>>>
>>>> -david
>>>>
>>>> On Apr 20, 2013, at 10:39 PM, David Alves <da...@gmail.com>
>> wrote:
>>>>
>>>>> Hi Jacques
>>>>>
>>>>> I'm implementing a ProjectFilter for HBase and I got to the point
>>>> where I need to pass to HBase the fields that are required (even if it's
>>>> simply "all" as in *).
>>>>> How to know which fields to scan in the SE and their expected
>> type?
>>>>> There's a bunch of schema stuff in the
>>>> org/apache/drill/exec/schema but I can't figure how SE uses that.
>>>>> Will this info come inside the scan logical op in
>>>> getReadEntries(Scan scan) (in the arbitrary "selection" section)?
>>>>> Is this method still going to receive a logical Scan op or is this
>>>> just a legacy stuff that you didn't have the chance to get to yet?
>>>>> BatchSchema seems to only refer to field ids…
>>>>>
>>>>> I'm thinking this is most likely because the work is still very
>>>> much in progress but as I browse the code I can see you have put a lot
>> of
>>>> thought into almost everything even when it's not being used right now
>> and
>>>> I don't want to make any stupid assumption.
>>>>> I can definitely make that info get to the SE iface myself just
>>>> wondering how do you envision it should get there…
>>>>>
>>>>> Best
>>>>> David
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>
>>
Re: question about schema
Posted by Lisen Mu <im...@gmail.com>.
David,
Suppose we have planned to use domainId+uid+timestamp as my HTable rowkey.
I wish to retrieve uid portion from my rowkey, like:
SELECT distinct(uid) from `my_table` where xxx
Or, I wish I can do:
a) SELECT xxx from `my_table` where domainId='a'
b) SELECT xxx from `my_table` where uid='a@abc.com'
And HBase SE would determine the best startKey and endKey according to
rowkey definition info, so a) and b) would get different performance.
> about selection/Filter & aggregation:
I have too many questions that I feel it be better to wait your HBase SE
first... However:
How to push down aggregation and selection into scan pop?
@Jacques, It seems to me that your idea is to use a scan pop node to
describe what SE would do in a query, right?
Would scan pop become a little too complicated if scan pop stay SE
independent? Since mysql & mongo need more for scan pop.
Previously I thought you would provide something like
RecordReader getReader(PhysicalPlan subPlan)
SE advertises ability back to drill, drill push part of physical plan to SE
and let SE figure out how to deal with the subdag as long as SE can provide
correct RecordBatch.
On Mon, Apr 22, 2013 at 12:06 PM, David Alves <da...@gmail.com> wrote:
> Hi Lisen
>
> Phoenix has been a good source of inspiration.
> Had it not been for license issues (non-standard license) and the
> fact it is designed to run locally I would have used it directly instead of
> coding my own.
> Not completely sure what you mean wrt to "map fields in the query
> into portion of rowkey in HBase" but here's what I'm doing with regard to
> the operations that are pushed to HBase:
>
> Projection comes from setting the interesting CF's and CQ's in the
> Scan prior to starting it (where those come from in drill was the reason
> for my previous email).
> Selection comes from setting Filters that are created directly
> form expresssions in drlll and are submitted with the scan.
> Partial Aggregation (which I'm not doing right now but will do
> soon ) will come from co-processors.
> Joins: I'm investigating a couple on pushing some of the work to
> hbase.
>
> All the remaining operations will happen within drill itself.
>
> Best
> David
>
> On Apr 21, 2013, at 10:45 PM, Lisen Mu <im...@gmail.com> wrote:
>
> > David,
> >
> > Another case about schema: how to map fields in the query into portion of
> > rowkey in HBase? Like phoenix does.
> > http://files.meetup.com/1350427/IntelPhoenixHBaseMeetup.ppt
> >
> > I think it might be common in HBase schema design that several logical
> > parts form rowkey in a particular order for the most frequent access
> > pattern.
> >
> >
> >
> >
> > On Sun, Apr 21, 2013 at 1:45 PM, David Alves <da...@gmail.com>
> wrote:
> >
> >> had a "duh" moment, realizing that, of course, I don't need a
> >> ProjectFilter as I can set the relevant cq's and cf's on HBase's Scan.
> >> the question or how to get the names of the columns the query is asking
> >> for or even "*" if that is the case, still stands though…
> >>
> >> -david
> >>
> >> On Apr 20, 2013, at 10:39 PM, David Alves <da...@gmail.com>
> wrote:
> >>
> >>> Hi Jacques
> >>>
> >>> I'm implementing a ProjectFilter for HBase and I got to the point
> >> where I need to pass to HBase the fields that are required (even if it's
> >> simply "all" as in *).
> >>> How to know which fields to scan in the SE and their expected
> type?
> >>> There's a bunch of schema stuff in the
> >> org/apache/drill/exec/schema but I can't figure how SE uses that.
> >>> Will this info come inside the scan logical op in
> >> getReadEntries(Scan scan) (in the arbitrary "selection" section)?
> >>> Is this method still going to receive a logical Scan op or is this
> >> just a legacy stuff that you didn't have the chance to get to yet?
> >>> BatchSchema seems to only refer to field ids…
> >>>
> >>> I'm thinking this is most likely because the work is still very
> >> much in progress but as I browse the code I can see you have put a lot
> of
> >> thought into almost everything even when it's not being used right now
> and
> >> I don't want to make any stupid assumption.
> >>> I can definitely make that info get to the SE iface myself just
> >> wondering how do you envision it should get there…
> >>>
> >>> Best
> >>> David
> >>>
> >>>
> >>>
> >>>
> >>
> >>
>
>
Re: question about schema
Posted by David Alves <da...@gmail.com>.
Hi Lisen
Phoenix has been a good source of inspiration.
Had it not been for license issues (non-standard license) and the fact it is designed to run locally I would have used it directly instead of coding my own.
Not completely sure what you mean wrt to "map fields in the query into portion of rowkey in HBase" but here's what I'm doing with regard to the operations that are pushed to HBase:
Projection comes from setting the interesting CF's and CQ's in the Scan prior to starting it (where those come from in drill was the reason for my previous email).
Selection comes from setting Filters that are created directly form expresssions in drlll and are submitted with the scan.
Partial Aggregation (which I'm not doing right now but will do soon ) will come from co-processors.
Joins: I'm investigating a couple on pushing some of the work to hbase.
All the remaining operations will happen within drill itself.
Best
David
On Apr 21, 2013, at 10:45 PM, Lisen Mu <im...@gmail.com> wrote:
> David,
>
> Another case about schema: how to map fields in the query into portion of
> rowkey in HBase? Like phoenix does.
> http://files.meetup.com/1350427/IntelPhoenixHBaseMeetup.ppt
>
> I think it might be common in HBase schema design that several logical
> parts form rowkey in a particular order for the most frequent access
> pattern.
>
>
>
>
> On Sun, Apr 21, 2013 at 1:45 PM, David Alves <da...@gmail.com> wrote:
>
>> had a "duh" moment, realizing that, of course, I don't need a
>> ProjectFilter as I can set the relevant cq's and cf's on HBase's Scan.
>> the question or how to get the names of the columns the query is asking
>> for or even "*" if that is the case, still stands though…
>>
>> -david
>>
>> On Apr 20, 2013, at 10:39 PM, David Alves <da...@gmail.com> wrote:
>>
>>> Hi Jacques
>>>
>>> I'm implementing a ProjectFilter for HBase and I got to the point
>> where I need to pass to HBase the fields that are required (even if it's
>> simply "all" as in *).
>>> How to know which fields to scan in the SE and their expected type?
>>> There's a bunch of schema stuff in the
>> org/apache/drill/exec/schema but I can't figure how SE uses that.
>>> Will this info come inside the scan logical op in
>> getReadEntries(Scan scan) (in the arbitrary "selection" section)?
>>> Is this method still going to receive a logical Scan op or is this
>> just a legacy stuff that you didn't have the chance to get to yet?
>>> BatchSchema seems to only refer to field ids…
>>>
>>> I'm thinking this is most likely because the work is still very
>> much in progress but as I browse the code I can see you have put a lot of
>> thought into almost everything even when it's not being used right now and
>> I don't want to make any stupid assumption.
>>> I can definitely make that info get to the SE iface myself just
>> wondering how do you envision it should get there…
>>>
>>> Best
>>> David
>>>
>>>
>>>
>>>
>>
>>
Re: question about schema
Posted by Lisen Mu <im...@gmail.com>.
David,
Another case about schema: how to map fields in the query into portion of
rowkey in HBase? Like phoenix does.
http://files.meetup.com/1350427/IntelPhoenixHBaseMeetup.ppt
I think it might be common in HBase schema design that several logical
parts form rowkey in a particular order for the most frequent access
pattern.
On Sun, Apr 21, 2013 at 1:45 PM, David Alves <da...@gmail.com> wrote:
> had a "duh" moment, realizing that, of course, I don't need a
> ProjectFilter as I can set the relevant cq's and cf's on HBase's Scan.
> the question or how to get the names of the columns the query is asking
> for or even "*" if that is the case, still stands though…
>
> -david
>
> On Apr 20, 2013, at 10:39 PM, David Alves <da...@gmail.com> wrote:
>
> > Hi Jacques
> >
> > I'm implementing a ProjectFilter for HBase and I got to the point
> where I need to pass to HBase the fields that are required (even if it's
> simply "all" as in *).
> > How to know which fields to scan in the SE and their expected type?
> > There's a bunch of schema stuff in the
> org/apache/drill/exec/schema but I can't figure how SE uses that.
> > Will this info come inside the scan logical op in
> getReadEntries(Scan scan) (in the arbitrary "selection" section)?
> > Is this method still going to receive a logical Scan op or is this
> just a legacy stuff that you didn't have the chance to get to yet?
> > BatchSchema seems to only refer to field ids…
> >
> > I'm thinking this is most likely because the work is still very
> much in progress but as I browse the code I can see you have put a lot of
> thought into almost everything even when it's not being used right now and
> I don't want to make any stupid assumption.
> > I can definitely make that info get to the SE iface myself just
> wondering how do you envision it should get there…
> >
> > Best
> > David
> >
> >
> >
> >
>
>
Re: question about schema
Posted by David Alves <da...@gmail.com>.
had a "duh" moment, realizing that, of course, I don't need a ProjectFilter as I can set the relevant cq's and cf's on HBase's Scan.
the question or how to get the names of the columns the query is asking for or even "*" if that is the case, still stands though…
-david
On Apr 20, 2013, at 10:39 PM, David Alves <da...@gmail.com> wrote:
> Hi Jacques
>
> I'm implementing a ProjectFilter for HBase and I got to the point where I need to pass to HBase the fields that are required (even if it's simply "all" as in *).
> How to know which fields to scan in the SE and their expected type?
> There's a bunch of schema stuff in the org/apache/drill/exec/schema but I can't figure how SE uses that.
> Will this info come inside the scan logical op in getReadEntries(Scan scan) (in the arbitrary "selection" section)?
> Is this method still going to receive a logical Scan op or is this just a legacy stuff that you didn't have the chance to get to yet?
> BatchSchema seems to only refer to field ids…
>
> I'm thinking this is most likely because the work is still very much in progress but as I browse the code I can see you have put a lot of thought into almost everything even when it's not being used right now and I don't want to make any stupid assumption.
> I can definitely make that info get to the SE iface myself just wondering how do you envision it should get there…
>
> Best
> David
>
>
>
>