hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From matshyeq <matsh...@gmail.com>
Subject Re: Bucket pruning
Date Mon, 23 Mar 2015 10:40:32 GMT
To me there's practically very little difference between partitioning and
bucketing (partitioning defines split criteria explicitly whereas bucketing
somewhat implicitly) . Hive however recognises the latter as a separate
feature and handles the two in quite different way.

There's already a feature request proposition to unify and bring the
optimisations across (so it would address the "bucket pruning" issue I
believe you're having):

https://issues.apache.org/jira/browse/HIVE-9523

Probably best if you vote for it so it gets some traction…

Regards
~Maciek

On Fri, Mar 13, 2015 at 12:22 PM, cobby <ququrico@yahoo.com> wrote:

> hi, thanks for the detailed response.
> i will experiment with your suggested orc bloom filter solution.
>
> it seems to me the obvious, most straight forward solution is to add
> support for hash partitioning. so i can do something like:
>
> create table T()
> partitioned by (x into num_partitions,..).
>
> upon insert hash(x) determines which partition to put the record in. upon
> select, the query processor can now hash on x and scan only that partition
> (this optimization will probably work only on = and other discrete
> filtering but thats true for partitioning in general).
> it seems all of this can be done early in the query plan phase and have no
> effect on underling infra.
>
> regards,cobby.
>
>
>
> > On 12 במרץ 2015, at 23:05, Gopal Vijayaraghavan <gopalv@apache.org>
> wrote:
> >
> > Hi,
> >
> > No and it¹s a shame because we¹re stuck on some compatibility details
> with
> > this.
> >
> > The primary issue is the fact that the InputFormat is very generic and
> > offers no way to communicate StorageDescriptor or bucketing.
> >
> > The split generation for something SequenceFileInputFormat lives inside
> > MapReduce, where it has no idea about bucketing.
> >
> > So InputFormat.getSplits(conf) returns something relatively arbitrary,
> > which contains a mixture of files when CombineInputFormat is turned on.
> >
> > I have implemented this twice so far for ORC (for custom Tez jobs, with
> > huge wins) by using an MRv2 PathFilter over the regular OrcNewInputFormat
> > implementation, by turning off combine input and using Tez grouping
> > instead.
> >
> > But that has proved to be very fragile for a trunk feature, since with
> > schema evolution of partitioned tables older partitions may be bucketed
> > with a different count from a newer partition - so the StorageDescriptor
> > for each partition has to be fetched across before we can generate a
> valid
> > PathFilter.
> >
> > The SARGs are probably a better way to do this eventually as they can
> > implement IN_BUCKET(1,2) to indicate 1 of 2 instead of the ³00000_1²
> > PathFilter which is fragile.
> >
> >
> > Right now, the most fool-proof solution we¹ve hit upon was to apply the
> > ORC bloom filter to the bucket columns, which is far safer as it does not
> > care about the DDL - but does a membership check on the actual metadata &
> > prunes deeper at the stripe-level if it is sorted as well.
> >
> > That is somewhat neat since this doesn¹t need any new options for
> querying
> > - it automatically(*) kicks in for your query pattern.
> >
> > Cheers,
> > Gopal
> > (*) - conditions apply - there¹s a threshold for file-size for these
> > filters to be evaluated during planning (to prevent HS2 from burning
> CPU).
> >
> >
> > From:  Daniel Haviv <daniel.haviv@veracity-group.com>
> > Reply-To:  "user@hive.apache.org" <user@hive.apache.org>
> > Date:  Thursday, March 12, 2015 at 2:36 AM
> > To:  "user@hive.apache.org" <user@hive.apache.org>
> > Subject:  Bucket pruning
> >
> >
> > Hi,
> > We created a bucketed table and when we select in the following way:
> > select *
> > from testtble
> > where bucket_col ='X';
> >
> > We observe that there all of the table is being read and not just the
> > specific bucket.
> >
> > Does Hive support such a feature ?
> >
> >
> > Thanks,
> > Daniel
> >
> >
>

Mime
View raw message