hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mich Talebzadeh" <m...@peridale.co.uk>
Subject RE: Hive Bucketing
Date Mon, 25 Jan 2016 21:07:05 GMT
Hi,

 

Can you do

 

show create table <table_name>

 

And send the output please.

 

Thanks

 

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: 谭成灶 [mailto:tanxinz@live.cn] 
Sent: 25 January 2016 15:37
To: user@hive.apache.org
Subject: 答复: Hive Bucketing

 

Hi,how to efficient insert into an orc bucket  table,I found it too slow.thanks you 

  _____  

发件人: Mich Talebzadeh <mailto:mich@peridale.co.uk> 
发送时间: ‎2016/‎1/‎23 7:31
收件人: user@hive.apache.org <mailto:user@hive.apache.org> 
主题: RE: Hive Bucketing

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 <mailto: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 <mailto:mich@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 <mailto:akansha.15aug85@gmail.com>
] 
Sent: 22 January 2016 21:55
To: user@hive.apache.org <mailto: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


Mime
View raw message