Created 04-27-2016 12:01 PM
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?
Created 04-27-2016 12:58 PM
Want to get a detailed solution you have to login/registered on the community
Register/LoginCreated 04-27-2016 12:58 PM
Want to get a detailed solution you have to login/registered on the community
Register/LoginCreated 04-27-2016 02:11 PM
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?
Created 04-27-2016 03:03 PM
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.
Created 04-28-2016 04:21 AM
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?
Created 04-28-2016 01:53 PM
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
Created 05-10-2016 09:19 AM
1)How to select Bucket1 files in folder country=AU?could anybody provide sql query for that?
Created 07-05-2016 10:14 PM
You can do a table sample.
Select * from bucketed_user tablesample(bucket 1 out of 2 on state) where country = AU;