Thanks for detailed explanation. Even without bucket pruning, expectation
from bucketing is performance improvement. I am joining two tables which
are bucketed on same no of buckets and column and comparing the performance
with join of two unbucketed tables on bucket led column. I am using mapr
dist and map join conversion is enabled by default. Performance is same in
both the cases. I increased data size from 120 gb to 400 gb to see clear
performance difference. But still results are same. Have set
hive.optimize.bucketmapjoin=true.

Any clue why that would be happening.

Thanks,
AJ
On Jan 22, 2016 4:31 PM, "Mich Talebzadeh" <[email protected]> wrote:

> Hi,
>
>
>
> In general my understanding is that it will be possible to use bucket
> pruning much like partition pruning (elimination) soon
>
>
>
> Bucketing in Hive refers to hash partitioning where a hashing function is
> applied. Likewise an RDBMS like Oracle, Hive will apply a linear hashing
> algorithm to prevent data from clustering within specific partitions.
> Hashing is very effective if the column selected for bucketing has very
> high selectivity like an ID column where selectivity (select
> count(distinct(column))/count(column) ) = 1.  In this case, the created
> partitions/ files will be as evenly sized as possible. In a nutshell
> bucketing is a method to get data evenly distributed over many
> partitions/files.  One should define the number of buckets by a power of
> two -- 2^n,  like 2, 4, 8, 16 etc to achieve best results. Again bucketing
> will help concurrency in Hive. It may even allow a partition wise join i.e.
> a join between two tables that are bucketed on the same column with the
> same number of buckets (anyone has tried this?)
>
>
>
> One more things. When one defines the number of buckets at table creation
> level in Hive, the number of partitions/files will be fixed. In contrast,
> with partitioning you do not have this limitation.
>
> .
>
> Have you considered creating these tables as ORC tables?
>
>
>
> HTH
>
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> *Sybase ASE 15 Gold Medal Award 2008*
>
> A Winning Strategy: Running the most Critical Financial Data on ASE 15
>
>
> http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf
>
> Author of the books* "A Practitioner’s Guide to Upgrading to Sybase ASE
> 15", ISBN 978-0-9563693-0-7*.
>
> co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN
> 978-0-9759693-0-4*
>
> *Publications due shortly:*
>
> *Complex Event Processing in Heterogeneous Environments*, ISBN:
> 978-0-9563693-3-8
>
> *Oracle and Sybase, Concepts and Contrasts*, ISBN: 978-0-9563693-1-4, volume
> one out shortly
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> NOTE: The information in this email is proprietary and confidential. This
> message is for the designated recipient only, if you are not the intended
> recipient, you should destroy it immediately. Any information in this
> message shall not be understood as given or endorsed by Peridale Technology
> Ltd, its subsidiaries or their employees, unless expressly so stated. It is
> the responsibility of the recipient to ensure that this email is virus
> free, therefore neither Peridale Technology Ltd, its subsidiaries nor their
> employees accept any responsibility.
>
>
>
> *From:* Akansha Jain [mailto:[email protected]]
> *Sent:* 22 January 2016 23:20
> *To:* [email protected]
> *Subject:* RE: Hive Bucketing
>
>
>
> Thanks for response. I am using 0.13 mapr version. Could you tell more
> about bucket pruning.
>
> On Jan 22, 2016 3:09 PM, "Mich Talebzadeh" <[email protected]> wrote:
>
> Ok we are talking about bucket pruning here
>
>
>
> What version of Hive are using?
>
>
>
> Bucket pruning I believe is available from version 2.0
>
>
>
> HTH
>
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> *Sybase ASE 15 Gold Medal Award 2008*
>
> A Winning Strategy: Running the most Critical Financial Data on ASE 15
>
>
> http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf
>
> Author of the books* "A Practitioner’s Guide to Upgrading to Sybase ASE
> 15", ISBN 978-0-9563693-0-7*.
>
> co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN
> 978-0-9759693-0-4*
>
> *Publications due shortly:*
>
> *Complex Event Processing in Heterogeneous Environments*, ISBN:
> 978-0-9563693-3-8
>
> *Oracle and Sybase, Concepts and Contrasts*, ISBN: 978-0-9563693-1-4, volume
> one out shortly
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> NOTE: The information in this email is proprietary and confidential. This
> message is for the designated recipient only, if you are not the intended
> recipient, you should destroy it immediately. Any information in this
> message shall not be understood as given or endorsed by Peridale Technology
> Ltd, its subsidiaries or their employees, unless expressly so stated. It is
> the responsibility of the recipient to ensure that this email is virus
> free, therefore neither Peridale Technology Ltd, its subsidiaries nor their
> employees accept any responsibility.
>
>
>
> *From:* Akansha Jain [mailto:[email protected]]
> *Sent:* 22 January 2016 21:55
> *To:* [email protected]
> *Subject:* Hive Bucketing
>
>
>
> Hi All,
> I have enabled bucketing in table. I created 256 buckets on user id. Now
> when I am querying (select count(*) from table where userid =172839393)
> that table, map reduce should only use single partitioned file as input to
> mappers. But its considering all files as input to mapper and I don't see
> any performance benefit when I run same query in unbucketed table.
>
> Do I have to set any property before running queries on bucketed tables. I
> tried join query also, but no performance improvement. In fact, I think
> it's taking few more seconds as compared to unbucketed table.
>
> Thanks,
> AJ
>
>

Reply via email to