question Re: Hive Bucket clarification in Archives of Support Questions (Read Only)

Hive Bucket clarification

vamsi123 — Wed, 27 Apr 2016 19:01:06 GMT

CREATETABLEbucketed_user( firstnameVARCHAR(64), lastnameVARCHAR(64), addressSTRING, cityVARCHAR(64), stateVARCHAR(64), postSTRING, phone1VARCHAR(64), phone2STRING, emailSTRING, webSTRING ) COMMENT'A bucketed sorted user table' PARTITIONEDBY(countryVARCHAR(64)) CLUSTEREDBY(state)SORTEDBY(city)INTO 32BUCKETS STORED ASSEQUENCEFILE;

could anybody tell what is the purpouse of CLUSTEREDBY(state)SORTEDBY(city) in bucket table creation?

Re: Hive Bucket clarification

tmccuch — Wed, 27 Apr 2016 19:58:01 GMT

The CLUSTERED BY clause is used to divide the table into buckets. Rows with the same bucketed column will always be stored in the same bucket. In this case, even though there are 50 possible states, the rows in this table will be clustered into 32 buckets. The SORTED BY clause keeps the rows in each bucket ordered by one or more columns. This does not enforce global ordering across buckets for the whole table, only local ordering within each bucket, as each bucket is physically a separate file in the table directory.

If the tables being joined are bucketized on the join columns, and the number of buckets in one table is a multiple of the number of buckets in the other table, the buckets can be joined on the map-side. Map-side joins on bucketed tables will be faster - as the mapper processing a bucket of the left table knows that the matching rows in the right table will be in their corresponding bucket, so it doesn't need to scan the whole table. Map-side joins on tables with sorted by buckets are even more efficient, as the join boils down to just merging the already sorted buckets.

A more detailed explanation, along with MapJoin restrictions, can be found here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins.

Re: Hive Bucket clarification

vamsi123 — Wed, 27 Apr 2016 21:11:47 GMT

Let us say i have 4 countries.each country has 10 states.Totally 32 buckets will be created and 32 files will be created on HDFS.But i have confusion with partition how many folders will be created where 32 files will be created .is it within each partition?

Re: Hive Bucket clarification

tmccuch — Wed, 27 Apr 2016 22:03:23 GMT

In Hive, each partition is physically a separate subdirectory under the table directory. Buckets would then be physically represented as separate files within those subdirectories. Using your example above where you have 4 countries and 32 buckets, this would result in 4 subdirectories under the table directory, each containing 32 files.

Re: Hive Bucket clarification

vamsi123 — Thu, 28 Apr 2016 11:21:54 GMT

I tried example in the link:http://hadooptutorial.info/bucketing-in-hive/

I created four folders

country=AU	dir	2016-04-28 00:03	rwxr-xr-x	naresh	supergroup
country=CA	dir	2016-04-28 00:03	rwxr-xr-x	naresh	supergroup
country=UK	dir	2016-04-28 00:03	rwxr-xr-x	naresh	supergroup
country=US	dir	2016-04-28 00:03	rwxr-xr-x	naresh	supergroup
country=country	dir	2016-04-28 00:03	rwxr-xr-x	naresh	supergroup

each folder contains 32 files.

clarifications:

1)How to select Bucket1 files in folder country=AU?

2)How to select Bucket1 files in folder country=country? and also why this folder is created it is partitioned by country so four folders should be created and what is this fifth folder?

Re: Hive Bucket clarification

bpreachuk — Thu, 28 Apr 2016 20:53:11 GMT

hi @Tom McCuch and @vamsi valiveti. Just wanted to clarify - it is legal to have two bucketed tables where the number of buckets in one table is a multiple of the number of buckets in the other table, but for pragmatic performance reasons it is best to have the number of buckets be the same. IMHO If you are going to bucket your data, you are doing it because you need a more efficient join - and having a non-matching number of buckets removes that ability to do a sort-merge bucket join.

See this post on bucket join versus sort-merge bucket join. it's very good. http://stackoverflow.com/questions/20199077/hive-efficient-join-of-two-tables

Re: Hive Bucket clarification

vamsi123 — Tue, 10 May 2016 16:19:30 GMT

1)How to select Bucket1 files in folder country=AU?could anybody provide sql query for that?

Re: Hive Bucket clarification

efusaro — Wed, 06 Jul 2016 05:14:27 GMT

You can do a table sample.

Select * from bucketed_user tablesample(bucket 1 out of 2 on state) where country = AU;