You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Akansha Jain <ak...@gmail.com> on 2016/01/22 22:54:36 UTC
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
RE: Hive Bucketing
Posted by Mich Talebzadeh <mi...@peridale.co.uk>.
My conclusion is that from performance point of view as of Hive 1.2.1
bucketing has limited scope if any. I cannot comment on Hive 2 as I have not
got it.
Thanks,
Dr Mich Talebzadeh
LinkedIn
https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUr
V8Pw
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.
-----Original Message-----
From: Gopal Vijayaraghavan [mailto:gopal@hortonworks.com] On Behalf Of Gopal
Vijayaraghavan
Sent: 26 January 2016 21:36
To: user@hive.apache.org
Subject: Re: Hive Bucketing
> Ok so what is the resolution here? My understanding is that bucketing
>does not improve the performance. Is that correct?
There are no right answers here - I spend a lot of time fixing over-zealous
optimization attempts
<http://www.slideshare.net/t3rmin4t0r/data-organization-hive-meetup/5>
If you use bucketing to speed up a query without understanding the interplay
with other parameters involved (total # of partitions, size of each
partition, bucket col type, skew towards buckets), then it generally ends up
in disaster. And depending on whether you pay for HDP support or not, I
might turn out to be your tow-truck driver.
Bucketing in hive-1.0 is not a general performance feature - it was intended
as a scalability feature for JOINs.
Bucketed map-joins can be really slow if you're limited to MapReduce
(https://issues.apache.org/jira/browse/HIVE-4488).
Even excluding the execution on cluster, both Spark & Mapreduce serializes
the lookup tables before the query kicks off.
That means there is a pause before the big table can be read (or even tasks
scheduled) - Tez pipelines the scheduling with the generation, so it's not
so bad (through different EdgeManagers).
But a bucket map-join is still slower than a regular map-join within a
single task, because the Tez can cache the hashtable for the regular join as
it is the same one for any split it encounters in the vertex.
*SO*, if your mapjoins are OOM'ing you might want to consider bucketing -
otherwise they're wasted CPUs for JOINs.
With Hive-2.0, if Tez thinks your map-join might OOM, it might bucket data
at runtime & produce a dynamic version of a bucketed map-join
https://issues.apache.org/jira/browse/HIVE-10673
This feature by itself pays for all the complexity Tez has with its runtime
edge reconfiguration.
Back to filters.
You can get speedups in filter queries with bucketing, even in Hive-1.0 (if
the data is sorted & clustered on same col).
Even then, the split-elimination with predicate lookups is unavailable to
any engine using CombineHiveInputFormat (i.e MapReduce & Spark).
So you have to be using ORC+Tez & sorting aligned precisely along your
lookup direction.
If you're on Parquet, it gets a little worse (+~3s or so for each task wave
spin up) and skip reading those rows entirely.
Cheers,
Gopal
Re: Hive Bucketing
Posted by Gopal Vijayaraghavan <go...@apache.org>.
> Ok so what is the resolution here? My understanding is that bucketing
>does not improve the performance. Is that correct?
There are no right answers here - I spend a lot of time fixing
over-zealous optimization attempts
<http://www.slideshare.net/t3rmin4t0r/data-organization-hive-meetup/5>
If you use bucketing to speed up a query without understanding the
interplay with other parameters involved (total # of partitions, size of
each partition, bucket col type, skew towards buckets), then it generally
ends up in disaster. And depending on whether you pay for HDP support or
not, I might turn out to be your tow-truck driver.
Bucketing in hive-1.0 is not a general performance feature - it was
intended as a scalability feature for JOINs.
Bucketed map-joins can be really slow if you're limited to MapReduce
(https://issues.apache.org/jira/browse/HIVE-4488).
Even excluding the execution on cluster, both Spark & Mapreduce serializes
the lookup tables before the query kicks off.
That means there is a pause before the big table can be read (or even
tasks scheduled) - Tez pipelines the scheduling with the generation, so
it's not so bad (through different EdgeManagers).
But a bucket map-join is still slower than a regular map-join within a
single task, because the Tez can cache the hashtable for the regular join
as it is the same one for any split it encounters in the vertex.
*SO*, if your mapjoins are OOM'ing you might want to consider bucketing -
otherwise they're wasted CPUs for JOINs.
With Hive-2.0, if Tez thinks your map-join might OOM, it might bucket data
at runtime & produce a dynamic version of a bucketed map-join
https://issues.apache.org/jira/browse/HIVE-10673
This feature by itself pays for all the complexity Tez has with its
runtime edge reconfiguration.
Back to filters.
You can get speedups in filter queries with bucketing, even in Hive-1.0
(if the data is sorted & clustered on same col).
Even then, the split-elimination with predicate lookups is unavailable to
any engine using CombineHiveInputFormat (i.e MapReduce & Spark).
So you have to be using ORC+Tez & sorting aligned precisely along your
lookup direction.
If you're on Parquet, it gets a little worse (+~3s or so for each task
wave spin up) and skip reading those rows entirely.
Cheers,
Gopal
RE: Hive Bucketing
Posted by Mich Talebzadeh <mi...@peridale.co.uk>.
Ok so what is the resolution here? My understanding is that bucketing does not improve the performance. Is that correct?
Dr Mich Talebzadeh
LinkedIn 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 <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:akansha.15aug85@gmail.com]
Sent: 25 January 2016 22:44
To: user@hive.apache.org
Subject: RE: Hive Bucketing
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" <mich@peridale.co.uk <ma...@peridale.co.uk> > 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
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 <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:akansha.15aug85@gmail.com <ma...@gmail.com> ]
Sent: 22 January 2016 23:20
To: user@hive.apache.org <ma...@hive.apache.org>
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" <mich@peridale.co.uk <ma...@peridale.co.uk> > 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
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 <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:akansha.15aug85@gmail.com <ma...@gmail.com> ]
Sent: 22 January 2016 21:55
To: user@hive.apache.org <ma...@hive.apache.org>
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
RE: Hive Bucketing
Posted by Akansha Jain <ak...@gmail.com>.
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" <mi...@peridale.co.uk> 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:akansha.15aug85@gmail.com]
> *Sent:* 22 January 2016 23:20
> *To:* user@hive.apache.org
> *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" <mi...@peridale.co.uk> 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:akansha.15aug85@gmail.com]
> *Sent:* 22 January 2016 21:55
> *To:* user@hive.apache.org
> *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
>
>
RE: Hive Bucketing
Posted by Mich Talebzadeh <mi...@peridale.co.uk>.
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
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 <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:akansha.15aug85@gmail.com]
Sent: 22 January 2016 23:20
To: user@hive.apache.org
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" <mich@peridale.co.uk <ma...@peridale.co.uk> > 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
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 <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:akansha.15aug85@gmail.com <ma...@gmail.com> ]
Sent: 22 January 2016 21:55
To: user@hive.apache.org <ma...@hive.apache.org>
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
RE: Hive Bucketing
Posted by Akansha Jain <ak...@gmail.com>.
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" <mi...@peridale.co.uk> 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:akansha.15aug85@gmail.com]
> *Sent:* 22 January 2016 21:55
> *To:* user@hive.apache.org
> *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
>
RE: Hive Bucketing
Posted by Mich Talebzadeh <mi...@peridale.co.uk>.
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> 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/> 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:akansha.15aug85@gmail.com]
Sent: 22 January 2016 21:55
To: user@hive.apache.org
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