Hive Bucket clarification

[#1]

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?

[#2 · Accepted solution]

,

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.

[#3]

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?

[#4]

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.

[#5]

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?

[#6]

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

[#7]

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

[#8]

You can do a table sample.

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