How can we decide the total no. of buckets for a hive table

Question

i am bit new to hadoop. As per my knowledge buckets are fixed no. of partitions in hive table and hive uses the no. of reducers same as the total no. of buckets defined while creating the table. So can anyone tell me how to calculate the total no. of buckets in a hive table. Is there any formula for calculating the total number of buckets ?

Satirist

Got a formula #buckets = (x * Average_partition_size) / JVM_memory_available_to_your_Hadoop_tasknode ; where x (>1) the "factor of conservatism". But not clear about it. Need a clear formula on this – Satirist

Answer 1

Lets take a scenario Where table size is: 2300 MB, HDFS Block Size: 128 MB

Now, Divide 2300/128=17.96

Now, remember number of bucket will always be in the power of 2.

So we need to find n such that 2^n > 17.96

n=5

So, I am going to use number of buckets as 2^5=32

Hope, It will help some of you.

Highspeed

Answer 2

From the documentation link

In general, the bucket number is determined by the expression hash_function(bucketing_column) mod num_buckets. (There's a '0x7FFFFFFF in there too, but that's not that important). The hash_function depends on the type of the bucketing column. For an int, it's easy, hash_int(i) == i. For example, if user_id were an int, and there were 10 buckets, we would expect all user_id's that end in 0 to be in bucket 1, all user_id's that end in a 1 to be in bucket 2, etc. For other datatypes, it's a little tricky. In particular, the hash of a BIGINT is not the same as the BIGINT. And the hash of a string or a complex datatype will be some number that's derived from the value, but not anything humanly-recognizable. For example, if user_id were a STRING, then the user_id's in bucket 1 would probably not end in 0. In general, distributing rows based on the hash will give you a even distribution in the buckets.

Teeming

Thanks. But my question is how can we decide the total no. of buckets for a hive table. – Satirist

Thanks. But my question is how can we decide the total no. of buckets for a hive table. For ex- CREATE EXTERNAL TABLE SALES_EXT_BUCKET (store_id STRING, order_no STRING, Order_Date STRING) CLUSTERED BY (order_no) INTO 4 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' stored as textfile; So here how can we decide to have total no. of buckets as 4. I know total no. of buckets are always the power of 2 . Is there any formula for that ? if yes, then appreciate any inputs – Satirist

I think you have seen this, to get that above formulae. IN short, it actually depends on your use case and how you want to query at later stage. this has a practical example which might help you. Happy learning – Teeming

Answer 3

If you want to know how many buckets you should choose in your CLUSTER BY clause, I believe it is good to choose a number that results in buckets that are at or just below your HDFS block size.

This should help avoid having HDFS allocate memory to files that are mostly empty.

Also choose a number that is a power of two.

You can check your HDFS block size with:

hdfs getconf -confKey dfs.blocksize

Snead

Can you please elaborate your answer? Let's suppose we have 1TB of data, then how much buckets we can assign? Please explain me. – Shrike

@Shrike unfortunately it depends. You could start by checking your block size see here. From there you could naively try num_buckets = 1TB/block-size. There may be advantages to further considering the memory requirements of each datum (e.g. in case they are larger than the block size?), not totally sure. – Snead

One more question, How to choose the bucketing column? What if our columns cardinality value is 2? If it's not the right one to select then what should be the perfect cardinality value should we consider? – Shrike

@Shrike it comes down to a hash function. Read more here: How does Hive distribute the rows across the buckets? In general, the bucket number is determined by the expression hash_function(bucketing_column) mod num_buckets. ... – Snead

Answer 4

optimal bucket number is ( B * HashTableSize of Table ) / Total Memory of Node, B=1.01

Concurrent

Answer 5

size of data/block size =answer 2^n compare with answer. closest N will br no. of buckets

Auditory