Prerequisite: create stocks table as describe in the first portion of this article.
Create table by with partition by year and bucket on symbol column.
hive> CREATE EXTERNAL TABLE `stocks_bucket`(
`tr_date` string,
`open` double,
`high` double,
`low` double,
`close` double,
`volume` double,
`adjclose` double,
`symbol` string)
PARTITIONED BY(year int)
CLUSTERED BY (`symbol`) INTO 4 BUCKETS;
Before loading data into bucketed table, set the right number of reducers or set the hive configuration that will determine the number reducers by its own.
hive> set map.reduce.tasks = 4; -- This number should be equal to the number of buckets
set hive.exec.dynamic.partition=true; -- Required if you want to create dynamic partitioning using insert into statement
set hive.enforce.bucketing = true; -- This is recommended
Populate the stocks_bucket table with the data from stocks table
INSERT INTO `stocks_bucket`
PARTITION (year)
SELECT
`tr_date`,
`open`,
`high`,
`low`,
`close`,
`volume`,
`adjclose`,
`symbol`,
year(tr_date) FROM stocks
View the directory of stocks_bucket table.
$ hadoop fs -ls /user/hive/warehouse/stocks_bucket
Found 19 items
drwxrwxrwt - cloudera cloudera 1 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/.hive-staging_hive_2018-08-28_03-27-52_161_558394225296515089-1
drwx------ - cloudera cloudera 1 2018-08-28 03:27 /user/hive/warehouse/stocks_bucket/_scratchdir_hive_2018-08-28_03-27-52_161_558394225296515089-14
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2000
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2001
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2002
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2003
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2004
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2005
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2006
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2007
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2008
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2009
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2010
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2011
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2012
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2013
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2014
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2015
drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2016
View the directory for a given partition. Since we created 4 buckets based on the stocks symbol, stocks symbols will be exclusively distributed among 4 files.
$ hadoop fs -ls /user/hive/warehouse/stocks_bucket/year=2000
Found 4 items
-rwxrwxrwt 1 cloudera cloudera 812167 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2000/000000_0
-rwxrwxrwt 1 cloudera cloudera 652720 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2000/000001_0
-rwxrwxrwt 1 cloudera cloudera 851500 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2000/000002_0
-rwxrwxrwt 1 cloudera cloudera 735537 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2000/000003_0
$ hadoop fs -cat /user/hive/warehouse/stocks_bucket/year=2000/000000_0 | head
2000-08-2213.37513.87513.37513.5222700.013.006862PXD
2000-08-2225.75001926.62525.43748126.6252544000.03.278705CSX
2000-11-2941.06248141.06248140.2540.9375191.23904E78.546621LOW
2000-08-2221.25000121.31250420.25000620.6874946013900.09.815813USB
2000-11-2966.812567.12563.2565.06259.82804E722.376616MSFT
2000-11-2966.12496267.43752366.12496267.1249627696800.07.741655AET
2000-11-2950.551.049.87550.0795400.019.656488TXT
2000-11-29134.5139.375133.25138.25775200.034.5625LH
2000-11-2914.37514.514.2514.4375963800.013.910117PXD
2000-11-2926.31251926.87501926.31251926.5624813726600.03.347279CSX
Since we have not specified our own field delimiter, hive used its default.
Each bucket should have approximately 512 MB of data. Try to choose the number of bucket ax 2 ^ n that 4, 8, 16 etc.
By using buckets, HQL query can easily and efficiently do data aggregation, sampling), bucket-side joins, and map-side joins (also known as hash join) data correlation and scope.
hive> select tr_date, symbol, adjclose from stocks_bucket TABLESAMPLE(BUCKET 1 OUT OF 4 ON symbol)
where year = 2016 limit 3;
+-------------+---------+-------------+--+
| tr_date | symbol | adjclose |
+-------------+---------+-------------+--+
| 2016-04-11 | NFX | 34.450001 |
| 2016-03-23 | ADS | 209.800003 |
| 2016-07-15 | HUM | 158.889999 |
+-------------+---------+-------------+--+
Choose the column that is often used for joining with other tables, for example customer id, employee id, product id etc.
Reference: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL+BucketedTables