Andy,
Could you please attach "explain plan including all attributes" for this
query ?
I'm not able to reproduce this behavior with the latest build. What version
of drill are you running with ?
0: jdbc:drill:schema=dfs> explain plan including all attributes for select
a1 from `position` where dir0='F113' and dir1='2014-03-27';
+------------+------------+
| text | json |
+------------+------------+
| 00-00 Screen : rowType = RecordType(ANY a1): rowcount = 10.0,
cumulative cost = {31.0 rows, 47.0 cpu, 0.0 io, 0.0 network, 0.0 memory},
id = 57712
00-01 Project(a1=[$2]) : rowType = RecordType(ANY a1): rowcount =
10.0, cumulative cost = {30.0 rows, 46.0 cpu, 0.0 io, 0.0 network, 0.0
memory}, id = 57711
00-02 Project(dir0=[$0], dir1=[$2], a1=[$1]) : rowType =
RecordType(ANY dir0, ANY dir1, ANY a1): rowcount = 10.0, cumulative cost =
{20.0 rows, 42.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 57710
00-03 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=maprfs:/test/position/F113/2014-03-27/0_0_0.parquet]],
selectionRoot=/test/position, numFiles=1, columns=[`dir0`, `dir1`, `a1`]]])
: rowType = RecordType(ANY dir0, ANY a1, ANY dir1): rowcount = 10.0,
cumulative cost = {10.0 rows, 30.0 cpu, 0.0 io, 0.0 network, 0.0 memory},
id = 57709
| {
"head" : {
"version" : 1,
"generator" : {
"type" : "ExplainHandler",
"info" : ""
},
"type" : "APACHE_DRILL_PHYSICAL",
"options" : [ ],
"queue" : 0,
"resultMode" : "EXEC"
},
"graph" : [ {
"pop" : "parquet-scan",
"@id" : 3,
"entries" : [ {
"path" : "maprfs:/test/position/F113/2014-03-27/0_0_0.parquet"
} ],
Thanks,
Vicky.
On Thu, Apr 16, 2015 at 12:21 PM, Andy Pernsteiner <
[email protected]> wrote:
> I have a dataset with the following structure:
>
> /clsc_ocode/yyyy-mm-dd/file.parquet
>
> Where clsc_ocode might be something like:
>
> AAAA
> BBBB
> CCCC
> etc
>
> What we're trying to do is run the following query, and have it only scan
> the relevant dirs/files:
>
> select c_status from `position` where dir0='F113' and dir1='2014-03-27';
>
>
> However, the explain plan tells me that its scanning everything (see
> below). Any clues on how I can get drill to only scan the 'correct' dirs
> and files?
>
>
>
> 0: jdbc:drill:> explain plan for select c_status from `position` where
> dir0='F113' and dir1='2014-03-27';
> +------------+------------+
> | text | json |
> +------------+------------+
> | 00-00 Screen
> 00-01 UnionExchange
> 01-01 Project(c_status=[$2])
> 01-02 SelectionVectorRemover
> 01-03 Filter(condition=[AND(=($0, 'F113'), =($1,
> '2014-03-27'))])
> 01-04 Project(dir0=[$0], dir1=[$2], c_status=[$1])
> 01-05 Scan(groupscan=[ParquetGroupScan
> [entries=[ReadEntryWithPath [path=maprfs:/user/myuser/project/position/
>
> *GT06;WSB4*/2014-03-26/214c279334946e65-7e32c56eed93cbc1_1051246080_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-04-02/214c279334946e65-7e32c56eed93cbc1_192659286_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-04-01/214c279334946e65-7e32c56eed93cbc2_371458216_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-06-21/894b9942f8411e85-7d163997afdf2587_940200931_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-07-29/214c279334946e65-7e32c56eed93cbc2_664248475_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-06-19/214c279334946e65-7e32c56eed93cbc0_2053035507_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-06-30/214c279334946e65-7e32c56eed93cbbf_1281606632_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-03-05/214c279334946e65-7e32c56eed93cbc0_1369385312_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756486_1553652051_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-06-20/214c279334946e65-7e32c56eed93cbc0_328577881_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-06-27/214c279334946e65-7e32c56eed93cbbf_753269315_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-03-31/214c279334946e65-7e32c56eed93cbc1_1467857798_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-04-03/214c279334946e65-7e32c56eed93cbc0_274342802_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-09-02/d940124da9265b62-db78d733b4e41a9d_1017833885_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-07-03/214c279334946e65-7e32c56eed93cbc2_37298498_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-07-15/214c279334946e65-7e32c56eed93cbbf_1237344643_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-06-26/214c279334946e65-7e32c56eed93cbc2_1847884103_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-03-27/214c279334946e65-7e32c56eed93cbc0_9210719_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-07-02/214c279334946e65-7e32c56eed93cbc0_1371477905_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-21/894b9942f8411e85-7d163997afdf2587_69864010_data.1.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-21/894b9942f8411e85-7d163997afdf2587_69864010_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-07-29/214c279334946e65-7e32c56eed93cbc1_680866327_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-19/214c279334946e65-7e32c56eed93cbc1_488271693_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-30/214c279334946e65-7e32c56eed93cbc2_452230455_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756486_1114217993_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756486_1114217993_data.1.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-20/214c279334946e65-7e32c56eed93cbc1_604208402_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-27/214c279334946e65-7e32c56eed93cbc2_35452641_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-09-02/d940124da9265b62-db78d733b4e41a9d_121272728_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-09-02/d940124da9265b62-db78d733b4e41a9d_121272728_data.1.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-07-03/214c279334946e65-7e32c56eed93cbc1_594862257_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-07-15/214c279334946e65-7e32c56eed93cbc0_37656589_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-26/214c279334946e65-7e32c56eed93cbbf_956289869_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB52/2014-06-21/894b9942f8411e85-7d163997afdf2585_873498190_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB52/2014-06-30/214c279334946e65-7e32c56eed93cbc0_1914683943_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB52/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756484_762992808_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB52/2014-06-27/214c279334946e65-7e32c56eed93cbc0_1098724507_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB52/2014-09-02/d940124da9265b62-db78d733b4e41a9b_1313631650_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB52/2014-07-03/214c279334946e65-7e32c56eed93cbbf_121420287_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB52/2014-07-15/214c279334946e65-7e32c56eed93cbc2_136444256_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-25/214c279334946e65-7e32c56eed93cbc0_1650113784_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-26/214c279334946e65-7e32c56eed93cbbf_1340747001_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-04-02/214c279334946e65-7e32c56eed93cbc2_1364839309_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-24/214c279334946e65-7e32c56eed93cbc1_750510284_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-04-01/214c279334946e65-7e32c56eed93cbc1_536235491_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-06-21/894b9942f8411e85-7d163997afdf2585_726763609_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-04/214c279334946e65-7e32c56eed93cbbf_942238716_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-05/214c279334946e65-7e32c56eed93cbc2_506292193_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756484_567483995_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-31/214c279334946e65-7e32c56eed93cbbf_615989837_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-04-03/214c279334946e65-7e32c56eed93cbbf_616726188_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-09-02/d940124da9265b62-db78d733b4e41a9b_220071911_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-27/214c279334946e65-7e32c56eed93cbc2_278620626_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB23/2014-06-21/894b9942f8411e85-7d163997afdf2585_1824939453_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB23/2014-06-30/214c279334946e65-7e32c56eed93cbc2_1226007134_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB23/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756484_366513003_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB23/2014-06-27/214c279334946e65-7e32c56eed93cbc2_820371902_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB23/2014-09-02/d940124da9265b62-db78d733b4e41a9b_1199732126_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB23/2014-07-03/214c279334946e65-7e32c56eed93cbc2_1965630551_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/CB23/2014-07-15/214c279334946e65-7e32c56eed93cbbf_115161432_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/F122/2014-03-25/214c279334946e65-7e32c56eed93cbc2_324676563_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/F122/2014-03-26/214c279334946e65-7e32c56eed93cbc1_1975946170_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/F122/2014-04-02/214c279334946e65-7e32c56eed93cbc2_2035552145_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/F122/2014-03-24/214c279334946e65-7e32c56eed93cbbf_941712586_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/F122/2014-04-01/214c279334946e65-7e32c56eed93cbc1_1237069829_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/F122/2014-06-21/894b9942f8411e85-7d163997afdf2587_1708712130_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/F122/2014-07-29/214c279334946e65-7e32c56eed93cbc2_354733156_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/F122/2014-06-30/214c279334946e65-7e32c56eed93cbbf_532030629_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/F122/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756486_1274519202_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/F122/2014-06-20/214c279334946e65-7e32c56eed93cbc0_600238501_data.0.parq],
> ReadEntryWithPath
>
> [path=maprfs:/user/myuser/project/position/F122/2014-06-27/214c279334946e65-7e32c56eed93cbbf_1427614849_data.0.parq],
> ReadEntryWithPath [path=maprfs:/user/myuser/project/ |
>
>
>
>
>
>
> --
> Andy Pernsteiner
>
>
> Manager, Field Enablement
> ph: 206.228.0737
>
> www.mapr.com
>
> Now Available - Free Hadoop On-Demand Training
> <
> http://www.mapr.com/training?utm_source=Email&utm_medium=Signature&utm_campaign=Free%20available
> >
>
