Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Optimization on bucketized/sorted tables

Copy link to this message
Re: Optimization on bucketized/sorted tables
Hi Michael,
This JIRA is along the lines of your questions:

The following is based on my understanding so take it with a grain of salt:-)
You're right. The 4 kinds of queries you pointed out can be potentially be optimized if the source table(s) are bucketed and/or sorted by the appropriate columns. Another query that could be optimized based on bucketing/sorting is join. This is presently being done in bucketed map joins and sort merge joins.

However, like the JIRA ticket mentions, the bucketing/sorting information isn't presently stored in the metastore, so the queries can't make use of them without specifying hints like joins do.

To answer your last question, I think you have to explicitly (at least for now) mention DISTRIBUTE BY and SORT BY in your query and that's what I do in my queries too.


Mark Grover, Business Intelligence Analyst
OANDA Corporation

www: oanda.com www: fxtrade.com

"Best Trading Platform" - World Finance's Forex Awards 2009.
"The One to Watch" - Treasury Today's Adam Smith Awards 2009.
----- Original Message -----
From: "mdefoinplatel ext" <[EMAIL PROTECTED]>
Sent: Tuesday, March 20, 2012 10:19:41 AM
Subject: Optimization on bucketized/sorted tables
Hi folks,

I have several questions about optimization in Hive, they are mainly related to bucketized/sorted tables.

Let say I have a table T bucketized on user_id and sorted by user_id, time.


( user_id BIGINT,

time INT


CLUSTERED BY(user_id) SORTED BY(user_id, time) INTO 64 BUCKETS;

In a general way, I wonder which of the following operations will benefit from the fact that T is bucketized and sorted.

1) Group by

SELECT user_id, count(time) FROM T GROUP BY user_id;

2) Distribute by

SELECT user_id, time FROM T DISTRIBUTE BY user_id;

3) Distribute by, Sort by

SELECT user_id, time FROM T DISTRIBUTE BY user_id SORT BY user_id, time;

4) Insert into a bucketized/sorted table


( user_id BIGINT,

time INT


CLUSTERED BY(user_id) SORTED BY(user_id, time) INTO 64 BUCKETS;

set hive.enforce.bucketing = true;


Finally, on a slightly more specific topic…

Let say I want to perform the ‘sessionization’ on the table T and I am planning to call a python script to do that job.

To get a valid answer I must ensure that the data are sorted by user_id,time and that all the data for a given user_id are processed by a single call to my script.

I am planning to run the following query:

FROM (SELECT user_Id, time FROM T DISTRIBUTE BY user_id SORT BY user_id, time) s SELECT TRANSFORM (s.user_id, s.time) USING 'python session.py' AS user_id, avg_session, nb_session;

So I wonder first if this is the correct approach and second if the �� DISTRIBUTE BY user_id SORT BY user_id, time ’ clauses are required knowing that T is already bucketized and sorted on the right columns.

Many thanks in advance for your help,



Ce message et ses pieces jointes peuvent contenir des informations confidentielles ou privilegiees et ne doivent donc
pas etre diffuses, exploites ou copies sans autorisation. Si vous avez recu ce message par erreur, veuillez le signaler
a l'expediteur et le detruire ainsi que les pieces jointes. Les messages electroniques etant susceptibles d'alteration,
France Telecom - Orange decline toute responsabilite si ce message a ete altere, deforme ou falsifie. Merci.

This message and its attachments may contain confidential or privileged information that may be protected by law;
they should not be distributed, used or copied without authorisation.
If you have received this email in error, please notify the sender and delete this message and its attachments.
As emails may be altered, France Telecom - Orange is not liable for messages that have been modified, changed or falsified.
Thank you.