You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Andries Engelbrecht <ae...@maprtech.com> on 2015/02/04 02:27:12 UTC
Directory pruning with Drill
Is it required for the directory pruning to work that a top down filter of directories be applied?
My current observation is that for a directory structure as listed below, the pruning only works if the full tree is provided. If only a lower level directory is supplied in the filter condition Drill only uses it as a filter.
/2015
/01
/10
/11
/12
/13
/14
select count(id) from `/foo` t where dir0='2015' and dir1='01' and dir2='10'
Produces the correct pruning and query plan
01-02 Project(id=[$3]): rowcount = 3670316.0, cumulative cost = {1.1010948E7 rows, 1.4681284E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 28434
01-03 Project(dir0=[$0], dir1=[$3], dir2=[$2], id=[$1]): rowcount = 3670316.0, cumulative cost = {7340632.0 rows, 1.468128E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 28433
01-04 Scan(groupscan=[EasyGroupScan [selectionRoot=/foo, numFiles=24, columns=[`dir0`, `dir1`, `dir2`, `id`]
However
select count(id) from `/foo` t where dir2='10'
Produces full scan of all sub directories and only applies a filter condition after the fact. Notice the numFiles between the 2, even though it lists columns in the base scan
01-04 Filter(condition=[=($0, '10')]): rowcount = 9423761.7, cumulative cost = {1.88475234E8 rows, 3.76950476E8 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 27470
01-05 Project(dir2=[$1], id=[$0]): rowcount = 6.2825078E7, cumulative cost = {1.25650156E8 rows, 1.25650164E8 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 27469
01-06 Scan(groupscan=[EasyGroupScan [selectionRoot=/foo, numFiles=405, columns=[`dir2`, `id`]
Any thoughts?
Thanks
—Andries
Re: Directory pruning with Drill
Posted by Andries Engelbrecht <ae...@maprtech.com>.
Aman,
In my case I can clearly see that with numerics for the directory name pruning is not performed when quotes were not applied.
This is for JSON, which should not matter.
select count(id) from tweet_base where dir_year=2015 and dir_month=01 and dir_day=10
01-04 Filter(condition=[AND(=(CAST($1):ANY NOT NULL, 2015), =(CAST($2):ANY NOT NULL, 1), =(CAST($3):ANY NOT NULL, 10))]): rowcount = 212034.63825, cumulative cost = {1.88475234E8 rows, 1.005201264E9 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 34910
01-05 Project(id=[$2], dir0=[$3], dir1=[$1], dir2=[$0]): rowcount = 6.2825078E7, cumulative cost = {1.25650156E8 rows, 2.51300328E8 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 34909
01-06 Scan(groupscan=[EasyGroupScan [selectionRoot=/user/aengelbrecht/twitter/nfl, numFiles=405, columns=[`id`, `dir0`, `dir1`, `dir2`],
Where
select count(id) from tweet_base where dir_year='2015' and dir_month='01' and dir_day='10'
01-02 Project(id=[CAST(CAST($0):BIGINT):BIGINT NOT NULL]): rowcount = 3670316.0, cumulative cost = {1.1010948E7 rows, 1.4681284E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 32130
01-03 Project(id=[$2], dir0=[$3], dir1=[$1], dir2=[$0]): rowcount = 3670316.0, cumulative cost = {7340632.0 rows, 1.468128E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 32129
01-04 Scan(groupscan=[EasyGroupScan [selectionRoot=/user/aengelbrecht/twitter/nfl, numFiles=24, columns=[`id`, `dir0`, `dir1`, `dir2`],
I’m using Drill 0.7
Thanks
—Andries
On Feb 4, 2015, at 8:53 AM, Aman Sinha <as...@maprtech.com> wrote:
> Andries, the dir0=2015 with or without quotes should do the pruning.
> However, if the value is not numeric, such as 'Qtr1' then you need quotes.
> Here's my query which shows the pruning is being done:
>
> (there are 3 directories: 1994, 1995, 1996 and query selects one of them
> and Q1 within that directory):
>
> 0: jdbc:drill:zk=local> explain plan for select * from
> dfs.`/Users/asinha/data/multilevel/parquet` where dir0=1995 and dir1='Q1';
> +------------+------------+
> | text | json |
> +------------+------------+
> | 00-00 Screen
> 00-01 Project(*=[$0])
> 00-02 Project(*=[$0])
> 00-03 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
> [path=/Users/asinha/data/multilevel/parquet/1995/Q1/orders_95_q1.parquet]],
> selectionRoot=/Users/asinha/data/multilevel/parquet, numFiles=1,
> columns=[`*`]]])
>
> On Wed, Feb 4, 2015 at 8:06 AM, Andries Engelbrecht <
> aengelbrecht@maprtech.com> wrote:
>
>> Jason,
>>
>> Thanks for the clarification, I added these to the enhancement request.
>>
>> —Andries
>>
>>
>> On Feb 3, 2015, at 9:47 PM, Jason Altekruse <al...@gmail.com>
>> wrote:
>>
>>> Hao,
>>>
>>> The dir columns are always added to the records coming out of a scan. The
>>> issue is with trying to avoid unneeded reads altogether. If you look at
>> the
>>> query plan you should see that the scan is going to read all of the files
>>> and the filter against the directory column will be applied in a separate
>>> filter operation later. Currently we only support simple expressions,
>>> either equality or an in-list to specify partition filters that can be
>>> pushed into the scan operation.
>>>
>>> -Jason
>>>
>>> On Tue, Feb 3, 2015 at 8:59 PM, Hao Zhu <hz...@maprtech.com> wrote:
>>>
>>>> Strange, per my testing, we can do that:
>>>>
>>>> 0: jdbc:drill:zk=n1a:5181,n2a:5181,n3a:5181> select * from `hao/2015`
>> where
>>>> dir0=1;
>>>> +------------+------------+
>>>> | columns | dir0 |
>>>> +------------+------------+
>>>> | ["1","2","3"] | 1 |
>>>> +------------+------------+
>>>> 1 row selected (0.098 seconds)
>>>> 0: jdbc:drill:zk=n1a:5181,n2a:5181,n3a:5181> select * from `hao/2015`
>> where
>>>> dir0>1;
>>>> +------------+------------+
>>>> | columns | dir0 |
>>>> +------------+------------+
>>>> | ["1","2","3"] | 3 |
>>>> | ["1","2","3"] | 2 |
>>>> +------------+------------+
>>>> 2 rows selected (0.18 seconds)
>>>>
>>>> Thanks,
>>>> Hao
>>>>
>>>> On Tue, Feb 3, 2015 at 8:27 PM, Tomer Shiran <ts...@gmail.com> wrote:
>>>>
>>>>> The casting issue seems like a real bug. People want to do things like
>>>>> "dir0 > 2012"
>>>>>
>>>>> On Tue, Feb 3, 2015 at 6:00 PM, Andries Engelbrecht <
>>>>> aengelbrecht@maprtech.com> wrote:
>>>>>
>>>>>> Thanks.
>>>>>>
>>>>>> It will be good for users to understand the specifics of directory
>>>>> pruning.
>>>>>>
>>>>>> As an additional note is is important to not cast the data typeof the
>>>> dir
>>>>>> filter and to provide a string (i.e. dir0=‘2015’) for pruning to work
>>>>>> properly.
>>>>>> With dir0=2015 the query to works, but the directories are no pruned
>>>>>>
>>>>>> Similar if a view is created with columns for dir0, dir1, etc. the
>> data
>>>>>> types should not be casted or converted, based on current
>> observations.
>>>>>>
>>>>>> It may be good to make it a bit friendlier for a better user
>>>> experience,
>>>>>> will file an enhancement request.
>>>>>>
>>>>>> —Andries
>>>>>>
>>>>>>
>>>>>> On Feb 3, 2015, at 5:35 PM, Aman Sinha <as...@maprtech.com> wrote:
>>>>>>
>>>>>>> Yes, that's the expected behavior for now. Directory pruning where
>>>>> only
>>>>>>> subdirectory is specified is logically equivalent to wildcard
>>>> matching
>>>>> -
>>>>>>> '*/*/10' which is not supported yet. You could open an enhancement
>>>>>>> request.
>>>>>>>
>>>>>>> On Tue, Feb 3, 2015 at 5:27 PM, Andries Engelbrecht <
>>>>>>> aengelbrecht@maprtech.com> wrote:
>>>>>>>
>>>>>>>> Is it required for the directory pruning to work that a top down
>>>>> filter
>>>>>> of
>>>>>>>> directories be applied?
>>>>>>>>
>>>>>>>> My current observation is that for a directory structure as listed
>>>>>> below,
>>>>>>>> the pruning only works if the full tree is provided. If only a lower
>>>>>> level
>>>>>>>> directory is supplied in the filter condition Drill only uses it as
>>>> a
>>>>>>>> filter.
>>>>>>>>
>>>>>>>> /2015
>>>>>>>> /01
>>>>>>>> /10
>>>>>>>> /11
>>>>>>>> /12
>>>>>>>> /13
>>>>>>>> /14
>>>>>>>>
>>>>>>>> select count(id) from `/foo` t where dir0='2015' and dir1='01' and
>>>>>>>> dir2='10'
>>>>>>>> Produces the correct pruning and query plan
>>>>>>>> 01-02 Project(id=[$3]): rowcount = 3670316.0, cumulative
>>>>>> cost =
>>>>>>>> {1.1010948E7 rows, 1.4681284E7 cpu, 0.0 io, 0.0 network, 0.0
>>>> memory},
>>>>>> id =
>>>>>>>> 28434
>>>>>>>> 01-03 Project(dir0=[$0], dir1=[$3], dir2=[$2],
>>>> id=[$1]):
>>>>>>>> rowcount = 3670316.0, cumulative cost = {7340632.0 rows, 1.468128E7
>>>>> cpu,
>>>>>>>> 0.0 io, 0.0 network, 0.0 memory}, id = 28433
>>>>>>>> 01-04 Scan(groupscan=[EasyGroupScan
>>>>> [selectionRoot=/foo,
>>>>>>>> numFiles=24, columns=[`dir0`, `dir1`, `dir2`, `id`]
>>>>>>>>
>>>>>>>>
>>>>>>>> However
>>>>>>>> select count(id) from `/foo` t where dir2='10'
>>>>>>>> Produces full scan of all sub directories and only applies a filter
>>>>>>>> condition after the fact. Notice the numFiles between the 2, even
>>>>>> though it
>>>>>>>> lists columns in the base scan
>>>>>>>> 01-04 Filter(condition=[=($0, '10')]): rowcount =
>>>>>>>> 9423761.7, cumulative cost = {1.88475234E8 rows, 3.76950476E8 cpu,
>>>> 0.0
>>>>>> io,
>>>>>>>> 0.0 network, 0.0 memory}, id = 27470
>>>>>>>> 01-05 Project(dir2=[$1], id=[$0]): rowcount =
>>>>>>>> 6.2825078E7, cumulative cost = {1.25650156E8 rows, 1.25650164E8 cpu,
>>>>> 0.0
>>>>>>>> io, 0.0 network, 0.0 memory}, id = 27469
>>>>>>>> 01-06 Scan(groupscan=[EasyGroupScan
>>>>>>>> [selectionRoot=/foo, numFiles=405, columns=[`dir2`, `id`]
>>>>>>>>
>>>>>>>> Any thoughts?
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>>
>>>>>>>> —Andries
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>
>>
Re: Directory pruning with Drill
Posted by Aman Sinha <as...@maprtech.com>.
Andries, the dir0=2015 with or without quotes should do the pruning.
However, if the value is not numeric, such as 'Qtr1' then you need quotes.
Here's my query which shows the pruning is being done:
(there are 3 directories: 1994, 1995, 1996 and query selects one of them
and Q1 within that directory):
0: jdbc:drill:zk=local> explain plan for select * from
dfs.`/Users/asinha/data/multilevel/parquet` where dir0=1995 and dir1='Q1';
+------------+------------+
| text | json |
+------------+------------+
| 00-00 Screen
00-01 Project(*=[$0])
00-02 Project(*=[$0])
00-03 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=/Users/asinha/data/multilevel/parquet/1995/Q1/orders_95_q1.parquet]],
selectionRoot=/Users/asinha/data/multilevel/parquet, numFiles=1,
columns=[`*`]]])
On Wed, Feb 4, 2015 at 8:06 AM, Andries Engelbrecht <
aengelbrecht@maprtech.com> wrote:
> Jason,
>
> Thanks for the clarification, I added these to the enhancement request.
>
> —Andries
>
>
> On Feb 3, 2015, at 9:47 PM, Jason Altekruse <al...@gmail.com>
> wrote:
>
> > Hao,
> >
> > The dir columns are always added to the records coming out of a scan. The
> > issue is with trying to avoid unneeded reads altogether. If you look at
> the
> > query plan you should see that the scan is going to read all of the files
> > and the filter against the directory column will be applied in a separate
> > filter operation later. Currently we only support simple expressions,
> > either equality or an in-list to specify partition filters that can be
> > pushed into the scan operation.
> >
> > -Jason
> >
> > On Tue, Feb 3, 2015 at 8:59 PM, Hao Zhu <hz...@maprtech.com> wrote:
> >
> >> Strange, per my testing, we can do that:
> >>
> >> 0: jdbc:drill:zk=n1a:5181,n2a:5181,n3a:5181> select * from `hao/2015`
> where
> >> dir0=1;
> >> +------------+------------+
> >> | columns | dir0 |
> >> +------------+------------+
> >> | ["1","2","3"] | 1 |
> >> +------------+------------+
> >> 1 row selected (0.098 seconds)
> >> 0: jdbc:drill:zk=n1a:5181,n2a:5181,n3a:5181> select * from `hao/2015`
> where
> >> dir0>1;
> >> +------------+------------+
> >> | columns | dir0 |
> >> +------------+------------+
> >> | ["1","2","3"] | 3 |
> >> | ["1","2","3"] | 2 |
> >> +------------+------------+
> >> 2 rows selected (0.18 seconds)
> >>
> >> Thanks,
> >> Hao
> >>
> >> On Tue, Feb 3, 2015 at 8:27 PM, Tomer Shiran <ts...@gmail.com> wrote:
> >>
> >>> The casting issue seems like a real bug. People want to do things like
> >>> "dir0 > 2012"
> >>>
> >>> On Tue, Feb 3, 2015 at 6:00 PM, Andries Engelbrecht <
> >>> aengelbrecht@maprtech.com> wrote:
> >>>
> >>>> Thanks.
> >>>>
> >>>> It will be good for users to understand the specifics of directory
> >>> pruning.
> >>>>
> >>>> As an additional note is is important to not cast the data typeof the
> >> dir
> >>>> filter and to provide a string (i.e. dir0=‘2015’) for pruning to work
> >>>> properly.
> >>>> With dir0=2015 the query to works, but the directories are no pruned
> >>>>
> >>>> Similar if a view is created with columns for dir0, dir1, etc. the
> data
> >>>> types should not be casted or converted, based on current
> observations.
> >>>>
> >>>> It may be good to make it a bit friendlier for a better user
> >> experience,
> >>>> will file an enhancement request.
> >>>>
> >>>> —Andries
> >>>>
> >>>>
> >>>> On Feb 3, 2015, at 5:35 PM, Aman Sinha <as...@maprtech.com> wrote:
> >>>>
> >>>>> Yes, that's the expected behavior for now. Directory pruning where
> >>> only
> >>>>> subdirectory is specified is logically equivalent to wildcard
> >> matching
> >>> -
> >>>>> '*/*/10' which is not supported yet. You could open an enhancement
> >>>>> request.
> >>>>>
> >>>>> On Tue, Feb 3, 2015 at 5:27 PM, Andries Engelbrecht <
> >>>>> aengelbrecht@maprtech.com> wrote:
> >>>>>
> >>>>>> Is it required for the directory pruning to work that a top down
> >>> filter
> >>>> of
> >>>>>> directories be applied?
> >>>>>>
> >>>>>> My current observation is that for a directory structure as listed
> >>>> below,
> >>>>>> the pruning only works if the full tree is provided. If only a lower
> >>>> level
> >>>>>> directory is supplied in the filter condition Drill only uses it as
> >> a
> >>>>>> filter.
> >>>>>>
> >>>>>> /2015
> >>>>>> /01
> >>>>>> /10
> >>>>>> /11
> >>>>>> /12
> >>>>>> /13
> >>>>>> /14
> >>>>>>
> >>>>>> select count(id) from `/foo` t where dir0='2015' and dir1='01' and
> >>>>>> dir2='10'
> >>>>>> Produces the correct pruning and query plan
> >>>>>> 01-02 Project(id=[$3]): rowcount = 3670316.0, cumulative
> >>>> cost =
> >>>>>> {1.1010948E7 rows, 1.4681284E7 cpu, 0.0 io, 0.0 network, 0.0
> >> memory},
> >>>> id =
> >>>>>> 28434
> >>>>>> 01-03 Project(dir0=[$0], dir1=[$3], dir2=[$2],
> >> id=[$1]):
> >>>>>> rowcount = 3670316.0, cumulative cost = {7340632.0 rows, 1.468128E7
> >>> cpu,
> >>>>>> 0.0 io, 0.0 network, 0.0 memory}, id = 28433
> >>>>>> 01-04 Scan(groupscan=[EasyGroupScan
> >>> [selectionRoot=/foo,
> >>>>>> numFiles=24, columns=[`dir0`, `dir1`, `dir2`, `id`]
> >>>>>>
> >>>>>>
> >>>>>> However
> >>>>>> select count(id) from `/foo` t where dir2='10'
> >>>>>> Produces full scan of all sub directories and only applies a filter
> >>>>>> condition after the fact. Notice the numFiles between the 2, even
> >>>> though it
> >>>>>> lists columns in the base scan
> >>>>>> 01-04 Filter(condition=[=($0, '10')]): rowcount =
> >>>>>> 9423761.7, cumulative cost = {1.88475234E8 rows, 3.76950476E8 cpu,
> >> 0.0
> >>>> io,
> >>>>>> 0.0 network, 0.0 memory}, id = 27470
> >>>>>> 01-05 Project(dir2=[$1], id=[$0]): rowcount =
> >>>>>> 6.2825078E7, cumulative cost = {1.25650156E8 rows, 1.25650164E8 cpu,
> >>> 0.0
> >>>>>> io, 0.0 network, 0.0 memory}, id = 27469
> >>>>>> 01-06 Scan(groupscan=[EasyGroupScan
> >>>>>> [selectionRoot=/foo, numFiles=405, columns=[`dir2`, `id`]
> >>>>>>
> >>>>>> Any thoughts?
> >>>>>>
> >>>>>> Thanks
> >>>>>>
> >>>>>> —Andries
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>
> >>>>
> >>>
> >>
>
>
Re: Directory pruning with Drill
Posted by Andries Engelbrecht <ae...@maprtech.com>.
Jason,
Thanks for the clarification, I added these to the enhancement request.
—Andries
On Feb 3, 2015, at 9:47 PM, Jason Altekruse <al...@gmail.com> wrote:
> Hao,
>
> The dir columns are always added to the records coming out of a scan. The
> issue is with trying to avoid unneeded reads altogether. If you look at the
> query plan you should see that the scan is going to read all of the files
> and the filter against the directory column will be applied in a separate
> filter operation later. Currently we only support simple expressions,
> either equality or an in-list to specify partition filters that can be
> pushed into the scan operation.
>
> -Jason
>
> On Tue, Feb 3, 2015 at 8:59 PM, Hao Zhu <hz...@maprtech.com> wrote:
>
>> Strange, per my testing, we can do that:
>>
>> 0: jdbc:drill:zk=n1a:5181,n2a:5181,n3a:5181> select * from `hao/2015` where
>> dir0=1;
>> +------------+------------+
>> | columns | dir0 |
>> +------------+------------+
>> | ["1","2","3"] | 1 |
>> +------------+------------+
>> 1 row selected (0.098 seconds)
>> 0: jdbc:drill:zk=n1a:5181,n2a:5181,n3a:5181> select * from `hao/2015` where
>> dir0>1;
>> +------------+------------+
>> | columns | dir0 |
>> +------------+------------+
>> | ["1","2","3"] | 3 |
>> | ["1","2","3"] | 2 |
>> +------------+------------+
>> 2 rows selected (0.18 seconds)
>>
>> Thanks,
>> Hao
>>
>> On Tue, Feb 3, 2015 at 8:27 PM, Tomer Shiran <ts...@gmail.com> wrote:
>>
>>> The casting issue seems like a real bug. People want to do things like
>>> "dir0 > 2012"
>>>
>>> On Tue, Feb 3, 2015 at 6:00 PM, Andries Engelbrecht <
>>> aengelbrecht@maprtech.com> wrote:
>>>
>>>> Thanks.
>>>>
>>>> It will be good for users to understand the specifics of directory
>>> pruning.
>>>>
>>>> As an additional note is is important to not cast the data typeof the
>> dir
>>>> filter and to provide a string (i.e. dir0=‘2015’) for pruning to work
>>>> properly.
>>>> With dir0=2015 the query to works, but the directories are no pruned
>>>>
>>>> Similar if a view is created with columns for dir0, dir1, etc. the data
>>>> types should not be casted or converted, based on current observations.
>>>>
>>>> It may be good to make it a bit friendlier for a better user
>> experience,
>>>> will file an enhancement request.
>>>>
>>>> —Andries
>>>>
>>>>
>>>> On Feb 3, 2015, at 5:35 PM, Aman Sinha <as...@maprtech.com> wrote:
>>>>
>>>>> Yes, that's the expected behavior for now. Directory pruning where
>>> only
>>>>> subdirectory is specified is logically equivalent to wildcard
>> matching
>>> -
>>>>> '*/*/10' which is not supported yet. You could open an enhancement
>>>>> request.
>>>>>
>>>>> On Tue, Feb 3, 2015 at 5:27 PM, Andries Engelbrecht <
>>>>> aengelbrecht@maprtech.com> wrote:
>>>>>
>>>>>> Is it required for the directory pruning to work that a top down
>>> filter
>>>> of
>>>>>> directories be applied?
>>>>>>
>>>>>> My current observation is that for a directory structure as listed
>>>> below,
>>>>>> the pruning only works if the full tree is provided. If only a lower
>>>> level
>>>>>> directory is supplied in the filter condition Drill only uses it as
>> a
>>>>>> filter.
>>>>>>
>>>>>> /2015
>>>>>> /01
>>>>>> /10
>>>>>> /11
>>>>>> /12
>>>>>> /13
>>>>>> /14
>>>>>>
>>>>>> select count(id) from `/foo` t where dir0='2015' and dir1='01' and
>>>>>> dir2='10'
>>>>>> Produces the correct pruning and query plan
>>>>>> 01-02 Project(id=[$3]): rowcount = 3670316.0, cumulative
>>>> cost =
>>>>>> {1.1010948E7 rows, 1.4681284E7 cpu, 0.0 io, 0.0 network, 0.0
>> memory},
>>>> id =
>>>>>> 28434
>>>>>> 01-03 Project(dir0=[$0], dir1=[$3], dir2=[$2],
>> id=[$1]):
>>>>>> rowcount = 3670316.0, cumulative cost = {7340632.0 rows, 1.468128E7
>>> cpu,
>>>>>> 0.0 io, 0.0 network, 0.0 memory}, id = 28433
>>>>>> 01-04 Scan(groupscan=[EasyGroupScan
>>> [selectionRoot=/foo,
>>>>>> numFiles=24, columns=[`dir0`, `dir1`, `dir2`, `id`]
>>>>>>
>>>>>>
>>>>>> However
>>>>>> select count(id) from `/foo` t where dir2='10'
>>>>>> Produces full scan of all sub directories and only applies a filter
>>>>>> condition after the fact. Notice the numFiles between the 2, even
>>>> though it
>>>>>> lists columns in the base scan
>>>>>> 01-04 Filter(condition=[=($0, '10')]): rowcount =
>>>>>> 9423761.7, cumulative cost = {1.88475234E8 rows, 3.76950476E8 cpu,
>> 0.0
>>>> io,
>>>>>> 0.0 network, 0.0 memory}, id = 27470
>>>>>> 01-05 Project(dir2=[$1], id=[$0]): rowcount =
>>>>>> 6.2825078E7, cumulative cost = {1.25650156E8 rows, 1.25650164E8 cpu,
>>> 0.0
>>>>>> io, 0.0 network, 0.0 memory}, id = 27469
>>>>>> 01-06 Scan(groupscan=[EasyGroupScan
>>>>>> [selectionRoot=/foo, numFiles=405, columns=[`dir2`, `id`]
>>>>>>
>>>>>> Any thoughts?
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>> —Andries
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>>
>>
Re: Directory pruning with Drill
Posted by Hao Zhu <hz...@maprtech.com>.
Thanks, I thought we were saying the SQL "dir0>1;" does not work
functionally.
Not I got that, it does not do directory pruning.
On Tue, Feb 3, 2015 at 9:47 PM, Jason Altekruse <al...@gmail.com>
wrote:
> Hao,
>
> The dir columns are always added to the records coming out of a scan. The
> issue is with trying to avoid unneeded reads altogether. If you look at the
> query plan you should see that the scan is going to read all of the files
> and the filter against the directory column will be applied in a separate
> filter operation later. Currently we only support simple expressions,
> either equality or an in-list to specify partition filters that can be
> pushed into the scan operation.
>
> -Jason
>
> On Tue, Feb 3, 2015 at 8:59 PM, Hao Zhu <hz...@maprtech.com> wrote:
>
> > Strange, per my testing, we can do that:
> >
> > 0: jdbc:drill:zk=n1a:5181,n2a:5181,n3a:5181> select * from `hao/2015`
> where
> > dir0=1;
> > +------------+------------+
> > | columns | dir0 |
> > +------------+------------+
> > | ["1","2","3"] | 1 |
> > +------------+------------+
> > 1 row selected (0.098 seconds)
> > 0: jdbc:drill:zk=n1a:5181,n2a:5181,n3a:5181> select * from `hao/2015`
> where
> > dir0>1;
> > +------------+------------+
> > | columns | dir0 |
> > +------------+------------+
> > | ["1","2","3"] | 3 |
> > | ["1","2","3"] | 2 |
> > +------------+------------+
> > 2 rows selected (0.18 seconds)
> >
> > Thanks,
> > Hao
> >
> > On Tue, Feb 3, 2015 at 8:27 PM, Tomer Shiran <ts...@gmail.com> wrote:
> >
> > > The casting issue seems like a real bug. People want to do things like
> > > "dir0 > 2012"
> > >
> > > On Tue, Feb 3, 2015 at 6:00 PM, Andries Engelbrecht <
> > > aengelbrecht@maprtech.com> wrote:
> > >
> > > > Thanks.
> > > >
> > > > It will be good for users to understand the specifics of directory
> > > pruning.
> > > >
> > > > As an additional note is is important to not cast the data typeof the
> > dir
> > > > filter and to provide a string (i.e. dir0=‘2015’) for pruning to work
> > > > properly.
> > > > With dir0=2015 the query to works, but the directories are no pruned
> > > >
> > > > Similar if a view is created with columns for dir0, dir1, etc. the
> data
> > > > types should not be casted or converted, based on current
> observations.
> > > >
> > > > It may be good to make it a bit friendlier for a better user
> > experience,
> > > > will file an enhancement request.
> > > >
> > > > —Andries
> > > >
> > > >
> > > > On Feb 3, 2015, at 5:35 PM, Aman Sinha <as...@maprtech.com> wrote:
> > > >
> > > > > Yes, that's the expected behavior for now. Directory pruning where
> > > only
> > > > > subdirectory is specified is logically equivalent to wildcard
> > matching
> > > -
> > > > > '*/*/10' which is not supported yet. You could open an
> enhancement
> > > > > request.
> > > > >
> > > > > On Tue, Feb 3, 2015 at 5:27 PM, Andries Engelbrecht <
> > > > > aengelbrecht@maprtech.com> wrote:
> > > > >
> > > > >> Is it required for the directory pruning to work that a top down
> > > filter
> > > > of
> > > > >> directories be applied?
> > > > >>
> > > > >> My current observation is that for a directory structure as listed
> > > > below,
> > > > >> the pruning only works if the full tree is provided. If only a
> lower
> > > > level
> > > > >> directory is supplied in the filter condition Drill only uses it
> as
> > a
> > > > >> filter.
> > > > >>
> > > > >> /2015
> > > > >> /01
> > > > >> /10
> > > > >> /11
> > > > >> /12
> > > > >> /13
> > > > >> /14
> > > > >>
> > > > >> select count(id) from `/foo` t where dir0='2015' and dir1='01' and
> > > > >> dir2='10'
> > > > >> Produces the correct pruning and query plan
> > > > >> 01-02 Project(id=[$3]): rowcount = 3670316.0,
> cumulative
> > > > cost =
> > > > >> {1.1010948E7 rows, 1.4681284E7 cpu, 0.0 io, 0.0 network, 0.0
> > memory},
> > > > id =
> > > > >> 28434
> > > > >> 01-03 Project(dir0=[$0], dir1=[$3], dir2=[$2],
> > id=[$1]):
> > > > >> rowcount = 3670316.0, cumulative cost = {7340632.0 rows,
> 1.468128E7
> > > cpu,
> > > > >> 0.0 io, 0.0 network, 0.0 memory}, id = 28433
> > > > >> 01-04 Scan(groupscan=[EasyGroupScan
> > > [selectionRoot=/foo,
> > > > >> numFiles=24, columns=[`dir0`, `dir1`, `dir2`, `id`]
> > > > >>
> > > > >>
> > > > >> However
> > > > >> select count(id) from `/foo` t where dir2='10'
> > > > >> Produces full scan of all sub directories and only applies a
> filter
> > > > >> condition after the fact. Notice the numFiles between the 2, even
> > > > though it
> > > > >> lists columns in the base scan
> > > > >> 01-04 Filter(condition=[=($0, '10')]): rowcount =
> > > > >> 9423761.7, cumulative cost = {1.88475234E8 rows, 3.76950476E8 cpu,
> > 0.0
> > > > io,
> > > > >> 0.0 network, 0.0 memory}, id = 27470
> > > > >> 01-05 Project(dir2=[$1], id=[$0]): rowcount =
> > > > >> 6.2825078E7, cumulative cost = {1.25650156E8 rows, 1.25650164E8
> cpu,
> > > 0.0
> > > > >> io, 0.0 network, 0.0 memory}, id = 27469
> > > > >> 01-06 Scan(groupscan=[EasyGroupScan
> > > > >> [selectionRoot=/foo, numFiles=405, columns=[`dir2`, `id`]
> > > > >>
> > > > >> Any thoughts?
> > > > >>
> > > > >> Thanks
> > > > >>
> > > > >> —Andries
> > > > >>
> > > > >>
> > > > >>
> > > > >>
> > > > >>
> > > > >>
> > > > >>
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > >
> >
>
Re: Directory pruning with Drill
Posted by Jason Altekruse <al...@gmail.com>.
Hao,
The dir columns are always added to the records coming out of a scan. The
issue is with trying to avoid unneeded reads altogether. If you look at the
query plan you should see that the scan is going to read all of the files
and the filter against the directory column will be applied in a separate
filter operation later. Currently we only support simple expressions,
either equality or an in-list to specify partition filters that can be
pushed into the scan operation.
-Jason
On Tue, Feb 3, 2015 at 8:59 PM, Hao Zhu <hz...@maprtech.com> wrote:
> Strange, per my testing, we can do that:
>
> 0: jdbc:drill:zk=n1a:5181,n2a:5181,n3a:5181> select * from `hao/2015` where
> dir0=1;
> +------------+------------+
> | columns | dir0 |
> +------------+------------+
> | ["1","2","3"] | 1 |
> +------------+------------+
> 1 row selected (0.098 seconds)
> 0: jdbc:drill:zk=n1a:5181,n2a:5181,n3a:5181> select * from `hao/2015` where
> dir0>1;
> +------------+------------+
> | columns | dir0 |
> +------------+------------+
> | ["1","2","3"] | 3 |
> | ["1","2","3"] | 2 |
> +------------+------------+
> 2 rows selected (0.18 seconds)
>
> Thanks,
> Hao
>
> On Tue, Feb 3, 2015 at 8:27 PM, Tomer Shiran <ts...@gmail.com> wrote:
>
> > The casting issue seems like a real bug. People want to do things like
> > "dir0 > 2012"
> >
> > On Tue, Feb 3, 2015 at 6:00 PM, Andries Engelbrecht <
> > aengelbrecht@maprtech.com> wrote:
> >
> > > Thanks.
> > >
> > > It will be good for users to understand the specifics of directory
> > pruning.
> > >
> > > As an additional note is is important to not cast the data typeof the
> dir
> > > filter and to provide a string (i.e. dir0=‘2015’) for pruning to work
> > > properly.
> > > With dir0=2015 the query to works, but the directories are no pruned
> > >
> > > Similar if a view is created with columns for dir0, dir1, etc. the data
> > > types should not be casted or converted, based on current observations.
> > >
> > > It may be good to make it a bit friendlier for a better user
> experience,
> > > will file an enhancement request.
> > >
> > > —Andries
> > >
> > >
> > > On Feb 3, 2015, at 5:35 PM, Aman Sinha <as...@maprtech.com> wrote:
> > >
> > > > Yes, that's the expected behavior for now. Directory pruning where
> > only
> > > > subdirectory is specified is logically equivalent to wildcard
> matching
> > -
> > > > '*/*/10' which is not supported yet. You could open an enhancement
> > > > request.
> > > >
> > > > On Tue, Feb 3, 2015 at 5:27 PM, Andries Engelbrecht <
> > > > aengelbrecht@maprtech.com> wrote:
> > > >
> > > >> Is it required for the directory pruning to work that a top down
> > filter
> > > of
> > > >> directories be applied?
> > > >>
> > > >> My current observation is that for a directory structure as listed
> > > below,
> > > >> the pruning only works if the full tree is provided. If only a lower
> > > level
> > > >> directory is supplied in the filter condition Drill only uses it as
> a
> > > >> filter.
> > > >>
> > > >> /2015
> > > >> /01
> > > >> /10
> > > >> /11
> > > >> /12
> > > >> /13
> > > >> /14
> > > >>
> > > >> select count(id) from `/foo` t where dir0='2015' and dir1='01' and
> > > >> dir2='10'
> > > >> Produces the correct pruning and query plan
> > > >> 01-02 Project(id=[$3]): rowcount = 3670316.0, cumulative
> > > cost =
> > > >> {1.1010948E7 rows, 1.4681284E7 cpu, 0.0 io, 0.0 network, 0.0
> memory},
> > > id =
> > > >> 28434
> > > >> 01-03 Project(dir0=[$0], dir1=[$3], dir2=[$2],
> id=[$1]):
> > > >> rowcount = 3670316.0, cumulative cost = {7340632.0 rows, 1.468128E7
> > cpu,
> > > >> 0.0 io, 0.0 network, 0.0 memory}, id = 28433
> > > >> 01-04 Scan(groupscan=[EasyGroupScan
> > [selectionRoot=/foo,
> > > >> numFiles=24, columns=[`dir0`, `dir1`, `dir2`, `id`]
> > > >>
> > > >>
> > > >> However
> > > >> select count(id) from `/foo` t where dir2='10'
> > > >> Produces full scan of all sub directories and only applies a filter
> > > >> condition after the fact. Notice the numFiles between the 2, even
> > > though it
> > > >> lists columns in the base scan
> > > >> 01-04 Filter(condition=[=($0, '10')]): rowcount =
> > > >> 9423761.7, cumulative cost = {1.88475234E8 rows, 3.76950476E8 cpu,
> 0.0
> > > io,
> > > >> 0.0 network, 0.0 memory}, id = 27470
> > > >> 01-05 Project(dir2=[$1], id=[$0]): rowcount =
> > > >> 6.2825078E7, cumulative cost = {1.25650156E8 rows, 1.25650164E8 cpu,
> > 0.0
> > > >> io, 0.0 network, 0.0 memory}, id = 27469
> > > >> 01-06 Scan(groupscan=[EasyGroupScan
> > > >> [selectionRoot=/foo, numFiles=405, columns=[`dir2`, `id`]
> > > >>
> > > >> Any thoughts?
> > > >>
> > > >> Thanks
> > > >>
> > > >> —Andries
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> >
>
Re: Directory pruning with Drill
Posted by Hao Zhu <hz...@maprtech.com>.
Strange, per my testing, we can do that:
0: jdbc:drill:zk=n1a:5181,n2a:5181,n3a:5181> select * from `hao/2015` where
dir0=1;
+------------+------------+
| columns | dir0 |
+------------+------------+
| ["1","2","3"] | 1 |
+------------+------------+
1 row selected (0.098 seconds)
0: jdbc:drill:zk=n1a:5181,n2a:5181,n3a:5181> select * from `hao/2015` where
dir0>1;
+------------+------------+
| columns | dir0 |
+------------+------------+
| ["1","2","3"] | 3 |
| ["1","2","3"] | 2 |
+------------+------------+
2 rows selected (0.18 seconds)
Thanks,
Hao
On Tue, Feb 3, 2015 at 8:27 PM, Tomer Shiran <ts...@gmail.com> wrote:
> The casting issue seems like a real bug. People want to do things like
> "dir0 > 2012"
>
> On Tue, Feb 3, 2015 at 6:00 PM, Andries Engelbrecht <
> aengelbrecht@maprtech.com> wrote:
>
> > Thanks.
> >
> > It will be good for users to understand the specifics of directory
> pruning.
> >
> > As an additional note is is important to not cast the data typeof the dir
> > filter and to provide a string (i.e. dir0=‘2015’) for pruning to work
> > properly.
> > With dir0=2015 the query to works, but the directories are no pruned
> >
> > Similar if a view is created with columns for dir0, dir1, etc. the data
> > types should not be casted or converted, based on current observations.
> >
> > It may be good to make it a bit friendlier for a better user experience,
> > will file an enhancement request.
> >
> > —Andries
> >
> >
> > On Feb 3, 2015, at 5:35 PM, Aman Sinha <as...@maprtech.com> wrote:
> >
> > > Yes, that's the expected behavior for now. Directory pruning where
> only
> > > subdirectory is specified is logically equivalent to wildcard matching
> -
> > > '*/*/10' which is not supported yet. You could open an enhancement
> > > request.
> > >
> > > On Tue, Feb 3, 2015 at 5:27 PM, Andries Engelbrecht <
> > > aengelbrecht@maprtech.com> wrote:
> > >
> > >> Is it required for the directory pruning to work that a top down
> filter
> > of
> > >> directories be applied?
> > >>
> > >> My current observation is that for a directory structure as listed
> > below,
> > >> the pruning only works if the full tree is provided. If only a lower
> > level
> > >> directory is supplied in the filter condition Drill only uses it as a
> > >> filter.
> > >>
> > >> /2015
> > >> /01
> > >> /10
> > >> /11
> > >> /12
> > >> /13
> > >> /14
> > >>
> > >> select count(id) from `/foo` t where dir0='2015' and dir1='01' and
> > >> dir2='10'
> > >> Produces the correct pruning and query plan
> > >> 01-02 Project(id=[$3]): rowcount = 3670316.0, cumulative
> > cost =
> > >> {1.1010948E7 rows, 1.4681284E7 cpu, 0.0 io, 0.0 network, 0.0 memory},
> > id =
> > >> 28434
> > >> 01-03 Project(dir0=[$0], dir1=[$3], dir2=[$2], id=[$1]):
> > >> rowcount = 3670316.0, cumulative cost = {7340632.0 rows, 1.468128E7
> cpu,
> > >> 0.0 io, 0.0 network, 0.0 memory}, id = 28433
> > >> 01-04 Scan(groupscan=[EasyGroupScan
> [selectionRoot=/foo,
> > >> numFiles=24, columns=[`dir0`, `dir1`, `dir2`, `id`]
> > >>
> > >>
> > >> However
> > >> select count(id) from `/foo` t where dir2='10'
> > >> Produces full scan of all sub directories and only applies a filter
> > >> condition after the fact. Notice the numFiles between the 2, even
> > though it
> > >> lists columns in the base scan
> > >> 01-04 Filter(condition=[=($0, '10')]): rowcount =
> > >> 9423761.7, cumulative cost = {1.88475234E8 rows, 3.76950476E8 cpu, 0.0
> > io,
> > >> 0.0 network, 0.0 memory}, id = 27470
> > >> 01-05 Project(dir2=[$1], id=[$0]): rowcount =
> > >> 6.2825078E7, cumulative cost = {1.25650156E8 rows, 1.25650164E8 cpu,
> 0.0
> > >> io, 0.0 network, 0.0 memory}, id = 27469
> > >> 01-06 Scan(groupscan=[EasyGroupScan
> > >> [selectionRoot=/foo, numFiles=405, columns=[`dir2`, `id`]
> > >>
> > >> Any thoughts?
> > >>
> > >> Thanks
> > >>
> > >> —Andries
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> >
> >
>
Re: Directory pruning with Drill
Posted by Tomer Shiran <ts...@gmail.com>.
The casting issue seems like a real bug. People want to do things like
"dir0 > 2012"
On Tue, Feb 3, 2015 at 6:00 PM, Andries Engelbrecht <
aengelbrecht@maprtech.com> wrote:
> Thanks.
>
> It will be good for users to understand the specifics of directory pruning.
>
> As an additional note is is important to not cast the data typeof the dir
> filter and to provide a string (i.e. dir0=‘2015’) for pruning to work
> properly.
> With dir0=2015 the query to works, but the directories are no pruned
>
> Similar if a view is created with columns for dir0, dir1, etc. the data
> types should not be casted or converted, based on current observations.
>
> It may be good to make it a bit friendlier for a better user experience,
> will file an enhancement request.
>
> —Andries
>
>
> On Feb 3, 2015, at 5:35 PM, Aman Sinha <as...@maprtech.com> wrote:
>
> > Yes, that's the expected behavior for now. Directory pruning where only
> > subdirectory is specified is logically equivalent to wildcard matching -
> > '*/*/10' which is not supported yet. You could open an enhancement
> > request.
> >
> > On Tue, Feb 3, 2015 at 5:27 PM, Andries Engelbrecht <
> > aengelbrecht@maprtech.com> wrote:
> >
> >> Is it required for the directory pruning to work that a top down filter
> of
> >> directories be applied?
> >>
> >> My current observation is that for a directory structure as listed
> below,
> >> the pruning only works if the full tree is provided. If only a lower
> level
> >> directory is supplied in the filter condition Drill only uses it as a
> >> filter.
> >>
> >> /2015
> >> /01
> >> /10
> >> /11
> >> /12
> >> /13
> >> /14
> >>
> >> select count(id) from `/foo` t where dir0='2015' and dir1='01' and
> >> dir2='10'
> >> Produces the correct pruning and query plan
> >> 01-02 Project(id=[$3]): rowcount = 3670316.0, cumulative
> cost =
> >> {1.1010948E7 rows, 1.4681284E7 cpu, 0.0 io, 0.0 network, 0.0 memory},
> id =
> >> 28434
> >> 01-03 Project(dir0=[$0], dir1=[$3], dir2=[$2], id=[$1]):
> >> rowcount = 3670316.0, cumulative cost = {7340632.0 rows, 1.468128E7 cpu,
> >> 0.0 io, 0.0 network, 0.0 memory}, id = 28433
> >> 01-04 Scan(groupscan=[EasyGroupScan [selectionRoot=/foo,
> >> numFiles=24, columns=[`dir0`, `dir1`, `dir2`, `id`]
> >>
> >>
> >> However
> >> select count(id) from `/foo` t where dir2='10'
> >> Produces full scan of all sub directories and only applies a filter
> >> condition after the fact. Notice the numFiles between the 2, even
> though it
> >> lists columns in the base scan
> >> 01-04 Filter(condition=[=($0, '10')]): rowcount =
> >> 9423761.7, cumulative cost = {1.88475234E8 rows, 3.76950476E8 cpu, 0.0
> io,
> >> 0.0 network, 0.0 memory}, id = 27470
> >> 01-05 Project(dir2=[$1], id=[$0]): rowcount =
> >> 6.2825078E7, cumulative cost = {1.25650156E8 rows, 1.25650164E8 cpu, 0.0
> >> io, 0.0 network, 0.0 memory}, id = 27469
> >> 01-06 Scan(groupscan=[EasyGroupScan
> >> [selectionRoot=/foo, numFiles=405, columns=[`dir2`, `id`]
> >>
> >> Any thoughts?
> >>
> >> Thanks
> >>
> >> —Andries
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
>
>
Re: Directory pruning with Drill
Posted by Andries Engelbrecht <ae...@maprtech.com>.
Thanks.
It will be good for users to understand the specifics of directory pruning.
As an additional note is is important to not cast the data typeof the dir filter and to provide a string (i.e. dir0=‘2015’) for pruning to work properly.
With dir0=2015 the query to works, but the directories are no pruned
Similar if a view is created with columns for dir0, dir1, etc. the data types should not be casted or converted, based on current observations.
It may be good to make it a bit friendlier for a better user experience, will file an enhancement request.
—Andries
On Feb 3, 2015, at 5:35 PM, Aman Sinha <as...@maprtech.com> wrote:
> Yes, that's the expected behavior for now. Directory pruning where only
> subdirectory is specified is logically equivalent to wildcard matching -
> '*/*/10' which is not supported yet. You could open an enhancement
> request.
>
> On Tue, Feb 3, 2015 at 5:27 PM, Andries Engelbrecht <
> aengelbrecht@maprtech.com> wrote:
>
>> Is it required for the directory pruning to work that a top down filter of
>> directories be applied?
>>
>> My current observation is that for a directory structure as listed below,
>> the pruning only works if the full tree is provided. If only a lower level
>> directory is supplied in the filter condition Drill only uses it as a
>> filter.
>>
>> /2015
>> /01
>> /10
>> /11
>> /12
>> /13
>> /14
>>
>> select count(id) from `/foo` t where dir0='2015' and dir1='01' and
>> dir2='10'
>> Produces the correct pruning and query plan
>> 01-02 Project(id=[$3]): rowcount = 3670316.0, cumulative cost =
>> {1.1010948E7 rows, 1.4681284E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
>> 28434
>> 01-03 Project(dir0=[$0], dir1=[$3], dir2=[$2], id=[$1]):
>> rowcount = 3670316.0, cumulative cost = {7340632.0 rows, 1.468128E7 cpu,
>> 0.0 io, 0.0 network, 0.0 memory}, id = 28433
>> 01-04 Scan(groupscan=[EasyGroupScan [selectionRoot=/foo,
>> numFiles=24, columns=[`dir0`, `dir1`, `dir2`, `id`]
>>
>>
>> However
>> select count(id) from `/foo` t where dir2='10'
>> Produces full scan of all sub directories and only applies a filter
>> condition after the fact. Notice the numFiles between the 2, even though it
>> lists columns in the base scan
>> 01-04 Filter(condition=[=($0, '10')]): rowcount =
>> 9423761.7, cumulative cost = {1.88475234E8 rows, 3.76950476E8 cpu, 0.0 io,
>> 0.0 network, 0.0 memory}, id = 27470
>> 01-05 Project(dir2=[$1], id=[$0]): rowcount =
>> 6.2825078E7, cumulative cost = {1.25650156E8 rows, 1.25650164E8 cpu, 0.0
>> io, 0.0 network, 0.0 memory}, id = 27469
>> 01-06 Scan(groupscan=[EasyGroupScan
>> [selectionRoot=/foo, numFiles=405, columns=[`dir2`, `id`]
>>
>> Any thoughts?
>>
>> Thanks
>>
>> —Andries
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
Re: Directory pruning with Drill
Posted by Aman Sinha <as...@maprtech.com>.
Yes, that's the expected behavior for now. Directory pruning where only
subdirectory is specified is logically equivalent to wildcard matching -
'*/*/10' which is not supported yet. You could open an enhancement
request.
On Tue, Feb 3, 2015 at 5:27 PM, Andries Engelbrecht <
aengelbrecht@maprtech.com> wrote:
> Is it required for the directory pruning to work that a top down filter of
> directories be applied?
>
> My current observation is that for a directory structure as listed below,
> the pruning only works if the full tree is provided. If only a lower level
> directory is supplied in the filter condition Drill only uses it as a
> filter.
>
> /2015
> /01
> /10
> /11
> /12
> /13
> /14
>
> select count(id) from `/foo` t where dir0='2015' and dir1='01' and
> dir2='10'
> Produces the correct pruning and query plan
> 01-02 Project(id=[$3]): rowcount = 3670316.0, cumulative cost =
> {1.1010948E7 rows, 1.4681284E7 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
> 28434
> 01-03 Project(dir0=[$0], dir1=[$3], dir2=[$2], id=[$1]):
> rowcount = 3670316.0, cumulative cost = {7340632.0 rows, 1.468128E7 cpu,
> 0.0 io, 0.0 network, 0.0 memory}, id = 28433
> 01-04 Scan(groupscan=[EasyGroupScan [selectionRoot=/foo,
> numFiles=24, columns=[`dir0`, `dir1`, `dir2`, `id`]
>
>
> However
> select count(id) from `/foo` t where dir2='10'
> Produces full scan of all sub directories and only applies a filter
> condition after the fact. Notice the numFiles between the 2, even though it
> lists columns in the base scan
> 01-04 Filter(condition=[=($0, '10')]): rowcount =
> 9423761.7, cumulative cost = {1.88475234E8 rows, 3.76950476E8 cpu, 0.0 io,
> 0.0 network, 0.0 memory}, id = 27470
> 01-05 Project(dir2=[$1], id=[$0]): rowcount =
> 6.2825078E7, cumulative cost = {1.25650156E8 rows, 1.25650164E8 cpu, 0.0
> io, 0.0 network, 0.0 memory}, id = 27469
> 01-06 Scan(groupscan=[EasyGroupScan
> [selectionRoot=/foo, numFiles=405, columns=[`dir2`, `id`]
>
> Any thoughts?
>
> Thanks
>
> —Andries
>
>
>
>
>
>
>
>
>
>