[
https://issues.apache.org/jira/browse/DRILL-7038?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16803526#comment-16803526
]
Bridget Bevens edited comment on DRILL-7038 at 3/28/19 1:50 AM:
----------------------------------------------------------------
Hi [~KazydubB],
I'm planning to add the following information to this page:
https://drill.apache.org/docs/querying-directories/
Can you please review and let me know if the information is accurate?
Starting in Drill 1.16, if a query on a file system table references
partitioned columns only and also has a DISTINCT or GROUP BY operation, Drill
only reads the data partition(s) referenced. For example, if a table, table1,
is partitioned into the following directories:
/table1/2016
/table1/2017
/table1/2018
And you run either of the following queries against table1:
select distinct dir0 from dfs.`/table`;
select dir0 from dfs.`/table` group by dir0;
Drill only scans dir0 (/table1/2016) instead of scanning all three of the
directories.
Thanks,
Bridget
was (Author: bbevens):
Hi [~KazydubB],
I'm planning to add the following information to this page:
https://drill.apache.org/docs/querying-directories/
Can you please review and let me know if the information is accurate?
Starting in Drill 1.16, if a query on a file system table references
partitioned columns only and also has a DISTINCT or GROUP BY operation, Drill
only reads the data partition(s) referenced. For example, if a table, table1,
is partitioned into the following directories:
{{/table1/2016
/table1/2017
/table1/2018}}
And you run either of the following queries against table1:
{{select distinct dir0 from dfs.`/table`;
select dir0 from dfs.`/table` group by dir0;}}
Drill only scans dir0 (/table1/2016) instead of scanning all three of the
directories.
Thanks,
Bridget
> Queries on partitioned columns scan the entire datasets
> -------------------------------------------------------
>
> Key: DRILL-7038
> URL: https://issues.apache.org/jira/browse/DRILL-7038
> Project: Apache Drill
> Issue Type: Improvement
> Reporter: Bohdan Kazydub
> Assignee: Bohdan Kazydub
> Priority: Major
> Labels: doc-impacting, ready-to-commit
> Fix For: 1.16.0
>
>
> For tables with hive-style partitions like
> {code}
> /table/2018/Q1
> /table/2018/Q2
> /table/2019/Q1
> etc.
> {code}
> if any of the following queries is run:
> {code}
> select distinct dir0 from dfs.`/table`
> {code}
> {code}
> select dir0 from dfs.`/table` group by dir0
> {code}
> it will actually scan every single record in the table rather than just
> getting a list of directories at the dir0 level. This applies even when
> cached metadata is available. This is a big penalty especially as the
> datasets grow.
> To avoid such situations, a logical prune rule can be used to collect
> partition columns (`dir0`), either from metadata cache (if available) or
> group scan, and drop unnecessary files from being read. The rule will be
> applied on following conditions:
> 1) all queried columns are partitoin columns, and
> 2) either {{DISTINCT}} or {{GROUP BY}} operations are performed.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
